欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

精简Command版SqlHelper

程序员文章站 2023-08-27 08:52:16
我在写CSharp程序对数据库进行操作时发现Connection对象起到了连接数据库的做用,实际执行SQL语句使用的是Command对象的方法,所以对SqlHelper进行了重写,具体如下: 一、创建一个ParameterCommand对象,只包含CommandText和Parameters属性,主 ......

我在写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);
        }
    }