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)
1e70ed79-0c4a-46f4-a1ce-019e6a00720d|2|5.0