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