精简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);        }    }