Creating an ORM in C# - Part 3

Today is another post concerning the HaterAide ORM that I've been working on lately. The last couple of posts have dealt with the reflection and class definition portions of the code. Today I'm going to start posting about the SQL portion of the ORM. Specifically I'm going to discuss how I've gone about creating a database and tables from the information that the class definitions give us.

The first item that I need to deal with is the fact that this implementation needs to be extensible. Different databases have slightly different implementations/key words. So I decided to go with a provider model. The code is rather straightforward and basic. As such I'm just going to have the code in the download at the bottom of the post, but the classes that are of interest are the ProviderCollection, ProviderConfigSection, and ProviderManager (and also the IProvider class). You'll also note that in the download, a lot of the classes from Part 1 and Part 2 of this series have been slightly modified.

There provider model allows me to plug in classes for each database that I want to deal with. At present though, all I care about is SQL Server.  Since that's the case I've also added an SQL Server provider (and a number of helper classes that are in various states of completion). Anyway, the provider manager starts up, creates any and all providers, and uses the one that we set as the default in the Web.Config file (once again, I'm only developing this for web apps so slight modifications would be needed to make this a bit more general purpose). It then calls the create function on the provider which creates the database.

If you look at the Create function in the Provider class:

 internal override void Create()
 {
     string Database = Regex.Match(ConnectionString, "Initial Catalog=(.\*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "");
     string TempConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.\*?;)", "");
     SQLBuilder.CreateDatabase(TempConnectionString, Database, false);
     foreach (Type Key in ClassManager.Keys)
     {
         SQLBuilder.CreateTable(ConnectionString, ClassManager\[Key\], ClassManager, false);
     }
 }

You will see that the only things it does, is parses the connection string finding the name of the database that we're connecting to and calling two functions. One function that is called is the CreateDatabase function and the other being CreateTable.
The CreateDatabase function can be seen below:

 public static void CreateDatabase(string ConnectionString, string DatabaseName, bool RecreateIfExists)
 {
     bool Exists = false;
     Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("SELECT \* FROM Master.sys.Databases where name=@DatabaseID", ConnectionString, CommandType.Text);
     if (!RecreateIfExists)
     {
         try
         {
             Helper.Open();
             Helper.AddParameter("@DatabaseID", DatabaseName, 100);
             Helper.ExecuteReader();
             if (Helper.Read())
             {
                 Exists = true;
             }
         }
         catch { }
         finally { Helper.Close(); }
     }
     if (!Exists)
     {
         Helper.Command = "CREATE DATABASE " + DatabaseName.Replace("'", "");
         try
         {
             Helper.Open();
             Helper.ExecuteNonQuery();
         }
         catch { }
         finally { Helper.Close(); }
     }
 }

You'll notice that it uses the SQLHelper class from my utility library, but it's just to simplify connecting to the database. Anyway, the function simply does a call to the Master database asking what databases exist (and specifically if the database we want exists). If it doesn't exist, it creates the database. If it does exist, it skips that step. The CreateTable function is where the interesting parts occur:

 public static void CreateTable(string ConnectionString, Class Class, ClassManager Manager, bool RecreateIfExists)
 {
     bool Exists = false;
     string ClassName = Class.OriginalType.Name;
     Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select \* from sys.Tables where name=@TableID", ConnectionString, CommandType.Text);
     if (!RecreateIfExists)
     {
         try
         {
             Helper.Open();
             Helper.AddParameter("@TableID", ClassName, 200);
             Helper.ExecuteReader();
             if (Helper.Read())
             {
                 Exists = true;
             }
         }
         catch { }
         finally { Helper.Close(); }
     }
     if (!Exists)
     {
         CreateTable Table = new CreateTable(Class, Manager);
         Helper.Command = Table.ToString();
         try
         {
             Helper.Open();
             Helper.ExecuteNonQuery();
         }
         catch { }
         finally { Helper.Close(); }
     }
 }

The beginning portion of the function is very similar to the CreateDatabase function, simply asking if the table exists. After that it simply creates the table. However it does this by creating a CreateTable object:

 internal class CreateTable:IStatement
 {
     public CreateTable(Class Class,ClassManager Manager)
     {
         \_Class = Class;
         foreach (Property TempProperty in Class.Properties)
         {
             if (TempProperty.Attribute.AttributeType == AttributeType.ID || TempProperty.Attribute.AttributeType == AttributeType.Reference)
             {
                 Columns.Add(GlobalFunctions.GetSQLType(TempProperty.Attribute, Class, Manager));
             }
         }
     } 
  
     private List<IDataType> Columns = new List<IDataType>();
     private Class \_Class = null; 
  
     public override string ToString()
     {
         StringBuilder Builder = new StringBuilder("CREATE TABLE " + \_Class.OriginalType.Name + "(");
         StringBuilder ListBuilder = new StringBuilder();
         string Splitter = "";
         foreach (IDataType Column in Columns)
         {
             if (Column != null&&!Column.Type.Equals("List",StringComparison.CurrentCultureIgnoreCase))
             {
                 Builder.Append(Splitter + Column.ToString());
                 Splitter = ",";
             }
             else if (Column != null && Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
             {
                 ListBuilder.Append(" " + Column.ToString());
             }
         }
         Builder.Append(")"); 
  
         return Builder.ToString() + ListBuilder.ToString();
     }
 }

The CreateTable object is one of the many helper classes (in this case a statement class) that the system uses to actually form the SQL functions that it queries against the database. In this case the CreateTable constructor goes through each property within the class and (assuming it's an ID or Reference type since we're not dealing with classes yet) creates an IDataType class that it puts into a list. The IDataType class can be seen below:

 internal class IDataType
 {
     public IDataType()
     {
     } 
  
     public IDataType(Attribute Attribute)
     {
         Name = Attribute.Name;
         if (Attribute.AttributeType == AttributeType.ID)
             Constraints.Add(new PrimaryKey());
     } 
  
     public string Name { get; set; }
     public List<IConstraint> Constraints = new List<IConstraint>(); 
  
     public string Type { get; set; } 
  
     public override string ToString()
     {
         StringBuilder Builder = new StringBuilder(Name + " " + Type);
         foreach (IConstraint Constraint in Constraints)
         {
             Builder.Append(Constraint.ToString());
         }
         return Builder.ToString();
     }
 }

The IDataType classes are created by a global function which uses the type of the property to determine which one to create. For instance a double would create a Float class, a Listwould throw back a List class, and a bool would pass back a Bit class. These individual classes then look at the attributes of the property and determine what if any sort of constraints to place on the object. For instance, if this is our ID, it creates a PrimaryKey class and adds it to a constraints list and each different type may have its own individual constraints that it needs (for instance the NVarChar class uses the Size constraint). All of these are straightforward and rather uniform with one exception, the List class:

 internal class Int : IDataType
 {
     public Int(Attribute Attribute)
         : base(Attribute)
     {
         Type = "Int";
         if (Attribute.AutoIncrement)
             Constraints.Add(new AutoIncrement());
     }
 } 
  
 internal class List:IDataType
 {
     public List(Attribute Attribute, Class Class, ClassManager Manager)
         : base(Attribute)
     {
         \_Class = Class;
         Type = "List";
         Name = Class.OriginalType.Name + "\_" + Attribute.Name;
         Type ObjectType = Attribute.Type.GetGenericArguments()\[0\];
         Attribute TempAttribute=new Attribute();
         TempAttribute.AttributeType=AttributeType.Reference;
         TempAttribute.Length=Attribute.Length;
         TempAttribute.Name=Attribute.Name;
         TempAttribute.Type=ObjectType;
         IDataType DataType = GlobalFunctions.GetSQLType(TempAttribute, Class, Manager);
         if (DataType != null)
         {
             Columns.Add(DataType);
         }
         else
         {
             //Find matching class and ID
         }
         foreach (Property Property in Class.Properties)
         {
             if (Property.Attribute.AttributeType == AttributeType.ID)
             {
                 IDataType IDType = GlobalFunctions.GetSQLType(Property.Attribute, Class, Manager);
                 IDName = IDType.Name;
                 IDType.Constraints.Clear();
                 if (IDType is NVarChar)
                 {
                     IDType.Constraints.Add(new Size(Property.Attribute.Length));
                 }
                 Columns.Add(IDType);
             }
         }
     } 
  
     private string IDName = "";
     private List<IDataType> Columns = new List<IDataType>();
     private Class \_Class; 
  
     public override string ToString()
     {
         StringBuilder Builder = new StringBuilder("CREATE TABLE " + Name + "("); 
  
         string Splitter = "";
         string PrimaryKey="";
         foreach (IDataType Column in Columns)
         {
             if (Column != null)
             {
                 PrimaryKey+=Splitter+Column.Name;
                 Builder.Append(Splitter + Column.ToString());
                 Splitter = ",";
             }
         }
         Builder.Append(", PRIMARY KEY(" + PrimaryKey + ")");
         Builder.Append(", FOREIGN KEY(" + IDName + ") REFERENCES " + \_Class.OriginalType.Name + "(" + IDName + ")");
         Builder.Append(")"); 
  
         return Builder.ToString();
     }
 }

The top class is the Int class and as you can see, it's rather small. Most of the data type classes are this way but as you can see, the List class doesn't fall under that category. Whenever you have a list in a database, you can't exactly have it in the same table as the rest of the data without replicating all of the information for a row multiple times which isn't exactly desirable (or you can concat the data together, but it's usually more of a pain than it's worth).  As such you create a new table with the ID (or whatever information you need to join the two tables) and the list item.  As such the List class has to do this (create the secondary table).  In order to do this, it needs to figure out what the type within the list is, create the base type for that.  It then has to go back and find the ID for the class. This information is then used to create the table, set the primary/foreign keys, etc. You'll also notice that back in the CreateTable class that we also have to separate out the List items as they are separate commands from the main table. But that's it really. Everything else gets thrown into the main table. This in turn gets thrown back to the SQLBuilder class, which actually sends the commands to the database.

That's the basics at this point. We can create the tables and database for basic types. In the future I'll add in insertion, selection, and deletion. Once that's added, next come the more difficult types to deal with, namely other classes (ManyToMany, ManyToOne, and Map attribute types). But for now, take a look, leave feedback, and happy coding.

• Previous: Motion Detection in C#
• Next: Creating an ORM in C# - Part 4