BinaryIntellect DatabaseHelper class & Microsoft Data Access Application Block class SqlHelper.
Introduction:
In this article we are going to explore some new features of the generic data access supported by ADO.Net 2, and also we will have a look at generic collections and generic interfaces. After that we will put all together and use ASP.Net new ObjectDataSource control to populate data from a business class to the new ASP.Net GridView. BinaryIntellect's DatabaseHelper & Microsoft SqlHelper: BinaryIntellect Consulting produced a generic Data Access class, they named it DatabaseHelper and it is available to download with the source code. I had worked with the earlier version of Microsoft Data Application Block SqlHelper class; it was great and very useful. From this point and after examining DatabaseHelper class source code, I thought that I can make a combination of both Data Access Layer classes, using the generic data access provided by BinaryIntellect's DatabaseHelper class which uses new ADO.Net database provider features and SqlHelper. Microsoft Data Application Block has a good feature that it provides functions to leverage a static cache of stored procedure parameters, and the ability to discover parameters for stored procedures at run-time. This feature is missing in the current version of DatabaseHelper.
Note: Explaining of DatabaseHelper work is out of this article scope.
Modifications to DatabaseHelper Class:
DatabaseHelper class has set of methods that enable retrieving, modifying and adding data to the data source. It supports normal ad-hoc arbitrary SQL statements as well as stored procedures. The key modification is to build methods to leverage a static cache of stored procedure parameters, and the ability to discover parameters for stored procedures at run-time. To accomplish this, I imported a class from Microsoft Data Application Block named SqlHelperParameterCache. I renamed the class to be ParameterCache as it supposed to generic for any data source. All SqlParameters, SqlCommand, SqlConnection or any provider specific references replaced with provider generic references. For example, any reference to SqlConnection replaced with a reference to DbConnection, and any reference to SqlParameter replaced with a reference to DbParameter. Also the new ParameterCache class is marked as static and internal.
Listing 1 - PamarmeterCache.DiscoverSpParameterSet Method:
private static DbParameter[] DiscoverSpParameterSet(DbConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
if (connection is SqlConnection)
{
SqlCommandBuilder.DeriveParameters((SqlCommand)cmd);
}
else if(connection is OracleConnection)
{
OracleCommandBuilder.DeriveParameters((OracleCommand)cmd);
}
else if(connection is OdbcConnection)
{
OdbcCommandBuilder.DeriveParameters((OdbcCommand)cmd);
}
else if(connection is OleDbConnection)
{
OleDbCommandBuilder.DeriveParameters((OleDbCommand)cmd);
}
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
DbParameter[] discoveredParameters = new DbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
DatabaseHelper Class:
DatabaseHelper has 4 main methods for retrieving data from and updating data into the underlying data source. These methods are:
- ExecuteNonQuery.
- ExecuteScalar.
- ExecuteReader.
- ExecuteDataSet.
There is also AddParameter method that enables you to add parameter to the command encapsulated inside DatabaseHelper class. Every method has 5 overloads. Here I'll show the main the overloaded methods that are similar to SqlHelper methods. In SqlHelper, there are methods that accepts array of objects as parameter values. In our modified DatabaseHelper I just implemented the same idea. Let's take ExecuteNonQuery as sample.
Listing 2 - DatabaseHelper.ExecuteNonQuery Method:
public int ExecuteNonQuery(string spName, ConnectionState connectionstate, params object[] parameterValues)
{
objCommand.CommandText = spName;
objCommand.CommandType = CommandType.StoredProcedure;
DbParameter[] parameters = ParameterCache.GetSpParameterSet(objConnection, spName);
AssignParameterValues(parameters, parameterValues);
objCommand.Parameters.AddRange(parameters);
int i = -1;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
i = objCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
objConnection.Close();
}
}
return i;
}
Note that the method in Listing 2 is only for use with stored procedures.
This method has 2 differences from the similar original implementation in the original DatabaseHelper class. 1st one it only support stored procedures, 2nd it accepts set of parameter values.
DatabaseHelper as core data layer (Northwind simple data layer)
In this section we will build a simple data layer for Northwind database. We will use our new DatabaseHelper class to implement the functionality of this data layer. The Data layer is consist of 4 classes, NWBaseDB, NWCategoriesDB, NWSuppliersDB and NWProductsDB.
NWBaseDB is an abstract class that holds a protected reference to DatabaseHelper. All other 3 classes are inheriting from NWBaseDB. NWCategoriesDB has a method that retrieves categories list from the categories table -GetCategories method-. This method invokes a stored procedure -usp_GetCategories- through the DatabaseHelper reference's ExecuteReader method.
Note: All stored procedure is available with the downloadable project.
Listing 3 - NWCategoriesDB.GetCategories() Method:
public DbDataReader GetCategories()
{
return _db.ExecuteReader("usp_GetCategories");
}
Note that _db is an object of type DatabaseHelper.
NWSuppliersDB has a method that retrieves suppliers list from the suppliers table -GetSuppliers method-.
This method invokes a stored procedure -usp_GetSuppliers- through he DatabaseHelper reference's ExecuteReader method.
Listing 4 - NWSuppliersDB.GetSuppliers () Method:
public DbDataReader GetSuppliers()
{
return _db.ExecuteReader("usp_GetSuppliers");
}
NWProductsDB has 2 methods, one retrieves products list from the products table -GetProductsList method- which invokes a stored procedure -usp_GetProductsList- through the DatabaseHelper reference's ExecuteReader method.
This method is overloaded as it accepts category id to filter products list by category. The 2nd method updates product details by invoking a stored procedure -usp_UpdateProductDetails- through the DatabaseHelper reference's ExecuteNonQuery method.
Listing 5 - NWProductsDB Class:
public class NWProductsDB : NWBaseDB
{
public NWProductsDB(string connectionString, string errorLogFile) : base(connectionString, errorLogFile) { }
public NWProductsDB(string connectionString) : base(connectionString, null) { }
public DbDataReader GetProductsList(int catId)
{
return _db.ExecuteReader("usp_GetListOfProducts", catId);
}
public DbDataReader GetProductsList()
{
return _db.ExecuteReader("usp_GetListOfProducts", DBNull.Value);
}
public bool UpdateProductDetails(int prodId, string prodName, string qtyPerUnit, decimal unitPrice, short unitsInStock, bool discontinued, int supplierId, int categoryId)
{
int rowsAffected = _db.ExecuteNonQuery("usp_UpdateProductDetails", prodId, prodName, qtyPerUnit, unitsInStock, discontinued, supplierId, categoryId); return (rowsAffected > 0);
}
}
Building the business layer (Northwind business layer classes):
Now after finishing the data layer, and to complete our simple 3 tier structure, we need to build the business layer. And thought we can explore some more features of .Net framework 2 while building this layer. Northwind business layer consist of 9 simple classes, 3 for every entity (Categories, Suppliers and Products).
All are similar in the architecture. So we will have a look at only three classes. NWCategoryItem Class This class represents category record. Means it has properties that represent fields in the northwind database table Categories. There are similar classes for each of Products and Supplier Entities. NWCategoryItemComparer Class This class implements the new generic interface IComparer. Generic interfaces are new feature of .Net Framework 2.0. This interface has a method that is used by Sort method of List class. So we implement this class to support sorting for our records.
Note that we are not using DataSet or DataTable classes, as we will use our own collection; this is the reason behind why we need to implement our sort functionality. Listing 6 shows the implementation of IComparer.CompareTo Method inside NWCategoryItemComparer. There are similar classes for each of Products and Supplier Entities.
Listing 6 - NWCategoryItemComparer Class:
public class NWCategoryItemComparer : System.Collections.Generic.IComparer
{
private enum SortBy { CategoryId, CategoryName, Description}
private SortBy _sortBy = SortBy.CategoryId;
public NWCategoryItemComparer() { sortBy = SortBy.CategoryId; }
public NWCategoryItemComparer(string sortBy)
{
switch (sortBy.ToUpper())
{
case "CATEGORYNAME":
case "CATEGORYNAME DESC":
_sortBy = SortBy.CategoryName;
break;
case "DESCRIPTION":
case "DESCRIPTION DESC": _sortBy = SortBy.Description;
break;
default:
_sortBy = SortBy.CategoryId;
break;
}
}
#region IComparer Members
public int Compare(NWCategoryItem x, NWCategoryItem y)
{
switch (_sortBy)
{
case SortBy.CategoryName:
return x.CategoryName.ToLower().CompareTo(y.CategoryName.ToLower());
case SortBy.Description:
return x.Description.ToLower().CompareTo(y.Description.ToLower());
default:
return x.CategoryId.CompareTo(y.CategoryId);
}
}
#endregion
}
NWProductBusiness Class
This is static class, marked as data object class using DataObjectAttribute class. This class is acting as a proxy to data layer class NWProductsDB. A similar class is build for each entity (Categories and Supplies). This class has 2 methods, one of them is overloaded. The methods are GetProductsList which return a list of NWProductItem objects, and UpdateProductDetails which accept an object of type NWProductItem. GetProductsList method return a strongly typed collection of NWProductItem using generic collection List. AS you will see now, it was just as easy as tying List and you get your strongly typed collection ready. Methods of this class will be exposed by ASP.Net new ObjectDataSource Web Server Control as we will see later on here.
Listing 7 - NWCategoryItemComparer Class:
[DataObject(true)]
public static class NWProductBusiness
{
private readonly static string _connectionString;
static NWProductBusiness()
{
_connectionString = ConfigurationManager.ConnectionStrings["NWConnString"].ConnectionString;
}
[DataObjectMethod(DataObjectMethodType.Select, true)]
public static List<NWProductItem> GetProductsList(string sortBy)
{
return GetProductsList(-1, sortBy);
}
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<NWProductItem> GetProductsList(int catId, string sortBy)
{
NWProductsDB db = new NWProductsDB(_connectionString);
DbDataReader reader = null;
List<NWProductItem> products = new List<NWProductItem>(50);
if (catId > 0)
{
reader = db.GetProductsList(catId);
}
else
{
reader = db.GetProductsList();
}
if (reader != null)
{
while (reader.Read())
{
NWProductItem product = new NWProductItem(reader.GetInt32(0));
product.ProductName = reader.GetString(1);
product.SupplierId = (!reader.IsDBNull(2)) ? reader.GetInt32(2) : -1;
product.CategoryId = (!reader.IsDBNull(3)) ? reader.GetInt32(3) : -1;
product.QuantityPerUnit = (!reader.IsDBNull(4)) ? reader.GetString(4) : String.Empty; product.UnitPrice = (!reader.IsDBNull(5)) ? reader.GetDecimal(5) : decimal.Zero; product.UnitsInStock = (!reader.IsDBNull(6)) ? reader.GetInt16(6) : (short)0; product.Discontinued = reader.GetBoolean(7);
product._supplierName = (!reader.IsDBNull(8)) ? reader.GetString(8) : String.Empty; product._categoryName = (!reader.IsDBNull(9)) ? reader.GetString(9) : String.Empty; products.Add(product);
}
reader.Close();
}
products.Sort(new NWProductItemComparer(sortBy));
if(sortBy.Contains("DESC")) products.Reverse();
return products;
}
[DataObjectMethod(DataObjectMethodType.Update)]
public static bool UpdateProductDetails(NWProductItem product)
{
NWProductsDB db = new NWProductsDB(_connectionString);
return db.UpdateProductDetails(product.ProductId, product.ProductName, product.QuantityPerUnit, product.UnitPrice, product.UnitsInStock, product.Discontinued, product.SupplierId, product.CategoryId);
}
}
Building presentation layer, Unleash the power of Visual Studio.Net 2005 Visual Web Developer:
- Drag a GridView and 3 ObjectDataSource controls into your web form 2.
- Name the GridView gvProducts, and the 3 ObjectDataSource as the following: odsCategories, odsSuppliers and odsProducts.
- Now Configure odsCategories by following the screen shots.
- Now configure the odsSuppliers just the same, but instead of selecting NWCategoryBusiness as the Data Component, we will select NWSupplierBusiness class.
- Configuring odsProducts as the following
- Now it is Time to Configure the GridView
- Click on Edit Columns, then convert CategoryName and CompanyName columns to TemplaeField so we can add 2 DropDownList to EditItemTemplate on each.
- From GridView Task smart tag window select Edit Templates and select EditItemTemplate under CategoryName Column.
- Configure Categories DropDownList to use the odsCategories ObjectDataSource, and the Companies DropDownList to use odsSuppliers ObjectDataSource.
- Then configure the DataBindings on each to use the appropriate field.
Now if you had a look at your code behind, you will find it clear, no extra code added to you code behind file.
Hope this article added value to you.