Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Article

Data Access Layer (DAL) with SqlWrapper library

Rate me:
Please Sign up or sign in to vote.
4.74/5 (49 votes)
12 Jun 20057 min read 428.3K   5.9K   221   81
With SqlWrapper, you can easily create your DAL classes writing the most minimum amount of code.

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:

C#
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:

C#
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:

C#
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:

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

will be implemented like this:

C#
[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.

Image 1

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:
    C#
    [SWCommand(SWCommandType.InsertUpdate, "Shippers")]
    public abstract void ShippersInsertUpdate
        (
        [SWParameter(SWParameterType.Identity)]ref int ShipperID,
        [SWParameter(40)]string CompanyName,
        [SWParameter(24)]string Phone
        );

    SQL expression:

    SQL
    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:
    C#
    [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:

    SQL
    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:

C#
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):

C#
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:

Image 2

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

C#
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.

C#
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:

C#
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:

C#
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:

VB
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:

VB
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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Australia Australia
I am MCSD in .Net and VB6 and have a Bachelor degree in Computer Science. During my 8+ years of experience as a Software Developer I dealt with GUI, server side and database development. I live in Sydney, NSW, Australia.

Comments and Discussions

 
GeneralProblem with (Method not found: '?'.) Pin
Member 380238324-Sep-10 15:38
Member 380238324-Sep-10 15:38 
GeneralCongratulation Pin
Member 38023839-Apr-10 1:46
Member 38023839-Apr-10 1:46 
GeneralProblem with SqlWrapper in an ASP.NET 2.0 web service Pin
indra30407-Dec-09 6:20
indra30407-Dec-09 6:20 
GeneralAccessViolationException started to appear Pin
vsoft6-Feb-09 9:00
vsoft6-Feb-09 9:00 
GeneralError:Commn Language Runtime detected an invalid program Pin
praveenkumar palla30-Oct-08 20:21
praveenkumar palla30-Oct-08 20:21 
GeneralRe: Error:Commn Language Runtime detected an invalid program Pin
Sam Safonov26-Nov-08 14:30
Sam Safonov26-Nov-08 14:30 
GeneralUpdate reference Pin
pkmishra26-Apr-07 23:11
pkmishra26-Apr-07 23:11 
QuestionHow pass null values in insert statement Pin
souravganguly19-Oct-06 10:44
souravganguly19-Oct-06 10:44 
GeneralUsing TreatAsNull attribute for bit data type Pin
stamimi25-Aug-06 13:27
stamimi25-Aug-06 13:27 
What value should we use to set a bit to null? In the identity column, it looks like any negative number works, but with a bit we only have two options.

Please advice.
GeneralWhere was wrong? [modified] Pin
sonyama21-Aug-06 20:30
sonyama21-Aug-06 20:30 
GeneralExtending the Code [modified] Pin
dodiggitydag23-May-06 3:12
dodiggitydag23-May-06 3:12 
GeneralVisual Studio 2005 - String[0]: the size property has an invalid size of 0 Pin
carloagl10-Apr-06 5:47
carloagl10-Apr-06 5:47 
GeneralRe: Visual Studio 2005 - String[0]: the size property has an invalid size of 0 Pin
Andrey Shchurov10-Apr-06 17:52
Andrey Shchurov10-Apr-06 17:52 
GeneralRe: Visual Studio 2005 - String[0]: the size property has an invalid size of 0 Pin
carloagl10-Apr-06 21:54
carloagl10-Apr-06 21:54 
JokeI've solved it Pin
carloagl11-Apr-06 3:10
carloagl11-Apr-06 3:10 
GeneralRe: I've solved it Pin
deloford10-Jul-06 8:19
deloford10-Jul-06 8:19 
NewsCollaboration Pin
Salvatore Vetro27-Mar-06 21:56
Salvatore Vetro27-Mar-06 21:56 
GeneralVS2005 and InvalidProgramException Pin
salado10-Feb-06 11:27
salado10-Feb-06 11:27 
Questioni got the same problem too. DO someone can help us? Pin
glovebx4-Apr-06 14:29
glovebx4-Apr-06 14:29 
Jokei just figured it out!! Pin
glovebx4-Apr-06 18:18
glovebx4-Apr-06 18:18 
GeneralRe: i just figured it out!! Pin
Leonardo Cooper11-Dec-06 1:06
Leonardo Cooper11-Dec-06 1:06 
GeneralWhy not using ISqlWrapperBase instead of SqlWrapperBase Pin
kandlhans28-Nov-05 20:52
kandlhans28-Nov-05 20:52 
QuestionUsing sqlwrapper in vs2005? Pin
ideage14-Nov-05 22:58
ideage14-Nov-05 22:58 
AnswerRe: Using sqlwrapper in vs2005? Pin
ideage15-Nov-05 5:29
ideage15-Nov-05 5:29 
GeneralRe: Using sqlwrapper in vs2005? Pin
dodiggitydag22-May-06 4:29
dodiggitydag22-May-06 4:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.