Creating an ORM in C# - Part 6

7/6/2009

Part 1 - Defining our classes
Part 2 - Reflection and class analysis
Part 3 - Creating our database and tables
Part 4 - Creating our stored procedures
Part 5 - Class mappings

As always, I'm going to assume that you looked at the previous entries. So if you haven't, go take a look. This entry is just an addition to the last one (I would almost consider labeling it 5.5 instead of 6 actually). The last part of this series dealt with one to one class mappings and how we can deal with them in our relational database. It turns out it's fairly simple, with just a slight change from normal data (especially considering we implemented lazy loading). However I didn't get to the many to one and many to many mappings.

When dealing with one to one mappings, we could simply store the information as an extra entry in the table. With many to many though, that isn't an option. Instead we're basically dealing with a list. And like the lists that we've already dealt with, we need to set it up as a separate table. Thankfully, since we've already dealt with lists we're most of the way there. Anyway, let's start with the reflection and code generation:

   1: private void SetupManyToManyProperty(ILGenerator Generator,Type OriginalType)

   2: {

   3:     Label ExitIfStatement = Generator.DefineLabel();

   4:     Label ExitIf2Statement = Generator.DefineLabel();

   5:     Label ExitStatement = Generator.DefineLabel();

   6:     Generator.DeclareLocal(Field.FieldType);

   7:     Generator.DeclareLocal(typeof(bool));

   8:     Generator.Emit(OpCodes.Nop);

   9:     Generator.Emit(OpCodes.Ldarg_0);

  10:     Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);

  11:     Generator.Emit(OpCodes.Ldnull);

  12:     Generator.Emit(OpCodes.Ceq);

  13:     Generator.Emit(OpCodes.Ldc_I4_0);

  14:     Generator.Emit(OpCodes.Ceq);

  15:     Generator.Emit(OpCodes.Stloc_1);

  16:     Generator.Emit(OpCodes.Ldloc_1);

  17:     Generator.Emit(OpCodes.Brtrue_S, ExitIfStatement);

  18:     Generator.Emit(OpCodes.Nop);

  19:     Generator.Emit(OpCodes.Call, typeof(HaterAide.Factory).GetMethod("CreateSession"));

  20:     Generator.Emit(OpCodes.Ldarg_0);

  21:     Generator.Emit(OpCodes.Ldfld, IDField.FieldBuilder);

  22:     Generator.Emit(OpCodes.Box, IDField.FieldType);

  23:     Generator.Emit(OpCodes.Ldarg_0);

  24:     Generator.Emit(OpCodes.Ldflda, Field.FieldBuilder);

  25:     Generator.Emit(OpCodes.Ldstr, _Attribute.Name);

  26:     MethodInfo TempMethod = typeof(HaterAide.Session).GetMethod("SelectList");

  27:     TempMethod = TempMethod.MakeGenericMethod(new Type[] { OriginalType, Field.FieldType.GetGenericArguments()[0] });

  28:     Generator.Emit(OpCodes.Callvirt, TempMethod);

  29:     Generator.Emit(OpCodes.Nop);

  30:     Generator.Emit(OpCodes.Nop);

  31:     Generator.MarkLabel(ExitIfStatement);

  32:     Generator.Emit(OpCodes.Ldarg_0);

  33:     Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);

  34:     Generator.Emit(OpCodes.Ldnull);

  35:     Generator.Emit(OpCodes.Ceq);

  36:     Generator.Emit(OpCodes.Ldc_I4_0);

  37:     Generator.Emit(OpCodes.Ceq);

  38:     Generator.Emit(OpCodes.Stloc_1);

  39:     Generator.Emit(OpCodes.Ldloc_1);

  40:     Generator.Emit(OpCodes.Brtrue_S, ExitIf2Statement);

  41:     Generator.Emit(OpCodes.Nop);

  42:     Generator.Emit(OpCodes.Ldarg_0);

  43:     Generator.Emit(OpCodes.Newobj, Field.FieldType.GetConstructor(new Type[0] { }));

  44:     Generator.Emit(OpCodes.Stfld, Field.FieldBuilder);

  45:     Generator.Emit(OpCodes.Nop);

  46:     Generator.MarkLabel(ExitIf2Statement);

  47:     Generator.Emit(OpCodes.Ldarg_0);

  48:     Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);

  49:     Generator.Emit(OpCodes.Stloc_0);

  50:     Generator.Emit(OpCodes.Br_S, ExitStatement);

  51:     Generator.MarkLabel(ExitStatement);

  52:     Generator.Emit(OpCodes.Ldloc_0);

  53:     Generator.Emit(OpCodes.Ret);

  54: }

If you compared this to the one to one mapping, you actually wouldn't see much in terms of change. There is one change though, instead of getting the Select function, we are looking for the SelectList function in the Session class. And since the Session class is just a proxy, we may as well take a look at what it ends up calling:

   1: internal override object SelectListByID<T1, T2>(object IDValue, string FieldName)

   2: {

   3:     Class TempInputClassDefinition = null;

   4:     Class TempOutputClassDefinition = null;

   5:     try

   6:     {

   7:         TempInputClassDefinition = ClassManager[typeof(T1)];

   8:         TempOutputClassDefinition = ClassManager[typeof(T2)];

   9:     }

  10:     catch { TempInputClassDefinition = null; TempOutputClassDefinition = null; }

  11:     return SQLBuilder.SelectListByID<T2>(ConnectionString, IDValue, TempInputClassDefinition, TempOutputClassDefinition, ClassManager, FieldName);

  12: }

This in turn calls:

   1: internal static object SelectListByID<T>(string ConnectionString, object IDValue,

   2:     Class TempInputClassDefinition, Class TempOutputClassDefinition, ClassManager ClassManager,string FieldName)

   3: {

   4:     Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("", ConnectionString, CommandType.Text);

   5:     SelectManyToMany TempSelect = new SelectManyToMany(TempInputClassDefinition, TempOutputClassDefinition, ClassManager, FieldName);

   6:     return TempSelect.SelectByID<T>(IDValue, Helper);

   7: }

Which in turn builds a SelectManyToMany class:

   1: internal class SelectManyToMany:IStatement

   2: {

   3:     public SelectManyToMany(Class InputClass,Class OutputClass, ClassManager Manager,string FieldName)

   4:         : base(InputClass, Manager)

   5:     {

   6:         this._OutputClass = OutputClass;

   7:         this._FieldName = FieldName;

   8:     }

9:

  10:     private Class _OutputClass = null;

  11:     private string _FieldName = "";

12:

  13:     public object SelectByID<T>(object IDValue, SQLHelper Helper)

  14:     {

  15:         List<T> ReturnList = new List<T>();

16:

  17:         foreach(Property TempProperty in _Class.Properties)

  18:         {

  19:             if(TempProperty.Attribute.Name.Equals(_FieldName))

  20:             {

  21:                 Helper.Command=TempProperty.Attribute.MappedProperty+"_Select_"+_Class.OriginalType.Name;

  22:                 Helper.CommandType=CommandType.StoredProcedure;

  23:                 break;

  24:             }

  25:         }

  26:         List<object> IDList = LoadIDList(IDValue, Helper, ReturnList);

  27:         ReturnList=LoadList(IDList,Helper,ReturnList);

  28:         return ReturnList;

  29:     }

30:

  31:     private List<T> LoadList<T>(List<object> IDList,SQLHelper Helper,List<T> ReturnList)

  32:     {

  33:       Session TempSession=Factory.CreateSession();

  34:         foreach (object ID in IDList)

  35:         {

  36:             T ClassInstance = (T)Activator.CreateInstance(_OutputClass.DerivedType);

  37:             TempSession.Select<T>(ID, out ClassInstance);

  38:             ReturnList.Add(ClassInstance);

  39:         }

  40:         return ReturnList;

  41:     }

  42:     private List<object> LoadIDList<T>(object IDValue, SQLHelper Helper, List<T> ReturnList)

  43:     {

  44:         List<object> IDList = new List<object>();

  45:         IDataType IDField=GlobalFunctions.GetSQLType(_Class.IDField,_Class,_Manager);

  46:         try

  47:         {

  48:             Helper.Open();

  49:             Helper.AddParameter("@" + _Class.OriginalType.Name + "_" + IDField.Name, IDValue, IDField.DataType);

  50:             Helper.ExecuteReader();

  51:             while (Helper.Read())

  52:             {

  53:                 IDList.Add(Helper.GetParameter(_OutputClass.OriginalType.Name + "_" + _OutputClass.IDField.Name, null));

  54:             }

  55:         }

  56:         catch { }

  57:         finally { Helper.Close(); }

  58:         return IDList;

  59:     }

  60: }

The class works like the other IStatement classes. The big difference is that it loads a list instead of a single item. In fact since we already have a select function for a single entity, all it does is loads the list of IDs from the database and calls select on the individual IDs.

So we can load the items, but at present we have no table to actually pull the IDs from, we have no way to save them, etc. So let's start going down that road by defining a many to many data type.

   1: internal class ManyToManyClassType:IDataType

   2: {

   3:     public ManyToManyClassType(Attribute Attribute, Class Class, ClassManager Manager)

   4:         : base(Attribute)

   5:     {

   6:         Type MappedType = Attribute.Type.GetGenericArguments()[0];

   7:         Class MappedClass = Manager[MappedType];

   8:         _Class = Class;

   9:         _Manager = Manager;

  10:         _MappedClass = MappedClass;

11:

  12:         MappedIDField = GlobalFunctions.GetSQLType(MappedClass.IDField, MappedClass, Manager);

  13:         IDField = GlobalFunctions.GetSQLType(Class.IDField, Class, Manager);

  14:         IDField.Constraints.Clear();

  15:         MappedIDField.Constraints.Clear();

  16:     }

17:

  18:     public IDataType IDField { get; set; }

  19:     public IDataType MappedIDField { get; set; }

  20:     public Class _Class = null;

  21:     public Class _MappedClass = null;

  22:     public ClassManager _Manager = null;

23:

  24:     public override string CreateTableCommand()

  25:     {

  26:         StringBuilder Builder = new StringBuilder("CREATE TABLE " + Attribute.MappedProperty + "(" + Name + "ID int IDENTITY,");

  27:         Builder.Append(_Class.OriginalType.Name + "_" + IDField.CreateTableCommand() + ",");

  28:         Builder.Append(_MappedClass.OriginalType.Name + "_" + MappedIDField.CreateTableCommand());

  29:         Builder.Append(", PRIMARY KEY(" + Name + "ID)");

  30:         Builder.Append(")");

  31:         return Builder.ToString();

  32:     }

33:

  34:     public override string CreateInsertCommand()

  35:     {

  36:         StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Attribute.MappedProperty + "_Insert\n");

  37:         Builder.Append(IDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _Class.OriginalType.Name + "_") + ",\n");

  38:         Builder.Append(MappedIDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _MappedClass.OriginalType.Name + "_"));

  39:         Builder.Append("\nAS\nINSERT INTO " + Attribute.MappedProperty + "(");

  40:         Builder.Append(_Class.OriginalType.Name + "_" + IDField.Name + ",");

  41:         Builder.Append(_MappedClass.OriginalType.Name + "_" + MappedIDField.Name + ") VALUES (");

  42:         Builder.Append("@"+_Class.OriginalType.Name + "_" + IDField.Name + ",");

  43:         Builder.Append("@" + _MappedClass.OriginalType.Name + "_" + MappedIDField.Name + ")\nRETURN'\n");

  44:         return Builder.ToString();

  45:     }

46:

  47:     public override string CreateDeleteCommand()

  48:     {

  49:         StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Attribute.MappedProperty + "_Delete_" + _Class.OriginalType.Name + "\n");

  50:         StringBuilder MappedBuilder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Attribute.MappedProperty + "_Delete_" + _MappedClass.OriginalType.Name + "\n");

51:

  52:         Builder.Append(IDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _Class.OriginalType.Name + "_") + ",\n");

  53:         MappedBuilder.Append(MappedIDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _MappedClass.OriginalType.Name + "_"));

54:

  55:         Builder.Append("\nAS\nDELETE FROM " + Attribute.MappedProperty + " WHERE ");

  56:         MappedBuilder.Append("\nAS\nDELETE FROM " + Attribute.MappedProperty + " WHERE ");

57:

  58:         Builder.Append(_Class.OriginalType.Name + "_" + IDField.Name + "=");

  59:         Builder.Append("@" + _Class.OriginalType.Name + "_" + IDField.Name + "\nRETURN'\n");

60:

  61:         MappedBuilder.Append(_MappedClass.OriginalType.Name + "_" + MappedIDField.Name + "=");

  62:         MappedBuilder.Append("@" + _MappedClass.OriginalType.Name + "_" + MappedIDField.Name + "\nRETURN'\n");

63:

  64:         return Builder.ToString() + MappedBuilder.ToString();

  65:     }

66:

  67:     public override string CreateSelectCommand()

  68:     {

  69:         StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Attribute.MappedProperty + "_Select_" + _Class.OriginalType.Name + "\n");

  70:         StringBuilder MappedBuilder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Attribute.MappedProperty + "_Select_" + _MappedClass.OriginalType.Name + "\n");

71:

  72:         Builder.Append(IDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _Class.OriginalType.Name + "_") + "\n");

  73:         MappedBuilder.Append(MappedIDField.CreateStoredProcedureParameter(false, true).Replace("@", "@" + _MappedClass.OriginalType.Name + "_") + "\n");

74:

  75:         Builder.Append("\nAS\nSELECT "+_Class.OriginalType.Name+"_"+IDField.Name+","+_MappedClass.OriginalType.Name+"_"+MappedIDField.Name+" FROM "+Attribute.MappedProperty);

  76:         Builder.Append(" WHERE "+_Class.OriginalType.Name+"_"+IDField.Name+"=@"+_Class.OriginalType.Name+"_"+IDField.Name);

77:

  78:         MappedBuilder.Append("\nAS\nSELECT " + _Class.OriginalType.Name + "_" + IDField.Name + "," + _MappedClass.OriginalType.Name + "_" + MappedIDField.Name + " FROM " + Attribute.MappedProperty);

  79:         MappedBuilder.Append(" WHERE " + _MappedClass.OriginalType.Name + "_" + MappedIDField.Name + "=@" + _MappedClass.OriginalType.Name + "_" + MappedIDField.Name);

80:

  81:         Builder.Append("\nRETURN'\n");

  82:         MappedBuilder.Append("\nRETURN'\n");

  83:         return Builder.ToString() + MappedBuilder.ToString();

  84:     }

  85: }

We did something similar with one to one mapped classes. As you can see we have our create table command, insert command, etc. And all we need to do is add the type to what our GlobalFunctions class returns and our statements should pick it up automatically (with some minor changes which you can find in the code linked to below). The big changes come when we actually try to save the item.

   1: private void SetupLists(object Object, SQLHelper Helper, Type ObjectType)

   2: {

   3:     foreach (IDataType Column in Columns)

   4:     {

   5:         if (Column != null && Column is ManyToManyClassType)

   6:         {

   7:             ManyToManyClassType ManyToManyColumn = ((ManyToManyClassType)Column);

   8:             PropertyInfo IDPropertyInfo = ObjectType.GetProperty(ManyToManyColumn._Class.IDField.Name);

   9:             object IDValue = IDPropertyInfo.GetValue(Object, null);

  10:             Helper.Command = Column.Attribute.MappedProperty + "_Insert";

  11:             PropertyInfo ListInfo = ObjectType.GetProperty(Column.Attribute.Name);

  12:             object ListValue = ListInfo.GetValue(Object, null);

  13:             if (ListValue != null)

  14:             {

  15:                 Type ListType = ListValue.GetType();

  16:                 PropertyInfo IndexProperty = ListType.GetProperty("Item");

  17:                 PropertyInfo CountProperty = ListType.GetProperty("Count");

  18:                 int Count = (int)CountProperty.GetValue(ListValue, null);

  19:                 Type MappedObjectType = ManyToManyColumn._MappedClass.OriginalType;

  20:                 PropertyInfo MappedIDPropertyInfo = MappedObjectType.GetProperty(ManyToManyColumn._MappedClass.IDField.Name);

  21:                 for (int x = 0; x < Count; ++x)

  22:                 {

  23:                     object MappedClassObject = IndexProperty.GetValue(ListValue, new object[] { x });

  24:                     object MappedClassID = MappedIDPropertyInfo.GetValue(MappedClassObject, null);

  25:                     try

  26:                     {

  27:                         Helper.Open();

  28:                         Helper.ClearParameters();

  29:                         Helper.AddParameter("@" + ManyToManyColumn._Class.OriginalType.Name + "_" + ManyToManyColumn._Class.IDField.Name, IDValue, ManyToManyColumn.IDField.DataType);

  30:                         Helper.AddParameter("@" + ManyToManyColumn._MappedClass.OriginalType.Name + "_" + ManyToManyColumn._MappedClass.IDField.Name, MappedClassID, ManyToManyColumn.MappedIDField.DataType);

  31:                         Helper.ExecuteNonQuery();

  32:                     }

  33:                     catch { }

  34:                     finally { Helper.Close(); }

  35:                 }

  36:             }

  37:         }

  38:         else if (Column != null && (Column is List))//|| Column is ManyToManyClassType))

  39:         {

  40:             string IDName = ((List)Column).IDName;

  41:             SqlDbType IDType = ((List)Column).IDType;

  42:             string DataName = ((List)Column).DataName;

  43:             SqlDbType DataType = ((List)Column).DataType;

  44:             int DataSize = ((List)Column).Size;

45:

  46:             PropertyInfo IDPropertyInfo = ObjectType.GetProperty(IDName);

  47:             object IDValue = IDPropertyInfo.GetValue(Object, null);

48:

  49:             Helper.Command = Column.Name + "_Insert";

50:

  51:             PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Attribute.Name);

  52:             object ListValue = PropertyInfo.GetValue(Object, null);

  53:             if (ListValue != null)

  54:             {

  55:                 Type ListType = ListValue.GetType();

  56:                 PropertyInfo IndexProperty = ListType.GetProperty("Item");

  57:                 PropertyInfo CountProperty = ListType.GetProperty("Count");

  58:                 int Count = (int)CountProperty.GetValue(ListValue, null);

  59:                 for (int x = 0; x < Count; ++x)

  60:                 {

  61:                     try

  62:                     {

  63:                         Helper.Open();

  64:                         Helper.ClearParameters();

  65:                         Helper.AddParameter("@" + IDName, IDValue, IDType);

  66:                         if (DataType == SqlDbType.NVarChar)

  67:                         {

  68:                             Helper.AddParameter("@" + DataName, (string)IndexProperty.GetValue(ListValue, new object[] { x }), DataSize);

  69:                         }

  70:                         else

  71:                         {

  72:                             Helper.AddParameter("@" + DataName, IndexProperty.GetValue(ListValue, new object[] { x }), DataType);

  73:                         }

  74:                         Helper.ExecuteNonQuery();

  75:                     }

  76:                     catch { }

  77:                     finally { Helper.Close(); }

  78:                 }

  79:             }

  80:         }

  81:     }

  82: }

You will see that we check if we're dealing with a ManyToManyClassType and if we are we find the list object, get each individual item, etc. in a similar manner to all lists thus far. However we have an added step of finding the ID for each individual object and inserting that into the table instead of the whole object. All we need to worry about is saving the connection to the correct object. The reason for this is that the object is already saved in another table. Our code for cascading a save handles this for us with only a little bit of extra code:

   1: private void CascadeSave(object Object, Type ObjectType)

   2: {

   3:     Session TempSession = Factory.CreateSession();

   4:     foreach (IDataType Column in Columns)

   5:     {

   6:         if (Column.Attribute.Cascade)

   7:         {

   8:             PropertyInfo Property = ObjectType.GetProperty(Column.Name);

   9:             object Value=Property.GetValue(Object,null);

  10:             if (Value != null)

  11:             {

  12:                 if (Column is ManyToManyClassType)

  13:                 {

  14:                     ManyToManyClassType ManyToManyColumn = ((ManyToManyClassType)Column);

  15:                     Type ListType = Value.GetType();

  16:                     PropertyInfo IndexProperty = ListType.GetProperty("Item");

  17:                     PropertyInfo CountProperty = ListType.GetProperty("Count");

  18:                     int Count = (int)CountProperty.GetValue(Value, null);

  19:                     Type MappedObjectType = ManyToManyColumn._MappedClass.OriginalType;

  20:                     for (int x = 0; x < Count; ++x)

  21:                     {

  22:                         object MappedClassObject = IndexProperty.GetValue(Value, new object[] { x });

  23:                         TempSession.Save(MappedClassObject);

  24:                     }

  25:                 }

  26:                 else

  27:                 {

  28:                     TempSession.Save(Value);

  29:                 }

  30:             }

  31:         }

  32:     }

  33: }

I've only shown you the insert function but the delete and update work in a similar manner. With that we have our insert, update, delete, and select functions working for many to many mappings. Now what about many to one?

To be honest, I've seen many to one mappings dealt with in many different ways. The most popular is to treat them as one sided one to one mappings when saving the data. For instance, let's assume we have a parent item that has multiple child items. The parent wouldn't save anything extra in this scenario; instead the child items would hold a link back to the parent item.

This works the vast majority of the time but I find that there is an issue if ever the code needs to change from a many to one to a many to many mapping (since that's usually more common than a switch to a one to one mapping). So I decided to simply treat it like a many to many mapping. It gets its own table to store the links, its own stored procedures, etc. The only caveat is when we're saving, deleting, loading, etc. we have to check whether we're dealing with a list or a single item. If it's a list, we deal with it exactly as if it were a many to many mapping. In the single item scenario we treat it like a many to many mapping but we skip the reflection portions that deal with the list object. So half of the time we call Select and half the time we call SelectList. That's it really.

So at this point we can load, save, and delete pretty much anything we want. So on to dealing with the Caching right? Not quite. Sadly at this point we can only load a single item by its ID. That isn't exactly the most useful way to get our information. We still need to add a way to deal with that. That will be next time, but after that we're ready to deal with the Cache. Or at least I think so unless something comes up. So take a look at the code, leave feedback, and happy coding.

Comments

Name *

Email (used to grab Gravatar image)

Website

Comment *

Other Posts in ORM

Creating an ORM in C# - Part 6

Comments

Recent Posts

Recent Comments