In this article I will implement a component that centralizes data access functionality with the following design goals in mind:
1.  Make calling stored procedures easy by using one API consistently without sacrificing any ADO.NET functionality or performance.
2.  Support multiple databases and connection strings.
3.  Support execution of plain text SQL statements as well as stored procedures.
4.  Support calling more than one stored procedure in the same connection as a batch and support communicating parameters between these stored procedures. This removes unnecessary round-trips, which can provide a performance boost for many data access scenarios.
5.  Support all data types, including image, text, etc.
6.  Provide a variety of methods to support all types of data retrieval techniques in ADO.NET, e.g, SqlCommand ExecuteNonQuery, ExecuteReader, ExecuteScalar, ExecuteXmlReader methods, and SqlDataAdapter Fill and FillSchema methods and their overloads.
7.  Cache parameter metadata and parameter collections created for stored procedures to use in subsequent calls.
8.  Encapsulate all SqlParameter-related work by creating SqlParameter objects inside the component to improve maintainability and productivity but still provide a way to access the SqlCommand object running behind the scenes to enable access to output and return parameters.
9.  Provide properties to change transaction isolation levels for both single and batch calls.
10.  Provide a way to change CommandBehavior, used by the ExecuteReader method.
11.  Provide a property to bind InfoMessage handlers for MS SqlServer messages.
12.  Provide settings for caching, such as the refresh rate of the parameter collection and parameter metadata caches, and the initial and maximum size of the parameter cache.
13.  Mark Assembly as CLSCompliant to make it available to all .NET languages.
14.  Good exception management.

Now let's look at some of these design goals in detail and see how they will be implemented. All of the code for this article can be downloaded from www.sys-con.com/dot net/sourcec.cfm.

Making Calling Stored Procedures Easy
The data access helper component will provide an abstraction layer over existing ADO.NET APIs so that the component clients will not have to deal with the details of ADO.NET. This encapsulation will also give us the chance to apply some of the best practices in ADO.NET usage. Our other goal is to implement a straightforward and easy-to-use interface. The following code shows how this interface will look.

DataAccess dal = new DataAccess("ConnectionStringKey");
dal.AddSqlStatement("SELECT * FROM Customers");
dal.ExecuteCall();

The preceding code shows a call for executing plain text SQL statements, which are added via an AddSqlStatement call. For stored procedures another set of methods will be necessary, as in the following example.

DataAccess dal = new DataAccess("ConnectionStringKey");
dal.BeginProc("StoredProcedureName");
dal.AddParameter(ParameterName1,ParameterValue1);
dal.AddParameter(ParameterName2,ParameterValue2);
dal.EndProc();
dal.ExecuteCall();

Each stored procedure call will begin with a BeginProc call with the stored procedure name as a parameter. Then a series of AddParameter calls will specify the values of the stored procedure parameters. An EndProc call will end the stored procedure call. Finally the call will be executed by calling one of the Execute methods. You can see a listing of these Execute methods later in the article.

Supporting Multiple Databases and Connection Strings
An application may have to access several different databases. To support such scenarios we will store multiple connection strings in an XML configuration file called DalConfig.xml. The connection strings will be differentiated from each other by a key value, which will be specified in the XML configuration file as follows:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connection key="NORTHWIND">data source=(local);
Initial Catalog=NORTHWIND;Integrated Security=SSPI</connection>
<connection key="PUBS">data source=(local);
Initial Catalog=PUBS;Integrated Security=SSPI</connection>
<configuration>

The key attribute of the connection element will be used as a parameter in the constructor to specify the connection string to be used:

DataAccess dal = new DataAccess("NORTHWIND");
//use northwind database

Support for All Data Types
The interface will support the following AddParameter overloads to support different data types.

void AddParameter(string pName,string value)
void AddParameter(string pName,decimal value)
void AddParameter(string pName,double value)
void AddParameter(string pName,int value)
void AddParameter(string pName,bool value)
void AddParameter(string pName,byte value)
void AddParameter(string pName,Guid value)
void AddParameter(string pName,float value)
void AddParameter(string pName,char value)
void AddParameter(string pName,byte[] value)
//image, binary data

Providing Execute Methods
As you know, ADO.NET provides several different techniques to execute database calls. SqlCommand and SqlDataAdapter are two classes that provide these techniques, and the interface of our component will provide the following methods to allow the same functionality, with the methods defined as follows:

Encapsulate SqlParameter-Related Work
When calling stored procedures using ADO.NET it is necessary to create SqlParameter objects and add these to the SqlParameterCollection of the SqlCommand. This collection is accessed via the Parameters property of SqlCommand. The following information is needed to create the SqlParameter objects.

• Name
• Size
• Precision
• Scale
• Nullability
• Direction: Input/Output
• SqlDbType

DataAccess dal = new DataAccess("NORTHWIND");

SqlCommand _ com = dal.BeginProc("InsertCategory");
dal.AddParameter("@CategoryName", "New Category Name");
dal.EndProc();

dal.ExecuteNonQuery();

//Get Return and Output Values
int rowCount = (int) com.Parameters["@RC"].Value;
int newID = (int) com.Parameters["@NewCategoryID"].Value;

As you see, the number of lines required for the same call has been halved and SqlParameter manipulation is totally encapsulated inside the component. You may wonder how the component knows what values to specify for SqlParameter properties. The AddParameter method does not allow the programmer to specify any of the properties mentioned above, such as length or size. The answer is in retrieving this information from the metadata tables in the database. For each database, MS SQL Server maintains system tables that keep the metadata information about objects in that database, and using the following query it is possible to return the stored procedures, their parameters, and information about these parameters.

SELECT
objs.name SpName, cols.name Pname, cols.prec,cols.scale,
cols.isnullable,cols.isoutparam,cols.length,cols.xtype
FROM sysobjects objs
JOIN syscolumns cols ON objs.id = cols.id
WHERE objs.type = 'P' ORDER BY SpName

The query select list contains the stored procedure name, parameter name, precision, scale, nullability status, direction, length, and type < which will all be used by the helper component to create the corresponding SqlParameter objects. The return code parameter of stored procedures requires special attention since it is not returned from this query and not kept in the system tables. Therefore I will create a parameter of type int with the name "@RC" for each stored procedure call.

Caching of Parameter Information
Caching is one of the important concepts that can improve application performance dramatically. Instead of using valuable resources (database connections, memory, etc.) to re-create the same result every time, it may be possible to cache the outcome of the operation for future needs and save these valuable resources. In the data access helper component there are two good opportunities to use caching:
1.  Caching the parameter metadata retrieved from the database
2.  Caching SqlParameter collection objects created during previous stored procedure calls

Caching the Parameter Metadata
As mentioned in the previous section, the data access helper component will retrieve the parameter metadata information from all stored procedures in the database and will use this information to create SqlParameter objects for incoming stored procedure calls. For performance reasons this clearly cannot be done for each and every call. My solution is caching this information in a static variable of type DataTable and querying this copy in subsequent calls to retrieve parameter information.

Caching SqlParameter Collection Objects
SqlParameter Collection offers another good opportunity to use caching. When a stored procedure is called, the data access helper component will first check this cache and use the parameter collection from the cache instead of going to the parameter metadata to retrieve information about parameters. The cache will be implemented as a static hashtable with the stored procedure name as the key and ParameterCollection object as the value. The helper component will go to the lower-level cache (parameter metadata) only if the parameters are not found or the first cache has expired.

Another thing we have to take into consideration is the expiration conditions for these caches. The (parameter metadata) cache will refresh at specified intervals, set as a configuration parameter in the DalConfig.xml file as shown in the following code:

<!-Refresh parameter metadata every 10 minutes>
<refreshRate>10</refreshRate>

This same setting will be used by the SqlParameter collection cache to clear itself at specified intervals. There will be two other settings for this cache: the initial size of the cache and the maximum size. The initial size setting will be used as a parameter when creating the hashtable for the cache, and the maximum size will be the upper limit, which keeps the cache from consuming a lot of memory. These two parameters are set in the DalConfig.xml file as follows:

<parameterCacheMinSize>50
</parameterCacheMinSize>
<!-Max 200 stored procedure parameters will be cached>
<parameterCacheMaxSize>200
<parameterCacheMaxSize>

Support for Calling Stored Procedures in a Batch
Batching is calling more than one stored procedure in a single database session. As you can imagine, this will prevent many round-trips in several scenarios and help improve performance. The first scenario that comes to mind is working with master-detail records. Let's say you have to insert the Order record first and then the LineItems of that Order. To prevent the round-trip between these two calls you can create a third stored procedure and call these two stored procedures within the third stored procedure, but this can lead to other problems if you have several stored procedures that need to run in a similar fashion. Fortunately, there is an easy way to do this, which is by generating the SQL statement for multiple calls and sending this SQL statement block to the database as you would a single command. For each stored procedure call in the command collection the component will generate a series of "EXEC ap_ StoredProcedureName @param1, @param2, @param 3 OUTPUT" SQL statements, and will send this as a single command to the database.

Another nice-to-have feature for a batch would be the ability to pass variables to or from other stored procedure calls and return the value of variables from calls. Our component will provide the following interface to support this functionality:

ReturnParameter ("@param3");
//this will generate a SELECT statement for the param3

UseParameter("@StoredProc2Param1","@StoredProc1Param1",1);

This API will set the value of StoredProc2Param1 to use the value of StoredProc1Param1 from the first stored procedure in the collection. The numbering begins at 1 and is incremented with each BeginProc() call. Please see the code samples for how this feature is used.

Class Design
The component is composed of nine different classes. The functionality provided by each class is as follows:

All classes except DataAccess, SqlCustomCommand, and SqlCustomCommandCollection are internal. Please download the source code to see the the implementation and associated code samples.

Using the Component
The following code samples may help you better understand the functionality offered by the data access helper component.

Single Call
The following simple code shows how to make a single stored procedure call.

//Northwind is the key in the XML configuration file
for the connection string entry
DataAccess _dal = new DataAccess("NORTHWIND");

_dal.BeginProc("ap_GetAllCustomersByStateByZip ");
_dal.AddParameter("@State", "CA");
_dal.AddParameter("@Zip", "95032");
_dal.EndProc();

//Call one of many execute methods
SqlDataReader _customerReader = _dal.ExecuteReader();

This sample calls the ap_GetAllCustomersByStateByZip stored procedure and retrieves a SqlDataReader.

Batch Call
The following sample shows how batch calls can be made. The only difference from the previous example is another block of Begin/End Proc calls prior to calling the execute method. This code inserts the order first and then the details of that order in a single session.

_dal.BeginProc("ap_InsertOrder");
_dal.AddParameter("@CustomerName","Cenk");
_dal.AddParameter("@OrderTotal",1000);
_dal.AddParameter("@OrderId",1);
_dal.EndProc();
_dal.BeginProc("ap_InsertOrderDetails");
_dal.AddParameter("@OrderId",1);
_dal.AddParameter("@ItemId","X1");
_dal.AddParameter("@Quantity",5);
_dal.AddParameter("@LineCost",1000);
_dal.EndProc();
_dal.ExecuteNonQuery();

As you can see, the only difference between making a single call and a batch call is having another block of BeginProc() and EndProc() method calls before execution.

Variables in Batch Calls
This sample shows the usage of the UseParameter and ReturnParameter methods. Let's assume ap_InsertOrder has an output parameter @OrderId and we want to use this parameter as an input to ap_InsertOrderDetails.

_dal.BeginProc("ap_ InsertOrder ");
_dal.AddParameter("@CustomerName","Cenk");
_dal.AddParameter("@OrderTotal",1000);
_dal.ReturnParameter("@OrderId");
_dal.EndProc();

_dal.BeginProc("ap_InsertOrderDetails");
_dal.UseParameter("@OrderId","@OrderId",1);
_dal.AddParameter("@ItemId","X1");
_dal.AddParameter("@Quantity",5);
_dal.AddParameter("@LineCost",1000);
_dal.EndProc();
DataTable _dataTable = new DataTable("OrderInfo");

_dal.ExecuteDataTable(_dataTable);

Accessing SqlCommand Parameters
The BeginProc method returns a SqlCommand object that can then be used to access the output parameters or the "@RC" return code parameter of the command. This feature is not available in batch calling because a SQL statement is generated for the command collection and executed in batch calls instead of making single calls to the database. In order to access the values of these variables you can use the ReturnParameter method.

DataAccess _dal = new DataAccess("NORTHWIND");

SqlCommand _ com = _dal.BeginProc
("ap_GetAllCustomersByStateByZip ");
_dal.AddParameter("@State", "CA");
_dal.AddParameter("@Zip", "95032");
_dal.EndProc();

//Call one of many execute methods
DataTable _customersTable = new DataTable("Customers");
dal.ExecuteDataTable(_customersTable);
int _numberOfRows = (int) com.Parameters["NumberOfRows"].Value;

Plain Text SQL Statements with Stored Procedures
The following sample sends the select statement as well as the stored procedure call to the database in a single batch.

DataAccess _dal = new DataAccess("NORTHWIND");

SqlCommand _ com = _dal.BeginProc
("ap_GetAllCustomersByStateByZip ");
_dal.AddParameter("@State", "CA");
_dal.AddParameter("@Zip", "95032");
_dal.EndProc();

_dal.AddSqlStatement("Select * From Orders");

DataSet _ds = new DataSet();
_dal.ExecuteDataSet(_ds);

Setting the Transaction Isolation Level You can use the TransactionIsolationLevel property of DataAccess to adjust the isolation level. For single commands this will cause a call to BeginTransaction and other transaction-related methods in ADO. NET, whereas in batch calls the generated SQL statement will have a SET TRANSACTION ISOLATION LEVEL statement at the beginning. The property can be set to ReadCommitted as follows.

_dal.TransactionIsolationLevel = IsolationLevel.ReadCommitted;

Binding InfoMessage Handlers
The following sample shows how to bind InfoMessage handlers to listen to messages coming from MS SQL Server.

_dal.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

protected void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach (SqlError err in args.Errors)
{
Console.WriteLine("The {0} has received a severity {1},
state {2} error number {3}\n" +
"on line {4} of procedure {5} on server {6}:\n{7}",
err.Source, err.Class, err.State, err.Number, err.LineNumber,
err.Procedure, err.Server, err.Message);
}
}

Exception Handling
After catching an exception and doing its own cleanup, the component rethrows the exception so your application can catch it and handle it appropriately. The following code sample shows a typical exception-handling block.

_dal.AddSqlStatement("Select * From Orders");

DataSet _ds = new DataSet();
try
{_dal.ExecuteDataSet(_ds);}
catch(SqlException e)
{
throw; // instead do your cleanup}
}
catch // other exceptions
{

}

Conclusion
As the layered approach to building applications becomes increasingly popular I am sure we will see more and more examples of data application blocks coming out for .NET. In this article you have seen an example of such a component, which isolates the business logic components from the data access logic. Please see the Resources section for further information.

Resources