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