ARTICLE

Generic Data Layer - One time creation

Posted by Santhosh Veeraraman Articles | Visual C# September 06, 2007
This article will explain how to create a generic data layer that can be used with very less amount of coding. It is a known fact that, most of the time we create applications for Create , Select , Update, Delete. And every time, we end up with writing similar set of code for every execution of our stored procedures.
Reader Level:

Introduction:

 

It is a known fact that, most of the time we create applications for Create, Select, Update, Delete. And every time, we end up with writing similar set of code for every execution of our stored procedures.

 

Our approach is constantly repeated like creating connection, creating command object, adding parameters etc and finally to execute.

 

I am just trying to write a wrapper class for the data application block.

 

Pre-assumptions:

 

We assume that, the database is MS SQL Server (widely used) we take advantage of Microsoft Data Application Block. I am attaching the dll file.

 

Why do I need it?

 

Mainly Re-Usability of code.

 

Once you come up with a generic class for your data access, you can use it anywhere. Once you are finished with it, you can achieve the task with very less amount of coding.

 

Limitations:

 

For now, the code is only for stored procedures. Extend it as you wish. Please overload the methods if you wish to have. Likewise, you can also have extended methods as we have in our SqlHelper class. (Data application block).

 

Code:

 

using System;

using System.Data;

using System.Collections;

using System.Collections.Generic;

using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

 

/// <summary>

/// This is a generic DAC layer.

/// </summary>

 

namespace Ellaar.DAC

{

    public class DAC

    {

        // Execute the stored procedure

        public static DataSet Execute(string StoredProcName)

        {

            // Generic collection of my DacParameter object

            List<Parameters.DacParameter> ParameterPairs = Parameters.MyParameters;

            DataSet ds;

 

            // Validation for Parameter collection

            try

            {

                // if you want to force the user to give parameters, use this code

                if (ParameterPairs.Count <= 0)

                {

                    //throw new Exception("Invalid parameter supply. Check your BL Method");

                }

            }

           

            catch (Exception Ex)

            {

                throw (Ex);

            }

 

            // Get connection String - I've created this for web app. Hence it is reading from web.config.

            // You can modify this as you wish.

            System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);

            string ConnString = "";

 

            if (0 < rootWebConfig.ConnectionStrings.ConnectionStrings.Count)

            {

                ConnString = rootWebConfig.ConnectionStrings.ConnectionStrings["DfltConnection"].ToString();

            }

 

            // Using a Transaction Here: To ensure data integrity

            SqlTransaction Trans;

            using (SqlConnection Conn = new SqlConnection(ConnString))

            {

                Conn.Open();

                Trans = Conn.BeginTransaction();

                try

                {

                    // If there is any parameters then iterate through the collection

                    // and bind it

                    if (ParameterPairs.Count > 0)

                    {

                        SqlParameter[] arparams = new SqlParameter[ParameterPairs.Count];

 

                        int Count = 0;

                        foreach (Parameters.DacParameter pObject in ParameterPairs)

                        {

                            arparams[Count] = new SqlParameter(pObject.Name, pObject.Type);

                            arparams[Count].Value = pObject.Value;

 

                            //Increment counter

                            Count++;

                        }

 

                        // Execute stored procedure

                        ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName, arparams);

                    }

 

                    // else I just need to execute it Eg: Procedures that have only SELECT queries

                    else

                    {

                        ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName);

                    }

 

                    // Yes, go ahead with Committing the transaction

                    Trans.Commit();

                }

               

                catch (Exception Ex)

                {

                    Trans.Rollback();

                    throw (Ex);

                }

 

                finally

                {

                    if (Conn.State == ConnectionState.Open)

                    {

                        Conn.Close();

                        Conn.Dispose();

                    }

                    Parameters.MyParameters.Clear();

                }

 

                return ds;

            }

        }

 

        // My Parameters class

        public class Parameters

        {

            protected internal static List<DacParameter> MyParameters = new List<DacParameter>();

            public static void Add(string Name, SqlDbType dbtype, object value)

            {

                DacParameter KeyValuePair = new DacParameter();

                KeyValuePair.Name = Name;

                KeyValuePair.Type = dbtype;

                KeyValuePair.Value = value;

                MyParameters.Add(KeyValuePair);

            }

 

            // class param, type , value class : Aggregation

            public class DacParameter

            {

                private string _ParameterName;

                public string Name

                {

                    get

                    {

                        return _ParameterName;

                    }

                    set

                    {

                        _ParameterName = value;

                    }

                }

 

                private System.Data.SqlDbType _Type;

                public System.Data.SqlDbType Type

                {

                    get

                    {

                        return _Type;

                    }

                    set

                    {

                        _Type = value;

                    }

                }

 

                private object _value;

                public object Value

                {

                    get

                    {

                        return _value;

                    }

                    set

                    {

                        _value = value;

                    }

                }

            }

        }

    }

}

  

How do I use it? (Some examples):

 

  1. To create a State

        DAC.Parameters.Add("@StateName", SqlDbType.VarChar, StateName);

        DAC.Parameters.Add("@StateAbbr", SqlDbType.VarChar, StateAbbr);

        DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath);

        DAC.Execute("spInsertState");

 

  1. To create a City

        DAC.Parameters.Add("@CityName", SqlDbType.VarChar, CityName);

        DAC.Parameters.Add("@MetroId", SqlDbType.Int, MetroId);

        DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath);

        DAC.Execute("spInsertCity");

 

  1. SPs without any parameters

    DataSet ds = DAC.Execute("spGetStates");

 

Conclusion:

 

I hope this could of helpful for you.  Please try this and extend the class as you wish. Please do post your suggestions and feedback. 

COMMENT USING