I just finished a presentation at SQL Saturday in Tampa, Florida, called SQL Server Data Access Tips & Techniques. During the presentation I presented numerous examples of simplifying the data access layer of an application by using several frameworks, o/r mappers, and code generators:
- Enterprise Library Data Access Application Block
- LINQ To SQL
- ADO.NET Entity Framework
- LightSpeed
- PLINQO ( via CodeSmith )
- SubSonic
- NHibernate
Surprisingly, many of the attendees were unfamiliar with the various tools.
Enterprise Library DAAB
One of the goals of the presentation was to help developers reduce the amount of code they needed to write for data access, particularly if they were still hand-coding ADO.NET.
In particular, one of the advantages of the Data Access Application Block in Enterprise Library is how productive it can be in helping you not only write less code but also write code that adheres to proven practices to assure better performance and less chances of resource leaks, etc.
We discussed an example of calling a stored procedure in a database agnostic way using Database Provider Factories in ADO.NET 2.0. The code can get a little verbose:
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand selectCommand = factory.CreateCommand())
{
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.CommandText = "SalesByCategory";
selectCommand.Connection = connection;
DbParameter categoryParameter = selectCommand.CreateParameter();
categoryParameter.ParameterName = "@CategoryName";
categoryParameter.Value = "Beverages";
DbParameter yearParameter = selectCommand.CreateParameter();
yearParameter.ParameterName = "@OrdYear";
yearParameter.Value = "1998";
selectCommand.Parameters.AddRange(new[] {categoryParameter, yearParameter});
connection.Open();
using (IDataReader dr = selectCommand.ExecuteReader())
{
while (dr.Read())
Console.WriteLine(string.Format("{0}: {1:c}", dr["ProductName"], dr["TotalPurchase"]));
}
}
}
Compare this to using the Data Access Application Block in Enterprise Library:
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader dr = db.ExecuteReader("SalesByCategory", "Beverages", "1998"))
{
while (dr.Read())
Console.WriteLine(string.Format("{0}: {1:c}", dr["ProductName"], dr["TotalPurchase"]));
}
Far less code and less to think about in terms of opening and closing connections, properly catching exceptions, etc.
Conclusion
If you find yourself hand-coding ADO.NET code in your .NET applications, don't! There are many tools that can help you be more productive and adhere to proven practices like the Enterprise Library Data Access Application Block.