精簡Command版SqlHelper

我在寫CSharp程式對資料庫進行操作時發現Connection對象起到了連接資料庫的做用，實際執行SQL語句使用的是Command對象的方法，所以對SqlHelper進行了重寫，具體如下：

一、創建一個ParameterCommand對象，只包含CommandText和Parameters屬性，主要用於以事務的方式批量執行SQL語句，我感覺比創建List<string> commandTexts和List<List<DbParameter>> paras兩個參數方便，也不容易出錯

    public class ParameterCommand
    {

        private List<DbParameter> paras = new List<DbParameter>();

        public string CommandText { get; set; }

        public List<DbParameter> Parameters
        {
            get
            {
                return paras;
            }
        }

    }

二、精簡Command版SqlHelper代碼如下，傳入Command對象做為參數用於執行SQL語句

    public static class SqlHelper
    {

        private static void ResetCommandProperty(DbCommand command, string commandText, params DbParameter[] paras)
        {
            command.Parameters.Clear();
            command.CommandText = commandText;
            command.Parameters.AddRange(paras);
        }

        public static void ExecuteNonQuery(DbCommand command, List<ParameterCommand> paraCommands)
        {
            command.Transaction = command.Connection.BeginTransaction();
            foreach (ParameterCommand paraCommand in paraCommands)
            {
                try
                {
                    ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    command.Transaction.Rollback();
                    throw ex;
                }
            }
            command.Transaction.Commit();
        }

        public static void ExecuteNonQuery(DbCommand command, string commandText, params DbParameter[] paras)
        {
            ResetCommandProperty(command, commandText, paras);
            command.ExecuteNonQuery();
        }

        public static DbDataReader ExecuteReader(DbCommand command, ParameterCommand paraCommand)
        {
            ResetCommandProperty(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
            return command.ExecuteReader();
        }

        public static DbDataReader ExecuteReader(DbCommand command, string commandText, params DbParameter[] paras)
        {
            ResetCommandProperty(command, commandText, paras);
            return command.ExecuteReader();
        }

        public static object ExecuteScalar(DbCommand command, ParameterCommand paraCommand)
        {
            ResetCommandProperty(command, paraCommand.CommandText,paraCommand.Parameters.ToArray());
            return command.ExecuteScalar();
        }

        public static object ExecuteScalar(DbCommand command, string commandText, params DbParameter[] paras)
        {
            ResetCommandProperty(command, commandText, paras);
            return command.ExecuteScalar();
        }

        public static DataTable ExecuteTable(DbCommand command, ParameterCommand paraCommand)
        {
            return ExecuteTable(command, paraCommand.CommandText, paraCommand.Parameters.ToArray());
        }

        public static DataTable ExecuteTable(DbCommand command, string commandText, params DbParameter[] paras)
        {
            DataTable table = new DataTable();
            ResetCommandProperty(command, commandText, paras);
            using (DbDataAdapter adapter = DbProviderFactories.GetFactory(command.Connection).CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                adapter.Fill(table);
            }
            return table;
        }

    }

三、封裝的通用DatabaseClient

    public abstract class DatabaseClient
    {
        private DbConnection connection;

        public abstract DbConnection GetConnection();

        private DbCommand GetCommand()
        {
            if (connection == null)
            {
                connection = GetConnection();
            }
            if (connection.State == ConnectionState.Broken)
            {
                connection.Close();
            }
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            return connection.CreateCommand();
        }

        public void ExecuteNonQuery(List<ParameterCommand> paraCommands)
        {
            using (DbCommand command = GetCommand())
            {
                SqlHelper.ExecuteNonQuery(command, paraCommands);
            }
        }

        public void ExecuteNonQuery(string commandText, params DbParameter[] paras)
        {
            using (DbCommand command = GetCommand())
            {
                SqlHelper.ExecuteNonQuery(command, commandText, paras);
            }
        }

        public DbDataReader ExecuteReader(ParameterCommand paraCommand)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteReader(command, paraCommand);
            }
        }

        public DbDataReader ExecuteReader(string commandText, params DbParameter[] paras)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteReader(command, commandText, paras);
            }
        }

        public object ExecuteScalar(ParameterCommand paraCommand)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteScalar(command, paraCommand);
            }
        }

        public object ExecuteScalar(string commandText, params DbParameter[] paras)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteScalar(command, commandText, paras);
            }
        }

        public DataTable ExecuteTable(ParameterCommand paraCommand)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteTable(command, paraCommand);
            }
        }

        public DataTable ExecuteTable(string commandText, params DbParameter[] paras)
        {
            using (DbCommand command = GetCommand())
            {
                return SqlHelper.ExecuteTable(command, commandText, paras);
            }
        }

    }

四、舉個慄子：MySQL版客戶端

    public class MySqlClient : DatabaseClient
    {
        private string connectionString;

        public MySqlClient(string dataSource, string userName, string password)
        {
            connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + ";Charset=utf8";
        }

        public MySqlClient(string dataSource, string userName, string password, string database)
        {
            connectionString = "DataSource=" + dataSource + ";UserName=" + userName + ";Password=" + password + "Database=" + database + ";Charset=utf8";
        }

        public override System.Data.Common.DbConnection GetConnection()
        {
            return new MySqlConnection(connectionString);
        }
    }