C# BaseDAL sample code

using System;
using System.Configuration;

using System.Data;

using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace CRM.Lib.DAL {
    internal abstract class BaseDAL {

        protected SqlConnection cnn;
        protected SqlCommand com;
        protected SqlTransaction tran;

        #region CTOR



        protected BaseDAL() {
            //add reference to System.Configuration.dll

            string connStr = "Data Source=" + ConfigurationManager.AppSettings["srv"] + ";";
            connStr += "user=sa;password=" + ConfigurationManager.AppSettings["pwd"] + ";";
            connStr += " database=" + ConfigurationManager.AppSettings["db"] + ";";

            //connection pooling
            /**/
            connStr += "connection reset=false;";
            connStr += "connection lifetime=0;";
            connStr += "enlist=true;";
            connStr += "min pool size=1;";
            connStr += "max pool size=50";
            /**/
            cnn = new SqlConnection(connStr);


            this.com = new SqlCommand();
            this.com.CommandType = CommandType.StoredProcedure;
            this.com.CommandTimeout = 120;
            //SqlDependency.Start(connD);
            try {
                if (this.cnn.State == ConnectionState.Closed) this.cnn.Open();
                this.com.Connection = cnn;
            }
            catch (Exception ex) {
#if DEBUG
                throw new Exception(ex.Message);
#else
                throw new Exception(ex.Message);
                //throw new Exception("error");
#endif
            }

        }

        ~BaseDAL() {
            try {
                //if (cnn != null)
                //    cnn.Dispose();
            }
            finally { }
        }

        #endregion

        #region Methods

        protected DataRow GetFirstRow() {
            DataTable dt = this.GetDataTable();

            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;

        }

        protected DataTable GetDataTable() {
            DataTable ret = new DataTable();
            try {
                ret.Load(this.com.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            cnn.Close();

            return ret;
        }

        protected DataTable GetDataTable2(string tablename) {
            DataTable ret = new DataTable();
            try {
                if (this.cnn.State == ConnectionState.Closed) this.cnn.Open();
                ret.Load(this.com.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            cnn.Close();

            return ret;
        }
        protected SqlDataReader GetDataReader() {
            return this.com.ExecuteReader(CommandBehavior.CloseConnection);

        }

        protected DataSet GetDataSet2(string parentname, string childname) {
            DataSet ds = new DataSet();

            DataTable parent = new DataTable(parentname);
            DataTable child = new DataTable(childname);

            try {
                SqlDataReader dr = this.com.ExecuteReader();

                parent.Load(dr);
                ds.Tables.Add(parent);

                dr.NextResult();

                child.Load(dr);

                ds.Tables.Add(child);


                dr.Close();
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
            }


            cnn.Close();

            return ds;
        }

        void dep_OnChange(object sender, SqlNotificationEventArgs e) {
            System.Diagnostics.Debug.WriteLine("Received OnChange Event");

            //if (e.Info ==NpgsqlConnection.  SqlNotificationInfo.Invalid) {
            //    System.Diagnostics.Debug.WriteLine("Invalid Statement");
            //}
            return;
        }

        protected DataSet GetDataSet(string tablename) {
            SqlDataAdapter da = new SqlDataAdapter(this.com);

            DataSet ret = new DataSet();
            da.Fill(ret, tablename);

            cnn.Close();
            da.Dispose();

            return ret;
        }

        internal void Begin() {
            tran = this.cnn.BeginTransaction();
            this.com.Transaction = tran;
        }
        internal void End(bool Commit) {
            if (Commit)
                tran.Commit();
            else
                tran.Rollback();
        }

        #endregion
    }
}