Generic Data Layer - One time creation

Introduction

It is a known fact that most of the time, we create applications for creating, Selecting, updating, and Deleting. And every time, we end up writing a similar set of code for every execution of our stored procedures.

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

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

Prerequisites 

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 a Generic Data Layer?

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 of Generic Data Layer in C#

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

Example

using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
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 are 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 the Generic Data Layer in C#?

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");

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");

SPs without any parameters.

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

Conclusion

I hope this will of helpful to you.  Please try this and extend the class as you wish. Please post your suggestions and feedback.