Combining SqlConnection and SqlCommand in one Class in C#

来源：互联网发布：数控机床如何编程编辑：程序博客网时间：2024/06/06 01:23
原文：http://www.codeproject.com/Tips/826963/Combining-SqlConnection-and-SqlCommand-in-one-Clas
#define ActivateTransactionusing System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Reflection;/// <summary>/// Summary description for Database/// </summary>/// namespace CustomDatabase{    #region All Enum    public enum CommandTypeEnum    {        /// <summary>        ///         /// </summary>        Text,        /// <summary>        ///         /// </summary>        StoredProcedure    }    public enum CommandMode    {        NonQuery,        Scalar,        Reader    }    #endregion    public class Database : IDisposable    {        private string m_sConnectionString;        private string strSqlCommandText;        private SqlConnection Conn = null;        private SqlTransaction oTrans = null;        private object oScalar;        List<Parameters> ParameterList = new List<Parameters>();        public Database()        {        }        protected internal Database(string sConnectionString)        {            m_sConnectionString = sConnectionString;        }        protected internal string CommandText        {            set            {                strSqlCommandText = value;            }        }        private string ConnectionString        {            get            {                return m_sConnectionString;            }        }        /// <summary>        /// Call or Execute all Reader         /// </summary>        protected internal int ExecuteCommandReader(CommandTypeEnum sqlType, ref DataTable oTable)        {            int rowAffected;            return ExecuteCommand(CommandMode.Reader, sqlType, ref oScalar, out rowAffected, ref oTable);        }        protected internal int ExecuteCommandReader(ref DataTable oTable)        {            int rowAffected;            object oScalar = null;            return ExecuteCommand(CommandMode.Reader, CommandTypeEnum.StoredProcedure, ref oScalar, out rowAffected, ref oTable);        }        /// <summary>        /// Call or Execute all Scalar         /// </summary>        protected internal int ExecuteCommandScalar()        {            int rowAffected;            DataTable oTable = new DataTable();            return ExecuteCommand(CommandMode.Scalar, CommandTypeEnum.StoredProcedure, ref oScalar, out rowAffected, ref oTable);        }        protected internal object ExecuteCommandScalar(ref object eScalar)        {            int rowAffected;            DataTable oTable = new DataTable();            ExecuteCommand(CommandMode.Scalar, CommandTypeEnum.StoredProcedure, ref eScalar, out rowAffected, ref oTable);            return oScalar;        }                /// <summary>        /// Call or Execute all Non Query         /// </summary>        ///         protected internal int ExecuteCommandNonQuery(CommandTypeEnum sqlType, out int nRowsAffected)        {            DataTable oDataTable = new DataTable();            return ExecuteCommand(CommandMode.NonQuery, sqlType, ref oScalar, out nRowsAffected, ref oDataTable);        }        protected internal int ExecuteCommandNonQuery(out int nRowsAffected)        {            DataTable oDataTable = new DataTable();            return ExecuteCommand(CommandMode.NonQuery, CommandTypeEnum.StoredProcedure, ref oScalar, out nRowsAffected, ref oDataTable);        }        #region ExecuteCommands        private int ExecuteCommand(CommandMode eMode, CommandTypeEnum sqlType, ref object ScalarOutput, out int nRowsAffected, ref DataTable oTable)        {            if (Conn != null)            {                if (Conn.State == ConnectionState.Open)                {                    using (SqlCommand oCmd = new SqlCommand())                    {                        oCmd.CommandText = strSqlCommandText;                        switch (sqlType)                        {                            case CommandTypeEnum.StoredProcedure:                                oCmd.CommandType = CommandType.StoredProcedure;                                break;                            case CommandTypeEnum.Text:                                oCmd.CommandType = CommandType.Text;                                break;                        }                        foreach (Parameters List in ParameterList)                        {                            SqlParameter oParam = new SqlParameter(List.ParameterName, List.ParameterValues);                            oParam.DbType = List.ParameterType;                            oCmd.Parameters.Add(oParam);                        }                        SqlParameter oRetParam = new SqlParameter("RETURN_VALUE", DBNull.Value);                        oRetParam.Direction = ParameterDirection.ReturnValue;                        oCmd.Parameters.Add(oRetParam);                        oCmd.Connection = Conn;                        nRowsAffected = 0;                        switch (eMode)                        {                            case CommandMode.NonQuery:                                nRowsAffected = oCmd.ExecuteNonQuery();                                break;                            case CommandMode.Scalar:                                ScalarOutput = oCmd.ExecuteScalar();                                break;                            case CommandMode.Reader:                                SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);                                if (oTable != null)                                {                                    oAdapter.Fill(oTable);                                }                                break;                        }#if ActivateTransaction                        if (oTrans != null)                        {                            oCmd.Transaction = oTrans;                        }#endif                        return Convert.ToInt32(oCmd.Parameters["RETURN_VALUE"].Value);                    }                }                else throw new DataException("Connection not open");            }            else throw new DataException("Connection not open");        }        #endregion        protected internal void Open()        {            try            {                Conn = new SqlConnection();                Conn.ConnectionString = ConnectionString;                Conn.Open();            }            catch (Exception ex)            {                throw ex;            }        }        /// <summary>        /// Close the connection to the Sql db        /// </summary>        protected internal void Close()        {            if (Conn != null)            {                Conn.Close();                Conn = null;            }        }        protected internal void AddParameter(string Name, object value, DbType Type)        {            ParameterList.Add(new Parameters() { ParameterName = Name, ParameterType = Type, ParameterValues = value });        }#if ActivateTransaction        /// <summary>        /// Begins a Transaction        /// </summary>        protected internal void BeginTransaction()        {            if (Conn != null)            {                oTrans = Conn.BeginTransaction();            }            else                throw new DataException("Connection not open");        }        /// <summary>        /// Commits an active transaction        /// </summary>        protected internal void CommitTransaction()        {            if (oTrans != null)            {                oTrans.Commit();                oTrans = null;            }            else                throw new DataException("Not in transcation");        }        /// <summary>        /// Rollbacks an active transaction        /// </summary>        protected internal void RollbackTransaction()        {            if (oTrans != null)            {                oTrans.Rollback();                oTrans = null;            }            else                throw new DataException("Not in transcation");        }#endif        #region Connection Dispose        public void Dispose()        {            if (Conn != null)            {                Conn.Dispose();            }        }        #endregion    }    public class Parameters    {        public string ParameterName { get; set; }        public DbType ParameterType { get; set; }        public object ParameterValues { get; set; }    }}
0 0