ADO.Net Framework as Database Helper

 Interface to work with ADO.Net sample and it will work like DB helper class


using System;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;
using System.Web.UI;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Caching;
using System.Text;

/// <summary>
/// Summary description for DataBaseInterface
/// </summary>
///

namespace Ramosh.Support.DataLayer
{
    public class DBInterface : BasePage<DBInterface>
    {
        /// <summary>
        /// Enum of DataTables in DataSet
        /// </summary>
        public enum Tables
        {
            Statuses = 0,
            Types,
            Categories
        }

        public enum eGetValueDropDown
        {
            STATE ,
            CITY ,
            LOCATION
        }

        #region Properties & Fields

        /// <summary>
        /// Property to manage the DataSet of static data
        /// </summary>
        private static DataSet StaticDataSet
        {
            get
            {
                // Get DataSet from Cache
                Cache oCache = HttpContext.Current.Cache;
                DataSet ds = (DataSet)oCache[StaticDataSetCache];

                // If the DataSet is not in Cache then generate it and cache it
                if (ds == null)
                {
                    ds = CacheStaticData();
                }

                return ds;
            }
        }

        private const string StaticDataSetCache = "StaticDataSetCache";

        #endregion

        /// <summary>
        /// Bind static data to a ListControl (DropDownList / ListBox / RadioButtonList / CheckBoxList) with a header value
        /// </summary>
        /// <param name="listControl"></param>
        /// <param name="tableIndex"></param>
        /// <param name="header"></param>
        public static void Bind(ListControl listControl, Tables tableIndex, string header)
        {
            // Get the DataTable from the cached DataSet
            DataTable dt = GetData(tableIndex);

            // Set the DataValueField and DataTextField of the LsitControl       
            listControl.DataValueField = dt.Columns[0].ToString();
            listControl.DataTextField = dt.Columns[1].ToString();

            // Bind the data to the LsitControl
            listControl.DataSource = dt;
            listControl.DataBind();

            // Add the header row if required
            if (header.Length > 0)
            {
                listControl.Items.Insert(0, new ListItem("- " + header + " -", "0"));
            }
        }

        /// <summary>
        /// Clear the Static Data from the Cache
        /// </summary>
        public static void ClearCache()
        {
            Cache oCache = HttpContext.Current.Cache;
            oCache.Remove("StaticDataSetCache");
        }

        /// <summary>
        /// Cache Static Data
        /// </summary>
        public static DataSet CacheStaticData()
        {
            Cache oCache = System.Web.HttpContext.Current.Cache;

            // Get Static DataSet
            DataSet ds = GetDataSet(Convert.ToString(eGetValueDropDown.STATE));
            // Insert into cache
            oCache.Insert(StaticDataSetCache, ds);

            return ds;
        }

        /// <summary>
        /// Get a DataTable from the cached DataSet
        /// </summary>
        /// <param name="tableIndex"></param>
        /// <returns></returns>
        public static DataTable GetData(Tables tableIndex)
        {
            return StaticDataSet.Tables[(int)tableIndex];
        }


      


        public SqlConnection con;
        private static int SQL_DEADLOCK_ERROR_NUM = 1205;
        #region database connection stuffs and parmeters
        /// <summary>
        /// Returns a new SqlConnection.
        /// </summary>
        /// <returns>SqlConnection object</returns>
        public static SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ASPNETDB"].ConnectionString; ;
            return conn;
        }
        /// <summary>
        /// Helper method to set properties of command object and its parameters
        /// </summary>
        /// <param name="spParams"></param>
        /// <param name="command"></param>
        private static void InflateCommandObject(SpParamCollection spParams, SqlCommand command)
        {
            command.CommandTimeout = 0;//Config.ExecuteTimeout;
            command.CommandType = CommandType.StoredProcedure;

            if (null == spParams) return;

            foreach (SpParam param in spParams)
            {
                if (true == param.ParamDirectionOut)
                    command.Parameters.Add(param.Name, param.Value).Direction = ParameterDirection.Output;
                else
                    command.Parameters.Add(param.Name, param.Value);
            }
        }


        #endregion

        # region Exceute non query command and insert and update the vale in databas
        // <summary>
        /// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return integer which is getting from DB
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static Int16 ExecProcNonQueryText(string procName, SpParamCollection spParams)
        {
            Int16 ret = 0;
            ExecProcNonQueryText(procName, spParams, GetConnection(), out ret);
            return ret;
        }

        /// <summary>
        /// Execute the given stored procedure. No results are expected from the proc.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static void ExecProcNonQueryText(string procName, SpParamCollection spParams, SqlConnection conn, out Int16 ret)
        {
            SqlCommand command = new SqlCommand();
            try
            {
                conn.Open();
               
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                command.CommandText = procName;
                //command.ExecuteNonQuery();
               // InflateCommandObject(spParams, command);
               
             //   command.CommandTimeout = 0;
                ret = (Int16)command.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        command.CommandTimeout = 60;
                        ret = (Int16)command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
            finally
            {
                command = null;
                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }
        }
        // <summary>
        /// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return integer which is getting from DB
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static Int16 ExecProcNonQuery(string procName, SpParamCollection spParams)
        {
            Int16 ret = 0;
            ExecProcNonQuery(procName, spParams, GetConnection(), out ret);
            return ret;
        }
        /// <summary>
        /// Execute the given stored procedure. No results are expected from the proc.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static void ExecProcNonQuery(string procName, SpParamCollection spParams, SqlConnection conn, out Int16 ret)
        {
            SqlCommand command = new SqlCommand(procName, conn);


            try
            {
                InflateCommandObject(spParams, command);
                conn.Open();
                command.CommandTimeout = 0;
                ret = (Int16)command.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        command.CommandTimeout = 60;
                        ret = (Int16)command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
            finally
            {
                command = null;
                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }
        }
        # endregion

        #region To populate the data into the datareader from database

        /// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return datareader which is getting from DB
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static SqlDataReader ExecProcAndPopulateDataReader(string procName, SpParamCollection spParams)
        {
            SqlDataReader dr;
            ExecProcAndPopulateDataReader(procName, spParams, out dr, GetConnection());
            return dr;
        }

        /// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return datareader which is getting from DB
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static SqlDataReader ExecProcAndPopulateDataReaderText(string QueryText,SpParamCollection spParams)
        {
            SqlDataReader dr;
            ExecProcAndPopulateDataReaderTextType(QueryText, out dr, GetConnection());
            return dr;
        }

        public static void ExecProcAndPopulateDataReaderTextType(string QueryText,  out SqlDataReader dr, SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(QueryText, conn);
            conn.Open();

            try
            {
                command.CommandType = CommandType.Text;
              //  InflateCommandObject(spParams, command);
                dr = command.ExecuteReader();
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        dr = command.ExecuteReader();
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
        }


        /// <summary>
        /// Execute the given stored procedure and then populate the DataReader with the data.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        /// <param name="dr"></param>
        public static void ExecProcAndPopulateDataReader(string procName, SpParamCollection spParams, out SqlDataReader dr, SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(procName, conn);
            conn.Open();

            try
            {
                InflateCommandObject(spParams, command);
                dr = command.ExecuteReader();
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        dr = command.ExecuteReader();
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
        }
        #endregion

        #region Use of dataset to populate the balue from database and put into dataset

        /// <summary>
        /// Overloaded method for ExecProcAndFillDataSet that uses default sql connection
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        /// <param name="ds"></param>
        public static void ExecProcAndFillDataSetText(string procName, SpParamCollection spParams, DataSet ds)
        {
            ExecProcAndFillDataSetText(procName, spParams, ds, GetConnection());
        }
        /// <summary>
        /// Execute the given stored procedure and then populate the DataSet with the returned data.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        /// <param name="ds"></param>
        public static void ExecProcAndFillDataSetText(string procName, SpParamCollection spParams, DataSet ds, SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(procName, conn);
            command.CommandType = CommandType.Text;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            try
            {
                InflateCommandObject(spParams, command);

                //Fill DataSet

                dataAdapter.Fill(ds);
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        dataAdapter.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
            finally
            {
                command = null;
                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }


        /// <summary>
        /// Overloaded method for ExecProcAndFillDataSet that uses default sql connection
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        /// <param name="ds"></param>
        public static void ExecProcAndFillDataSet(string procName, SpParamCollection spParams, DataSet ds)
        {
            ExecProcAndFillDataSet(procName, spParams, ds, GetConnection());
        }
        /// <summary>
        /// Execute the given stored procedure and then populate the DataSet with the returned data.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        /// <param name="ds"></param>
        public static void ExecProcAndFillDataSet(string procName, SpParamCollection spParams, DataSet ds, SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(procName, conn);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            try
            {
                InflateCommandObject(spParams, command);

                //Fill DataSet

                dataAdapter.Fill(ds);
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        dataAdapter.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
            finally
            {
                command = null;
                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        #endregion

        #region Read data from databes and return it into XML File

        // <summary>
        /// Overloaded method for ExecProcAndReturnXmlDocument that uses default sql connection
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static XmlDocument ExecProcAndReturnXmlDocument(string procName, SpParamCollection spParams)
        {
            XmlDocument xmlDoc = null;
            xmlDoc = ExecProcAndReturnXmlDocument(procName, spParams, GetConnection());
            return xmlDoc;
        }
        /// <summary>
        /// Execute the given stored procedure which is expected to return an xml document
        /// (using sql FOR XML clause), and then return that xml document.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="spParams"></param>
        public static XmlDocument ExecProcAndReturnXmlDocument(string procName, SpParamCollection spParams, SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(procName, conn);
            XmlDocument xmlDoc = null;

            try
            {
                InflateCommandObject(spParams, command);
                conn.Open();

                //Get XmlDocument
                XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
                xmlDoc = new XmlDocument();
                xmlDoc.Load(reader);
                reader.Close();
            }
            catch (SqlException sqlEx)
            {
                if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
                {
                    try
                    {
                        //Get XmlDocument
                        XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
                        xmlDoc = new XmlDocument();
                        xmlDoc.Load(reader);
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        throw ex; ;
                    }
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception ex)
            {
                throw ex; ;
            }
            finally
            {
                command = null;
                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return xmlDoc;
        }
        #endregion

        #region Private Methods to get the value in data set

        /// <summary>
        /// Get Static data from database
        /// </summary>
        /// <returns></returns>
        public  static DataSet GetDataSet(string SprocName)
        {
            SqlDataReader dr = null;
            DataSet ds = new DataSet();
            try
            {
                //SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StaticDataHelperConnectionString"].ToString());
              //  SqlCommand cmd = new SqlCommand("proc_StaticDataSelect", GetConnection());
                SqlCommand cmd = new SqlCommand(SprocName, GetConnection());

               
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // Load DataSet with multiple resultsets from the SqlDataReader
                ds.Load(dr, LoadOption.OverwriteChanges, GetDataTablesValuesArray());

                return ds;
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                {
                    dr.Close();
                }
            }
        }

        /// <summary>
        /// Helper method to return an string array of the values in the Tables enum
        /// </summary>
        /// <returns></returns>
        private static string[] GetDataTablesValuesArray()
        {
            string[] s = new string[0];
            foreach (Tables value in Enum.GetValues(typeof(Tables)))
            {
                Array.Resize(ref s, s.Length + 1);
                s.SetValue(value.ToString(), s.Length - 1);
            }
            return s;
        }

        #endregion
    }

    #region Structs Make a parmetariged straucture
    /// <summary>
    /// Represents a stored procedure's command paramater
    /// name/value pair and the direction of the parameter
    /// </summary>
    public struct SpParam
    {
        private string m_paramName;
        private object m_paramValue;
        private bool m_paramDirectionOut;

        public string Name
        {

            get { return m_paramName; }
        }

        public object Value
        {
            get { return m_paramValue; }
        }

        public bool ParamDirectionOut
        {
            get { return m_paramDirectionOut; }
        }

        /// <summary>
        /// Overridden constructor that sets the paramDirectionOut parameter to false
        /// </summary>
        public SpParam(string paramName, object paramValue) : this(paramName, paramValue, false) { }

        /// <summary>
        /// Constructor that sets paramDirectionOut parameter specifically
        /// </summary>
        public SpParam(string paramName, object paramValue, bool paramDirectionOut)
        {
            m_paramName = paramName;
            m_paramValue = paramValue;
            m_paramDirectionOut = paramDirectionOut;
        }
    }
    #endregion

    #region Parameters
    /// <summary>
    /// Represents a collection of parameters (SpParams').
    /// </summary>
    ///

    public class SpParamCollection : ArrayList
    {
        public SpParamCollection()
        {
        }

        public void Add(SpParam param)
        {
            base.Add(param);
        }
        public void clear()
        {
            base.Clear();
        }

    }

    #endregion
}

==================================================


Using of the Method which is created there as

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
/// Summary description for WIN_CgPearl
/// </summary>
///
namespace Ramosh.Support.DataLayer.DAL
{
    public class WIN_CgPearlDAL : DBInterface
    {
        public WIN_CgPearlDAL()
        {
        }

        internal static Int16 Insert_CgPearl(WIN_CgPearl objCgPearl)
        {
            Int16 ret = 0;
            DataSet dts = new DataSet();
            try
            {
                SpParamCollection spParams = new SpParamCollection();

                spParams.Add(new SpParam("@CgPearlName", objCgPearl.CgPearlName));
                spParams.Add(new SpParam("@CreatedDate", objCgPearl.CreatedDate));
                spParams.Add(new SpParam("@ModifiedDate", objCgPearl.ModifiedDate));
                spParams.Add(new SpParam("@IsActive", objCgPearl.IsActive));
                ret = ExecProcNonQuery("WIN_Insert_CgPearl", spParams);
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            return ret;
        }

        internal static Int16 Delete_CgPearl(WIN_CgPearl objCgPearl)
        {
            Int16 ret = 0;
            DataSet dts = new DataSet();
            try
            {
                SpParamCollection spParams = new SpParamCollection();

                spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
                ret = ExecProcNonQuery("WIN_Delete_CgPearl", spParams);
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            return ret;
        }

        internal static Int16 Update_CgPearl(WIN_CgPearl objCgPearl)
        {
            Int16 ret = 0;
            DataSet dts = new DataSet();
            try
            {
                SpParamCollection spParams = new SpParamCollection();
                spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
                spParams.Add(new SpParam("@CgPearlName", objCgPearl.CgPearlName));
                spParams.Add(new SpParam("@ModifiedDate", objCgPearl.ModifiedDate));
                spParams.Add(new SpParam("@IsActive", objCgPearl.IsActive));
                ret = ExecProcNonQuery("WIN_Update_CGPearl", spParams);
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            return ret;
        }

        internal static BasePage<WIN_CgPearl> Select_CgPearl()
        {
            IDataReader oReader = null;
            BasePage<WIN_CgPearl> objCgPearl = new BasePage<WIN_CgPearl>();
         
                SpParamCollection spParams = new SpParamCollection();
                oReader = ExecProcAndPopulateDataReader("WIN_Select_CgPearl", spParams);
                while (oReader.Read())
                {
                    objCgPearl.Add(new WIN_CgPearl(oReader));
                }
          
            return objCgPearl;
        }

        internal static BasePage<WIN_CgPearl> Select_CgPearl_Id(WIN_CgPearl objCgPearl)
        {
            IDataReader oReader = null;
            BasePage<WIN_CgPearl> objBCgPearl = new BasePage<WIN_CgPearl>();
            try
            {
                SpParamCollection spParams = new SpParamCollection();
                spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
                oReader = ExecProcAndPopulateDataReader("WIN_Select_CgPearl_Id", spParams);
                while (oReader.Read())
                {
                    objBCgPearl.Add(new WIN_CgPearl(oReader));
                }
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            return objBCgPearl;
        }
    }
}