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.
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!
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.
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>();
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.
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
//Assume that we already got an Employee object named emp from somewhere
SimpleSql.StoredProcedure("pInsertEmployee").Execute(emp);
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 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:
//Contrived example for demonstration purposes
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
{
/// <summary>
/// The QueryBuilder class uses the Builder Pattern
/// with the StoredProcedure and Query Objects.
/// This is what makes the fluent interface in SimpleSql possible.
/// </summary>
/// <typeparam name="T">Any class that inherits
/// from the BaseQuery class</typeparam>
///
public class QueryBuilder<T> where T : BaseQuery
{
protected internal T query = default(T);
/// <summary>
/// Public constructor that sets the query object
/// used (either StoredProcedure or Query)
/// </summary>
/// <param name="obj">The BaseQuery
/// or child of BaseQuery object being stored</param>
///
public QueryBuilder(T obj)
{
query = obj;
}
/// <summary>
/// This method is used to map the fields of an object
/// to the fields in a DbDataReader in Fetches and FetchAlls
/// </summary>
/// <param name="objectRecordMapper">The Func delegate to execute</param>
/// <returns>this</returns>
///
public QueryBuilder<T> Map(Func<DbDataReader, object> objectRecordMapper)
{
query.Map(objectRecordMapper);
return this;
}
/// <summary>
/// Set the time the query will wait for the command to complete
/// </summary>
/// <param name="timeout">time to wait</param>
/// <returns>this</returns>
///
public QueryBuilder<T> CommandTimeout(int timeout)
{
query.CommandTimeout = timeout;
return this;
}
/// <summary>
/// Use this in an existing transaction. Ideally for use
/// with the SimpleSql.CreateTransaction() method
/// </summary>
/// <param name="transaction">the
/// existing transaction to use</param>
/// <returns>this</returns>
///
public QueryBuilder<T> WithTransaction(DbTransaction transaction)
{
query.WithTransaction(transaction);
return this;
}
#region Action Endpoints
/// <summary>
/// This will fetch a single record from the DB and map it to a concrete object
/// </summary>
/// <typeparam name="O">The type of object
/// to map this query to</typeparam>
/// <returns>The object specified by the type parameter</returns>
///
public O Fetch<O>()
{
return query.Fetch<O>();
}
/// <summary>
/// This will map each record in the DB resultset to a concrete object
/// </summary>
/// <typeparam name="O">The type of object
/// to map this query to</typeparam>
/// <returns>A list of strongly type objects specified
/// by the type parameter</returns>
///
public List<O> FetchAll<O>()
{
return query.FetchAll<O>();
}
/// <summary>
/// Executes a query where you don't expect any results back
/// </summary>
///
public void Execute()
{
query.Execute();
}
/// <summary>
/// Executes a query that returns a single value
/// </summary>
/// <typeparam name="O">The type of value
/// that you want returned</typeparam>
/// <returns>The type specified by the type parameter</returns>
///
public O ExecuteScalar<O>()
{
return query.ExecuteScalar<O>();
}
/// <summary>
/// Fetches multiple resultSets
/// </summary>
/// <returns>A MultiResult object containing the collection of results</returns>
///
public MultiResult FetchMultiple()
{
return query.FetchMultiple();
}
/// <summary>
/// Gives you access to the underlying DbDataReader
/// to handle the records the way you want
/// </summary>
/// <returns>DbDataReader object</returns>
///
public DbDataReader FetchReader()
{
return query.FetchReader();
}
/// <summary>
/// Fetches a DataSet
/// </summary>
/// <returns>returns a DataSet</returns>
///
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.
//Code snippet from QueryBuilder class
public class QueryBuilder<T> where T : BaseQuery
{
protected internal T query = default(T);
/// <summary>
/// Public constructor that sets the query object
/// used (either StoredProcedure or Query)
/// </summary>
/// <param name="obj">The BaseQuery or child
/// of BaseQuery object being stored</param>
///
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!
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>();
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;
}
/*
Additional methods not shown......
*/
}
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.
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>();
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:
//ComplexEmployee Class makes use of the Address class
public class ComplexEmployee{
public Int32 EmployeeID { get; set; }
public String EmployeeFirstName { get; set; }
public String EmployeeLastName { get; set; }
//A more complex property
public Address EmployerAddress { get; set; }
}
//Holds all the address details
public class Address{
public String Address1 { get; set; }
public String City { get; set; }
public String State { get; set; }
public String Zip { get; set; }
}
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.
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.
//From Code above
MultiResult results = SimpleSql.......
//Get first resultset
Employee emp = results.Fetch<Employee>(0);
//Get second resultset
List<ComplexEmployee> cplxEmps =
results.FetchAll<ComplexEmployee>(1);
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.
//Declare a collection that will be filled with any output parameters
OutputParameterCollection opColl = null;
Employee emp = SimpleSql
.StoredProcedure("GetOneEmployeeWithOutput")
.AddParameter("@EmployeeID", 1)
.AddOutParameter("@DateHiredOutputParam", DbType.String)
.Fetch<Employee>(out opColl);
//I can call the output parameter in the collection by Name
DateTime dateHired = opColl.GetValue("@DateHiredOutputParam").Fetch<DateTime>();
//Or by Index
//DateTime dateHired = opColl.GetValue(0).Fetch<DateTime>();
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.
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.
//Have your class inherit from SimpleDAL
public class EmployeeDAL : SimpleDAL
{
/// <summary>
/// You can explicitly pass in the name of the ConnectionString
/// from the .config file via the constructor.
/// This will allow you to use this connection with any method
/// that uses the SimpleSql methods in this class.
/// Another benefit is that you can now use a Dependency Injection
// framework like StructureMap or Spring.Net etc.
/// to inject the name of the ConnectionString!
/// </summary>
/// <param name="connectConfigName">ConnectionString
/// name from the .config file</param>
///
public EmployeeDAL(string connectConfigName)
: base(connectConfigName)
{
}
/// <summary>
/// A Sample method that gets all employees from the DB
/// </summary>
/// <returns>List of employees</returns>
///
public List<Employee> GetEmployees()
{
List<Employee> employees = null;
try
{
//SimpleSql is built into the DAL so you
//can directly call Query or StoredProcedure
employees = StoredProcedure("pGetAllEmployees")
.FetchAll<Employee>();
}
catch (SimpleSqlException sse)
{
throw;
//I'm just throwing it up the method chain.
//Do whatever you want here to handle the error.
}
return employees;
}
}
There are three projects included with SimpleSql.
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!
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:
//C# 2.0 implementation of the MultiResult FetchAll method
List<T> resultset = new List<T>();
ArrayList results = resultList[index] as ArrayList;
//If this object isn't an ArrayList the value will be null
if (null != results){
foreach (Object r in (ArrayList)results)
{
resultset.Add((T)r);
}
}else{
//Assume its just one object, so add that one object into the list
resultset.Add((T)results);
}
return resultset;
Now, with var and Extension Methods, the code is a bit more compact (if you remove the comments).
//C# 3.5 implementation of the MultiResult FetchAll method
List<T> resultset = new List<T>();
//The new var keyword uses type inference to figure out what the actual type is
var results = resultList[index];
if (results is ArrayList)
{
//The Cast<T> Extension method save a few lines of looping code
resultset.InsertRange(0, ((ArrayList)results).Cast<T>());
}
else
{
resultset.Add((T)results);
}
return resultset;
Fetch or FetchAll call to the database ObjectSQLAdapter class and the Reflection.Emit code that would've prevented you from passing an object to Execute with any properties other than of type String | You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
