SQLHelper.cs

namespace sps.dataaccess
{
    /**//// <summary>
    /// sqlhelper 被dataaccess里的任何类调用，执行insert，update，setvalid和select等组成的存储过程
    /// </summary>
    public class sqlhelper
    {
        //缓存sqlparameter数组对象的hashtable
        private static hashtable paramcache = hashtable.synchronized(new hashtable());

        //连接字符串
        public static string str_connection;

        //sqlconnection
        //private static sqlconnection conn;

        "public functions"#region "public functions"
        /**//// <summary>
        /// 获取sqlconnection
        /// </summary>
        /// <remark>
        /// 因为sqlconnection不是托管资源，所以在这里无法回收，所以在这里屏蔽掉
        /// </remark>
        /// <returns>sqlconnection对象</returns>
//        public static sqlconnection getconnection()
//        {
//            if(conn==null)
//                conn=new sqlconnection(str_connection);
//            return conn;
//        }

        /**//// <summary>
        /// 执行xxxinsert,xxxupdate,xxxsetvalid类型的存储过程
        /// </summary>
        /// <remarks>
        /// 参数hashtable里的key名需要和存储过程里的参数名保持一致；
        /// 存储过程的参数全部要在hashtable中存在；
        /// </remarks>
        /// <param name="hashtable">由存储过程参数组成的hashtable</param>
        /// <param name="strspname">存储过程名称</param>
        /// <returns>insert情况的pkid、或update、setvalid情况的改变记录数</returns>
        public static int modifytable(hashtable hashtable,string strspname)
        {
            //获取sql连接
            //sqlconnection conn=getconnection();
            using(sqlconnection conn=new sqlconnection(str_connection))
            {
                //依据连接字符串和存储过程名称 构造出这个存储过程的参数数组
                sqlparameter[] sqlparas=getspparameterset(conn.connectionstring,strspname);

                //循环位每个存储参数数组的元素赋值   
                for(int i=0,j=sqlparas.length;i<j;i++)
                {
                    string strkeyname=sqlparas[i].parametername;
               
                    //当没有输入参数
                    try
                    {sqlparas[i].value=hashtable[strkeyname];}
                    catch
                    {sqlparas[i].value=null;}
                   
                    //如果输入参数是null
                    if(sqlparas[i].value==null)
                    {
                        switch(sqlparas[i].sqldbtype)
                        {
                            case sqldbtype.int:
                                sqlparas[i].value=-1;
                                break;
                            case sqldbtype.decimal:
                                sqlparas[i].value=-1;
                                break;
                            default:
                                sqlparas[i].value=dbnull.value;
                                break;
                        }
                    }
                }
               
                //执行存储过程
                sqlhelper.executenonquery(conn,commandtype.storedprocedure,strspname,sqlparas);

                //取出输出参数的值，
                //注意： insert,update,setvalid存储过程，只允许第一个参数类型为 out
                return (int)sqlparas[0].value;
            }
        }
       
        /**//// <summary>
        /// 执行getxxx类型的存储过程
        /// </summary>
        /// <remarks>
        /// 参数hashtable里的key名需要和存储过程里的参数名保持一致；
        /// 存储过程的参数全部要在hashtable中存在；
        /// </remarks>
        /// <param name="hashtable">由存储过程参数组成的hashtable</param>
        /// <param name="strspname">存储过程名称</param>
        /// <returns>dataset</returns>
        public static dataset getdataset(hashtable hashtable,string strspname)
        {
            //获取sql连接
            //sqlconnection conn=getconnection();

            using(sqlconnection conn=new sqlconnection(str_connection))
            {
                //依据连接字符串和存储过程名称 构造出这个存储过程的参数数组
                sqlparameter[] sqlparas=getspparameterset(conn.connectionstring,strspname);

                //循环位每个存储参数数组的元素赋值   
                for(int i=0,j=sqlparas.length;i<j;i++)
                {
                    string strkeyname=sqlparas[i].parametername;

                    //当没有输入参数
                    try
                    {sqlparas[i].value=hashtable[strkeyname];}
                    catch
                    {sqlparas[i].value=null;}
                   
                    //如果输入参数是null
                    if(sqlparas[i].value==null)
                    {
                        switch(sqlparas[i].sqldbtype)
                        {
                            case sqldbtype.int:
                                sqlparas[i].value=-1;
                                break;
                            case sqldbtype.decimal:
                                sqlparas[i].value=-1;
                                break;
                            default:
                                sqlparas[i].value=dbnull.value;
                                break;
                        }
                    }
                }
               
                //执行存储过程
                dataset dtreturn=sqlhelper.executedataset(conn,commandtype.storedprocedure,strspname,sqlparas);

                //返回结果集
                return dtreturn;
            }
        }
        #endregion

       
        private utility methods & constructors#region private utility methods & constructors

        static sqlhelper()
        {
            namevaluecollection nvc=(namevaluecollection)configurationsettings.getconfig("database");
            str_connection="server="+ desencryptor.desdecrypt(nvc["server"])
                +";database="+ desencryptor.desdecrypt(nvc["database"]) +";uid="
                + desencryptor.desdecrypt(nvc["uid"])
                +";pwd="+ desencryptor.desdecrypt(nvc["pwd"]) +"";
//            str_connection="server=.;database=ql_sps;uid=sa;pwd=sa";
        }

        /**//// <summary>
        /// 把sqlparameter数组赋值给command
        ///
        /// this behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as inputoutput)
        /// where the user provided no input value.
        /// </summary>
        /// <param name="command">要添加参数的sqlcommand</param>
        /// <param name="commandparameters">被添加的sqlparameter数组</param>
        private static void attachparameters(sqlcommand command, sqlparameter[] commandparameters)
        {
            foreach (sqlparameter p in commandparameters)
            {
                //check for derived output value with no value assigned
                if ((p.direction == parameterdirection.inputoutput) && (p.value == null))
                {
                    p.value = dbnull.value;
                }
               
                command.parameters.add(p);
            }
        }

        /**//// <summary>
        /// 按需创建sqlcommand，并且设定connection，transaction，命令类别
        /// </summary>
        /// <param name="command">要创建的sqlcommand</param>
        /// <param name="connection">sql server连接</param>
        /// <param name="transaction">事务或null值</param>
        /// <param name="commandtype">命令类别 (stored procedure)</param>
        /// <param name="commandtext">存储过程名称</param>
        /// <param name="commandparameters">与sqlcommand有关的参数或null值</param>
        private static void preparecommand(sqlcommand command, sqlconnection connection, sqltransaction transaction, commandtype commandtype, string commandtext, sqlparameter[] commandparameters)
        {
            //如果连接没有打开，则打开连接
            if (connection.state != connectionstate.open)
            {
                connection.open();
            }

            //把数据库连接与sqlcommand关联起来
            command.connection = connection;

            //设置存储过程名称
            command.commandtext = commandtext;

            //如果需要transaction，则设置transaction
            if (transaction != null)
            {
                command.transaction = transaction;
            }

            //设置命令类型
            command.commandtype = commandtype;

            //添加参数
            if (commandparameters != null)
            {
                attachparameters(command, commandparameters);
            }

            return;
        }

       
        /**//// <summary>
        ///  执行一个不返回结果集的存储过程
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = executenonquery(conn, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">sql server数据库连接</param>
        /// <param name="commandtype">命令类型 (stored procedure)</param>
        /// <param name="commandtext">存储过程名</param>
        /// <param name="commandparameters">参数数组</param>
        /// <returns>返回命令所影响的记录数</returns>
        private static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
        {   
            //创建一个sqlcommand
            sqlcommand cmd = new sqlcommand();
            preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters);
           
            //finally, execute the command.
            int retval = cmd.executenonquery();
   
            // detach the sqlparameters from the command object, so they can be used again.
            cmd.parameters.clear();
            return retval;
        }
       
       
       
        /**//// <summary>
        /// 执行一个带参数的存储过程，返回一个结果集
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">sql server连接</param>
        /// <param name="commandtype">命令类别(stored procedure)</param>
        /// <param name="commandtext">存储过程名称</param>
        /// <param name="commandparameters">参数数组</param>
        /// <returns>返回的dataset</returns>
        private static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
        {
            //创建一个命令
            sqlcommand cmd = new sqlcommand();
            preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters);
           
            //创建 dataadapter 和 dataset
            sqldataadapter da = new sqldataadapter(cmd);
            dataset ds = new dataset();

            da.fill(ds);       
            cmd.parameters.clear();
           
            //返回结果集
            return ds;                       
        }

        /**//// <summary>
        /// 从存储过程里面，构造出sqlparameter数组
        /// </summary>
        /// <param name="connectionstring">sql server连接字符串</param>
        /// <param name="spname">存储过程名称</param>
        /// <param name="includereturnvalueparameter">标志是否把返回值加入sqlparameter数组</param>
        /// <returns>sqlparameter数组</returns>
        private static sqlparameter[] discoverspparameterset(string connectionstring, string spname, bool includereturnvalueparameter)
        {
           
            using (sqlconnection cn = new sqlconnection(connectionstring))
            using (sqlcommand cmd = new sqlcommand(spname,cn))
            {
                cn.open();
                cmd.commandtype = commandtype.storedprocedure;

                //要返回的数组从下面而来
                sqlcommandbuilder.deriveparameters(cmd);

                if (!includereturnvalueparameter)
                {
                    //默认sqlparameter数组的第一个元素是存储过程的返回值
                    cmd.parameters.removeat(0);
                }

                sqlparameter[] discoveredparameters = new sqlparameter[cmd.parameters.count];

                cmd.parameters.copyto(discoveredparameters, 0);

                return discoveredparameters;
            }
        }

        //深拷贝缓存里的参数数组
        private static sqlparameter[] cloneparameters(sqlparameter[] originalparameters)
        {
            sqlparameter[] clonedparameters = new sqlparameter[originalparameters.length];
            for (int i = 0, j = originalparameters.length; i < j; i++)
            {
                clonedparameters[i] = (sqlparameter)((icloneable)originalparameters[i]).clone();
            }
            return clonedparameters;
        }

        /**//// <summary>
        /// 从存储过程解析出这个存储过程的参数的集合
        /// </summary>
        /// <remarks>
        /// 首先从数据库查询, 然后缓存起来供以后调用
        /// </remarks>
        /// <param name="connectionstring">sql server 的 connection string</param>
        /// <param name="spname">存储过程名称</param>
        /// <returns>sqlparameters数组</returns>
        public static sqlparameter[] getspparameterset(string connectionstring, string spname)
        {
            return getspparameterset(connectionstring, spname, false);
        }

        /**//// <summary>
        /// 从存储过程解析出这个存储过程的参数的集合
        /// </summary>
        /// <remarks>
        /// 首先从数据库查询, 然后缓存起来供以后调用
        /// </remarks>
        /// <param name="connectionstring">sql server 的 connection string</param>
        /// <param name="spname">存储过程名称</param>
        /// <param name="includereturnvalueparameter">标志返回值是否放到返回的参数数组</param>
        /// <returns>sqlparameters数组</returns>
        private static sqlparameter[] getspparameterset(string connectionstring, string spname, bool includereturnvalueparameter)
        {
            //定义key
            string hashkey = connectionstring + ":" + spname + (includereturnvalueparameter ? ":include returnvalue parameter":"");

            sqlparameter[] cachedparameters;
           
            //依据key从缓存hashtable里取出值
            cachedparameters = (sqlparameter[])paramcache[hashkey];

            if (cachedparameters == null)
            {    //如果取出的值是null，则从数据库获取存储过程的所有参数，并且放入缓存       
                cachedparameters = (sqlparameter[])(paramcache[hashkey] = discoverspparameterset(connectionstring, spname, includereturnvalueparameter));
            }
           
            return cloneparameters(cachedparameters);
        }   
        #endregion
    }   
}

项目中一直使用的一个类，觉得还是蛮好用的。
public abstract class sqlhelper {
 
        public sqlhelper() {
        }
       
        /**//// <summary>
        /// 连接字符串
        /// </summary>
        public static string conn_string = "provider=microsoft.jet.oledb.4.0;data source="+ environment.currentdirectory + @"/salarystat.mdb;user id=admin;password=;jet oledb:database password=tcyald";

        /**//// <summary>
        /// 用于执行一段sql语句，只有三个最简的必要参数，省去了commandtype。
        /// to excute a sql statement, which reuturns a integer stand for effect line number.
        /// default command type is text
        /// </summary>
        /// <param name="connstring">连接字符串 (conntection string)</param>
        /// <param name="cmdtext">command的字符串 (sql statement)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>返回影响行数 (effect line number)</returns>
        public static int executenonquery(string connstring, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();

            using (oledbconnection conn = new oledbconnection(connstring)) {
                preparecommand(cmd, conn, null, commandtype.text, cmdtext, cmdparms);
   
                int val = cmd.executenonquery();
                cmd.parameters.clear();
                return val;
   
            }
        }

        /**//// <summary>
        /// 用于执行一段sql语句。
        /// to excute a sql statement, which reuturns a integer stand for effect line number.
        /// </summary>
        /// <param name="connstring">连接字符串 (connection string)</param>
        /// <param name="cmdtype">command的字符串 (sql statement)</param>
        /// <param name="cmdtext">command的类型，具体见：commandtype (command type)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>返回影响行数 (effect line number)</returns>
        public static int executenonquery(string connstring, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {

            oledbcommand cmd = new oledbcommand();

            using (oledbconnection conn = new oledbconnection(connstring)) {
                preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
                int val = cmd.executenonquery();
                cmd.parameters.clear();
                return val;
            }
        }

        /**//// <summary>
        /// 用于执行一段sql语句。传入的值是connection.
        /// to excute a sql statement, which reuturns a integer stand for effect line number.
        /// a connection is passed in instead of a connection string
        /// </summary>
        /// <param name="conn">一个以初始化好的oledbconnection (a conncection)</param>
        /// <param name="connstring">连接字符串 (conntection string)</param>
        /// <param name="cmdtext">command的字符串 (sql statement)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>返回影响行数 (effect line number)</returns>
        public static int executenonquery(oledbconnection conn, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {

            oledbcommand cmd = new oledbcommand();

            preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
            int val = cmd.executenonquery();
            cmd.parameters.clear();
            return val;
        }

        /**//// <summary>
        /// 用于执行一段sql语句。需要传入一个事务transaction.
        /// to excute a sql statement, which reuturns a integer stand for effect line number.
        /// a transaction is reqired
        /// </summary>
        /// <param name="trans">一个trasaction (trasaction)</param>
        /// <param name="cmdtype">command的字符串 (sql statement)</param>
        /// <param name="cmdtext">command的类型，具体见：commandtype (command type)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>返回影响行数 (effect line number)</returns>
        public static int executenonquery(oledbtransaction trans, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();
            preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, cmdparms);
            int val = cmd.executenonquery();
            cmd.parameters.clear();
            return val;
        }

        /**//// <summary>
        /// 用于执行一个select语句返回一个datareader，省略了commandtype参数
        /// to excute a sql statement, and reuturns a datareader.
        /// default command type is text
        /// </summary>
        /// <param name="connstring">连接字符串 (conntection string)</param>
        /// <param name="cmdtext">command的字符串 (sql statement)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>datareader</returns>
        public static oledbdatareader executereader(string connstring, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();
            oledbconnection conn = new oledbconnection(connstring);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandbehaviour.closeconnection will not work
            try {
                preparecommand(cmd, conn, null, commandtype.text, cmdtext, cmdparms);
                oledbdatareader rdr = cmd.executereader(commandbehavior.closeconnection);
                //    cmd.parameters.clear();
                return rdr;
            }
            catch {
                conn.close();
                throw;
            }
        }

        /**//// <summary>
        /// 用于执行一个select语句返回一个datareader
        /// to excute a sql statement, and reuturns a datareader.
        /// </summary>
        /// <param name="connstring">连接字符串 (connection string)</param>
        /// <param name="cmdtype">command的字符串 (sql statement)</param>
        /// <param name="cmdtext">command的类型，具体见：commandtype (command type)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>datareader</returns>
        public static oledbdatareader executereader(string connstring, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();
            oledbconnection conn = new oledbconnection(connstring);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandbehaviour.closeconnection will not work
            try {
                preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
                oledbdatareader rdr = cmd.executereader(commandbehavior.closeconnection);
                //    cmd.parameters.clear();
                return rdr;
            }
            catch {
                conn.close();
                throw;
            }
        }

        /**//// <summary>
        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列，省去了commandtype
        /// to excute , a sql statement, and returns the first column of the first line
        /// default command type is text
        /// </summary>
        /// <param name="connstring">连接字符串 (conntection string)</param>
        /// <param name="cmdtext">command的字符串 (sql statement)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>the first column of the first line</returns>
        public static object executescalar(string connstring, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();

            using (oledbconnection conn = new oledbconnection(connstring)) {
                preparecommand(cmd, conn, null, commandtype.text, cmdtext, cmdparms);
                object val = cmd.executescalar();
                cmd.parameters.clear();
                return val;
            }
        }

        /**//// <summary>
        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列
        /// to excute a sql statement, and returns the first column of the first line
        /// </summary>
        /// <param name="connstring">连接字符串 (connection string)</param>
        /// <param name="cmdtype">command的字符串 (sql statement)</param>
        /// <param name="cmdtext">command的类型，具体见：commandtype (command type)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>the first column of the first line</returns>
        public static object executescalar(string connstring, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {
            oledbcommand cmd = new oledbcommand();

            using (oledbconnection conn = new oledbconnection(connstring)) {
                preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
                object val = cmd.executescalar();
                cmd.parameters.clear();
                return val;
            }
        }

        /**//// <summary>
        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列
        /// to excute a sql statement, and returns the first column of the first line
        /// a connection is passed in instead of a connection string
        /// </summary>
        /// <param name="conn">一个以初始化好的oledbconnection (a conncection)</param>
        /// <param name="connstring">连接字符串 (conntection string)</param>
        /// <param name="cmdtext">command的字符串 (sql statement)</param>
        /// <param name="cmdparms">参数列表 (paramters)</param>
        /// <returns>the first column of the first line</returns>
        public static object executescalar(oledbconnection conn, commandtype cmdtype, string cmdtext, params oledbparameter[] cmdparms) {
  
            oledbcommand cmd = new oledbcommand();

            preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
            object val = cmd.executescalar();
            cmd.parameters.clear();
            return val;
        }

        /**//// <summary>
        /// 在执行sql语句之前的准备工作
        /// </summary>
        /// <param name="cmd">command</param>
        /// <param name="conn">connection</param>
        /// <param name="trans">trasaction</param>
        /// <param name="cmdtype">command类型</param>
        /// <param name="cmdtext">command字符串</param>
        /// <param name="cmdparms">参数列表</param>
        private static void preparecommand(oledbcommand cmd, oledbconnection conn, oledbtransaction trans, commandtype cmdtype, string cmdtext, oledbparameter[] cmdparms) {

            if (conn.state != connectionstate.open)
                conn.open();

            cmd.connection = conn;
            cmd.commandtext = cmdtext;

            if (trans != null)
                cmd.transaction = trans;

            cmd.commandtype = cmdtype;

            if (cmdparms != null) {
                foreach (oledbparameter parm in cmdparms)
                    cmd.parameters.add(parm);
            }
        }