public class DBAccess:IDisposable
    {

       // private string DefaultDbString = string.Format(ConfigurationManager.ConnectionStrings["DefaultDbString"].ConnectionString,Config.Password);
        private string DefaultDbString = string.Format(ConfigurationManager.ConnectionStrings["DefaultDbString"].ConnectionString, "Citi2013");

        #region public methods

        public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection cnn = new SqlConnection(DefaultDbString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, commandText, commandParameters);

                int retval = cmd.ExecuteNonQuery();
                return retval;

            }

        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, out SqlParameter[] outputs, out object returnValue, params SqlParameter[] commandParameters)
        {
            returnValue = null;
            using (SqlConnection cnn = new SqlConnection(DefaultDbString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, commandText, commandParameters);
                int retval = cmd.ExecuteNonQuery();

                List<SqlParameter> outlists = new List<SqlParameter>();
                foreach (SqlParameter param in cmd.Parameters)
                {
                    if (param.Direction == ParameterDirection.Output)
                        outlists.Add(param);
                    if (param.Direction == ParameterDirection.ReturnValue)
                        returnValue = param.Value;
                }

                if (outlists != null && outlists.Count > 0)
                    outputs = outlists.ToArray();
                else
                    outputs = null;

                return retval;
            }
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, out SqlParameter[] outputs, params SqlParameter[] commandParameters)
        {
            using (SqlConnection cnn = new SqlConnection(DefaultDbString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, commandText, commandParameters);
                int retval = cmd.ExecuteNonQuery();

                List<SqlParameter> outlists = new List<SqlParameter>();
                foreach (SqlParameter param in cmd.Parameters)
                {
                    if (param.Direction == ParameterDirection.Output)
                        outlists.Add(param);
                }

                if (outlists != null && outlists.Count > 0)
                    outputs = outlists.ToArray();
                else
                    outputs = null;

                return retval;

            }
        }

        public DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection cnn = new SqlConnection(DefaultDbString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, commandText, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }

        public object ExecFunction(CommandType commandType, string fnName, params SqlParameter[] parameterValues)
        {
            using (SqlConnection cnn = new SqlConnection(DefaultDbString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, fnName, parameterValues);

                cmd.ExecuteNonQuery();

                return cmd.Parameters[0].Value;
            }
        }

        public bool ExcuteDataTableBulkCopy(DataTable sourceDatatable, string destinationTableName, string columns)
        {
            bool re = false;
            string sqlText = "SELECT TOP 1" + columns + "FROM" + destinationTableName;
            try
            {
                using (SqlConnection cnn = new SqlConnection(DefaultDbString))
                {
                    cnn.Open();
                    DataTable destinationTable = ExecuteDataset(cnn, CommandType.Text, sqlText).Tables[0];
                    if (destinationTable.Columns.Count == sourceDatatable.Columns.Count)
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn))
                        {
                            bulkCopy.DestinationTableName = destinationTableName;
                            SqlBulkCopyColumnMapping[] mapArray = new SqlBulkCopyColumnMapping[sourceDatatable.Columns.Count];
                            for (int i = 0; i < sourceDatatable.Columns.Count; i++)
                            {
                                mapArray[i] = new SqlBulkCopyColumnMapping(sourceDatatable.Columns[i].ColumnName, destinationTable.Columns[i].ColumnName);
                                bulkCopy.ColumnMappings.Add(mapArray[i]);
                            }
                            bulkCopy.WriteToServer(sourceDatatable);
                            re = true;
                        }
                    }
                }
            }
            catch
            {
                throw;
            }
            return re;

        }
        public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            try
            {
                using (SqlConnection cnn = new SqlConnection(DefaultDbString))
                {
                    cnn.Open();
                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(cmd, cnn, (SqlTransaction)null, commandType, commandText, commandParameters);

                    return cmd.ExecuteScalar();
                }
            }
            catch
            {
                throw;
            }
        }
        #endregion

        #region private method

        private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandTimeout = 90000;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;

            if (commandParameters != null)
            {
                AttachParameter(command, commandParameters);

            }
        }

        private void AttachParameter(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                if (p.Direction == ParameterDirection.InputOutput && p.Value == null)
                {
                    p.Value = DBNull.Value;
                }
                command.Parameters.Add(p);
            }

        }

        private DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }

        private int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

            int retval = cmd.ExecuteNonQuery();
            return retval;
        }

        private void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }
            for (int i = 0; i < commandParameters.Length; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }
        }
        #endregion

        #region IDisposable Members
        public void Dispose()
        {
            throw new NotImplementedException();
        }
        #endregion

        #region IDisposable Members
        void IDisposable.Dispose()
        {
            throw new NotImplementedException();
        }
        #endregion
    }

    public class SqlHelper
    {
        private DBAccess _DBAccess;
        private DBAccess DBAccess
        {
            get
            {
                if (_DBAccess == null)
                    _DBAccess = new DBAccess();
                return _DBAccess;
            }
        }

        public int ExecuteNonQueryStoreProcedure(string strSpName, params SqlParameter[] parameterValues)
        {
            try
            {
                return DBAccess.ExecuteNonQuery(CommandType.StoredProcedure, strSpName, parameterValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int ExecuteNonQueryStoreProcedure(string strSpName, out SqlParameter[] outputs, out object returnValue, params SqlParameter[] parameterValues)
        {
            try
            {
                return DBAccess.ExecuteNonQuery(CommandType.StoredProcedure, strSpName, out outputs, out returnValue, parameterValues);
            }
            catch (Exception ex)
            {
                //log.Error(ex.Message);
                outputs = null;
                returnValue = null;
                return -2;
                throw ex;
            }
        }

        public int ExecuteNonQueryStoreProcedure(string strSpName, out SqlParameter[] outputs, params SqlParameter[] parameterValues)
        {
            try
            {
                return DBAccess.ExecuteNonQuery(CommandType.StoredProcedure, strSpName, out outputs, parameterValues);
            }
            catch (Exception ex)
            {
                //log.Error(ex.Message);
                outputs = null;
                return -2;
                throw ex;
            }
        }

        public DataSet ExecuteStoreProcedureDataSet(string strSpName, params SqlParameter[] parameterValues)
        {
            try
            {
                DataSet ds = DBAccess.ExecuteDataset(CommandType.StoredProcedure, strSpName, parameterValues);
                return ds;
            }
            catch (Exception ex)
            {
                // log.Error(ex.Message);
                return null;
                throw ex;
            }
        }

        public DataSet ExecuteStoreProcedureDataSet_check(string strSpName, params SqlParameter[] parameterValues)
        {
            try
            {
                DataSet ds = DBAccess.ExecuteDataset(CommandType.StoredProcedure, strSpName, parameterValues);
                return ds;
            }
            catch (Exception ex)
            {
                //log.Error(ex.Message);

                throw ex;
            }
        }

        public object ExecFunction(string fnName, params SqlParameter[] parameterValues)
        {
            try
            {
                return DBAccess.ExecFunction(CommandType.StoredProcedure, fnName, parameterValues);
            }
            catch (Exception ex)
            {
                //log.Error(ex.Message);
                return null;
                throw ex;
            }
        }
        public bool BulkCopy(DataTable dt, string tableName, string columns)
        {
            try
            {
                return DBAccess.ExcuteDataTableBulkCopy(dt, tableName, columns);
            }
            catch (Exception ex)
            {
                //log.Error(ex.Message);
                return false;
            }
        }
        public object ExecuteDataScalar(string commandText, params SqlParameter[] parameterValues)
        {
            try
            {
                return DBAccess.ExecuteScalar(CommandType.StoredProcedure, commandText, parameterValues);
            }
            catch (Exception ex)
            {
                // log.Error(ex.Message);
                return null;
            }
        }
    }

   public class BaseBusiness
    {
        private SqlHelper _SqlHelper;
        public SqlHelper SqlHelper
        {
            get
            {
                if (_SqlHelper == null)
                    _SqlHelper = new SqlHelper();
                return _SqlHelper;
            }
        }

    }