Multipurpose Data Access Layer part 2

In this 2nd part i’ll try to describe more detail in coding which i mentioned it in part 1,

a. ConnectionString Class

• First Of all you need to save your connection string variable in your web config, in my case i put all of the setting in particular configuration setting class which being taken from webconfig setting.
• I must inherit from the WebPageBase class for become the master of base page class, this WebPageBase class inherit the System.web.UI.Page , this class will become the property provider to every page in Web UI such as retrive the current user,current password and of course the applicationID that we need.
• And the last thing i made two public static method which return the string of connectionstring and providername, both of those method will filter the appropriate connection string and provider name which inputed from each outside ( define input parameter ).

     let see the complete module :

     //********************************************************************************************************
// SUBJECT      : MULTIPLE CONNECTION STRING AND PROVIDER CLASS
// CREATED BY   : DODDY CH SAPUTRA , MCAD.NET 
// CREATED DATE : JUNE 26,2007
//********************************************************************************************************

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using FrameworkLayer.WebUtility;
using FrameworkLayer.ConfigurationUtility;
namespace FrameworkLayer.DataUtility
{
    public class ConnectionToDatabase : FrameworkLayer.WebUtility.WebPageBase
    {

   
        //******* Code yang udAH OKAY MONCOR *****************
        //public string Key
        //{
        //    get { return base.IdApplication; }
        //}
        //****************************************************

        //******** Tester Key ********************************
        public string _Key = “GCS”;

        public string Key
        {
            get { return _Key; }
            set { _Key = “GCS”; }
        }
        //******************************************************
        public static string GetCN()
        {
            string Cn = string.Empty;
            ConnectionToDatabase oKey = new ConnectionToDatabase();
            string Key = oKey.Key.ToString();

            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.GcsAppKey)
            {
                Cn = ConfigurationManager.ConnectionStrings["CN_GCS"].ConnectionString;
            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.ElearningAppKey)
            {
                Cn = ConfigurationManager.ConnectionStrings["CN_ELN"].ConnectionString;
            }
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.MMwebAppkey)
            {
                Cn = ConfigurationManager.ConnectionStrings["CN_MMW"].ConnectionString;

            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.BidAppKey)
            {
                Cn = ConfigurationManager.ConnectionStrings["CN_BID"].ConnectionString;

            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.OtherAppKey)
            {
                Cn = ConfigurationManager.ConnectionStrings["CN_OTHERS"].ConnectionString;
            }

            return Cn;
        }

        public static string GetPV()
        {

            string Pv = string.Empty;
            ConnectionToDatabase oKey = new ConnectionToDatabase();
            string Key = oKey.Key.ToString();

            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.GcsAppKey)
            {
                Pv = ConfigurationManager.ConnectionStrings["CN_GCS"].ProviderName ;
            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.ElearningAppKey)
            {

                Pv = ConfigurationManager.ConnectionStrings["CN_ELN"].ProviderName;
            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.MMwebAppkey)
            {
                Pv = ConfigurationManager.ConnectionStrings["CN_MMW"].ProviderName;
            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.BidAppKey)
            {
                Pv = ConfigurationManager.ConnectionStrings["CN_BID"].ProviderName;
            }
            else
            if (Key == FrameworkLayer.ConfigurationUtility.WebApplicationConfiguration.OtherAppKey)
            {
                Pv = ConfigurationManager.ConnectionStrings["CN_OTHERS"].ProviderName;
            }

            return Pv;
        }
     }
}
  

b. Query Builder Class

• I made theree public overloading method ( polimorphism method ) which could receive the SQL server provider and the config define. This all method has function the define and select the proper provider name for which consumed from the connectionstring class.
• All Of Query syntax was embed either in this class wheter has a function to service all of query type and command type such as text query or stored procedure command type.

Let’s get rock’n roll of the code :

 

//***********************************************************************************************************
//  SUBJECT             : DATA ACCESS ENGINE PROVIDER AND QUERY HELPER
//  CREATED BY          : Doddy Christiana Saputra , MCAD.NET
//  LAST MODIFIED DATE  : JUNE 22,2007
//***********************************************************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.IO;
using FrameworkLayer.LoggingUtility;
using FrameworkLayer.DataUtility;
using FrameworkLayer.ConfigurationUtility;

namespace FrameworkLayer.DataUtility
{
    public class QueryBuilder : IDisposable
    {
        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        Logging msg = new Logging();
      

        //private string strCN = FrameworkLayer.DataUtility.ConnectionToDatabase.GetCN();
        //private string strPV  = FrameworkLayer.DataUtility.ConnectionToDatabase.GetPV();
        public string _CN = string.Empty;
        public string strCN
        {
            get { return _CN; }
            set { _CN = FrameworkLayer.DataUtility.ConnectionToDatabase.GetCN(); }
        }

        public string _PV = string.Empty;
        public string strPV
        {
            get { return _PV; }
            set { _PV = FrameworkLayer.DataUtility.ConnectionToDatabase.GetPV(); }
        }
 
        //Overloading Provider
        public QueryBuilder(Providers provider): this(FrameworkLayer.DataUtility.ConnectionToDatabase.GetCN(), provider)
        {

        }

        //Overloading Connection String
        public QueryBuilder(string connectionstring): this(connectionstring, Providers.SqlServer)
        {
        }

        //Overloading get Connection String and Provider
        public QueryBuilder(): this(FrameworkLayer.DataUtility.ConnectionToDatabase.GetCN(), Providers.ConfigDefined)
        {
        }

        public QueryBuilder(string connectionstring,Providers provider)
        {  
            //Pemilihan Provider Database
            strConnectionString = connectionstring;
            switch (provider)
            {
                case Providers.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case Providers.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case Providers.Oracle:
                    objFactory = OracleClientFactory.Instance;
                    break;
                case Providers.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;
                case Providers.ConfigDefined:
                    string providername = FrameworkLayer.DataUtility.ConnectionToDatabase.GetPV();
                    switch (providername)
                    {
                        case “System.Data.SqlClient”:
                            objFactory = SqlClientFactory.Instance;
                            break;
                        case “System.Data.OleDb”:
                            objFactory = OleDbFactory.Instance;
                            break;
                        case “System.Data.OracleClient”:
                            objFactory = OracleClientFactory.Instance;
                            break;
                        case “System.Data.Odbc”:
                            objFactory = OdbcFactory.Instance;
                            break;
                    }
                    break;

            }
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();

            objConnection.ConnectionString = strConnectionString;
            objCommand.Connection = objConnection;
        }

       

      
        public int AddParameter(string name,object value)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value=value;
            return objCommand.Parameters.Add(p);
        }

        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }

        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }

        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query,CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query,ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query,CommandType.Text,connectionstate);
        }

        public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i=-1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                msg.HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query,CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                msg.HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query,CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader=null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                msg.HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }

        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query,CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                msg.HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }
       
        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }

    }

    public enum Providers
    {
        SqlServer,OleDb,Oracle,ODBC,ConfigDefined
    }

    public enum ConnectionState
    {
        KeepOpen,CloseOnExit
    }
   
}