Data Access Layer (DAL) with SqlWrapper library

• Download demo project - 14.7 Kb
• Download source - 86.3 Kb

Introduction

Have you ever had to create a data access layer in your projects? It is most probable that you have created a class or classes having several methods to call stored procedures or execute SQL expressions. It is very boredom work especially if the database contains more than a dozen tables. And the worst part of this is that the methods have almost the same steps (create a command object, fill its properties, execute and return a result) and rarely contains some other logic. You have two ways: write the source code manually or generate it. In both ways, there will be a lot of source code.

I tried both ways before and I thought that there had to be something easier. The article about AutoSproc Tool gave me an idea and I developed my SqlWrapper library.

A simple example

Let's write two classes to work with the database NorthWind. One class Orders1 using the usual way:

public class Orders1
{
    private SqlConnection m_connection = null;

    public SqlConnection Connection
    {
        get{return m_connection;}
        set{m_connection = value;}
    }

    public DataSet CustOrdersDetail(int OrderID)
    {
        SqlCommand cmd = new SqlCommand("CustOrdersDetail", m_connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@OrderID", SqlDbType.Int);
        cmd.Parameters["@OrderID"].Value = OrderID;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public int CountByEmployee(int EmployeeID)
    {
        SqlCommand cmd = new SqlCommand(
            "select count(*) from Orders where EmployeeID=@EmployeeID", 
            m_connection);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
        cmd.Parameters["@EmployeeID"].Value = EmployeeID;
        int count = (int)cmd.ExecuteScalar();
        return count;
    }
    
}

and the other class Orders2 using SqlWrapper:

public abstract class Orders2 : SqlWrapperBase
{
    public abstract DataSet CustOrdersDetail(int OrderID);

    [SWCommand("select count(*) from Orders where EmployeeID=@EmployeeID")]
    public abstract int CountByEmployee(int EmployeeID);
}

Now let's see how these classes are used:

SqlConnection cnn = new SqlConnection(
    ConfigurationSettings.AppSettings["ConnectionString"]);
cnn.Open();

// working with the ordinary class

Orders1 orders1 = new Orders1();
orders1.Connection = cnn;
DataSet ds1 = orders1.CustOrdersDetail(10248);
int count1 = orders1.CountByEmployee(6);

// working with the wrapped class

Orders2 orders2 = (Orders2)WrapFactory.Create(typeof(Orders2));
orders2.Connection = cnn;
DataSet ds2 = orders2.CustOrdersDetail(10248);
int count2 = orders2.CountByEmployee(6);

The difference is obvious. The class Orders2 contains much less code but in usage it is the same as class Orders1 except the way of creation.

How it works

In order to create a wrapper class, you have to inherit your class from SqlWrapperBase class and define abstract methods with custom attributes which will describe what you want to execute and how you want to get the result. If no method attribute is specified, then method name is used as a stored procedure name. You can also define any amount of concrete methods if you need to have some more logic besides a simple execution of a SQL expression. After that you can create an object of your class by calling the WrapFactory.Create() method. This method uses classes of System.Reflection.Emit namespace to add an implementation to abstract methods. For example, the following method:

[SWCommand("select count(*) from Orders where EmployeeID=@EmployeeID")]
public abstract int CountByEmployee(int EmployeeID);

will be implemented like this:

[SWCommand("select count(*) from Orders where EmployeeID=@EmployeeID")]
public int CountByEmployee(int EmployeeID)
{
    MethodInfo method = (MethodInfo)MethodBase.GetCurrentMethod();
    object[] values = new object[1];
    values[0] = EmployeeID;
    object obj = SWExecutor.ExecuteMethodAndGetResult(
            m_connection, 
            m_transaction, 
            method, 
            values, 
            m_autoCloseConnection);

    return (int)obj;
}

The main job is done in the method SWExecutor.ExecuteMethodAndGetResult() which creates the SqlCommand object and returns a result of its execution. For this purpose, the variable method provides the following information:

• command text
• command type
• execution method
• parameter names
• parameter data types
• parameter directions
• parameter sizes, scales and precisions
• behavior in case null or DBNull is returned instead of a scalar value
• behavior in case DBNull value must be passed to a parameter

Some of these information is taken from the method signature, the other from optional methods and method parameter attributes.

The following diagram shows how the SqlWrapper classes are associated.

SqlWrapperBase

SqlWrapperBase class is the base class for all the wrapper classes and contains the following properties with their corresponding fields:

• Connection property (wraps the protected m_connection field);
• Transaction property (wraps the protected m_transaction field);
• AutoCloseConnection property (wraps the protected m_autoCloseConnection field). If true then the connection object is closed each time after a command execution.

You can use protected fields in concrete methods of your wrapper class.

SWCommandAttribute

This is an optional method attribute and contains the following properties:

• CommandType can take the following values: SWCommandType.Text, SWCommandType.StoredProcedure and SWCommandType.InsertUpdate. The first three values correspond to System.Data.CommandType enumeration values. SWCommandType.InsertUpdate will be described later. Default value is SWCommandType.Text.
• CommandText contains a command text treated depending on CommandType property value.
• ReturnIfNull contains a value that will be returned if a result of a command execution is null or DBNull.
• MissingSchemaAction is a value of SqlDataAdapter.MissingSchemaAction. Default value is MissingSchemaAction.Add.

All the properties except CommandText property are optional.

Important: If this attribute is omitted then the method name is used for CommandText property and CommandType property equals SWCommandType.StoredProcedure.

SWParameterAttribute

This is an optional parameter attribute and contains the following properties:

• Name contains command parameter name. If omitted, then method parameter name is used.
• SqlDbType contains value type for a command parameter and corresponds to SqlParameter.SqlDbType property.
• Size contains a command parameter size and corresponds to SqlParameter.Size property.
• Precision contains a command parameter precision and corresponds to SqlParameter.Presision property.
• Scale contains a command parameter scale and corresponds to SqlParameter.Scale property.
• TreatAsNull contains a value that will be interpreted as DBNull value. This property is useful for scalar type parameters.
• ParameterType can take one of the following values: SWParameterType.Default, SWParameterType.SPReturnValue, SWParameterType.Key and SWParameterType.Identity. Default value is SWParameterType.Default.

All the properties are optional.

SWParameterType.SPReturnValue value of ParameterType property defines that this method parameter contains a stored procedure return value. In this case this method parameter must be passed by reference.

SWParameterType.Key and SWParameterType.Identity values of ParameterType property are used with SWCommandType.InsertUpdate value of SWCommandAttribute.CommandType property and their meanings are described later.

InsertUpdate commands

As SQL INSERT and UPDATE clauses are very trivial but are used very often, I have added the following command and parameter types in the SqlWrapper library that simplifies creating methods for inserting or updating data to a table:

• SWCommandType.InsertUpdate indicates that a special INSERT - UPDATE expression must be created. SWCommandAttribute.CommandText must contain a name of the table.
• SWParameterType.Identity indicates that this parameter is an identity table column type which identifies a data row. The method parameter must be passed by reference.
• SWParameterType.Key indicates that this parameter is a part of a key (not an identity table column type) which identifies a data row.

These are examples of INSERT - UPDATE expressions:

1. Method definition:

   [SWCommand(SWCommandType.InsertUpdate, "Shippers")]
   public abstract void ShippersInsertUpdate
       (
       [SWParameter(SWParameterType.Identity)]ref int ShipperID,
       [SWParameter(40)]string CompanyName,
       [SWParameter(24)]string Phone
       );

   SQL expression:

   if(@ShipperID is NULL) 
   begin  
       insert into [Shippers]([CompanyName], [Phone]) 
       values(@CompanyName, @Phone)  
       
       select @ShipperID = SCOPE_IDENTITY() 
   end 
   else 
   begin  
       update [Shippers] set 
       [CompanyName]=@CompanyName, 
       [Phone]=@Phone 
       where [ShipperID]=@ShipperID  
   end

2. Method definition:

   [SWCommand(SWCommandType.InsertUpdate, "Order Details")]
   public abstract void OrderDetailsInsertUpdate
       (
       [SWParameter(SWParameterType.Key)]int OrderID,
       [SWParameter(SWParameterType.Key)]int ProductID,
       Decimal UnitPrice,
       Int16 Quantity,
       float Discount
       );

   SQL expression:

   update [Order Details] set 
   [OrderID]=@OrderID, 
   [ProductID]=@ProductID, 
   [UnitPrice]=@UnitPrice, 
   [Quantity]=@Quantity, 
   [Discount]=@Discount 
   where [OrderID]=@OrderID and [ProductID]=@ProductID  
   
   if (@@rowcount = 0)  
       insert into [Order Details]([OrderID], [ProductID], [UnitPrice], 
       [Quantity], [Discount]) 
       values(@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)

As you can see, in the first example the variable @ShipperID is compared with NULL. By default, it has this value if a proper method parameter value equals or less than 0. If you want you can set any other value in SWParameter.TreatAsNull property, that will be treated as NULL.

Creating data access layer in your application

SqlWrapper library contains a base class DataAccessLayerBase that can be used for your custom data access layer (DAL) classes with the least possible code. All you have to do is to create a class inherited from DataAccessLayerBase class and declare properties of your wrapper class types like this:

public YourWrapperClass YourPropertyName
{
    get
    {
      return (YourWrapperClass)GetWrapped();
    }
}

And this is all. You can also add any other members if you need. This is an example of a DAL class that can be created (Orders2 is a class shown above, UserClass1 is some other wrapper class):

public class MyDAL : DataAccessLayerBase
{
    public UserClass1 UserClass1{get{return (UserClass1)GetWrapped();}}
    public Orders2 Orders2{get{return (Orders2)GetWrapped();}}
}

And this is a class diagram of your DAL class:

And here is an example of how your DAL class can be used:

MyDAL dal = new MyDAL();
dal.Init(cnn, true, true);
int c = dal.Orders2.CountByEmployee(6);
DataTable dt = dal.UserClass1.Method1();

Before using an object of your DAL class, you have to call one of the overloaded Init() methods inherited from the DataAccessLayerBase class.

public void Init(SqlConnection connection, bool autoCloseConnection, 
          bool ownsConnection);
public void Init(string connectionString, bool autoCloseConnection);

These methods are very important because besides setting of connection properties, they call a private method GenerateAllWrapped() which enumerates all the methods of your DAL class, creates objects of your wrapper classes and saves them in a private Hashtable m_swTypes:

private void GenerateAllWrapped()
{
    MethodInfo[] mis = this.GetType().GetMethods();
    for(int i = 0; i < mis.Length; ++i)
    {
        Type type = mis[i].ReturnType;
        if(type.GetInterface(typeof(ISqlWrapperBase).FullName) == 
                 typeof(ISqlWrapperBase))
        {
            if(mis[i].Name.StartsWith("get_"))
            {
                if(!m_swTypes.ContainsKey(mis[i].Name))
                {
                    ISqlWrapperBase sw = WrapFactory.Create(type);
                    m_swTypes[mis[i].Name] = sw;
                }
            }
        }
    }
}

Properties that you have declared call the protected method GetWrapped() that lookups a calling method's name and returns a proper object from m_swTypes:

protected ISqlWrapperBase GetWrapped()
{
    MethodInfo mi = (MethodInfo)(new StackTrace().GetFrame(1).GetMethod());
    ISqlWrapperBase res = (ISqlWrapperBase)m_swTypes[mi.Name];
    if(res == null)
    {
        throw new SqlWrapperException("The object is not initialized.");
    }
    return res;
}

Three methods of DataAccessLayerBase class simplify the work with transactions.

• BeginTransaction() and BeginTransaction(IsolationLevel iso) open a new transaction.
• RollbackTransaction() rolls back an open transaction.
• CommitTransaction() commits an open transaction.

Connection, Transaction or AutoCloseTransaction properties of wrapper classes are updated automatically when any of them is changed.

In addition, DataAccessLayerBase class has several methods (ExecuteDataSet(), ExecuteDataTable(), ExecuteScalar() and ExecuteNonQuery()) that help executing user SQL expressions and can be used in ad-hoc queries.

Other languages

I have tested SqlWrapper with VB.NET and I guess that it can work with any language in the .NET platform. In VB.NET, class Orders2 can be written this way:

Public MustInherit Class Orders2
    Inherits SqlWrapperBase

    Public MustOverride Function _
           CustOrdersDetail(ByVal OrderID As Integer) As DataSet

    <SWCommand("select count(*) from Orders where EmployeeID=@EmployeeID")> _
    Public MustOverride Function _
           CountByEmployee(ByVal EmployeeID As Integer) As Integer

End Class

and MyDAL this way:

Public Class MyDAL
    Inherits DataAccessLayerBase

    Public ReadOnly Property UserClass1() As UserClass1
    Get
        Return GetWrapped()
    End Get
    End Property

    Public ReadOnly Property Orders2() As Orders2
    Get
        Return GetWrapped()
    End Get
    End Property

End Class

What is planned to do

SqlWrapper library is constantly developing. For now, it has most of the things I encounter in my everyday work with .NET and MS SQL Server 2000 and in the nearest future I plan to add Oracle and OLEDB data providers.

History

• 27-05-2005 - Initial release.
• 09-06-2005 - VB.NET examples are added.

Reference

• Auto-Generating Wrapper Classes for Stored Procedures.