Combining SqlConnection and SqlCommand in one Class in C#

电玩女神 2022-08-13 01:28 149阅读 0赞

原文：[http://www.codeproject.com/Tips/826963/Combining-SqlConnection-and-SqlCommand-in-one-Clas][http_www.codeproject.com_Tips_826963_Combining-SqlConnection-and-SqlCommand-in-one-Clas]

#define ActivateTransaction
    using 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; }
        }
    
    }

[http_www.codeproject.com_Tips_826963_Combining-SqlConnection-and-SqlCommand-in-one-Clas]: http://www.codeproject.com/Tips/826963/Combining-SqlConnection-and-SqlCommand-in-one-Clas