Sunday, September 29, 2013

ASP.Net, C# (Data Access Layer - New )

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace DataAccessLayer
{
    public class DAL
    {
        public SqlConnection cn = new SqlConnection();
        string Error = string.Empty;

        public void OpenConnection()
        {
            try
            {
                if (cn.State == ConnectionState.Open)
                {
                    CloseConnection();
                }
                int time = cn.ConnectionTimeout;
                cn.ConnectionString = bs_config.getConnectionString("bs_cnn");
                //cn.ConnectionString = bs_config.getConnectionString(
                cn.Open();
            }
            catch (Exception ae)
            {
                bs_log.WriteError("DAL.cs", "OpenConnection()", ae.Message);
                throw new System.ArgumentException("Database Connection not made, Please see Event Log for more details"); ;
            }
        }

        public void CloseConnection()
        {
            try
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                    cn.Dispose();
                }
            }
            catch (Exception)
            {

            }
        }


        public DataSet FetchDataSet(string sql)
        {
            return FetchDataSet(sql, ref Error);
        }

        public DataSet FetchDataSet(string sql, ref string strError)
        {
            OpenConnection();
            DataSet ds = new DataSet();

            string error = string.Empty;
            int i = 0;
            try
            {
                Regex reg = new Regex("~@~");
                string[] queries = reg.Split(sql);

                foreach (string qry in queries)
                {
                    SqlDataAdapter sdapt = new SqlDataAdapter(qry, cn);
                    DataTable dt = new DataTable();
                    sdapt.Fill(dt);
                    ds.Tables.Add(dt);
                    i++;
                }
            }
            catch (Exception ae)
            {
                error += "Query Number: " + i.ToString() + " " + ae.Message.ToString();
            }
            finally
            {
                CloseConnection();
            }

            strError = error;
            return ds;
        }

        public DataSet FetchDataSet(SqlCommand command)
        {
            return FetchDataSet(command, ref Error);
        }

        public DataSet FetchDataSet(SqlCommand command, ref string strError)
        {
            OpenConnection();
            DataSet ds = new DataSet();
            int i = 0;
            try
            {
                string strQuery = command.CommandText;

                strError = "";
                Regex reg = new Regex("~@~");
                string[] queries = reg.Split(strQuery);

                foreach (string qry in queries)
                {
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SetSqlCommandParameters(cmd, qry, command);

                    SqlDataAdapter sdapt = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sdapt.Fill(dt);
                    ds.Tables.Add(dt);
                    i++;
                }
            }
            catch (Exception ae)
            {
                strError += "Query Number: " + i.ToString() + " " + ae.Message.ToString();
            }
            finally
            {
                CloseConnection();
            }

            if (strError == "" || strError == null)
            {
                strError = "T";
            }
            return ds;
        }

        public DataSet FetchDataSet(string StoredProcedure, SqlParameter[] parameters)
        {
            return FetchDataSet(StoredProcedure, parameters, ref Error);
        }

        public DataSet FetchDataSet(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                SqlDataAdapter sdapt = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                sdapt.Fill(ds);
                strError = "T";
                return ds;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }



        public DataTable FetchDataTable(string sql)
        {
            return FetchDataTable(sql, ref Error);
        }

        public DataTable FetchDataTable(string sql, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(sql, cn);
                DataTable dt = new DataTable();
                SqlDataAdapter sdapt = new SqlDataAdapter(command);
                sdapt.Fill(dt);

                strError = "T";
                return dt;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable FetchDataTable(SqlCommand command)
        {
            return FetchDataTable(command, ref Error);
        }

        public DataTable FetchDataTable(SqlCommand command, ref string strError)
        {
            OpenConnection();
            try
            {
                command.Connection = cn;
                DataTable dt = new DataTable();
                SqlDataAdapter sdapt = new SqlDataAdapter(command);

                sdapt.Fill(dt);

                strError = "T";
                return dt;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable FetchDataTable(string StoredProcedure, SqlParameter[] parameters)
        {
            return FetchDataTable(StoredProcedure, parameters, ref Error);
        }

        public DataTable FetchDataTable(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }
                SqlDataAdapter sdapt = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdapt.Fill(dt);
                strError = "T";
                return dt;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }



        public IDataReader FetchReader(string sql)
        {
            return FetchReader(sql, ref Error);
        }

        public IDataReader FetchReader(string sql, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(sql, cn);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                strError = "T";
                return reader;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
        }

        public IDataReader FetchReader(SqlCommand command)
        {
            return FetchReader(command, ref Error);
        }

        public IDataReader FetchReader(SqlCommand command, ref string strError)
        {
            OpenConnection();
            try
            {
                command.Connection = cn;
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                strError = "T";
                return reader;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
        }

        public IDataReader FetchReader(string StoredProcedure, SqlParameter[] parameters)
        {
            return FetchReader(StoredProcedure, parameters, ref Error);
        }

        public IDataReader FetchReader(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                strError = "T";
                return reader;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
        }



        public object FetchScaler(string sql)
        {
            return FetchScaler(sql, ref Error);
        }
       
        public object FetchScaler(string sql, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(sql, cn);
                object value = command.ExecuteScalar();

                strError = "T";
                return value;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public object FetchScaler(SqlCommand command)
        {
            return FetchScaler(command, ref Error);
        }

        public object FetchScaler(SqlCommand command, ref string strError)
        {
            OpenConnection();
            try
            {
                command.Connection = cn;
                object value = command.ExecuteScalar();

                strError = "T";
                return value;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public object FetchScaler(string StoredProcedure, SqlParameter[] parameters)
        {
            return FetchScaler(StoredProcedure, parameters, ref Error);
        }

        public object FetchScaler(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            OpenConnection();
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                object value = command.ExecuteScalar();

                strError = "T";

                if (value == null)
                    value = "";

                return value;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public object FetchScaler(string sql, SqlConnection thisConnection, SqlTransaction thisTransaction)
        {
            string strError = string.Empty;
            return FetchScaler(sql, ref strError, thisConnection, thisTransaction);
        }

        public object FetchScaler(string sql, ref string strError, SqlConnection thisConnection, SqlTransaction thisTransaction)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, thisConnection);
                command.Transaction = thisTransaction;

                object value = command.ExecuteScalar();

                strError = "T";
                return value;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                return null;
            }
        }



        public bool Execute(string StoredProcedure, SqlParameter[] parameters)
        {
            return Execute(StoredProcedure, parameters, true, ref Error);
        }

        public bool Execute(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            return Execute(StoredProcedure, parameters, true, ref strError);
        }

        public bool Execute(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction)
        {
            return Execute(StoredProcedure, parameters, hasTransaction, ref Error);
        }

        public bool Execute(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction, ref string strError)
        {
            OpenConnection();
            SqlTransaction trans = null;
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);

                if (hasTransaction)
                {
                    trans = cn.BeginTransaction();
                    command.Transaction = trans;
                }

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                command.ExecuteNonQuery();

                if (hasTransaction)
                    trans.Commit();

                strError = "T";
                return true;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                if (trans != null) trans.Rollback();

                return false;
            }
            finally
            {
                CloseConnection();
            }
        }

        public bool Execute(SqlCommand sqlCommand)
        {
            string strError = string.Empty;
            return Execute(sqlCommand, ref strError);
        }

        public bool Execute(SqlCommand sqlCommand, ref string strError)
        {
            OpenConnection();
            SqlTransaction trans = null;

            try
            {
                sqlCommand.Connection = cn;

                trans = cn.BeginTransaction();
                sqlCommand.Transaction = trans;

               
                sqlCommand.ExecuteNonQuery();

                trans.Commit();

                strError = "T";
                return true;
            }
            catch (Exception ae)
            {
                if (trans != null) trans.Rollback();
                strError = ae.Message;
                return true;
            }
            finally
            {
                CloseConnection();
            }
        }


        public IDataReader ExecuteAndGetReader(string StoredProcedure, SqlParameter[] parameters)
        {
            return ExecuteAndGetReader(StoredProcedure, parameters, true, ref Error);
        }

        public IDataReader ExecuteAndGetReader(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            return ExecuteAndGetReader(StoredProcedure, parameters, true, ref strError);
        }

        public IDataReader ExecuteAndGetReader(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction)
        {
            return ExecuteAndGetReader(StoredProcedure, parameters, hasTransaction, ref Error);
        }

        public IDataReader ExecuteAndGetReader(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction, ref string strError)
        {
            OpenConnection();
            SqlTransaction trans = null;
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);

                if (hasTransaction)
                {
                    trans = cn.BeginTransaction();
                    command.Transaction = trans;
                }

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                if (hasTransaction)
                    trans.Commit();

                strError = "T";
                return reader;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                if (trans != null) trans.Rollback();

                return null;
            }
        }




        public DataTable ExecuteAndGetTable(string StoredProcedure, SqlParameter[] parameters)
        {
            return ExecuteAndGetTable(StoredProcedure, parameters, true, ref Error);
        }

        public DataTable ExecuteAndGetTable(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            return ExecuteAndGetTable(StoredProcedure, parameters, true, ref strError);
        }

        public DataTable ExecuteAndGetTable(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction)
        {
            return ExecuteAndGetTable(StoredProcedure, parameters, hasTransaction, ref Error);
        }

        public DataTable ExecuteAndGetTable(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction, ref string strError)
        {
            OpenConnection();
            SqlTransaction trans = null;
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);

                if (hasTransaction)
                {
                    trans = cn.BeginTransaction();
                    command.Transaction = trans;
                }

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                SqlDataReader reader = command.ExecuteReader();

                DataTable dt = new DataTable();
                dt.Load(reader);

                reader.Close();

                if (hasTransaction)
                    trans.Commit();

                strError = "T";
                return dt;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                if (trans != null) trans.Rollback();

                return null;
            }
        }




        public object ExecuteAndGetScaler(string StoredProcedure, SqlParameter[] parameters)
        {
            return ExecuteAndGetScaler(StoredProcedure, parameters, true, ref Error);
        }

        public object ExecuteAndGetScaler(string StoredProcedure, SqlParameter[] parameters, ref string strError)
        {
            return ExecuteAndGetScaler(StoredProcedure, parameters, true, ref strError);
        }

        public object ExecuteAndGetScaler(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction)
        {
            return ExecuteAndGetScaler(StoredProcedure, parameters, hasTransaction, ref Error);
        }
       
        public object ExecuteAndGetScaler(string StoredProcedure, SqlParameter[] parameters, bool hasTransaction, ref string strError)
        {
            OpenConnection();
            SqlTransaction trans = null;
            try
            {
                SqlCommand command = new SqlCommand(StoredProcedure, cn);

                if (hasTransaction)
                {
                    trans = cn.BeginTransaction();
                    command.Transaction = trans;
                }

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter param in parameters)
                        command.Parameters.AddWithValue(param.ParameterName, param.Value);
                }

                object value = command.ExecuteScalar();

                if (hasTransaction)
                    trans.Commit();

                strError = "T";
                return value;
            }
            catch (Exception ae)
            {
                strError = ae.Message;
                if (trans != null) trans.Rollback();

                return null;
            }
            finally
            {
                CloseConnection();
            }
        }



        private void SetSqlCommandParameters(SqlCommand cmd, string qry, SqlCommand command)
        {
            foreach (System.Data.Common.DbParameter item in command.Parameters)
            {
                if (qry.Contains(item.ParameterName))
                {
                    cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
                }
            }
        }
    }
}

Posted by at

2 comments:

Subscribe to: Post Comments (Atom)