DataAccess Layer

using System;
using System.Data;
using System.Configuration;
using System.Linq;
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;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;

namespace Cms.BusinessLogic
{
    public class DataAccessBase
    {

        public enum InfoType
        {
            Video = 1,
            Event,
            Practise,
            Profile,
            StaticPage
        }
        #region All Private Member Variables are declare here
        SqlConnection _SqlCon;
        SqlDataAdapter _SqlDa;
        private int _DBReturnInt;
        private string _DBReturnString;
        private decimal _DBReturnDecimal;
        # endregion

        #region All Property of Private Member Variables are declare here

        public decimal DBReturnDecimal
        {
            get
            {
                return _DBReturnDecimal;
            }
        }

        public int DBReturnInt
        {
            get
            {
                return _DBReturnInt;
            }
        }

        public string DBReturnString
        {
            get
            {
                return _DBReturnString;
            }
        }

        # endregion

        #region Constructor

        public DataAccessBase()
        {
            _SqlCon = new SqlConnection(Convert.ToString(ConfigurationManager.ConnectionStrings["DocStrn"]));
        }

        #endregion

        #region Method
      

        public Boolean InsertRecord(SqlCommand _SqlCmd)
        {
            Int32 _RowAffect = ExecuteNonQuery(_SqlCmd);
            if (_RowAffect == 1)
                return true;
            else
                return false;
        }

        public Boolean UpdateRecord(SqlCommand _SqlCmd)
        {
            Int32 _RowAffect = ExecuteNonQuery(_SqlCmd);
            if (_RowAffect >= 1)
                return true;
            else
                return false;
        }

        public Boolean DeleteRecord(SqlCommand _SqlCmd)
        {
            Int32 _RowAffect = ExecuteNonQuery(_SqlCmd);
            if (_RowAffect >= 1)
                return true;
            else
                return false;

        }

        public Boolean ReturnInt(SqlCommand _SqlCmd)
        {
            object obj = ExecuteScalar(_SqlCmd);
            if (obj != null)
            {
                _DBReturnInt = Convert.ToInt32(obj);
                return true;
            }
            else
                return false;

        }

        public Boolean ReturnString(SqlCommand _SqlCmd)
        {
            object obj = ExecuteScalar(_SqlCmd);
            if (obj != null)
            {
                _DBReturnString = Convert.ToString(obj);
                return true;
            }
            else
                return false;
        }

        public Boolean ReturnDecimal(SqlCommand _SqlCmd)
        {
            object obj = ExecuteScalar(_SqlCmd);
            if (obj != null)
            {
                _DBReturnDecimal = Convert.ToDecimal(obj);
                return true;
            }
            else
                return false;
        }

        public DataSet GetDataSet(SqlCommand _SqlCmd)
        {
            DataSet ds = new DataSet();
            try
            {
                if (_SqlCmd != null)
                {
                    OpenConnection();
                    _SqlCmd.Connection = _SqlCon;
                    _SqlDa = new SqlDataAdapter();
                    _SqlDa.SelectCommand = _SqlCmd;
                    _SqlDa.Fill(ds);
                    return ds;
                }
                else
                    return null;

            }
            catch { throw; }
            finally
            {
                CloseConnection();
            }
        }

        //public DataTable GetDataTable(SqlCommand _SqlCmd)
        //{
        //    SqlDataReader _SqlDr;
        //    DataTable dtResult = new DataTable();
        //    try
        //    {
        //        _SqlDr = ExecuteReader(_SqlCmd);
        //        dtResult.Load(_SqlDr);
        //    }
        //    catch { throw; }
        //    finally
        //    {
        //        _SqlDr = null;
        //        CloseConnection();
        //    }
        //    return dtResult;
        //}

        public List<T> ExecuteReader<T>(SqlCommand _SqlCmd)
        {
            SqlDataReader dr;
            OpenConnection();
            _SqlCmd.Connection = _SqlCon;
            dr = _SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
            List<T> list = new List<T>();
            T obj = default(T);
            using (_SqlCon)
            {
                while (dr.Read())
                {
                    obj = Activator.CreateInstance<T>();
                    foreach (PropertyInfo prop in obj.GetType().GetProperties())
                    {
                        try
                        {
                            if (!object.Equals(dr[prop.Name], DBNull.Value))
                            {
                                prop.SetValue(obj, dr[prop.Name], null);
                            }
                        }
                        catch (IndexOutOfRangeException ex)
                        {
                      
                        }
                    }
                  
                    list.Add(obj);
                }
            }
          
            return list;
          
        }

        //public static List<T> DataReaderMapToList<T>(IDataReader dr)
        //{
        //    List<T> list = new List<T>();
        //    T obj = default(T);
        //    while (dr.Read())
        //    {
        //        obj = Activator.CreateInstance<T>();
        //        foreach (PropertyInfo prop in obj.GetType().GetProperties())
        //        {
        //            if (!object.Equals(dr[prop.Name], DBNull.Value))
        //            {
        //                prop.SetValue(obj, dr[prop.Name], null);
        //            }
        //        }
        //        list.Add(obj);
        //    }
        //    return list;
        //}

     
        private int ExecuteNonQuery(SqlCommand _SqlCmd)
        {
            Int32 _RowAffect = 0;
            try
            {
                if (_SqlCmd != null)
                {
                    OpenConnection();
                    _SqlCmd.Connection = _SqlCon;
                    _RowAffect = _SqlCmd.ExecuteNonQuery();
                }
            }
            catch { throw; }
            finally
            {
                CloseConnection();
            }
            return _RowAffect;
        }

        private void CloseConnection()
        {
            if (_SqlCon.State == ConnectionState.Open)
            {
                _SqlCon.Close();
            }
        }

        private void OpenConnection()
        {
            if (_SqlCon.State == ConnectionState.Closed)
            {
                _SqlCon.Open();
            }
        }

        private object ExecuteScalar(SqlCommand _SqlCmd)
        {
            object obj = null;
            try
            {
                if (_SqlCmd == null)
                {

                    return false;
                }
                else if (_SqlCmd != null)
                {
                    OpenConnection();
                    _SqlCmd.Connection = _SqlCon;
                    obj = _SqlCmd.ExecuteScalar();
                }

            }
            catch { throw; }
            finally
            {
                CloseConnection();
            }
            return obj;
        }     

        private List<Event> GetEvent(SqlDataReader reader)
        {
            Event info = new Event(reader);
            List<Event> infolist = new List<Event>();
            infolist.Add(info);
            return infolist;
        }

        private List<StaticPage> GetStaticPage(SqlDataReader reader)
        {
            StaticPage info = new StaticPage(reader);
            List<StaticPage> infolist = new List<StaticPage>();
            infolist.Add(info);
            return infolist;
        }

        private List<Profile> GetProfile(SqlDataReader reader)
        {
            Profile info = new Profile(reader);
            List<Profile> infolist = new List<Profile>();
            infolist.Add(info);
            return infolist;
        }

        private List<Practise> GetPractise(SqlDataReader reader)
        {
            Practise info = new Practise(reader);
            List<Practise> infolist = new List<Practise>();
            infolist.Add(info);
            return infolist;
        }

        private List<Video> GetVideo(SqlDataReader reader)
        {
            Video info = new Video(reader);
            List<Video> infolist = new List<Video>();
            infolist.Add(info);
            return infolist;
        }


        #endregion
    }
}