ADO.Net

ADO.Net概述

ADO.NET组件实了现连接数据库以及操作数据库中数据的功能；ADO（Active Data Object）是一个COM组件类库，用于访问数据库。

ADO.NET体系结构

• Connection类（连接）

该类主要用于数据库中建立连接和断开连接操作，能通过类获得当前数据库连接状态。

Data Source=服务器名称/数据库的实例名；Initial Catalog=数据库名称；User ID=用户名；Password=密码；

• Command类（命令）

向连接数据库发出操作命令，操作（增删改查）。

构造方法	说明
SqlCommand()	无参构造方法
SqlCommand(string commandText,SqlConnection conn)	带参的构造方法，第 1 个参数是要执行的 SQL 语句，第 2 个参数是数据库的连接对象

 

属性或方法	说明
CommandText	属性，Command 对象中要执行的 SQL 语句
Connection	属性，获取或设置数据库的连接对象
CommandType	属性，获取或设置命令类型
Parameters	属性，设置 Command 对象中 SQL 语句的参数
ExecuteReader()	方法，获取执行查询语句的结果
ExecuteScalar()	方法，返回查询结果中第 1 行第 1 列的值
ExecuteNonQuery()	方法，执行对数据表的增加、删除、修改操作

string connStr = "Data Source=服务器名称/数据库的实例名;Initial Catalog=数据库名称;User ID=用户名;Password=密码";
try
{
    //using方式释放资源，关闭连接 
    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("T-sql", conn);
        int insertRow=cmd.ExecuteNonQuery();
        if (insertRow > 0)
        {
            MessageBox.Show("执行影响返回行数:{" + insertRow + "}");
        }
        MessageBox.Show("已连接数据库");
    }
}
catch (Exception ex)
{
    MessageBox.Show("连接数据库失败！" + ex.Message);
}

• DataReader类（读取数据）

提供了顺序的，只读的方式读取Command对象获得的数据结果集，每次只在内存缓冲区里存储结果集的一条数据，并且会以独占的方式打开数据库连接，在其获得数据过程中其它操作不可以再使用连接对象。使用Datareader对象的效率比较高，如果要查询大量数据，同时不需要随机访问和修改数据，DataReader是优先的选择。

public SysFunction GetModelById(string id)
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConStr"].ToString());
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT * FROM SysFunction WHERE FunctionId =" + id, conn);
    SqlDataReader dataReader = cmd.ExecuteReader();
    SysFunction model = new SysFunction();
    while (dataReader.Read())
    {
        model.FunctionName = dataReader["FunctionName"].ToString();
        model.FunctionCode = dataReader["FunctionCode"].ToString();
        dataReader.GetInt("columnName1");//get方法性能更高
        dataReader.GetString("columnName3");
        dataReader.GetFloat("columnName3");
    }
    dataReader.Close();
    dataReader.Dispose();
　　conn.Close(); //关闭连接
    return model;
}

• DataAdaper类（桥梁）

该类与DataSet联用，它主要用于将数据库的结果运送到DataSet中保存。

• DataSet类（数据集）

该类与DataAdaper类似，都用于存放对数据库查询的集合。差异，DataSet数据可反复读取、更改值。

 

1.DbHelper（medreport应用）

查看代码

 public sealed class DbHelper : IDisposable
    {
        private DbConnection conn = null;
        private DbTransaction tran = null;
        private DbProviderFactory factory = null;
        private static string _connectionString = "";
        private static string _pDbType = "";

        #region 新建与数据库连接

        /// <summary>
        /// 使用默认的连接字串打开与SQL Server的连接
        /// </summary>
        public DbClass()
        {
            if (_connectionString == "") throw new Exception("没有建立默认连接");
            Open(_connectionString, _pDbType, false);
        }

        /// <summary>
        /// 用连接字符串打开与数据库的连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="pDbType">数据库类型</param>
        /// <param name="setDefault">是否设为默认连接</param>
        public DbClass(string connectionString, string pDbType, bool setDefault)
        {
            Open(connectionString, pDbType, setDefault);
        }

        /// <summary>
        /// 用连接字符串打开与数据库的连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="pDbType">数据库类型</param>
        private void Open(string connectionString, string pDbType, bool setDefault)
        {
            if (conn != null) throw new Exception("已经打开此连接");
            if (setDefault)
            {
                _connectionString = connectionString;
                _pDbType = pDbType;
            }
            factory = DbProviderFactories.GetFactory(pDbType);
            conn = factory.CreateConnection();
            conn.ConnectionString = connectionString;
            conn.Open();
        }

        /// <summary>
        /// 以SQLSERVER账户认证方式打开与SQLSERVER的连接
        /// </summary>
        /// <param name="serverName">数据库服务器IP</param>
        /// <param name="dbName">数据库名</param>
        /// <param name="userId">SQL Server用户名</param>
        /// <param name="passwd">SQL Server密码</param>
        /// <param name="minPoolSize">连接池最小连接数</param>
        /// <param name="maxPoolSize">连接池最大连接数</param>
        /// <param name="connTimeOut">连接超时时间</param>
        /// <param name="setDefault">是否设为默认连接</param>
        public DbClass(string serverName, string dbName, string userId, string passwd, string minPoolSize, string maxPoolSize, string connTimeOut, bool setDefault)
        {
            Open(serverName, dbName, true, userId, passwd, true, minPoolSize, maxPoolSize, connTimeOut, "System.Data.SqlClient", setDefault);
        }

        /// <summary>
        /// 拼接成连接字符串
        /// </summary>
        private void Open(string serverName, string dbName, bool usePasswd, string userId, string passwd, bool usePool, string minPoolSize, string maxPoolSize, string connTimeOut, string pDbType, bool setDefault)
        {
            string str = "Server=" + serverName + ";Database=" + dbName + ";Integrated Security=";
            if (usePasswd == true)
                str = str + "False;User ID=" + userId + ";Password=" + passwd + ";";
            else
                str = str + "True;";
            if (usePool == true)
            {
                if (maxPoolSize != "")
                    str = str + "Max Pool Size=" + maxPoolSize + ";";
                if (minPoolSize != "")
                    str = str + "Min Pool Size=" + minPoolSize + ";";
                str = str + "Pooling=True;";
            }
            else
                str = str + "Pooling=False;";
            if (connTimeOut != "") str = str + "Connect timeout=" + connTimeOut + ";";
            str = str.Substring(0, str.Length - 1);
            Open(str, pDbType, setDefault);
        }

        #endregion

        #region  public void ExecuteTransaction

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="SQLStringList">多条sql语句</param>
        /// <exception cref="Exception"></exception>
        public void ExecuteTransaction(ArrayList SQLStringList)
        {
            BeginTransaction();
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        ExecuteNonQuery(strsql);
                    }
                }
                Commit();
            }
            catch (DbException E)
            {
                Rollback();
                throw new Exception(E.Message);
            }
        }

        /// <summary>
        /// 连接打开
        /// </summary>
        public void Open()
        {
            conn.Open();
        }

        public void Close()
        {
            Rollback();
            conn.Close();
        }

        /// <summary>
        /// 打开连接事务
        /// </summary>
        public void BeginTransaction()
        {
            if (tran != null) throw new Exception("此连接已有打开事务！");
            if (conn.State != ConnectionState.Open) throw new Exception("连接没有打开时不能开始事务！");
            tran = conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            if (tran == null) throw new Exception("没有打开的事务，不能提交事务！");
            try
            {
                tran.Commit();
            }
            catch
            {
                throw;
            }
            finally
            {
                tran.Dispose();
                tran = null;
            }
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void Rollback()
        {
            if (tran == null) return;
            try
            {
                tran.Rollback();
            }
            finally
            {
                tran.Dispose();
                tran = null;
            }
        }

        #endregion

        #region public int ExecuteNonQuery

        #region 参数方法

        /// <summary>
        /// 添加参数到command
        /// </summary>
        /// <param name="command">command对象</param>
        /// <param name="commandParameters">参数</param>
        private void AttachParameters(DbCommand command, DbParameter[] commandParameters)
        {
            foreach (DbParameter p in commandParameters)
            {
                if (p != null)
                {
                    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) p.Value = DBNull.Value;
                    command.Parameters.Add(p);
                }
            }
        }

        #endregion

        /// <summary>
        /// 运行一般SQL语句，返回所更新的行数
        /// </summary>
        /// <param name="commandText">语句名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>所更新的行数</returns>
        public int ExecuteNonQuery(string commandText, params DbParameter[] commandParameters)
        {
            return DoExecuteNonQuery(true, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 运行SQL语句或存储过程，返回所更新的行数
        /// </summary>
        /// <param name="useDefaultTran">是否使用默认事务，为false的时候，不使用事务</param>
        /// <param name="commandType">CommandType.Text或者CommandType.StoredProcedure</param>
        /// <param name="commandText">sql语句或者存储过程名</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>所更新的行数</returns>
        private int DoExecuteNonQuery(bool useDefaultTran, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                if (useDefaultTran) if (tran != null) cmd.Transaction = tran;
                AttachParameters(cmd, commandParameters);
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception e1)
                {
                    throw new Exception(e1.Message + Environment.NewLine + "SQL=" + cmd.CommandText);
                }
            }
        }

        #endregion

        #region public object ExecuteScalar

        /// <summary>
        /// 运行一般SQL语句，返回object
        /// </summary>
        /// <param name="commandText">语句名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回object</returns>
        public object ExecuteScalar(string commandText, params DbParameter[] commandParameters)
        {
            return DoExecuteScalar(true, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 运行SQL语句或存储过程，返回object
        /// </summary>
        /// <param name="useDefaultTran">是否使用默认事务，为false的时候，不使用事务</param>
        /// <param name="commandType">CommandType.Text或者CommandType.StoredProcedure</param>
        /// <param name="commandText">sql语句或者存储过程名</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回object</returns>
        private object DoExecuteScalar(bool useDefaultTran, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                if (useDefaultTran) if (tran != null) cmd.Transaction = tran;
                AttachParameters(cmd, commandParameters);
                try
                {
                    return cmd.ExecuteScalar();
                }
                catch (Exception e1)
                {
                    throw new Exception(e1.Message + Environment.NewLine + "SQL=" + cmd.CommandText);
                }
            }
        }
        #endregion

        #region public DbDataReader ExecuteReader

        /// <summary>
        /// 运行一般SQL语句，返回DbDataReader
        /// </summary>
        /// <param name="commandText">语句名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DbDataReader</returns>
        public DbDataReader ExecuteReader(string commandText, params DbParameter[] commandParameters)
        {
            return DoExecuteReader(true, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 运行SQL语句或存储过程，返回DbDataReader
        /// </summary>
        /// <param name="useDefaultTran">是否使用默认事务，为false的时候，不使用事务</param>
        /// <param name="commandType">CommandType.Text或者CommandType.StoredProcedure</param>
        /// <param name="commandText">sql语句或者存储过程名</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DbDataReader</returns>
        private DbDataReader DoExecuteReader(bool useDefaultTran, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                if (useDefaultTran) if (tran != null) cmd.Transaction = tran;
                AttachParameters(cmd, commandParameters);
                try
                {
                    return cmd.ExecuteReader();
                }
                catch (Exception e1)
                {
                    throw new Exception(e1.Message + Environment.NewLine + "SQL=" + cmd.CommandText);
                }
            }
        }

        #endregion

        #region public DataSet ExecuteDataSet

        /// <summary>
        /// 运行一般SQL语句，返回DataSet
        /// </summary>
        /// <param name="commandText">语句名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, params DbParameter[] commandParameters)
        {
            return DoExecuteDataSet(true, CommandType.Text, commandText, -1, -1, "", commandParameters);
        }

        /// <summary>
        /// 运行SQL语句或存储过程，返回DataSet
        /// </summary>
        /// <param name="useDefaultTran">是否使用默认事务，为false的时候，不使用事务</param>
        /// <param name="commandType">CommandType.Text或者CommandType.StoredProcedure</param>
        /// <param name="commandText">sql语句或者存储过程名</param>
        /// <param name="startRecord">从其开始的从零开始的记录号，不设置用-1传进去</param>
        /// <param name="maxRecords">要检索的最大记录数</param>
        /// <param name="srcTable">用于表映射的源表的名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DataSet</returns>
        private DataSet DoExecuteDataSet(bool useDefaultTran, CommandType commandType, string commandText, int startRecord, int maxRecords, string srcTable, params DbParameter[] commandParameters)
        {
            using (DbCommand cmd = conn.CreateCommand())
            using (DbDataAdapter da = factory.CreateDataAdapter())
            {
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                if (useDefaultTran) if (tran != null) cmd.Transaction = tran;
                AttachParameters(cmd, commandParameters);
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                try
                {
                    if (startRecord == -1)
                        if (srcTable == "")
                            da.Fill(ds);
                        else
                            da.Fill(ds, srcTable);
                    else
                        da.Fill(ds, startRecord, maxRecords, srcTable);
                }
                catch (Exception e1)
                {
                    if (ds != null)
                    {
                        ds.Dispose();
                        ds = null;
                    }
                    throw new Exception(e1.Message + Environment.NewLine + "SQL=" + cmd.CommandText);
                }
                return ds;
            }
        }

        #endregion

        #region public DataTable ExecuteDataTable

        /// <summary>
        /// 运行一般SQL语句，返回DataTable
        /// </summary>
        /// <param name="commandText">语句名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteDataTable(string commandText, params DbParameter[] commandParameters)
        {
            return DoExecuteDataTable(true, CommandType.Text, commandText, -1, -1, commandParameters);
        }

        /// <summary>
        /// 运行SQL语句或存储过程，返回DataTable
        /// </summary>
        /// <param name="useDefaultTran">是否使用默认事务，为false的时候，不使用事务</param>
        /// <param name="commandType">CommandType.Text或者CommandType.StoredProcedure</param>
        /// <param name="commandText">sql语句或者存储过程名</param>
        /// <param name="startRecord">从其开始的从零开始的记录号，不设置用-1传进去</param>
        /// <param name="maxRecords">要检索的最大记录数</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回DataTable</returns>
        private DataTable DoExecuteDataTable(bool useDefaultTran, CommandType commandType, string commandText, int startRecord, int maxRecords, params DbParameter[] commandParameters)
        {
            using (DbCommand cmd = conn.CreateCommand())
            using (DbDataAdapter da = factory.CreateDataAdapter())
            {
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                if (useDefaultTran) if (tran != null) cmd.Transaction = tran;
                AttachParameters(cmd, commandParameters);
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                try
                {
                    if (startRecord == -1)
                        da.Fill(dt);
                    else
                        da.Fill(startRecord, maxRecords, dt);
                }
                catch (Exception e1)
                {
                    if (dt != null)
                    {
                        dt.Dispose();
                        dt = null;
                    }
                    throw new Exception(e1.Message + Environment.NewLine + "SQL=" + cmd.CommandText);
                }
                return dt;
            }
        }


        #endregion

        #region Insert和Update T-sql拼接

        private Dictionary<string, string> hs = null;

        /// <summary>
        /// 根据Append的字段 执行insert语句，然后清空所添加的字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>返回更新的行数</returns>
        public int ExecuteInsert(string tableName)
        {
            return ExecuteNonQuery(GetInsertSql(tableName));
        }

        /// <summary>
        /// 根据Append的字段 执行update语句，然后清空所添加的字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filter">update的条件 如ID=1 如有单引号需自己手工加db.Rep()</param>
        /// <returns>返回更新的行数</returns>
        public int ExecuteUpdate(string tableName, string filter)
        {
            return ExecuteNonQuery(GetUpdateSql(tableName, filter));
        }

        /// <summary>
        /// 添加字段及值，当值两边不需要加''号时采用此方法
        /// </summary>
        /// <param name="field">字段名</param>
        /// <param name="value">值</param>
        public void AppendValue(string field, object value)
        {
            if (hs == null) hs = new Dictionary<string, string>();
            if (value == null || value == DBNull.Value) value = "null";
            if (hs.ContainsKey(field))
                hs[field] = value.ToString();
            else
                hs.Add(field, value.ToString());
        }

        /// <summary>
        /// 添加字段及值，value的两边自动会加上db.rep()，不需手工加
        /// </summary>
        /// <param name="field"></param>
        /// <param name="value"></param>
        public void AppendString(string field, object value)
        {
            if (hs == null) hs = new Dictionary<string, string>();
            if (value == null || value == DBNull.Value) value = "";
            if (hs.ContainsKey(field))
                hs[field] = Rep(value.ToString());
            else
                hs.Add(field, Rep(value.ToString()));
        }
        /// <summary>
        /// 返回可插入的字串 加'str'
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public string Rep(object str)
        {
            if (str == null || str == DBNull.Value) return "''";
            return "'" + str.ToString().Replace("'", "''") + "'";
        }
        /// <summary>
        /// 清空所添加的字段
        /// </summary>
        public void AppendClear()
        {
            if (hs != null) hs.Clear();
        }

        /// <summary>
        /// 根据Append的字段 得到insert语句，然后清空所添加的字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>返回sql语句</returns>
        public string GetInsertSql(string tableName)
        {
            if (hs == null || hs.Count <= 0) return "";
            string sql = "insert into " + tableName + "(";
            string sql1 = "values(";
            foreach (string s in hs.Keys)
            {
                sql += s + ",";
                sql1 += hs[s] + ",";
            }
            sql = sql.Substring(0, sql.Length - 1) + ") " + sql1.Substring(0, sql1.Length - 1) + ") ";
            hs.Clear();
            return sql;
        }

        /// <summary>
        /// 根据Append的字段 得到update语句，然后清空所添加的字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filter">update的条件 如ID=1 如有单引号需自己手工加db.Rep()</param>
        /// <returns>返回sql语句</returns>
        public string GetUpdateSql(string tableName, string filter)
        {
            if (hs == null || hs.Count <= 0) return "";
            string sql = "update " + tableName + " set ";
            foreach (string s in hs.Keys)
            {
                sql += s + "=" + hs[s] + ",";
            }
            sql = sql.Substring(0, sql.Length - 1);
            if (filter.Trim() != "")
                sql += " where " + filter;
            hs.Clear();
            return sql;
        }

        #endregion

        #region 一般方法

        /// <summary>
        /// 获取sqlserver最后一行插入的Identity,@@IDENTITY值
        /// </summary>
        public int Identity
        {
            get
            {
                object o = ExecuteScalar("select @@IDENTITY");
                if (o == DBNull.Value)
                    throw new Exception("没有做过数据插入时，无法得到IDENTITY值！");
                else
                    return int.Parse(o.ToString());
            }
        }

        /// <summary>
        /// oracle获取最后一行插入的Identity
        /// </summary>
        /// <param name="seq">序列名称</param>
        /// <returns></returns>
        public int ReturnIdentity(string seq)
        {
            object obj = ExecuteScalar("select  " + seq + ".currval from  DUAL");
            if (obj != DBNull.Value)
            {

                return int.Parse(obj.ToString());
            }
            return 0;
        }

        #endregion

        #region 释放资源

        /// <summary>
        /// 释放所占资源
        /// </summary>
        public void Dispose()
        {
            try
            {
                if (tran != null) tran.Rollback();
            }
            finally
            {
                if (tran != null)
                {
                    try
                    {
                        tran.Dispose();
                        tran = null;
                    }
                    catch { }
                }
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                        conn = null;
                    }
                    catch { }
                }
                if (factory != null)
                {
                    try
                    {
                        factory = null;
                    }
                    catch { }
                }
            }
            AppendClear();
        }

        ~DbClass()
        {
            Dispose();
        }

        #endregion
    }

2.SqlServer帮助类（https://blog.csdn.net/MarcoPro/article/details/128401327）

查看代码

 class SqlHelper
    {
        public static string connectionString;
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();                            
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object ExecuteScalar(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }


        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }