EntityJustWorks - C# class/object/entity to SQL database/script/command mapper.

Note by author:

   Since writing this, I have expanded on this idea quite a bit. I have written a lightweight ORM class library that I call EntityJustWorks.
   The full project can be found on GitHub or CodePlex.


   EntityJustWorks not only goes from a class to DataTable (below), but also provides:

• Populate the public properties of a class from a DataTable

• DataTable to class as a C# code file (using CodeDOM)

• DataTable to class Assembly (using Reflection.Emit)

• C# class to a SQL 'CREATE TABLE' or 'INSERT INTO' script

• SQL 'SELECT' statement to a List<T> of populated classes, each one resembling a row

Security Warning:
This library generates dynamic SQL, and has functions that generate SQL and then immediately executes it. While it its true that all strings funnel through the function Helper.EscapeSingleQuotes, this can be defeated in various ways and only parameterized SQL should be considered SAFE. If you have no need for them, I recommend stripping semicolons ; and dashes --. Also there are some Unicode characters that can be interpreted as a single quote or may be converted to one when changing encodings. Additionally, there are Unicode characters that can crash .NET code, but mainly controls (think TextBox). You almost certainly should impose a white list:
string clean = new string(dirty.Where(c => "abcdefghijklmnopqrstuvwxyz0123456789.,\"_ !@".Contains(c)).ToArray());

PLEASE USE the SQLScript.StoredProcedure and DatabaseQuery.StoredProcedure classes to generate SQL for you, as the scripts it produces is parameterized. All of the functions can be altered to generate parameterized instead of sanitized scripts. Ever since people have started using this, I have been maintaining backwards compatibility. However, I may break this in the future, as I do not wish to teach one who is learning dangerous/bad habits. This project is a few years old, and its already showing its age. What is probably needed here is a total re-write, deprecating this version while keep it available for legacy users after slapping big warnings all over the place. This project was designed to generate the SQL scripts for standing up a database for a project, using only MY input as data. This project was never designed to process a USER'S input.! Even if the data isn't coming from an adversary, client/user/manually entered data is notoriously inconsistent. Please do not use this code on any input that did not come from you, without first implementing parameterization. Again, please see the SQLScript.StoredProcedure class for inspiration on how to do that.

    In this post, I bring the wagons 'round full circle and use what I have shown you in older posts to make a class that can create (or insert into) a table in an SQL database from a C# class object at run-time. The only prior knowledge needed would be the connection string. The name of the the table will be the name of the class Type, and the tables's columns are created from the class's public properties, with their names being the property names.

    If you want to take a data-first approach, you can generate the code to C# classes that mirrors a table in a relational database given a connection string and the table name, or use the new Emit class and generate a dynamic assembly at run-time that contains a class with public auto-properties matching the DataColumns that you can use to store DataRows in. Similarly, the name of class name will be the same name as the table, with each of the table's columns being represented by a public auto property on the class of the same name and type. An improvement here would be to create the class inside a dynamic assembly using System.Reflection.Emit. Anywho, I have been holding on to this code for too long I just want to get it out of the door.

    For those of you who just want the code:
      GitHub  (Download zip)
      BitBucket (Download)
      code.msdn.microsoft.com

    Going from a query to a DataTable is as simple as a call to SQLAdapter.Fill(). For populating the public properties of a class from a DataTable, see this post I made. To build a DataTable who's columns match the public properties of a class, check out this post here. Going from a DataTable to a class as a C# code file is covered in this previous post.

    Going from a DataTable to SQL CREATE TABLE or INSERT INTO scripts that can be passed to ExecuteNonQuery() to execute the command, see below.

    Here is the INSERT INTO code. This code takes advantage of some functions in the Helper class that converts a DataTable's ColumnNames and Row's Values as a List of String, which is then passed into a function that takes a list of string and formats it with a delimiter string and prefix and postfix strings so I can easily format the list to look like the COLUMNS and VALUES part of an SQL INSERT INTO statement. For example: INSERT INTO ([FullName], [Gender], [BirthDate]) VALUES ('John Doe', 'M', '10/3/1981''). If you want to view how the functions RowToColumnString(), RowToValueString(), and ListToDelimitedString() work, click here to view Helper.cs.

public static string InsertInto<T>(params T[] ClassObjects) where T : class
{
   DataTable table = Map.ClassToDatatable<T>(ClassObjects);
   return InsertInto(table);   // We don't need to check IsValidDatatable() because InsertInto does
}

public static string InsertInto(DataTable Table)
{
   if (!Helper.IsValidDatatable(Table))
      return string.Empty;

   StringBuilder result = new StringBuilder();
   foreach (DataRow row in Table.Rows)
   {
      if (row == null || row.Table.Columns.Count < 1 || row.ItemArray.Length < 1)
         return string.Empty;

      string columns = Helper.RowToColumnString(row);
      string values = Helper.RowToValueString(row);

      if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(values))
         return string.Empty;

      result.AppendFormat("INSERT INTO [{0}] {1} VALUES {2}", Table.TableName, columns, values);
   }

   return result.ToString();
}

    Here is the CREATE TABLE CODE. A very important function here is the GetDataTypeString() member, which converts the Column.DataType to the SQL equivalent as a string, which is used to craft a CREATE TABLE script. I use static strings formatted to work with String.Format to replace the crucial bits in the CREATE TABLE command:

public static string CreateTable<T>(params T[] ClassObjects) where T : class
{
 DataTable table = Map.ClassToDatatable<T>(ClassObjects);
 return Script.CreateTable(table);
}

public static string CreateTable(DataTable Table)
{
   if (Helper.IsValidDatatable(Table, IgnoreRows: true))
      return string.Empty;

   StringBuilder result = new StringBuilder();
   result.AppendFormat("CREATE TABLE [{0}] ({1}", Table.TableName, Environment.NewLine);

   bool FirstTime = true;
   foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
   {
      if (FirstTime) FirstTime = false;
      else
         result.Append(",");

      result.AppendFormat("[{0}] {1} {2}NULL{3}",
         column.ColumnName,
         GetDataTypeString(column.DataType),
         column.AllowDBNull ? "" : "NOT ",
         Environment.NewLine
      );
   }
   result.AppendFormat(") ON [PRIMARY]{0}GO", Environment.NewLine);

   return result.ToString();
}

private static string GetDataTypeString(Type DataType)
{
   switch (DataType.Name)
   {
      case "Boolean": return "[bit]";
      case "Char": return "[char]";
      case "SByte": return "[tinyint]";
      case "Int16": return "[smallint]";
      case "Int32": return "[int]";
      case "Int64": return "[bigint]";
      case "Byte": return "[tinyint] UNSIGNED";
      case "UInt16": return "[smallint] UNSIGNED";
      case "UInt32": return "[int] UNSIGNED";
      case "UInt64": return "[bigint] UNSIGNED";
      case "Single": return "[float]";
      case "Double": return "[double]";
      case "Decimal": return "[decimal]";
      case "DateTime": return "[datetime]";
      case "Guid": return "[uniqueidentifier]";
      case "Object": return "[variant]";
      case "String": return "[nvarchar](250)";
      default: return "[nvarchar](MAX)";
   }
}

...


    Here is definition of all the public members:

namespace EntityJustWorks.SQL
{
   public static class Convert
   {
      public static string SQLTableToCSharp(string ConnectionString, string TableName);
      public static bool ClassToSQLTable(string ConnectionString, params T[] ClassCollection);
   }
   
   public static class Code
   {
      public static string SQLTableToCSharp(string ConnectionString, string TableName);
      public static string DatatableToCSharp(DataTable Table);
   }

   public static class Script
   {
      public static string InsertInto<T>(params T[] ClassObjects);
      public static string InsertInto(DataTable Table);
      public static string CreateTable<T>(params T[] ClassObjects);
      public static string CreateTable(DataTable Table);
   }
   
   public static class Map
   {
      public static IList<T> DatatableToClass<T>(DataTable Table);
      public static DataTable ClassToDatatable<T>(params T[] ClassCollection);
      public static DataTable ClassToDatatable<T>();
   }

   public static class Query
   {
      public static IList<T> QueryToClass<T>(string ConnectionString, string FormatString_Query,
                                                            params object[] FormatString_Parameters);
      public static DataTable QueryToDataTable(string ConnectionString, string FormatString_Query,
                                                            params object[] FormatString_Parameters);
      public static T QueryToScalarType<T>(string ConnectionString, string FormatString_Query,
                                                            params object[] FormatString_Parameters);
      public static int ExecuteNonQuery(string ConnectionString, string FormatString_Command,
                                                            params object[] FormatString_Parameters);
   }
   
   public static class Helper
   {
      public static bool IsValidDatatable(DataTable Table, bool IgnoreRows = false);
      public static bool IsCollectionEmpty<T>(IEnumerable<T> Input);
      public static bool IsNullableType(Type Input);
   }
}

    Please feel free to comment, either on my GitHub or my blog, with criticisms or suggestions.

Create C# Class Code From a DataTable using CodeDOM

Note by author:

   Since writing this, I have expanded on this idea quite a bit. I have written a lightweight ORM class library that I call EntityJustWorks.
   The full project can be found on GitHub or CodePlex.


   EntityJustWorks not only goes from a class to DataTable (below), but also provides:

• Populate the public properties of a class from a DataTable

• DataTable to class as a C# code file (using CodeDOM)

• DataTable to class Assembly (using Reflection.Emit)

• C# class to a SQL 'CREATE TABLE' or 'INSERT INTO' script

• SQL 'SELECT' statement to a List<T> of populated classes, each one resembling a row

Security Warning:

This library generates dynamic SQL, and has functions that generate SQL and then immediately executes it. While it its true that all strings funnel through the function Helper.EscapeSingleQuotes, this can be defeated in various ways and only parameterized SQL should be considered SAFE. If you have no need for them, I recommend stripping semicolons ; and dashes --. Also there are some Unicode characters that can be interpreted as a single quote or may be converted to one when changing encodings. Additionally, there are Unicode characters that can crash .NET code, but mainly controls (think TextBox). You almost certainly should impose a white list: string clean = new string(dirty.Where(c => "abcdefghijklmnopqrstuvwxyz0123456789.,\"_ !@".Contains(c)).ToArray()); 

PLEASE USE the SQLScript.StoredProcedure and DatabaseQuery.StoredProcedure classes to generate SQL for you, as the scripts it produces is parameterized. All of the functions can be altered to generate parameterized instead of sanitized scripts. Ever since people have started using this, I have been maintaining backwards compatibility. However, I may break this in the future, as I do not wish to teach one who is learning dangerous/bad habits. This project is a few years old, and its already showing its age. What is probably needed here is a total re-write, deprecating this version while keep it available for legacy users after slapping big warnings all over the place. This project was designed to generate the SQL scripts for standing up a database for a project, using only MY input as data. This project was never designed to process a USER'S input.! Even if the data isn't coming from an adversary, client/user/manually entered data is notoriously inconsistent. Please do not use this code on any input that did not come from you, without first implementing parameterization. Again, please see the SQLScript.StoredProcedure class for inspiration on how to do that.

So far I have posted several times on the DataTable class. I have shown how to convert a DataTable to CSV or tab-delimited file using the clipboard, how to create a DataTable from a class using reflection, as well as how to populate the public properties of a class from a DataTable using reflection. Continuing along these lines, I decided to bring the DataTable-To-Class wagons around full-circle and introduce a class that will generate the C# code for the class that is used by the DataTableToClass<T> function, so you don't have to create it manually. The only parameter required to generate the C# class code is, of course, a DataTable.

The code below is rather trivial. It uses CodeDOM to build up a class with public properties that match the names and data types of the data columns of the supplied DataTable. I really wanted the output code to use auto properties. This is not supported by CodeDOM, however, so I used a little hack or workaround to accomplish the same thing. I simply added the getter and setter code for the property to the member's field name. CodeDOM adds a semicolon to the end of the CodeMemberField statement, which would cause the code not to compile, so I added two trailing slashes "//" to the end of the field name to comment out the semicolon. The whole point of creating auto properties was to have clean, succinct code, so after I generate the source code file, I clean up the commented-out semicolons by replacing every occurrence with an empty string. The main disadvantage of this 'workaround' is that the code cannot be used to generate a working class in Visual Basic code. I do have proper CodeDOM code that does not employ this workaround, but I prefer the output code to contain auto-properties; auto-generated code is notorious for being messy and hard to read, and I did not want my generated code to feel like generated code.


Below is the DataTableToCode function, its containing class and its supporting functions. The code is short, encapsulated, clean and commented, so I will just let it speak for itself:

public static class DataTableExtensions
{
   public static string DataTableToCode(DataTable Table)
   {
      string className = Table.TableName;
      if(string.IsNullOrWhiteSpace(className))
      {   // Default name
         className = "Unnamed";
      }
      className += "TableAsClass";
      
      // Create the class
      CodeTypeDeclaration codeClass = CreateClass(className);
      
      // Add public properties
      foreach(DataColumn column in Table.Columns)
      {
         codeClass.Members.Add( CreateProperty(column.ColumnName, column.DataType) );
      }
      
      // Add Class to Namespace
      string namespaceName = "AutoGeneratedDomainModels";
      CodeNamespace codeNamespace = new CodeNamespace(namespaceName);
      codeNamespace.Types.Add(codeClass);
      
      // Generate code
      string filename = string.Format("{0}.{1}.cs",namespaceName,className);
      CreateCodeFile(filename, codeNamespace);
      
      // Return filename
      return filename;
   }
   
   static CodeTypeDeclaration CreateClass(string name)
   {
      CodeTypeDeclaration result = new CodeTypeDeclaration(name);
      result.Attributes = MemberAttributes.Public;
      result.Members.Add(CreateConstructor(name)); // Add class constructor
      return result;
   }
   
   static CodeConstructor CreateConstructor(string className)
   {
      CodeConstructor result = new CodeConstructor();
      result.Attributes = MemberAttributes.Public;
      result.Name = className;
      return result;
   }
   
   static CodeMemberField CreateProperty(string name, Type type)
   {
      // This is a little hack. Since you cant create auto properties in CodeDOM,
      //  we make the getter and setter part of the member name.
      // This leaves behind a trailing semicolon that we comment out.
      //  Later, we remove the commented out semicolons.
      string memberName = name + "\t{ get; set; }//";
      
      CodeMemberField result = new CodeMemberField(type,memberName);
      result.Attributes = MemberAttributes.Public | MemberAttributes.Final;
      return result;
   }
   
   static void CreateCodeFile(string filename, CodeNamespace codeNamespace)
   {
      // CodeGeneratorOptions so the output is clean and easy to read
      CodeGeneratorOptions codeOptions = new CodeGeneratorOptions();
      codeOptions.BlankLinesBetweenMembers = false;
      codeOptions.VerbatimOrder = true;
      codeOptions.BracingStyle = "C";
      codeOptions.IndentString = "\t";
      
      // Create the code file
      using(TextWriter textWriter = new StreamWriter(filename))
      {
         CSharpCodeProvider codeProvider = new CSharpCodeProvider();
         codeProvider.GenerateCodeFromNamespace(codeNamespace, textWriter, codeOptions);
      }
      
      // Correct our little auto-property 'hack'
      File.WriteAllText(filename, File.ReadAllText(filename).Replace("//;", ""));
   }
}

An example of the resulting code appears below:

namespace AutoGeneratedDomainModels
{
   public class CustomerTableAsClass
   {
      public CustomerTableAsClass()
      {
      }
      public string FirstName   { get; set; }
      public string LastName    { get; set; }
      public int  Age           { get; set; }
      public char Sex           { get; set; }
      public string Address     { get; set; }
      public string Birthdate   { get; set; }
   }
}

I am satisfied with the results and look of the code. The DataTableToCode() function can be a huge time saver if you have a large number of tables you need to write classes for, or if each DataTable contains a large number of columns.

If you found any of this helpful, or have any comments or suggestions, please feel free to post a comment.