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
Part 6 – Many to Many and Many to One mappings
If you’ve yet to read the previous portions of this series, this one is just going to be confusing. So read the previous ones. Anyway, this is the final entry in the series. Today I cover select statements and also the cache. In other words, this is a long one (potentially). Anyway, let’s start with a small recap.
HaterAide was started to handle my ORM needs since I wasn’t happy with most of the options out there. The system uses a base class, reflection, and generics to map a class to a set of tables in a database. Database and table where covered along with the creation/use of select, insert, delete, and update stored procedures. In otherwords our basic functionality is covered at this point with one exception. The only select statement that the code covers is when you know the ID of the object that you want. Since that’s almost never the case, let’s look at how to go about getting something a bit more complex.
If you look at the various ORMs out there, most of them out there require you to either write SQL or their own query language to get anything remotely complex out of them. And since I’m trying to hide as much of the back end as possible, I decided to ignore this route. Instead I came up with a set of objects to help me out with queries.
public class And<T> : IClause<T>
{
public And(IClause<T> WhereStatement1, IClause<T> WhereStatement2)
: base()
{
_WhereStatement1 = WhereStatement1;
_WhereStatement2 = WhereStatement2;
}
protected IClause<T> _WhereStatement1 = null;
protected IClause<T> _WhereStatement2 = null;
internal override void SetupSQL(SQLHelper Helper,Class Class)
{
_WhereStatement1.SetupSQL(Helper,Class);
_WhereStatement2.SetupSQL(Helper,Class);
}
public override string ToString()
{
StringBuilder Builder = new StringBuilder(_ColumnName);
Builder.Append("(" + _WhereStatement1.ToString());
Builder.Append(" AND ");
Builder.Append(_WhereStatement2.ToString() + ")");
return Builder.ToString();
}
}
public class Or<T> : IClause<T>
{
public Or(IClause<T> WhereStatement1, IClause<T> WhereStatement2)
: base()
{
_WhereStatement1 = WhereStatement1;
_WhereStatement2 = WhereStatement2;
}
protected IClause<T> _WhereStatement1 = null;
protected IClause<T> _WhereStatement2 = null;
internal override void SetupSQL(SQLHelper Helper,Class Class)
{
_WhereStatement1.SetupSQL(Helper,Class);
_WhereStatement2.SetupSQL(Helper,Class);
}
public override string ToString()
{
StringBuilder Builder = new StringBuilder(_ColumnName);
Builder.Append("(" + _WhereStatement1.ToString());
Builder.Append(" OR ");
Builder.Append(_WhereStatement2.ToString() + ")");
return Builder.ToString();
}
}
public class Where<T> : IClause<T>
{
public Where(Expression<Func<T, object>> Expression, HaterAide.SQL.Enums.Operators Constraint, object Value)
: base(Expression, Constraint, Value)
{
}
public Where(Expression<Func<T, object>> Expression, HaterAide.SQL.Enums.Operators Constraint, object Value1, object Value2)
: base(Expression, Constraint, Value1)
{
_Value2 = Value2;
}
protected object _Value2 = null;
internal override void SetupSQL(SQLHelper Helper,Class Class)
{
if (_ColumnType.FullName.Equals("System.String", StringComparison.CurrentCultureIgnoreCase))
{
if (_Constraint == Enums.Operators.Between)
{
Helper.AddParameter("@" + _ColumnName + "1", (string)_Value, 255);
Helper.AddParameter("@" + _ColumnName + "2", (string)_Value2, 255);
}
else
{
Helper.AddParameter("@" + _ColumnName, (string)_Value, 255);
}
}
else
{
if (_Constraint == Enums.Operators.Between)
{
Helper.AddParameter("@" + _ColumnName + "1", _Value, GlobalFunctions.GetSQLType(_ColumnType));
Helper.AddParameter("@" + _ColumnName + "2", _Value2, GlobalFunctions.GetSQLType(_ColumnType));
}
else
{
Helper.AddParameter("@" + _ColumnName, _Value, GlobalFunctions.GetSQLType(_ColumnType));
}
}
}
public override string ToString()
{
StringBuilder Builder=new StringBuilder(_ColumnName);
if (_Constraint == Enums.Operators.Equals)
{
Builder.Append("=");
}
else if (_Constraint == Enums.Operators.GreaterThan)
{
Builder.Append(">");
}
else if (_Constraint == Enums.Operators.GreaterThanOrEqual)
{
Builder.Append(">=");
}
else if (_Constraint == Enums.Operators.LessThan)
{
Builder.Append("<");
}
else if (_Constraint == Enums.Operators.LessThanOrEqual)
{
Builder.Append("<=");
}
else if (_Constraint == Enums.Operators.Like)
{
Builder.Append(" LIKE ");
}
else if (_Constraint == Enums.Operators.NotEqual)
{
Builder.Append("<>");
}
if (_Constraint == Enums.Operators.Between)
{
Builder.Append(" BETWEEN ");
Builder.Append("@" + _ColumnName + "1");
Builder.Append(" AND ");
Builder.Append("@" + _ColumnName + "2");
}
else
{
Builder.Append("@" + _ColumnName);
}
return Builder.ToString();
}
}
There are a couple of enums as well but these classes are all that’s really needed for a simple query. The where clause takes in an expression just like the mapping class, an Operator (equals, less than, like, etc.), and a value to compare it to (in the case of between, it takes two values). These can then be joined together with ANDs and ORs to form more complex queries. So you want to find the brown widget that has the blue handle? No problem. What about ordering the items? That’s where this class comes in.
public class OrderBy<T> : IClause<T>
{
public OrderBy(Expression<Func<T, object>> Expression, Direction Direction, IClause<T> NestedOrderBy)
: base(Expression, Enums.Operators.Equals, null)
{
this._NestedOrderBy = NestedOrderBy;
this._Direction = Direction;
}
protected IClause<T> _NestedOrderBy;
protected Direction _Direction;
public override string ToString()
{
StringBuilder Builder = new StringBuilder();
Builder.Append(_ColumnName);
if (_Direction == Direction.Ascending)
{
Builder.Append(" ASC");
}
else
{
Builder.Append(" DESC");
}
if (_NestedOrderBy != null)
{
Builder.Append("," + _NestedOrderBy);
}
return Builder.ToString();
}
}
Very simple, uses the same unary expression set up. It also allows for nested items, with the outside items having more weight than the nested OrderBy objects. And all of this gets inputted to a new select function, SelectWhere, that is viewable in the code below. And just like the previous functions in the Session class, it calls the provider, which calls the SQL builder, which calls a newly created class.
internal class SelectWhere:IStatement
{
public SelectWhere(Class Class, ClassManager Manager)
: base(Class, Manager)
{
}
public List<T> Select<T>(SQLHelper Helper, IClause<T> WhereClause, IClause<T> OrderByClause)
{
Session TempSession = Factory.CreateSession();
List<T> ReturnList = new List<T>();
try
{
StringBuilder Builder=new StringBuilder("SELECT "+_Class.IDField.Name+" FROM "+_Class.OriginalType.Name);
if (WhereClause != null)
{
Builder.Append(" WHERE " + WhereClause.ToString());
}
if (OrderByClause != null)
{
Builder.Append(" ORDER BY " + OrderByClause.ToString());
}
Helper.Command = Builder.ToString();
Helper.CommandType = CommandType.Text;
Helper.ClearParameters();
Helper.Open();
WhereClause.SetupSQL(Helper, _Class);
Helper.ExecuteReader();
while (Helper.Read())
{
object ID = Helper.GetParameter(_Class.IDField.Name, null);
T TempObject = default(T);
TempSession.Select<T>(ID, out TempObject);
ReturnList.Add(TempObject);
}
}
catch { }
finally { Helper.Close(); }
return ReturnList;
}
}
And as you can see, all the class does is runs a query to find a list of objects that fullfill the query. So that’s it for basic queries. For anything more complex, I realized that I needed to be able to create my own stored procedures and call them. That’s even simpler.
internal class SelectStoredProcedure : IStatement
{
public SelectStoredProcedure(Class Class, ClassManager Manager)
: base(Class, Manager)
{
}
public List<T> Select<T>(SQLHelper Helper, List<IVariable> Variables, string StoredProcedure)
{
Session TempSession = Factory.CreateSession();
List<T> ReturnList = new List<T>();
try
{
Helper.Command = StoredProcedure;
Helper.CommandType = CommandType.StoredProcedure;
Helper.ClearParameters();
Helper.Open();
foreach(IVariable Variable in Variables)
{
Variable.SetupSQL(Helper);
}
Helper.ExecuteReader();
while (Helper.Read())
{
object ID = Helper.GetParameter(_Class.IDField.Name, null);
T TempObject = default(T);
TempSession.Select<T>(ID, out TempObject);
ReturnList.Add(TempObject);
}
}
catch { }
finally { Helper.Close(); }
return ReturnList;
}
}
The function just needs to know the name of the stored procedure and a list of variables.
public class Variable:IVariable
{
public Variable(string Name, object Value,SqlDbType DataType)
: base(Name, Value,DataType)
{
}
internal override void SetupSQL(SQLHelper Helper)
{
Helper.AddParameter(_Name, _Value, _DataType);
}
}
The variable class is just a holder for the name of the field, the value, and the SQL type. That’s it. Now if you paid attention, you’ll notice that each of these approaches only query the ID fields for the objects. Once it has the ID field, it calls the select function that already exists. The main reason for this is the simple fact that I’m lazy and didn’t want to rewrite all that code again. The second reason is how I’ve set up the cache.
The caching system that I’ve set up uses the same provider model that the SQL uses (so I’m going to skip that portion). Instead I’m just going to show you the actual class in charge of caching.
internal class ASPNetCache:ICache
{
public override bool Exists(string Name)
{
try
{
if (HttpContext.Current.Cache[Name] != null)
{
return true;
}
}
catch { }
return false;
}
public override void Save(string Name, object Value)
{
if (Exists(Name))
{
HttpContext.Current.Cache[Name] = Value;
}
else
{
HttpContext.Current.Cache.Insert(Name, Value, null,
System.Web.Caching.Cache.NoAbsoluteExpiration,
TimeSpan.FromHours(1.0));
}
}
public override void Delete(string Name)
{
if (Exists(Name))
{
HttpContext.Current.Cache.Remove(Name);
}
}
public override object this[string Name]
{
get { return HttpContext.Current.Cache[Name]; }
}
}
As you can see there are only three functions and one property. Save, Delete, and a retrieve. I like to keep code simple when possible and this was about as simple as I could make it. So what does this have to do with everything going through the Select function? Well let’s look at a scenario.
Let’s say that you have a table that holds three items. I make a query against their IDs, which thanks to our cache, places them in there without too much of an issue. Now let’s say that I make a query that asks the system for two of those items. The cache is rather dumb so unless we know before hand that the query is going to return something already in memory, we’re left with making the query and storing that in memory. So now we have 5 objects in memory. Now let’s say we update one of the objects and the object is stored twice in the sytem. When we save, it’s easy to tell that the item is there the first time because we queried against the ID, but what about the other instance? So either we search all of the objects in all of the stored queries, or I have to make that cache a lot smarter, OR I simply force everything through that select statement and simply store the information once. I chose the easy route.
So we delete the item from the cache when we delete from the database, update it when we save, and store it in the database when we call select. That’s it, we’re done. After the cache is in place, we have a library that will work as a simple ORM. I have no freakin’ clue how well it will do in the real world but, hey, I just wanted to see what went into making an ORM. Although I do have a project lined up where I plan to use it and improve upon it. I even see some areas where I could improve the system (and I’m sure everyone else out there sees the giant logic holes and issues that I’ve set up for myself as well). Anyway, download the code, take a look, leave feedback, and happy coding.
HaterAidePart7.zip (1,004.79 kb)
4e48de4c-bdc2-40f4-b1c6-c72b3eb82558|1|5.0