In a simple application architecture with three layers there is always a layer that queries data from the database, extract, shape it to some CLR typed entities, push it to next layers that initiates it. Right now we get plenty of frameworks to get this job done. But those who are not fond of ADO.NET Datasets, nHibernate, LINQ-to-SQL, EF etc. will usually stick with the classic old school approach of handling CRUDs in data access layer.
You will see how I have used the Classic DAL Helpers (from CodePlex.com) to get this job done in a much cleaner and easier way. In this article, I will try to demonstrate following topics with samples;
- Creating and handling stored commands.
- Materializing relational data into CLR types.
- Using template classes to reduce repetitive tasks.
The code samples are based on the Mini NorthWind project which have 3 main classes:
In a traditional approach, code to call a stored procedure in the database (with proper connection handling), extract relational data, create set of objects and identities could be very large even with rapid tools have been used such as Data adapters, EF, LINQ-to-SQL. Here is what I had to write:
public List<Category> GetAllCategories()
{
var connString = ConfigurationManager.ConnectionStrings["SampleModel"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
using (var comm = conn.CreateCommand())
{
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "dbo.GetAllCategories";
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
try
{
using (var reader = comm.ExecuteReader())
{
var cats = new List<Category>();
while (reader.Read())
{
var idOrdinal = reader.GetOrdinal("cid");
var nameOrdinal = reader.GetOrdinal("name");
if (!reader.IsDBNull(idOrdinal))
{
var category = new Category
{
Id = reader.GetInt32(idOrdinal),
Name = reader.GetString(nameOrdinal)
};
cats.Add(category);
}
}
return cats;
}
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
Below sections demonstrates how you can start refactoring this code using Classic DAL Helpers. There are few important classes, helpers and extensions methods that you can use in this refactoring process.
Creating and handling stored commands
As a good citizen, you have to add library namespace to your classes in order to use the extension methods and helpers in Classic DAL Helpers library.
using Microsoft.Data.Extensions;
Now you can use SqlDatabaseHelper.HandleCommand method to manage connection lifetime and to create a stored command as below:
var cats = SqlDatabaseHelper.HandleCommand<List<Category>>(
"SampleModel",
"dbo.GetAllCategories",
new SqlDatabaseHelper.CommandHandler<List<Category>>(GetAllCategoriesCommand));
Once this is done, you can get rid of few steps relates to stored command such as initialization of the database connection, retrieving connection string in the application configuration file, and creating and managing lifetime of a stored command. The HandleCommand<T> method facilitates an IDbCommand with a live connection for you to handle your stored command execution and you do not need to validate and verify whether the connection is opened or not since the helper method takes care of that part for you.
In order to pass parameters to your stored procedure you will require to instantiate SqlParameter objects, assign relevant values to those, pack it to your command before the execution. Here is how we used to do:
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "dbo.GetAllCategories";
var catParam = new SqlParameter { ParameterName = "cid", Value = categoryId, Direction = ParameterDirection.Input };
comm.Parameters.Add(catParam);
Instead of this, you can use the extension methods that provided by the Classic DAL Helpers library:
command.AddParameter<int>("cid", (int)args.First());Materializing relational data into CLR types
I heard the term “Materialization” from the EF Team. It means; converting the relational data returned via stored command into actual objects, pretty much similar to an ORM process. Here is a sample code that we used in previous section to populate Category objects:
using (var reader = comm.ExecuteReader())
{
var cats = new List<Category>();
while (reader.Read())
{
var idOrdinal = reader.GetOrdinal("cid");
var nameOrdinal = reader.GetOrdinal("name");
if (!reader.IsDBNull(idOrdinal))
{
var category = new Category
{
Id = reader.GetInt32(idOrdinal),
Name = reader.GetString(nameOrdinal)
};
cats.Add(category);
}
}
return cats;
}
Instead, I can materialize relational data into Category objects like this:
return reader.Materialize<Category>(this.Materialize);
The materialize extension method requires a delegate that knows how to map data record into a single Category object. The generic extension method Field<T> in IDataRecord can return value relevant to the Ordinal or the Column name. This is how it would look like:
protected override Category Materialize(IDataRecord record)
{
var cat = new Category
{
Id = record.Field<int>("cid"),
Name = record.Field<string>("name")
};
return cat;
}
While the materialization is more convenient, it is much more efficient to use a single materialize method per CLR type. However, you can use the same materialize method to instantiate multiple CLR types if they all derive from a common super class.
You can use the same ProductDataAccess object to materialize both Product and DiscontinuedProduct objects as below:
protected override Product Materialize(IDataRecord record)
{
Product prod = record.Field<DateTime?>("discontinued_date").HasValue ?
new DiscontinuedProduct
{
Id = record.Field<int>("pid"),
Name = record.Field<string>("name"),
CategoryId = record.Field<int>("cid"),
DiscontinuedDate = record.Field<DateTime>("discontinued_date")
} :
new Product
{
Id = record.Field<int>("pid"),
Name = record.Field<string>("name"),
CategoryId = record.Field<int>("cid")
};
return prod;
}
The CLR type could be a Business Entity in the model, a View Model used to present data, a Data Transfer Object that used to transport data among tiers, or it could be a Data Contract in your WCF service.
Using template classes to reduce repetitive tasks
A traditional Data Access Component would contain all the CRUDs related to a single business entity, facilitating ORM functionality to that same entity. The Classic DAL Helpers library contains few template classes. DataAccessObject template is one of the main classes you can use to implement CRUD functionality. Now I can declare Category and Product Data Access Objects as below:
public sealed class CategoryDataAccess : DataAccessObject<Category>
public sealed class ProductDataAccess : DataAccessObject<Product>
In order to use CRUD functionality via template DataAccessObject, you must make sure your CLR types are derived from RootEntity template. Now I can change my class declaration as in below sample:
I have extent Product class further to a DiscontinuedProduct class. The class declarations for this diagram look like this:
public sealed class Category : RootEntity
public class Product : RootEntity
public sealed class DiscontinuedProduct : Product
The Classic DAL Helpers library source code is now available in CodePlex.com. You can download directly from the below link;
CodePlex.com, Classic Data Access Layer Helpers and Samples
http://classicdalhelpers.codeplex.com/
.
![image[9]](http://lh5.ggpht.com/_ZAwmgj_UfH0/TGBDsHz8iPI/AAAAAAAAAbo/BGanLAaiJxA/s1600-h/image95%5B5%5D.png "image[9]")
