In .NET 1.1, I tried the original MS Data Access Application Block’s SqlHelper (you can still download it here). It was great for most of the common uses, but was lacking in some areas. The consuming code looked sloppy and encouraged blind faith that database objects never changed. It also didn’t support transactions as I would have liked, and didn’t support my obsession with custom entities. I started out writing an extension library that wrapped SqlHelper, but that felt very wrong… wrapping the ADO.NET wrapper (SqlHelper). I ended up writing my own version of SqlHelper called SqlHelper (nice name, eh?). You see, at this time I was getting over a bad relationship with a series of ORM products that had a negative effect on my productivity. I decided to revolt with good ol’ fashion data access methods that have never let us down.
The only thing worse than my ORM experience was the disgusting over-use of DataSet and DataTable. For my dollar, DataReader is where it’s at. I agree that using the reader is slightly more dangerous in the hands of an inexperienced or inattentive developer (did you know you have to close the reader when you’re done with it??). Nothing can compare with the speed and flexibility of the reader, which is why DataSet and DataAdapter use it at their core. If you are working with custom entities, instead of DataSets and DataTables, you would be crazy to not use the DataReader.
My SqlHelper worked in conjunction with my DataAccessLayer class that defined a few delegates that made reader-to-object-mapping a simple task. Once the mapping methods were written to be used with the delegates, which returned object or System.Collections.CollectionBase because we did not yet have generics (can you imagine??), you simply called the SqlHelper to do all of the hard work. SqlHelper did not implement all of the craziness that the original version contained. It was a short 450 lines of code that did nothing but access data in a safe and reliable way. In the example below, we have the GenerateDocumentFromReader method that is used by the GenerateObjectFromReader delegate. When SqlHelper.ExecuteReaderCmd is called, the delegate is passed in to map the reader results to my object… in this case a Document.
// Object generation method
private static object GenerateDocumentFromReader(IDataReader returnData)
{
Document document = new Document();
if (returnData.Read())
{
document = new Document(
(int)returnData["DocumentId"],
(byte[])returnData["DocumentBinary"],
returnData["FileName"].ToString(),
returnData["Description"].ToString(),
returnData["ContentType"].ToString(),
(int)returnData["FileSize"],
returnData["MD5Sum"].ToString(),
(bool) returnData["EnabledInd"],
(int)returnData["CreatorEmpId"],
Convert.ToDateTime(returnData["CreateDt"]),
(int)returnData["LastUpdateEmpId"],
Convert.ToDateTime(returnData["LastUpdateDt"]));
} return document;
}
public static Document GetDocumentByDocumentId(int documentId)
{
SqlCommand sqlCmd = new SqlCommand();
SqlHelper.SetCommandArguments(sqlCmd, CommandType.StoredProcedure, "usp_Document_GetDocumentByDocumentId");
SqlHelper.AddParameterToSqlCommand(sqlCmd, "@DocumentId", SqlDbType.Int, 0, ParameterDirection.Input, documentId);
DataAccessLayer.GenerateObjectFromReader gofr = new DataAccessLayer.GenerateObjectFromReader(GenerateDocumentFromReader);
Document document = SqlHelper.ExecuteReaderCmd(sqlCmd, gofr) as Document;
return document;
}
This worked wonderfully for years. After converting, I couldn’t imagine a project that used ORM, DataSets, or DataTables again. I’ve been on many 1.1 projects since writing my SqlHelper in 2004, and I have successfully converted them all. In early 2006, MS graced us with .NET 2.0. Generics, System.Transactions, and partial classes changed my life. In my first few exposures to generics, like Vinay “the Generic Guy” Ahuja’s 2005 Jax Code Camp presentation and Juval “My Hero” Lowy’s MSDN article “An Introduction to Generics”, I listened/read and pondered the millions of uses of generics. I adapted my SqlHelper heavily to use these new technologies and morphed it into something else that closely represented the newest version of the DAAB, Enterprise Library 3.
By this point, I wanted to convert to Enterprise Library. It was far better than the simple SqlHelper. It had better transaction support, though I don’t know if that included System.Transactions. I could have put my object generation extensions on top of it and it would have worked well for years. On home projects I had already converted to use EntLib. At work I was not so lucky. The deep stack trace when something went wrong scared everyone, and that is still a fear for those starting out in EntLib today. To ease the fears, I just created my replacement to SqlHelper… the Database class.
I used a lot of the same naming conventions as Enterprise Library. In fact, much of the consuming code was nearly identical (except for the fact that it did not implement the provider pattern and worked only with SQL Server). This was in anticipation of a quick adoption of Enterprise Library 3 in the workplace. Kind of a “see… not so bad” move on my part. Just like EntLib, you created a Database class using the DatabaseFactory that used your default connection string key. Commands and parameters were created and added with methods off of the Database class. Aside from the SqlCommand/DbCommand, everything looked and felt the same, but came in a small file with only 490 lines of code instead of 5 or more projects with 490 files. Using it felt the same, too. Only my object/collection generation extensions looked different from the standard reader, scalar, dataset routines. Below is the same code from above using the Database class and related classes to create a Document from a reader.
// Object generation method
private static Document GenerateDocumentFromReader(IDataReader returnData)
{
Document document = new Document();
if (returnData.Read())
{
document = new Document(
GetIntFromReader(returnData, "DocumentId"),
GetIntFromReader(returnData, "DocumentTypeId"),
GetStringFromReader(returnData, "DocumentTypeName"),
GetByteArrayFromReader(returnData, "DocumentBinary"),
GetStringFromReader(returnData, "FileName"),
GetStringFromReader(returnData, "Description"),
GetStringFromReader(returnData, "ContentType"),
GetIntFromReader(returnData, "FileSize"),
GetStringFromReader(returnData, "MD5Sum"),
GetStringFromReader(returnData, "CreatorEmpID"),
GetDateTimeFromReader(returnData, "CreateDt"),
GetStringFromReader(returnData, "LastUpdateEmpID"),
GetDateTimeFromReader(returnData, "LastUpdateDt"));
}
return document;
}
public static Document GetDocumentByDocumentId(int documentId)
{
Database db = DatabaseFactory.CreateDatabase(AppSettings.ConnectionStringKey);
SqlCommand sqlCmd = db.GetStoredProcCommand("usp_Document_GetDocumentByDocumentId");
db.AddInParameter(sqlCmd, "DocumentId", SqlDbType.Int, documentId);
GenerateObjectFromReader<Document> gofr = new GenerateObjectFromReader<Document>(GenerateDocumentFromReader);
Document document = CreateObjectFromDatabase<Document>(db, sqlCmd, gofr);
return document;
}
This, too, worked great for years. Other than a brief period in 2007 when I tried to wrap all of my data access code with WCF services, .NET 3.0 came and went with no changes to my data access methodology. In late 2007, I had lost all love of my SqlHelper and my Database/DataAccessLayer classes. With .NET 3.5 and Enterprise Library 4.0, I no longer felt the need to roll my own. .NET now had extension methods for me to extend Enterprise Library however I pleased. Enterprise Library supported System.Transactions making its use a dream if behind a WCF service that allowed transaction flow. With a succinct 190 lines of extension code, I had it made in the shade with Enterprise Library 4.0. In fact, I haven’t used anything since.
The consuming code was almost exactly the same. You’ll notice the SqlCommand has changed to DbCommand. The SqlDbType has changed to DbType. Other than that, it feels and works the same.
// Object generation method
private static Document GenerateDocumentFromReader(IDataReader returnData)
{
Document document = new Document();
if (returnData.Read())
{
document = new Document(
returnData.GetInt32("DocumentId"),
returnData.GetInt32("DocumentTypeId"),
returnData.GetString("DocumentTypeName"),
returnData.GetByteArray("DocumentBinary"),
returnData.GetString("FileName"),
returnData.GetString("Description"),
returnData.GetString("ContentType"),
returnData.GetInt32("FileSize"),
returnData.GetString("MD5Sum"),
returnData.GetString("CreatorEmpID"),
returnData.GetDateTime("CreateDt"),
returnData.GetString("LastUpdateEmpID"),
returnData.GetDateTime("LastUpdateDt"));
}
return document;
}
public static Document GetDocumentByDocumentID(int documentId)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("usp_Document_GetDocumentByDocumentId");
db.AddInParameter(cmd, "DocumentID", DbType.Int32, documentId);
GenerateObjectFromReader<Document> gofr = new GenerateObjectFromReader<Document>(GenerateDocumentFromReader);
Document document = db.CreateObject<Document>(cmd, gofr);
return document;
}
With a full suite of unit test projects available for download with the Enterprise Library source files, the fear should be abated for the remaining holdouts. Getting started is as easy as including two DLL references, and adding 5 lines of config. You can’t beat that!
I downloaded Enterprise Library 5 last week. I’ve been making use of new features such as result set mapping (eliminating the need for my object generation extensions), parameter mapping, and accessors that bring them all together. There’s a bunch of inversion of control features in place as well. I think I’ll be quite comfortable in my new EntLib5 home.
