Part 1 – Defining our classes
Part 2 – Reflection and class analysis
Part 3 – Creating our database and tables
Part 4 - Creating our stored procedures
Today I finally got around to adding support for classes… Well straight one to one mappings anyway, many to many and many to one are a bit trickier so I’ve put those off for another post. As always, you may want to read the earlier posts if you haven’t already. But in this post I’m going to cover loading of classes, lazy loading, and cascading updates/inserts.
Basically when you think about it, as far as our CRM is concerned, a class is just another data type. However unlike say an int, we really don’t want to load it when the rest of the class is loaded. I mean we can but that would potentially cause issues (we might end up loading our entire database or even end up in an endless loop of loading). So what most people do to combat this is use something called lazy loading. Lazy loading is simply a design pattern where you load/initialize an object only when it is needed. So how do we go about setting up our system to do lazy loading?
public Property(Attribute Attribute, TypeBuilder TypeBuilder, FieldBuilder ChangedField,ClassManager Manager)
{
Field = new Field(Attribute, TypeBuilder, FieldAttributes.Private);
if (Attribute.AttributeType == AttributeType.Map)
{
Attribute TempIDAttribute = new Attribute();
TempIDAttribute.AttributeType = Attribute.AttributeType;
Class TempClass = Manager[Attribute.Type];
foreach (Property TempProperty in TempClass.Properties)
{
if (TempProperty.Attribute.AttributeType == AttributeType.ID)
{
TempIDAttribute.Type = TempProperty.Attribute.Type;
}
}
TempIDAttribute.Name = Attribute.Name + "ID";
IDField = new Field(TempIDAttribute, TypeBuilder, FieldAttributes.Public);
}
MethodAttributes GetSetAttributes = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig | MethodAttributes.Virtual;
MethodBuilder ValuePropertyGet = TypeBuilder.DefineMethod("get_" + Attribute.Name, GetSetAttributes, Attribute.Type, Type.EmptyTypes);
ILGenerator Generator = ValuePropertyGet.GetILGenerator();
if (Attribute.AttributeType == AttributeType.Map)
{
Label ExitIfStatement = Generator.DefineLabel();
Label ExitIf2Statement = Generator.DefineLabel();
Label ExitStatement = Generator.DefineLabel();
Generator.DeclareLocal(Field.FieldType);
Generator.DeclareLocal(typeof(bool));
Generator.Emit(OpCodes.Nop);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Ldnull);
Generator.Emit(OpCodes.Ceq);
Generator.Emit(OpCodes.Ldc_I4_0);
Generator.Emit(OpCodes.Ceq);
Generator.Emit(OpCodes.Stloc_1);
Generator.Emit(OpCodes.Ldloc_1);
Generator.Emit(OpCodes.Brtrue_S, ExitIfStatement);
Generator.Emit(OpCodes.Nop);
Generator.Emit(OpCodes.Call, typeof(HaterAide.Factory).GetMethod("CreateSession"));
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, IDField.FieldBuilder);
Generator.Emit(OpCodes.Box, IDField.FieldType);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldflda, Field.FieldBuilder);
MethodInfo TempMethod = typeof(HaterAide.Session).GetMethod("Select");
TempMethod = TempMethod.MakeGenericMethod(new Type[] { Field.FieldType });
Generator.Emit(OpCodes.Callvirt, TempMethod);
Generator.Emit(OpCodes.Nop);
Generator.Emit(OpCodes.Nop);
Generator.MarkLabel(ExitIfStatement);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Ldnull);
Generator.Emit(OpCodes.Ceq);
Generator.Emit(OpCodes.Ldc_I4_0);
Generator.Emit(OpCodes.Ceq);
Generator.Emit(OpCodes.Stloc_1);
Generator.Emit(OpCodes.Ldloc_1);
Generator.Emit(OpCodes.Brtrue_S, ExitIf2Statement);
Generator.Emit(OpCodes.Nop);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Newobj, Field.FieldType.GetConstructor(new Type[0] { }));
Generator.Emit(OpCodes.Stfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Nop);
Generator.MarkLabel(ExitIf2Statement);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Stloc_0);
Generator.Emit(OpCodes.Br_S, ExitStatement);
Generator.MarkLabel(ExitStatement);
Generator.Emit(OpCodes.Ldloc_0);
Generator.Emit(OpCodes.Ret);
}
else
{
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Ret);
}
MethodBuilder ValuePropertySet = TypeBuilder.DefineMethod("set_" + Attribute.Name, GetSetAttributes, null, new Type[] { Attribute.Type });
Generator = ValuePropertySet.GetILGenerator();
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldarg_1);
Generator.Emit(OpCodes.Stfld, Field.FieldBuilder);
Generator.Emit(OpCodes.Ldarg_0);
Generator.Emit(OpCodes.Ldfld, ChangedField);
Generator.Emit(OpCodes.Ldstr, Attribute.Name);
Generator.Emit(OpCodes.Callvirt, typeof(List<string>).GetMethod("Add"));
Generator.Emit(OpCodes.Ret);
_Attribute = Attribute;
}
As you can see above the Property constructor has been changed slightly. There are two sections that check if the attribute type is a mapping. These are our updates to deal with classes. The first section defines an ID field (the ID of the mapped class will go there when we load it). The second section is the actual code for getting the object. It checks whether the object is null, if it is null it loads the object and saves it for later (by creating a session object and simply calling Select), however if it is still null it creates a default object. Either way, it returns whatever is held in the object. Now the only tricky thing in here is the fact that Select is a generic function. It took me a good hour to figure out that while I could get the function itself easily enough that I had to define what type it should use. That's done with the MakeGenericMethod call. It seems simple once you've seen it but you'll hit your head against the wall for a while if you don't know what to do as the exceptions that are returned are fairly useless...
Anyway, that’s it; we now have lazy loading and classes working… Ok, we still have some slight issues with the database, but the lazy loading is set up and our reflection is working properly. At this point though, we aren’t setting up the tables or stored procedures to hold the class mappings. So how do we do this? Well because of how I’ve set up the SQL building, all we really need to do is create a new data type:
internal class MapClassType:IDataType
{
public MapClassType(Attribute Attribute, Reflection.Class Class, ClassManager Manager)
: base(Attribute)
{
_Class = Class;
_Manager = Manager;
MappedClass = _Manager[Attribute.Type];
MappedDataType = GlobalFunctions.GetSQLType(MappedClass.IDField, MappedClass, _Manager);
DataType = MappedDataType.DataType;
Type = MappedDataType.Type;
}
private Class _Class = null;
private ClassManager _Manager = null;
public Class MappedClass { get; set; }
public IDataType MappedDataType { get; set; }
public override string CreateTableCommand()
{
StringBuilder Builder = new StringBuilder(Name + " " + MappedDataType.Type);
return Builder.ToString();
}
}
internal static class GlobalFunctions
{
public static IDataType GetSQLType(Attribute Attribute,Class Class,ClassManager Manager)
{
if (Attribute.Type.FullName.Equals("System.Int32", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.Int(Attribute);
}
else if (Attribute.Type.FullName.Equals("System.String", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.NVarChar(Attribute);
}
else if (Attribute.Type.FullName.Equals("System.Single", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.Float(Attribute);
}
else if (Attribute.Type.FullName.Equals("System.Double", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.Float(Attribute);
}
else if (Attribute.Type.FullName.Equals("System.Boolean", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.Bit(Attribute);
}
else if (Attribute.Type.FullName.StartsWith("System.DateTime", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.DateTime(Attribute);
}
else if (Attribute.Type.FullName.StartsWith("System.Guid", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.UniqueIdentifier(Attribute);
}
else if (Attribute.Type.FullName.StartsWith("System.Collections.Generic.List", StringComparison.CurrentCultureIgnoreCase))
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.List(Attribute, Class, Manager);
}
if (Attribute.AttributeType == AttributeType.Map)
{
return new HaterAide.SQL.SQLServer.Statements.DataTypes.MapClassType(Attribute, Class, Manager);
}
return null;
}
}
As you can see, it acts similarly to the List class in that it doesn’t use its own information but instead uses the ID field of the class that it maps to. And after we add it to the global function so that it’s being returned, Insert, Select, Update, and Delete classes will simply pick it up and use it properly for the table/stored procedures that are created.
So we have our table that can store the class’s ID so that it can load and map the classes properly. However we now have to deal with the more annoying part. We need to actually set up the various stored procedures such that they can use the ID properly.
private void SetupProperties(object Object, SQLHelper Helper, Type ObjectType)
{
foreach (IDataType Column in Columns)
{
if (Column != null && Column is MapClassType)
{
MapClassType TempColumn = (MapClassType)Column;
object MappedObject=ObjectType.GetProperty(TempColumn.Name).GetValue(Object,null);
if (MappedObject != null)
{
Type MappedObjectType = MappedObject.GetType();
object Value = MappedObjectType.GetProperty(TempColumn.MappedClass.IDField.Name).GetValue(MappedObject, null);
Helper.AddParameter("@" + TempColumn.Name, Value, TempColumn.MappedDataType.DataType);
}
else if(TempColumn.MappedDataType.DataType==SqlDbType.Int)
{
Helper.AddParameter("@" + TempColumn.Name, 0, TempColumn.MappedDataType.DataType);
}
else if (TempColumn.MappedDataType.DataType == SqlDbType.UniqueIdentifier)
{
Helper.AddParameter("@" + TempColumn.Name, Guid.Empty, TempColumn.MappedDataType.DataType);
}
}
else if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Name);
object Value = PropertyInfo.GetValue(Object, null);
if (Column is NVarChar && !Column.IsPrimaryKey)
{
foreach (IConstraint Constraint in Column.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + Column.Name, (string)Value, ((Size)Constraint).Length);
break;
}
}
}
else if (!Column.IsPrimaryKey)
{
Helper.AddParameter("@" + Column.Name, Value, Column.DataType);
}
}
}
}
Above is a function from the Insert class. The function looks for nonlist items and adds them to the stored procedure. The top part checks if the individual column is a mapped class. Inside that if statement, it first gets the object’s value. If the object is not null it gets the object’s ID field and the value of that field. It then adds that value into the stored procedure. A similar addition is made to the Update class. The Delete class doesn’t require any updates at all since the only thing the stored procedure cares about is the ID of the class. The Select class is slightly different.
private object LoadMainClass(object IDValue, SQLHelper Helper, object ClassInstance, IDataType IDField)
{
try
{
Helper.Open();
if (IDField is NVarChar)
{
foreach (IConstraint Constraint in IDField.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + IDField.Name, (string)IDValue, ((Size)Constraint).Length);
}
}
}
else
{
Helper.AddParameter("@" + IDField.Name, IDValue, IDField.DataType);
}
Helper.ExecuteReader();
if (Helper.Read())
{
ClassInstance = Activator.CreateInstance(_Class.DerivedType);
foreach (IDataType Column in Columns)
{
if (Column != null && Column is MapClassType)
{
FieldInfo TempField = _Class.DerivedType.GetField("_" + Column.Name + "IDDerived");
TempField.SetValue(ClassInstance, Helper.GetParameter(Column.Name, null));
}
else if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo TempProperty = _Class.DerivedType.GetProperty(Column.Name);
if (TempProperty.PropertyType.FullName.Equals("System.Single", StringComparison.CurrentCultureIgnoreCase))
{
TempProperty.SetValue(ClassInstance, float.Parse(Helper.GetParameter(Column.Name, null).ToString()), null);
}
else
{
TempProperty.SetValue(ClassInstance, Helper.GetParameter(Column.Name, null), null);
}
}
}
}
}
catch { }
finally { Helper.Close(); }
return ClassInstance;
}
Luckily we’ve set the mapped classes ID field in a predictable manner. So what we do is go through the list of columns like before and if we see a MapClassType object, we get the ID field that we created back in the reflection stage. We then set that value to the ID value that we have stored in our database. We don’t load the entire mapped class, just the ID, and we let the lazy loading that we set up before handle the rest.
Now we have our table, our stored procedures, and our lazy loading. That just leaves cascading saving of objects. This is actually slightly more difficult than it may look on the surface. Take for instance two classes, each have a mapping to one another. We save class A which then cascades to class B which then cascades to class A… Yay, infinite loop. Luckily a while back I set up the definitions to take in a boolean stating whether or not to do cascading on a specific property. So we simply set it up such that class B says don’t update class A where as class A says cascade down to B. This just means that the person defining the relationship needs to be careful but that’s the only issue. So for the Insert and Update classes we simply add a call to the function below from the Run function.
private void CascadeSave(object Object, Type ObjectType)
{
Session TempSession = Factory.CreateSession();
foreach (IDataType Column in Columns)
{
if (Column.Attribute.Cascade)
{
PropertyInfo Property = ObjectType.GetProperty(Column.Name);
object Value=Property.GetValue(Object,null);
if (Value != null)
{
TempSession.Save(Value);
}
}
}
}
That’s it really. We check our properties, we check if they’re null, and we save them. And since we’ve dealt with the infinite loop issue, we can actually make this function pretty dumb. Now you may assume that we’re done but there is yet one more thing that needs to be done. Specifically when we’re inserting an object for the first time, there is an issue. You see the order of operations when inserting is we first insert the main class information, we get back our new ID and set that in the object, insert our lists, and then cascade our insertion. Now what happens when the objects that it cascades haven’t been saved before? Their ID is going to be the default value (so 0 for an int, etc.). So when we first saved the initial object, all of those mapped classes are pointing to the class with an ID of 0. So how do we fix this? Well after the cascade, these items have an ID as they’ve been inserted. So at this point all that we need to do is update the main class (we can skip the lists, etc.). So we add this function:
private void UpdateMainProperties(object Object, Type ObjectType,SQLHelper Helper)
{
try
{
Helper.Command = _Class.OriginalType.Name + "_Update";
Helper.Open();
foreach (IDataType Column in Columns)
{
if (Column != null && Column is MapClassType)
{
MapClassType TempColumn = (MapClassType)Column;
object MappedObject = ObjectType.GetProperty(TempColumn.Name).GetValue(Object, null);
if (MappedObject != null)
{
Type MappedObjectType = MappedObject.GetType();
object Value = MappedObjectType.GetProperty(TempColumn.MappedClass.IDField.Name).GetValue(MappedObject, null);
Helper.AddParameter("@" + TempColumn.Name, Value, TempColumn.MappedDataType.DataType);
}
else if (TempColumn.MappedDataType.DataType == SqlDbType.Int)
{
Helper.AddParameter("@" + TempColumn.Name, 0, TempColumn.MappedDataType.DataType);
}
else if (TempColumn.MappedDataType.DataType == SqlDbType.UniqueIdentifier)
{
Helper.AddParameter("@" + TempColumn.Name, Guid.Empty, TempColumn.MappedDataType.DataType);
}
}
else if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Name);
object Value = PropertyInfo.GetValue(Object, null);
if (Column is NVarChar && !Column.IsPrimaryKey)
{
foreach (IConstraint Constraint in Column.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + Column.Name, (string)Value, ((Size)Constraint).Length);
break;
}
}
}
else
{
Helper.AddParameter("@" + Column.Name, Value, Column.DataType);
}
}
}
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
We could simply call Save again but our system is pretty dumb and would simply call cascade again. We don’t want to deal with that so a simple update function works well enough. And with that function we’re finally done with classes. Well one to one mapped classes anyway. We have yet to deal with the pain which is many to many and many to one. But hey, the CRM is getting closer to being done. So give it a try, leave feedback, and happy coding.
HaterAidePart5.zip (881.27 kb)
Be the first to rate this post - Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5
Part 1 – Defining our classes
Part 2 – Reflection and class analysis
Part 3 – Creating our database and tables
If you have yet to read the first three parts of this series, go back and do so. Not because the code in this section isn’t readable without it, but because each article builds on the ones before it. Anyway, today I’m showing the stored procedures that the system defines as well as the insert/savnig and selecting of an item.
Anyway, in the last article, we used an IStatement class called CreateTable in our building of the SQL to build each table. It has been modified slightly if you go back and look at it but it’s generally the same as in the last article. Anyway, along with creating a table, we can define four functions that the system will need to be able to do, insert, update, delete, and select. With the exception of the select statements, these functions really aren’t going to change much and as such I’ve decided that instead of creating the functions each time, I would simply use stored procedures. So let’s look at how the stored procedures are created:
internal static class SQLBuilder
{
public static void CreateStoredProcedures(string ConnectionString, Class Class, ClassManager Manager, bool RecreateIfExists)
{
CreateInsertStoredProcedures(ConnectionString, Class, Manager);
CreateUpdateStoredProcedures(ConnectionString, Class, Manager);
CreateDeleteStoredProcedures(ConnectionString, Class, Manager);
CreateSelectStoredProcedures(ConnectionString, Class, Manager);
}
private static void CreateSelectStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
{
bool Exists = false;
string ClassName = Class.OriginalType.Name;
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
if (!Exists)
{
Select SelectStoredProcedures = new Select(Class, Manager);
Helper.Command = SelectStoredProcedures.ToString();
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
}
private static void CreateDeleteStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
{
bool Exists = false;
string ClassName = Class.OriginalType.Name;
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
if (!Exists)
{
Delete DeleteStoredProcedures = new Delete(Class, Manager);
Helper.Command = DeleteStoredProcedures.ToString();
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
}
private static void CreateUpdateStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
{
bool Exists = false;
string ClassName = Class.OriginalType.Name;
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
if (!Exists)
{
Update UpdateStoredProcedures = new Update(Class, Manager);
Helper.Command = UpdateStoredProcedures.ToString();
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
}
private static void CreateInsertStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
{
bool Exists = false;
string ClassName = Class.OriginalType.Name;
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
if (!Exists)
{
Insert InsertStoredProcedures = new Insert(Class, Manager);
Helper.Command = InsertStoredProcedures.ToString();
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
}
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(); }
}
}
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(); }
}
}
internal static bool Save(string ConnectionString, object Object, Class ObjectDefinition, ClassManager ClassManager)
{
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("", ConnectionString, CommandType.Text);
Insert ObjectInsert = new Insert(ObjectDefinition, ClassManager);
ObjectInsert.Run(Object, Helper);
return false;
}
public static object SelectByID(string ConnectionString, object IDValue, Class Class, ClassManager Manager)
{
Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("", ConnectionString, CommandType.Text);
Select TempSelect=new Select(Class,Manager);
return TempSelect.SelectByID(IDValue,Helper);
}
}
internal class Insert:IStatement
{
public override string ToString()
{
StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + _Class.OriginalType.Name + "_Insert\n");
StringBuilder ListBuilder = new StringBuilder();
string Splitter = "";
string Splitter2="";
StringBuilder Values=new StringBuilder();
StringBuilder Parameters=new StringBuilder();
foreach (IDataType Column in Columns)
{
if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
if (!string.IsNullOrEmpty(Column.CreateStoredProcedureParameter(false, true)))
{
Builder.Append(Splitter + Column.CreateStoredProcedureParameter(false, true));
Values.Append(Splitter2 + Column.Name);
Parameters.Append(Splitter2 + "@" + Column.Name);
Splitter = ",\n";
Splitter2 = ",";
}
}
else if (Column != null && Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
ListBuilder.Append(" " + Column.CreateInsertCommand());
}
}
Builder.Append("\nAS\nINSERT INTO " + _Class.OriginalType.Name + "(" + Values.ToString() + ") VALUES (" + Parameters.ToString() + ")\nSELECT scope_identity() as [ID]\nRETURN'\n");
return Builder.ToString()+ListBuilder.ToString();
}
}
The SQL builder is called by our provider that we created last time. The builder then goes off and creates our stored procedures (note that at this time it doesn’t check to see if they exist first, that will be added later). The SQLBuilder then creates an Insert (or Update, Select, etc. object), passing it the class definition as well as the ClassManager. This class then parses out the information that it needs for each property of the class and builds up a string containing the stored procedure.
In the code above, it’s the insert stored procedure that is created. You’ll notice that it deals with lists in a different manner than the rest of the properties (note that we’re still not dealing with classes). In order to represent a list effectively in a database, you really need to separate out that information in a separate table. As such, the list needs its own insert procedure (and its own delete and select stored procedures also). The code for this is within the List class:
public override string CreateInsertCommand()
{
StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + Name + "_Insert\n");
string Splitter = "";
foreach(IDataType Column in Columns)
{
Builder.Append(Splitter + Column.CreateStoredProcedureParameter(false, true));
Splitter = ",\n";
}
Builder.Append("\nAS\nINSERT INTO "+Name+"(");
StringBuilder Values=new StringBuilder();
Splitter = "";
foreach(IDataType Column in Columns)
{
Values.Append(Splitter+"@"+Column.Name);
Builder.Append(Splitter+Column.Name);
Splitter=",";
}
Builder.Append(") VALUES(" + Values.ToString() + ")\nRETURN'\n");
return Builder.ToString();
}
It’s not pretty doing it this way and I’m sure that I’ll figure another way to do this, but it is what it is for now. Anyway, we have similar code for update, delete, and select stored procedures (note that the select versions only deal with selecting an item by the ID field and selecting all of the items). These stored procedures and then thrown back to the SQLBuilder class and it runs it against the database.
So at this point we have our tables, our stored procedures, etc. Now how do we actually insert something into our database? Well in order to do this I’ve created a helper class called Session:
public class Session
{
public Session()
{
}
internal Session(IProvider Provider)
{
_Provider = Provider;
}
public bool Save(object Object)
{
if (_Provider != null)
{
return _Provider.Save(Object);
}
return false;
}
public void Select<T>(object ID, out T Object)
{
if (_Provider != null)
{
Object= (T)_Provider.SelectByID<T>(ID);
return;
}
Object = default(T);
}
public void Delete(object Object)
{
if (_Provider != null)
{
_Provider.Delete(Object);
}
}
private IProvider _Provider=null;
}
As you can see, all it really does is acts as a provider proxy between us and the provider. The two functions that you will want to take a look at are Save and Select. The Save function is pretty straightforward but the Select statement takes a bit more explaining. In the Save function’s case, we can get the Type of the object at any time and discover what we should be selecting since we have our ClassManager already set up. In the case of the Select though, we don’t know what we’re suppose to be selecting. So in order to help, we use generics here. That way we can pull the Type class appropriately and pass that to our ClassManager to find out what to do.
Anyway, if we look back at the Provider class:
class Provider : IProvider
{
internal override bool Save(object Object)
{
Class TempClassDefinition = null;
try
{
TempClassDefinition = ClassManager[Object.GetType()];
}
catch { TempClassDefinition = null; }
return SQLBuilder.Save(ConnectionString, Object, TempClassDefinition, ClassManager);
}
internal override object SelectByID<T>(object IDValue)
{
Class TempClassDefinition = null;
try
{
TempClassDefinition = ClassManager[typeof(T)];
}
catch { TempClassDefinition = null; }
return SQLBuilder.SelectByID(ConnectionString, IDValue, TempClassDefinition, ClassManager);
}
}
You will notice that all it does is passes it on to the SQLBuilder class. The SQLBuilder class in turn simply creates an Insert/Select class depending on what is called and runs a function. Note that at this point Save only inserts items, but when Update is implemented it would check the ID and call the appropriate class here.
The Insert class uses the following functions to actually go about and insert the information:
public override void Run(object Object, SQLHelper Helper)
{
Type ObjectType = Object.GetType();
try
{
Helper.Command = _Class.OriginalType.Name + "_Insert";
Helper.CommandType = CommandType.StoredProcedure;
Helper.Open();
SetupProperties(Object, Helper, ObjectType);
object IDValue=Helper.ExecuteScalar();
SetID(Object, ObjectType, IDValue);
}
catch { }
finally { Helper.Close(); }
SetupLists(Object, Helper, ObjectType);
}
private void SetupLists(object Object, SQLHelper Helper, Type ObjectType)
{
foreach (IDataType Column in Columns)
{
if (Column != null && Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
string IDName=((List)Column).IDName;
SqlDbType IDType=((List)Column).IDType;
string DataName=((List)Column).DataName;
SqlDbType DataType=((List)Column).DataType;
int DataSize=((List)Column).Size;
PropertyInfo IDPropertyInfo = ObjectType.GetProperty(IDName);
object IDValue = IDPropertyInfo.GetValue(Object, null);
Helper.Command = Column.Name + "_Insert";
PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Attribute.Name);
object ListValue = PropertyInfo.GetValue(Object, null);
Type ListType = ListValue.GetType();
PropertyInfo IndexProperty = ListType.GetProperty("Item");
PropertyInfo CountProperty = ListType.GetProperty("Count");
int Count = (int)CountProperty.GetValue(ListValue, null);
for (int x = 0; x < Count; ++x)
{
try
{
Helper.Open();
Helper.AddParameter("@" + IDName, IDValue, IDType);
if (DataType == SqlDbType.NVarChar)
{
Helper.AddParameter("@" + DataName, (string)IndexProperty.GetValue(ListValue, new object[] { x }), DataSize);
}
else
{
Helper.AddParameter("@" + DataName, IndexProperty.GetValue(ListValue, new object[] { x }), DataType);
}
Helper.ExecuteNonQuery();
}
catch { }
finally { Helper.Close(); }
}
}
}
}
private void SetupProperties(object Object, SQLHelper Helper, Type ObjectType)
{
foreach (IDataType Column in Columns)
{
if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Name);
object Value = PropertyInfo.GetValue(Object, null);
if (Column is NVarChar && !Column.IsPrimaryKey)
{
foreach (IConstraint Constraint in Column.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + Column.Name, (string)Value, ((Size)Constraint).Length);
break;
}
}
}
else if (!Column.IsPrimaryKey)
{
Helper.AddParameter("@" + Column.Name, Value, Column.DataType);
}
}
}
}
private void SetID(object Object, Type ObjectType, object IDValue)
{
foreach (IDataType Column in Columns)
{
if (Column != null &&
!Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase) &&
Column.IsPrimaryKey)
{
PropertyInfo PropertyInfo = ObjectType.GetProperty(Column.Name);
if (Column is Int)
{
PropertyInfo.SetValue(Object, int.Parse(IDValue.ToString()), null);
}
else if (Column is UniqueIdentifier)
{
PropertyInfo.SetValue(Object, new Guid(IDValue.ToString()), null);
}
break;
}
}
}
The setup properties function uses some basic reflection to get each property’s value and inserts it as a parameter with the exception of lists (once again, these have to be handled seperately. We then run the stored procedure and get back the new ID (since we’re auto generating these from the database for now) and setting the ID field of the class. We then go through each list and insert it’s information. This is a bit more indepth as we have to get the List property, pull out a couple properties of the List (specifically Count and Item), and then set each item in the list up and run it seperately…
But when all is said and done, we can simply call Save and our item is saved to the database. Now what about selecting it back out?
public object SelectByID(object IDValue, SQLHelper Helper)
{
Helper.Command = _Class.OriginalType.Name + "_Select";
Helper.CommandType = CommandType.StoredProcedure;
object ClassInstance = null;
IDataType IDField = null;
IDField = FindIDField(IDField);
ClassInstance = LoadMainClass(IDValue, Helper, ClassInstance, IDField);
ClassInstance = LoadLists(IDValue, Helper, ClassInstance, IDField);
return ClassInstance;
}
private object LoadLists(object IDValue, SQLHelper Helper, object ClassInstance, IDataType IDField)
{
foreach (IDataType Column in Columns)
{
if (Column != null && Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo ColumnInfo = _Class.DerivedType.GetProperty(((List)Column).DataName);
Helper.Command = Column.Name + "_Select";
Helper.ClearParameters();
try
{
Helper.Open();
if (IDField is NVarChar)
{
foreach (IConstraint Constraint in IDField.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + IDField.Name, (string)IDValue, ((Size)Constraint).Length);
}
}
}
else
{
Helper.AddParameter("@" + IDField.Name, IDValue, IDField.DataType);
}
Helper.ExecuteReader();
ColumnInfo.SetValue(ClassInstance, Activator.CreateInstance(Column.Attribute.Type), null);
object CurrentList = ColumnInfo.GetValue(ClassInstance, null);
Type ListType = CurrentList.GetType();
while (Helper.Read())
{
object ListItemValue=Helper.GetParameter(((List)Column).DataName,null);
if (Column.Attribute.Type.GetGenericArguments()[0].FullName.Equals("System.Single", StringComparison.CurrentCultureIgnoreCase))
{
Column.Attribute.Type.InvokeMember("Add", BindingFlags.InvokeMethod, null, CurrentList, new object[] { float.Parse(ListItemValue.ToString()) });
}
else
{
Column.Attribute.Type.InvokeMember("Add", BindingFlags.InvokeMethod, null, CurrentList, new object[] { ListItemValue });
}
}
}
catch { }
finally
{
Helper.Close();
}
}
}
return ClassInstance;
}
private IDataType FindIDField(IDataType IDField)
{
foreach (IDataType Column in Columns)
{
if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
if (Column.IsPrimaryKey)
{
IDField = Column;
break;
}
}
}
return IDField;
}
private object LoadMainClass(object IDValue, SQLHelper Helper, object ClassInstance, IDataType IDField)
{
try
{
Helper.Open();
if (IDField is NVarChar)
{
foreach (IConstraint Constraint in IDField.Constraints)
{
if (Constraint is Size)
{
Helper.AddParameter("@" + IDField.Name, (string)IDValue, ((Size)Constraint).Length);
}
}
}
else
{
Helper.AddParameter("@" + IDField.Name, IDValue, IDField.DataType);
}
Helper.ExecuteReader();
if (Helper.Read())
{
ClassInstance = Activator.CreateInstance(_Class.DerivedType);
foreach (IDataType Column in Columns)
{
if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
{
PropertyInfo TempProperty = _Class.DerivedType.GetProperty(Column.Name);
if (TempProperty.PropertyType.FullName.Equals("System.Single", StringComparison.CurrentCultureIgnoreCase))
{
TempProperty.SetValue(ClassInstance, float.Parse(Helper.GetParameter(Column.Name, null).ToString()), null);
}
else
{
TempProperty.SetValue(ClassInstance, Helper.GetParameter(Column.Name, null), null);
}
}
}
}
}
catch { }
finally { Helper.Close(); }
return ClassInstance;
}
This is fairly similar. We first create our class object (using the Activator class) and then procede to load the main (non list) portions of the class. You’ll notice that there is a special case for floats. The reason for this is that they’re singles and we’re saving all of our floats as doubles within the database. This could be improved a bit by treating the various types as a bit more different instead of grouping them together.
Once that is loaded, we then go and load our lists. Once again we have to use a bit of reflection here to first create the list and then go and invoke the Add method on that list for each item. But that’s the only portions that may cause some confusion.
Anyway, that’s it really. At this point we send back our newly created object to the user. I would show you the update and delete code but I’m still working on it. So download the code, take a look, leave feedback, and happy coding.
HaterAidePart4.zip (656.09 kb)
Be the first to rate this post - Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5
If you have ever created an HTTP handler, you've most likely run into the annoying fact that the session state is not accessible. In fact most of the time you're going to run into a null reference exception and will take you a couple of hours to figure out what the heck is going on. However hope is not lost as you can turn on the session state for a handler.
public class MyHandler:IHttpHandler,IRequiresSessionState
Notice that last interface? That interface acts as a marker, letting the system know that the handler requires the session state for read and write access. We could just specify IReadOnlySessionState as well if we're only interested in reading the session state but not writing to it. So there you go, that's all that you need to do. So hopefully this helps someone out. Give it a try, leave feedback, and happy coding.
Be the first to rate this post - Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5
|
|