SQLHelper.cs

// Copyright(C) 2000-2006 Shixin Corporation
// All rights reserverd
// 文件名: SQLHelper.cs
// 创建者: 
// 创建日期: 2006-03-21
// 概述: DataAccess层的数据访问Helper文件，模仿自Microsoft DAAB1.0。
// 修改人/修改日期：  
// 开放STR_CONNECTION连接字符串，因为SqlConnection是非托管资源，无法自动回收。

using  System;
using  System.Data;
using  System.Xml;
using  System.Data.SqlClient;
using  System.Collections;
using  System.Configuration;
using  System.Collections.Specialized;

using  SPS.Common;


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

        /**//// <summary>
        /// 根据SQL语句查询返回DataSet
        /// </summary>
        /// <param name="SQLString">查询的SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string SQLString) {
            using (OleDbConnection connection = new OleDbConnection(CONN_STRING)) {
                DataSet ds = new DataSet();
                try {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString,connection);
                    command.Fill(ds,"ds");
                }
                catch(System.Data.OleDb.OleDbException ex) {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
  
        /**//// <summary>
        /// 根据SQL语句和查询参数查询返回DataSet
        /// </summary>
        /// <param name="SQLString">查询的SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string SQLString,params OleDbParameter[] cmdParms) {
            using (OleDbConnection connection = new OleDbConnection(CONN_STRING)) {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, connection, null,CommandType.Text,SQLString, cmdParms);
                using( OleDbDataAdapter da = new OleDbDataAdapter(cmd) ) {
                    DataSet ds = new DataSet();
                    try {
                        da.Fill(ds,"ds");
                        cmd.Parameters.Clear();
                    }
                    catch(System.Data.OleDb.OleDbException ex) {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
    }

转载于:https://www.cnblogs.com/xiongeee/archive/2006/12/19/596876.html