Introduction
I have been using CodeProject to further my knowledge of .NET for a few years now, and it is way past due that I give back! This is my first article. I had plans to write a number of articles in the past and have been beaten to the punch by many excellent articles on this site. While a data access framework is definitely nothing new, I think my oSo.SimpleSql (pronounced Oh So Simple SQL) framework has a couple of additions that the others don't. I make extensive use of several C# 2.0 and new 3.5 features such as Generics, Lambda Expressions, Extension methods as well as some useful patterns such as the Builder pattern and Fluent Interfaces. Hopefully, someone out in CodeProject-land finds this code or some of the associated concepts useful in their own projects.
All Data Access Frameworks have one thing in common that they are all trying to solve. They are all trying to make it as easy and painless as possible to get data out of and back into a data store which 99% of the time is a database. Many frameworks also try and hide the idiosyncrasies of each database behind a layer of abstraction that, in theory, allows you to easily swap out your data store for another (like going from SQL Server to MySQL, for instance). This abstraction is oftentimes used to additionally convert SQL results into objects that represent your business domain. I try to cover both easy data access and simple object relational mapping with SimpleSql.
Background
Why did I create SimpleSql? There are a myriad of frameworks like Microsoft's DAAB, LINQ to SQL, NHibernate, and the rising star SubSonic on the market now. Is there really a need for yet another one? I had been using an in-house data access framework/layer for a while that really only worked with SQL Server. I had planned to just update the framework to be a little more database agnostic and more closely mirror the types of projects I worked on. I seem to work on a lot of projects these days with Database Administrators that have already created a lot of the database structure. For security reasons, they like to make strict use of Stored Procedures and locked down views to access data. I wanted something that could integrate easily with existing code and database structures while having a super short learning curve for new developers coming on to any project using this framework. I started making a few changes here and there until it turned into a full scale rewrite. Hence, SimpleSql was born.
But, what about those other frameworks, you say? Well, I ruled out LINQ to SQL pretty quickly for several reasons. LINQ to SQL should've really been called LINQ to SQL Server since out of the box it only accesses one database. Also, Microsoft seems to be dropping support for it in favor of the next new Redmond technology, the Entity Framework. NHibernate is a really powerful ORM framework that can do pretty much anything. That's its biggest pro and con. Since it is so powerful, it has a bit of a learning curve. I wanted a data access framework that would allow developers who've never seen the framework to get up to speed very quickly.
SubSonic is the shiny new Object Relational Mapper (ORM) on the block. It is much simpler to use than many of the other ORM tools out there. But, like the other ORMs, it tries to emulate SQL through code while begrudgingly supporting Stored Procedures. SubSonic also works best if you let it control your database schema and associated classes. For new projects, this may be a good fit. But, if you have a lot of existing structure in place already and/or DB administrators that dictate a particular database schema and security, SubSonic won't be as good a fit. SubSonic also adds methods to your Plain Old C# Objects (POCOs) like "Save" to give them the ability to know how to save themselves to the database. I'm not a fan of this pattern. It makes it hard to use these same objects in different contexts and domains.
But, I don't want to sound too dismissive of SubSonic or any of the other frameworks. For instance, I do think SubSonic is very cool and has a ton of work put into it by some very smart guys. I plan to mimic much of its Visual Studio auto-generating code slickness for use in auto-generating my SimpleSql code! In the end, it was just about having a fun project to improve my .NET 3.5 skills!
A Few SimpleSql Benefits
- Keeps developers from having to write so much verbose ADO.NET code.
- It is database agnostic.
- It can create transactions without making the developer go back to ADO.NET. It also works with both TransactionScope and regular transactions.
- It works with output parameters and multiple resultsets, and can return strongly typed objects and collections.
- Is very easy to use.
Kudos
I have a number of people to thank on CodeProject for speeding up the development of my new data access framework. To cut out making so many initial Reflection calls, Herbrandson's Dynamic Code Generation article came in handy. I modified his code to make use of Generics everywhere it made sense. I also used a number of helper methods from Andrew Chan's High Performance Reflection ORM article, and modified them to meet the needs of my project. Some of the concepts in the sklivvz's SixPack CodeProject article also helped reinforce some of my design decisions. See the References at the bottom of the page for the links to all these articles.
Enough Talk, Where's the Code?
Basic Usage
You'll see a lot of frameworks with the method name "StoredProcedure" and I don't veer from that precedent :). There really isn't a better word out there to use.
using oSo
List<Employee> employees = SimpleSql
.StoredProcedure("GetAllEmployees")
.FetchAll<Employee>();
Feel the ORM!
This will call a Stored Procedure with no parameters, and will automagically fill in the public properties of each employee object for each record returned from the database. SimpleSql's Object Relational Mapping (ORM) capabilities make it a breeze to return objects and lists filled with database data. We'll look at more complex scenarios later. You can also make this same call using an inline query like so:
using oSo
List<Employee> employees = SimpleSql
.Query("Select * From Employees")
.FetchAll<Employee>();
StoredProcedure calls wouldn't be any good without the ability to specify parameters. Here's one with a couple of input parameters:
using oSo
Employee emp = SimpleSql
.StoredProcedure("pGetEmployee")
.AddParameter("@EmployeeID", 1)
.AddParameter("@EmployeeLastName", "Smith")
.Fetch<Employee>();
*Note: If no records are returned from a single Fetch<T>() call, a null is returned. If no records return from a FetchAll<T> call, an empty list with a count of zero is returned.
Save Me!
You can use the AddParameter methods like I did above to save and retrieve data with any database. But, if you're using SQL Server, it allows SimpleSql to discover the parameters that will be used in a Stored Procedure. This allows you to save your objects in a very simple and ORMish way. Saves like inserts and updates that don't require anything to be returned are handled using one of the Execute method overloads.
using oSo
SimpleSql.StoredProcedure("pInsertEmployee").Execute(emp);
That Object Doesn't Quite Relate
SimpleSql fills an object by matching the name of the DbDataReader field with a public property in your object. If the names match, the property's set with the database data. But, what happens when you have an existing object with a property that doesn't match the DbDataReader field name? SimpleSql gives you an easy way to handle this via the Column attribute.
using oSo.Attributes;
public class Employee{
public String EmployeeFirstName { get; set; }
[Column("EmployeeAge")]
public Int32 Age { get; set; }
}
Now, when SimpleSql fetches records, it will match the DbDataReader field with EmployeeAge to fill in the Age property. Since EmployeeFirstName doesn't have a Column attribute, it will use that name to match the database field. If the property name doesn't match and no Column attribute is specified, SimpleSql will simply ignore that property.
Fluent Interfaces, Builder Patterns, and Code, Oh My!
Fluent Interfaces is a term coined by Eric Evans and Martin Fowler. If you've worked with .NET for any amount of time, no doubt you have done something like this several times in your code:
String myString = "A SENTENCE I WANT TO LOWERCASE THAT ALSO CONTAINS AN ' APOSTROPHE.";
myString = myString.Replace("'", "").ToLower().Replace("a ", "A ");
The .NET String class uses the Builder pattern to make it possible to keep chaining method calls that return String. Fluent interfaces go one step beyond this by trying to make the chain of method calls more akin to a Domain Specific Language and read more like a sentence. Fluent Interfaces are harder to construct though, and require a bit of thought to get the methods names right. Here's the QueryBuilder class. It is one of the core classes in SimpleSql.
namespace oSo
{
public class QueryBuilder<T> where T : BaseQuery
{
protected internal T query = default(T);
public QueryBuilder(T obj)
{
query = obj;
}
public QueryBuilder<T> Map(Func<DbDataReader, object> objectRecordMapper)
{
query.Map(objectRecordMapper);
return this;
}
public QueryBuilder<T> CommandTimeout(int timeout)
{
query.CommandTimeout = timeout;
return this;
}
public QueryBuilder<T> WithTransaction(DbTransaction transaction)
{
query.WithTransaction(transaction);
return this;
}
#region Action Endpoints
public O Fetch<O>()
{
return query.Fetch<O>();
}
public List<O> FetchAll<O>()
{
return query.FetchAll<O>();
}
public void Execute()
{
query.Execute();
}
public O ExecuteScalar<O>()
{
return query.ExecuteScalar<O>();
}
public MultiResult FetchMultiple()
{
return query.FetchMultiple();
}
public DbDataReader FetchReader()
{
return query.FetchReader();
}
public DataSet FetchDataSet()
{
return query.FetchDataSet();
}
#endregion
}
}
There are a few things going on in this code, so let's take a closer look at QueryBuilder. The two main classes used in SimpleSql, namely StoredProcedure and Query, inherit from a base class called BaseQuery.
public class QueryBuilder<T> where T : BaseQuery
{
protected internal T query = default(T);
public QueryBuilder(T obj)
{
query = obj;
}
I am using Generics here to make sure that the class that QueryBuilder uses internally can be of any type as long as it inherits from the BaseQuery class at some point in its inheritance chain. The BaseQuery class does most of the heavy lifting for both the StoredProcedure and Query classes since most of the methods they use are common to both of them.
public QueryBuilder<T> CommandTimeout(int timeout)
{
query.CommandTimeout = timeout;
return this;
}
Notice the methods like CommandTimeout. After it sets the query object's CommandTimeout property, it returns itself. The "return this" is what allows us to chain other QueryBuilder methods together.
public O Fetch<O>()
{
return query.Fetch<O>();
}
You saw the Fetch() method used in examples earlier. Methods like Fetch() end the Builder pattern. I call them Action End Points (AEP) since these methods are either void or return a result from the database. All the AEP methods make use of classes from the System.Data.Common namespace to retrieve or persist data. This, theoretically, makes it possible to switch out the entire database by simply changing the provider name in the .config file!
But Where is the ConnectionString?
Since no connection string information was specified in any of the previous code samples, SimpleSql used the first connection string specified in the .config file. You can also pass the connection string by the name specified in the .config file, or explicitly pass all the connection string information.
using oSo
int employeeAge = SimpleSql
.StoredProcedure("pGetEmployeeAge",
"WebConfigConnectionName")
.AddParameter("@EmployeeID", 2)
.ExecuteScalar<int>();
This example returns a single value from a database strongly typed using Generics. No casting needed! You pass connection string parameters as a parameter of the StoredProcedure or Query constructor. If your connection string is not located in the .config file, you can pass the information in directly as well.
using oSo
string connString = "Data Source=.\SQLExpress;Initial Catalog=" +
"SimpleDb;User Id=SimpleUser;Password=SimplePassword;";
string provider = "System.Data.SqlClient";
int employeeAge = SimpleSql
.Query("Select EmployeeAge From dbo.Employee Where EmployeeID = 2",
connString, provider)
.ExecuteScalar<int>();
Can I Get an Extension?
I wanted all methods to go through the QueryBuilder class so I could chain method calls. But, some methods like "AddOutParameter" which adds an Output parameter are only specific to StoredProcedures and shouldn't show up in Intellisense when chaining method calls for Query. Extension methods to the rescue! Click here for more information about this great C# 3.5 feature.
public static class QueryBuilderExtensionMethods
{
public static QueryBuilder<StoredProcedure>
AddOutParameter(this QueryBuilder<StoredProcedure> qb,
string parameterName, object value)
{
((StoredProcedure)qb.query).AddOutputParameter(parameterName, value);
return qb;
}
}
The QueryBuilderExtensionMethods class allows me to define QueryBuilder methods that only show up in the Intellisense dropdown when I'm using the StoredProcedure class! Notice that QueryBuilder is typed directly to StoredProcedure and the internal Query object is cast to StoredProcedure to ensure that the Query object cannot see this method. The object "qb" is returned and is the same as returning "this" in the QueryBuilder class.
You're Injecting Me With What?
Try to mitigate the possibility of succumbing to SQL Injection Attacks when using inline queries. If the values that are part of your SQL query are coming from untrusted sources (i.e. the user via a form, a webservice, etc..), you should use SQL parameters to prevent one of the values from compromising your database. See the example below:
using oSo
using System.Data;
Employee emp = SimpleSql
.Query("Select EmployeeFirstName, EmployeeAddress
From Employee Where EmployeeID =
@EmployeeID AND EmployeeLastName = @TheLastName")
.AddParameter("@EmployeeID", 1, DbType.Int32)
.AddParameter("@EmployeeLastName", "Goodwrench", DbType.String, 50)
.Fetch<Employee>();
More Complex ORM Scenarios
SimpleSql can automagically sets the values of public properties that are strings or simple value types. But, in the real world, we have objects that contain properties that can contain other objects among other things. To keep SimpleSql "simple", SimpleSql handles all complex ORM scenarios through its Map method. Here are a couple of simple domain POCO objects:
public class ComplexEmployee{
public Int32 EmployeeID { get; set; }
public String EmployeeFirstName { get; set; }
public String EmployeeLastName { get; set; }
public Address EmployerAddress { get; set; }
}
public class Address{
public String Address1 { get; set; }
public String City { get; set; }
public String State { get; set; }
public String Zip { get; set; }
}
Pledging Lambda, Lambda, Lambda
Now, let's hydrate the ComplexEmployee object using SimpleSql.
using oSo
ComplexEmployee ce =
SimpleSql
.StoredProcedure("pGetEmployee")
.AddParameter("@EmployeeID", 1)
.Map(reader => {
return new ComplexEmployee()
{
EmployeeID = Convert.ToInt32(reader["EmployeeID"]),
EmployeeFirstName = Convert.ToString(reader["EmployeeFirstName"]),
EmployeeLastName = Convert.ToString(reader["EmployeeLastName"]),
EmployerAddress = new Address()
{
Address1 = Convert.ToString(reader["EmployeeAddress"]),
City = Convert.ToString(reader["EmployeeCity"]),
State = Convert.ToString(reader["EmployeeState"]),
Zip = Convert.ToString(reader["EmployeeZip"])
}
};
}).Fetch<ComplexEmployee>();
As you saw earlier in the QueryBuilder code, the Map() method takes a Func Delegate as a parameter. This allows us to use a Lambda Expression to define how the database data maps to the ComplexEmployee object. Also, notice that I made use of other .NET 3.x goodies like Automatic Properties and Object Initialization.
Dealing with Multiple Resultsets and Output Parameters
You may not make use of these two features too often. But, I wanted these features to be easily handled and strongly typed in SimpleSql for the times that you do need to use them. Let's first look at handling multiple resultsets:
using oSo;
using oSo.Results;
MultiResult results =
SimpleSql
.StoredProcedure("pFetchMultipleEmployeeResults")
.AddParameter("@EmployeeID", 1)
.Map(reader => {
return new Employee(){
FirstName = reader["EmployeeFirstName"].ToString()
, LastName = reader["EmployeeLastName"].ToString()
};
}).Map(reader => {
string phoneNum = (Convert.IsDBNull(reader["EmployeePhone"])) ?
"Unlisted" :
Convert.ToString(reader["EmployeePhone"]);
return new ComplexEmployee()
{
EmployeeFirstName = reader["EmployeeFirstName"].ToString()
, EmployeeLastName = reader["EmployeeLastName"].ToString()
EmployerAddress = new Address()
{
Address1 = Convert.ToString(reader["EmployeeAddress"]),
City = Convert.ToString(reader["EmployeeCity"]),
Phone = phoneNum
}
};
}).FetchMultiple();
When you call a Stored Procedure or query that returns multiple resultsets, the results come back in a MultiResult object. Each Map() method deals with one resultset. Use multiple Map() methods to handle multiple resultsets. Notice I didn't name this class something like ResultsCollection. I wanted to make sure people didn't get in the mindset that they should use a foreach to loop through the results. Each result contained in the MultiResult object should be retrieved in the order they were mapped, starting with zero.
Each Result in a MultiResult is Strongly Typed
MultiResult results = SimpleSql.......
Employee emp = results.Fetch<Employee>(0);
List<ComplexEmployee> cplxEmps =
results.FetchAll<ComplexEmployee>(1);
It's All About the Output
SimpleSql handles output parameters through an "out" parameter on the Fetch method. Many of the other AEP methods have an out parameter overload as well.
OutputParameterCollection opColl = null;
Employee emp = SimpleSql
.StoredProcedure("GetOneEmployeeWithOutput")
.AddParameter("@EmployeeID", 1)
.AddOutParameter("@DateHiredOutputParam", DbType.String)
.Fetch<Employee>(out opColl);
DateTime dateHired = opColl.GetValue("@DateHiredOutputParam").Fetch<DateTime>();
I Need You to Commit or I'll Have to Roll on Back
Although I'm not a real fan of handling transactions through code, I know this is unavoidably necessary from time to time. SimpleSql can use .NET 2.0's TransactionScope, or it can create and use regular transactions.
using (SimpleTransaction st = SimpleSql.CreateTransaction())
{
try
{
SimpleSql.StoredProcedure("pInsertEmployee")
.AddParameter("@EmployeeFirstName", "RollBack")
.AddParameter("@EmployeeLastName", "Candidate")
.AddParameter("@EmployeeAddress", "1 Rollback Lane")
.AddParameter("@EmployeeCity", "Los Angeles")
.AddParameter("@EmployeeState", "CA")
.AddParameter("@EmployeeZip", "90245")
.WithTransaction(st.Transaction)
.Execute();
SimpleSql.StoredProcedure("pInsertEmployee")
.AddParameter("@EmployeeFirstName", "Thrown")
.AddParameter("@EmployeeLastName", "Out")
.AddParameter("@EmployeeAddress", "2 Left Back Way")
.AddParameter("@EmployeeCity", "Los Angeles")
.AddParameter("@EmployeeState", "CA")
.AddParameter("@EmployeeZip", "90047")
.WithTransaction(st.Transaction)
.Execute();
st.Commit();
}
catch (SimpleSqlException sse)
{
st.Rollback();
Debug.WriteLine(sse.Message);
}
}
Instead of having to go back to ADO.NET to create a transaction, SimpleSql introduces the SimpleTransaction class. This class makes use of the Dispose pattern to close out the connection after the transaction is done. The CreateTransaction method has several overloads to specify connection string information. Also, note that all StoreProcedures participating in the Transaction will share the same connection information.
Code De-Coupling and the Case of the Proper Data Access Layer
To enable clean code separation and enable Unit Testing (not to mention proper architecture), I have also created a Data Access Layer (DAL) that uses SimpleSql. This will isolate all database calls to the DAL so you won't have more junior team members trying to use SimpleSql in code-behinds or *gasp* in the .aspx page itself! The DAL also gives you the ability to specify the connection string once through the constructor and use it for every data access method. Setting the connection string via the constructor also allows you to set the connection using a Dependency Injection framework like StructureMap.
public class EmployeeDAL : SimpleDAL
{
public EmployeeDAL(string connectConfigName)
: base(connectConfigName)
{
}
public List<Employee> GetEmployees()
{
List<Employee> employees = null;
try
{
employees = StoredProcedure("pGetAllEmployees")
.FetchAll<Employee>();
}
catch (SimpleSqlException sse)
{
throw;
}
return employees;
}
}
Included in the Zip Download
There are three projects included with SimpleSql.
- The fully commented SimpleSql source code including the example SQL Server Express 2005 database used. I also generated the SQL scripts if you wish to install the database on another version of SQL Server.
- Unit Tests were done using MSTest so people not familiar with Unit Testing wouldn't have to download and install another tool besides VS 2008. But, I would recommend XUnit if you have a choice.
- Last, but not least, I have included a quick sample Web Forms project that shows how to use the SimpleSql DAL. I wanted to do an MVC project, but I didn't want to get people who haven't been exposed to ASP.NET MVC mired down in Views, Controllers, and Routes just to show how you get data from your database to your ASPX page via SimpleSql!
Using SimpleSql in Your Project
Just make a reference to oSo.dll, put the "using oSo" and "using oSo.Exceptions" statements at the top of your class file, and have a go at it!
Points of Interest
This was fun making use of a lot of C# 3.5 features in one project. While I know some people love the new var keyword, I have not been so enamored to have this littered about my code. But, among the other fun things in the code, I finally found what I consider to be a very good use for var!
When returning a List<T> as one of the results from a multiple resultset, I strongly type the return. But, before the result is strongly typed, it is either stored in an object for a single result or in an ArrayList for multiple results. When the user tells SimpleSql the actual type, it is then converted to the proper type. Before the var keyword, I would've to use code like this:
List<T> resultset = new List<T>();
ArrayList results = resultList[index] as ArrayList;
if (null != results){
foreach (Object r in (ArrayList)results)
{
resultset.Add((T)r);
}
}else{
resultset.Add((T)results);
}
return resultset;
Now, with var and Extension Methods, the code is a bit more compact (if you remove the comments).
List<T> resultset = new List<T>();
var results = resultList[index];
if (results is ArrayList)
{
resultset.InsertRange(0, ((ArrayList)results).Cast<T>());
}
else
{
resultset.Add((T)results);
}
return resultset;
History
- 7 June, 2009: Initial version
- 8 June, 2009: Re-worded some text for clarity
- 9 June, 2009: Fixed some typos
- 11 June, 2009: Updated text to explain what happens when no records return from a
Fetch or FetchAll call to the database - 16 June, 2009: Updated source code to allow for parameterized queries. Also updated the article to mention this and make reference to SQL Injection attacks.
