基本的数据库操作类

Web.Config

 <connectionStrings>
  <add name="TestConn" connectionString="Data Source=Andy-NB;Initial Catalog=testDB;Persist Security Info=True;User ID=testID;Password=testPWD" providerName="System.Data.SqlClient"/>
 </connectionStrings>

类一:

 

Code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// DataBase 提供数据库操作的基类,不使用存储过程的操作数据库,使用只需稍微修改
/// </summary>
public class DataBase : IDisposable
{
    #region 构造函数
    public DataBase()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    #endregion

    #region 创建连接对象
    private SqlConnection con = null;  //创建连接对象
    #endregion

    #region   打开数据库连接
    /// <summary>
    /// 打开数据库连接.
    /// </summary>
    private void Open()
    {
        // 打开数据库连接
        if (con == null)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString);//我这web.config里连接字符串配置如此,使用时请按照自己的来配置
        }
        if (con.State == System.Data.ConnectionState.Closed)
        {
            con.Open();
        }

    }
    #endregion

    #region  关闭连接
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void Close()
    {
        if (con != null && con.State != System.Data.ConnectionState.Closed)
        {
            con.Close();
        }
    }
    #endregion

    #region 释放数据库连接资源
    /// <summary>
    /// 释放资源
    /// </summary>
    public void Dispose()
    {
        // 确认连接是否已经关闭
        if (con != null)
        {
            con.Dispose();
            con = null;
        }
    }
    #endregion

    #region   传入参数并且转换为SqlParameter类型
    /// <summary>
    /// 转换参数
    /// </summary>
    /// <param name="ParamName">存储过程名称或命令文本</param>
    /// <param name="DbType">参数类型</param></param>
    /// <param name="Size">参数大小</param>
    /// <param name="Value">参数值</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }

    /// <summary>
    /// 初始化参数值
    /// </summary>
    /// <param name="ParamName">存储过程名称或命令文本</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <param name="Direction">参数方向</param>
    /// <param name="Value">参数值</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        SqlParameter param;

        if (Size > 0) //指定参数大小
            param = new SqlParameter(ParamName, DbType, Size);
        else  //未指定大小
            param = new SqlParameter(ParamName, DbType);

        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
            param.Value = Value;
        return param;
    }

    #endregion

    #region   执行参数命令文本(无数据库中数据返回)
    /// <summary>
    /// 执行数据库操作命令，有参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数对象</param>
    /// <returns></returns>
    public int RunProc(string procName, SqlParameter[] prams)
    {
        SqlCommand cmd = CreateCommand(procName, prams);
        cmd.ExecuteNonQuery();
        this.Close();
        //得到执行成功返回值
        return (int)cmd.Parameters["ReturnValue"].Value;
    }

    /// <summary>
    /// 直接执行SQL语句，没有参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <returns></returns>
    public int RunProc(string procName)
    {
        this.Open();
        SqlCommand cmd = new SqlCommand(procName, con);
        cmd.ExecuteNonQuery();
        this.Close();
        return 1;
    }

    #endregion

    #region   执行参数命令文本(有返回值)
    /// <summary>
    /// 执行查询命令文本，并且返回DataSet数据集，有参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数对象</param>
    /// <param name="tbName">数据表名称</param>
    /// <returns></returns>
    public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
    {
        SqlDataAdapter dap = CreateDataAdaper(procName, prams);
        DataSet ds = new DataSet();
        dap.Fill(ds, tbName);
        this.Close();
        //得到执行成功返回值
        return ds;
    }

    /// <summary>
    /// 执行命令文本，并且返回DataSet数据集，无参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="tbName">数据表名称</param>
    /// <returns>DataSet</returns>
    public DataSet RunProcReturn(string procName, string tbName)
    {
        SqlDataAdapter dap = CreateDataAdaper(procName, null);
        DataSet ds = new DataSet();
        dap.Fill(ds, tbName);
        this.Close();
        //得到执行成功返回值
        return ds;
    }

    /// <summary>
    /// 执行命令文本，并且返回SqlDataReader数据集，无参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <returns>SqlDataReader</returns>
    public SqlDataReader RunProcReturn(string procName)
    {
        ///创建SqlCommand
        SqlCommand cmd = CreateCommand(procName, null);

        ///读取数据
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    /// <summary>
    /// 执行命令文本，并且返回SqlDataReader数据集，有参数
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数</param>
    /// <returns></returns>
    public SqlDataReader RunProcReturn(string procName, SqlParameter[] prams)
    {
        ///创建SqlCommand
        SqlCommand cmd = CreateCommand(procName, prams);

        ///读取数据
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    #endregion

    #region 将命令文本添加到SqlDataAdapter
    /// <summary>
    /// 创建一个SqlDataAdapter对象以此来执行命令文本
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数对象</param>
    /// <returns></returns>
    private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
    {
        this.Open();
        SqlDataAdapter dap = new SqlDataAdapter(procName, con);
        dap.SelectCommand.CommandType = CommandType.Text;  //执行类型：命令文本
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                dap.SelectCommand.Parameters.Add(parameter);
        }
        //加入返回参数
        dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return dap;
    }
    #endregion

    #region   将命令文本添加到SqlCommand
    /// <summary>
    /// 创建一个SqlCommand对象以此来执行命令文本
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams"命令文本所需参数</param>
    /// <returns>返回SqlCommand对象</returns>
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
        // 确认打开连接
        this.Open();
        SqlCommand cmd = new SqlCommand(procName, con);
        cmd.CommandType = CommandType.Text;　　　　 //执行类型：命令文本

        // 依次把参数传入命令文本
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }
        // 加入返回参数
        cmd.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return cmd;
    }
    #endregion

}

 

类二:

 

Code
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;

namespace TestWeb
{
    public class SQLTrans : IDisposable
    {
        #region ** 构造函数 **
        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLTrans()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
            transConnection = new SqlConnection(strConnect);
            transCommand = new SqlCommand();
            transCommand.Connection = transConnection;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLTrans(string strConnection)
        {
            transConnection = new SqlConnection(strConnection.Trim());
            transCommand = new SqlCommand();
            transCommand.Connection = transConnection;
        }
        #endregion ** 构造函数 **

        #region ** 属性 ConnectionString, Connection, Command, Transaction, HaveTrans**
        private SqlConnection transConnection;
        private SqlCommand transCommand;
        private SqlTransaction SQLTransaction;

        /// <summary>
        /// SQL连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return transConnection.ConnectionString;
            }
            set
            {
                transConnection.Close();
                transConnection.ConnectionString = value.Trim();
            }
        }
        /// <summary>
        /// SQL连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                return transConnection;
            }
        }
        /// <summary>
        /// SQL命令
        /// </summary>
        public SqlCommand Command
        {
            get
            {
                return transCommand;
            }
        }
        /// <summary>
        /// 事务
        /// </summary>
        public SqlTransaction Transaction
        {
            get
            {
                return SQLTransaction;
            }
        }
        /// <summary>
        /// 是否正在进行事务
        /// </summary>
        public bool HaveTrans
        {
            get
            {
                return SQLTransaction != null;
            }
        }
        #endregion ** 属性 **

        #region ** 内部方法 **
        /// <summary>
        /// 打开连接, 当连接打开时,不执行操作,当关闭时,打开,当断开时,先关闭,再打开
        /// </summary>
        private void Open()
        {
            if (transConnection.State == ConnectionState.Closed)
            {
                transConnection.Open();
            }
            else if (transConnection.State == ConnectionState.Broken)
            {
                transConnection.Close();
                transConnection.Open();
            }
        }
        #endregion ** 内部方法 **

        #region ** 对外接口 BeginTrans, Commit, Rollback**
        /// <summary>
        /// 释放时, 关于连接
        /// </summary>
        public void Dispose()
        {
            Commit();
            transConnection.Close();
        }
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTrans()
        {
            Open();
            SQLTransaction = transConnection.BeginTransaction();
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            if (SQLTransaction != null)
            {
                SQLTransaction.Commit();
                SQLTransaction = null;
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (SQLTransaction != null)
            {
                SQLTransaction.Rollback();
                SQLTransaction = null;
            }
        }
        #endregion ** 对外接口 **
    }
    public class SQLHelper : IDisposable
    {
        #region ** 构造函数 **
        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLHelper()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
            sqlConnection = new SqlConnection(strConnect);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLHelper(string strConnection)
        {
            sqlConnection = new SqlConnection(strConnection.Trim());
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLHelper(SQLTrans sqlTrans)
        {
            sqlConnection = null;
            sqlCommand = sqlTrans.Command;
            sqlTransaction = sqlTrans;
        }
        #endregion ** 构造函数 **

        #region ** 属性 ConnectionString, Connection, Command **
        /// <summary>
        /// 保存参数的Hashtable
        /// </summary>
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// 数据库连接
        /// </summary>
        private SqlConnection sqlConnection;
        /// <summary>
        /// 数据库命令
        /// </summary>
        private SqlCommand sqlCommand;
        /// <summary>
        /// 事务
        /// </summary>
        private SQLTrans sqlTransaction;

        /// <summary>
        /// SQL连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return sqlCommand.Connection.ConnectionString;
            }
            set
            {
                sqlCommand.Connection.Close();
                sqlCommand.Connection.ConnectionString = value.Trim();
            }
        }
        /// <summary>
        /// 数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                return this.sqlCommand.Connection;
            }
        }
        /// <summary>
        /// SQL命令
        /// </summary>
        public SqlCommand Command
        {
            get
            {
                return this.sqlCommand;
            }
        }
        #endregion ** 属性 **

        #region 数据库连接或关闭
        /// <summary>
        /// 释放时, 关闭数据库连接
        /// </summary>
        public void Dispose()
        {
            this.Close();
        }
        /// <summary>
        /// 打开连接, 当连接打开时,不执行操作,当关闭时,打开,当断开时,先关闭,再打开
        /// </summary>
        private void Open()
        {
            if (sqlCommand.Connection.State == ConnectionState.Closed)
            {
                sqlCommand.Connection.Open();
            }
            else if (sqlCommand.Connection.State == ConnectionState.Broken)
            {
                sqlCommand.Connection.Close();
                sqlCommand.Connection.Open();
            }
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        private void Close()
        {
            if (sqlConnection != null)
            {
                sqlConnection.Close();
            }
        }
        #endregion 数据库连接或关闭

        #region ** 从数据库(或HashTable)中取存储过程的参数 **
        /// <summary>
        /// 复制参数集
        /// </summary>
        /// <param name="originalParameters">原参数集</param>
        /// <returns>目标参数</returns>
        private 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>
        /// <param name="strSpName">存储过程名</param>
        /// <returns>参数集合</returns>
        private SqlParameter[] DiscoverSpParameters(string strSpName)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            SqlCommand sqlCmd = new SqlCommand(strSpName);
            sqlCmd.Connection = this.Connection;
            sqlCmd.CommandType = CommandType.StoredProcedure;

            Open();
            SqlCommandBuilder.DeriveParameters(sqlCmd);

            SqlParameter[] discoveredParameters = new SqlParameter[sqlCmd.Parameters.Count];

            sqlCmd.Parameters.CopyTo(discoveredParameters, 0);

            AssignParametersDefaultValue(discoveredParameters);

            return discoveredParameters;
        }
        /// <summary>
        /// 取存储过程参数,先从静态变量(Hashtable)取, 如果没有找到, 则从数据库取 
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <returns>参数结果集</returns>
        private SqlParameter[] GetSpParameters(string strSpName)
        {

            string hashKey = ConnectionString + ":" + strSpName;

            SqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameters(strSpName);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }
            return CloneParameters(cachedParameters);
        }
        #endregion ** 从数据库(或HashTable)中取存储过程的参数 **

        #region ** 保存SQL命令参数到HashTable, 或从HashTable取SQL命令参数 **
        /// <summary>
        /// 保存命令的参数到HashTable
        /// </summary>
        /// <param name="strSQL">命令字符串</param>
        /// <param name="sqlParameters">参数集</param>
        public void SetCommandParameters(string strSQL, params SqlParameter[] sqlParameters)
        {
            if (strSQL == null || strSQL.Length == 0)
            {
                throw new Exception("SQL命令为空");
            }
            string hashKey = ConnectionString + ":" + strSQL;
            paramCache[hashKey] = sqlParameters;
        }
        /// <summary>
        /// 从HashTable取命令的参数
        /// </summary>
        /// <param name="strSQL">命令字符串</param>
        /// <returns>参数集</returns>
        public SqlParameter[] GetCommandParameters(string strSQL)
        {
            if (strSQL == null || strSQL.Length == 0)
            {
                throw new ArgumentNullException("SQL命令为空");
            }

            string hashKey = ConnectionString + ":" + strSQL;
            SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }
        #endregion ** 保存SQL命令参数到HashTable, 或从HashTable取SQL命令参数 **

        #region ** 设置参数值 **
        /// <summary>
        /// 从数据库取存储过程的参数, 预设置默认值, 并保存在Hashtable中
        /// </summary>
        /// <param name="sqlParameters">SqlParameter 对象集会</param>
        private void AssignParametersDefaultValue(SqlParameter[] sqlParameters)
        {
            if (sqlParameters == null)
            {
                return;
            }
            foreach (SqlParameter sqlParameter in sqlParameters)
            {
                switch (sqlParameter.SqlDbType)
                {
                    case SqlDbType.Bit:
                    case SqlDbType.TinyInt:
                    case SqlDbType.SmallInt:
                    case SqlDbType.Int:
                    case SqlDbType.BigInt:
                    case SqlDbType.Float:
                    case SqlDbType.Decimal:
                    case SqlDbType.SmallMoney:
                    case SqlDbType.Money:
                    case SqlDbType.Real:
                        {
                            sqlParameter.Value = 0;
                            break;
                        }
                    case SqlDbType.Char:
                    case SqlDbType.NChar:
                    case SqlDbType.VarChar:
                    case SqlDbType.NVarChar:
                    case SqlDbType.Text:
                    case SqlDbType.NText:
                        {
                            sqlParameter.Value = "";
                            break;
                        }
                    case SqlDbType.DateTime:
                    case SqlDbType.SmallDateTime:
                        {
                            sqlParameter.Value = DateTime.Parse("1900-01-01 01:01:01");
                            break;
                        }
                    default:
                        {
                            sqlParameter.Value = DBNull.Value;
                            break;
                        }
                }
            }
        }
        private void AssignParameterValues(SqlParameter[] sqlParameters, DataRow dataRow)
        {
            AssignParametersDefaultValue(sqlParameters);
            if ((sqlParameters == null) || (dataRow == null))
            {
                return;
            }
            foreach (SqlParameter sqlParameter in sqlParameters)
            {
                if (sqlParameter.ParameterName == null || sqlParameter.ParameterName.Length <= 1)
                {
                    throw new Exception("绑定参数值时, 参数名为空");
                }
                if (dataRow.Table.Columns.IndexOf(sqlParameter.ParameterName.Substring(1)) != -1)
                {
                    sqlParameter.Value = dataRow[sqlParameter.ParameterName.Substring(1)];
                }
            }
        }
        private void AssignParameterValues(SqlParameter[] sqlParameters, object[] parameterValues)
        {
            AssignParametersDefaultValue(sqlParameters);
            if ((sqlParameters == null) || (parameterValues == null))
            {
                return;
            }
            if (sqlParameters.Length != parameterValues.Length)
            {
                throw new Exception("参数总数和值总数不相等");
            }
            for (int i = 0; i < sqlParameters.Length; i++)
            {
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        sqlParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] != null)
                {
                    sqlParameters[i].Value = parameterValues[i];
                }
            }
        }
        /// <summary>
        /// 绑定命令参数
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="sqlParameters"></param>
        private void PrepareCommand(SqlCommand sqlCmd, SqlParameter[] sqlParameters)
        {
            if (sqlCmd == null)
            {
                throw new Exception("命令为空");
            }
            if (sqlParameters != null)
            {
                AssignParametersDefaultValue(sqlParameters);
                foreach (SqlParameter sqlParameter in sqlParameters)
                {
                    if (sqlParameter != null)
                    {
                        sqlCmd.Parameters.Add(sqlParameter);
                    }
                }
            }
        }
        #endregion ** 设置参数值 **

        #region ** ExecuteNonQuery **
        /// <summary>
        /// 执行数据库(不返回任何参数)的命令
        /// </summary>
        /// <param name="strSQL">命令</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string strSQL)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            return ExecuteNonQuery(sqlCmd);
        }
        /// <summary>
        /// 执行数据库(不返回任何参数)的命令
        /// </summary>
        /// <param name="strSQL">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string strSQL, CommandType commandType)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            return ExecuteNonQuery(sqlCmd);
        }
        /// <summary>
        /// 执行数据库(不返回任何参数)的命令
        /// </summary>
        /// <param name="strSQL">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="sqlParameters">命令参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string strSQL, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            return ExecuteNonQuery(sqlCmd);
        }
        /// <summary>
        /// 执行数据库(不返回任何参数)的存储过程
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string strSpName, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                return ExecuteNonQuery(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteNonQuery(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行数据库(不返回任何参数)的存储过程
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="dataRow">参数值</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string strSpName, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                return ExecuteNonQuery(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteNonQuery(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行数据库(不返回任何参数)的命令
        /// </summary>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(SqlCommand sqlCmd)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                int iRet = sqlCmd.ExecuteNonQuery();
                return iRet;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }
        #endregion ExecuteNonQuery

        #region ExecuteDataSet
        /// <summary>
        /// 执行数据库命令,返回查询结果集
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string strSQL)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            return ExecuteDataSet(sqlCmd);
        }
        /// <summary>
        /// 执行数据库命令,返回查询结果集
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string strSQL, CommandType commandType)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            return ExecuteDataSet(sqlCmd);
        }
        /// <summary>
        /// 执行数据库命令,返回查询结果集
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="sqlParameters">命令参数</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string strSQL, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            return ExecuteDataSet(sqlCmd);
        }
        /// <summary>
        /// 执行存储过程,返回查询结果集
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="parameterValues">参数名</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string strSpName, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                return ExecuteDataSet(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteDataSet(strSpName, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程,返回查询结果集
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="dataRow">参数值</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string strSpName, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                return ExecuteDataSet(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteDataSet(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行数据库命令,返回查询结果集
        /// </summary>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(SqlCommand sqlCmd)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                using (SqlDataAdapter da = new SqlDataAdapter(sqlCmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }

        #endregion ExecuteDataSet

        #region ExecuteReader
        /// <summary>
        /// 执行SQL命令,返回DataReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string strSQL)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            return ExecuteReader(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令,返回DataReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strSQL">SQL命令类型</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string strSQL, CommandType commandType)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            return ExecuteReader(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令,返回DataReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strSQL">SQL命令类型</param>
        /// <param name="sqlParameters">SQL命令参数</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string strSQL, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            return ExecuteReader(sqlCmd);
        }
        /// <summary>
        /// 执行存储过程,返回DataReader
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="parameterValues">参数名</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string strSpName, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                return ExecuteReader(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteReader(strSpName, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程,返回DataReader
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="dataRow">参数值</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string strSpName, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                return ExecuteReader(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteReader(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行数据库命令,返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(SqlCommand sqlCmd)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                return dataReader;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }

        #endregion ExecuteReader

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL命令, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(string strSQL)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            return ExecuteScalar(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(string strSQL, CommandType commandType)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            return ExecuteScalar(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="sqlParameters">SQL命令参数</param>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(string strSQL, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            return ExecuteScalar(sqlCmd);
        }
        /// <summary>
        /// 执行存储过程, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <param name="strSpName">存储过程名称</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(string strSpName, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                return ExecuteScalar(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteScalar(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行存储过程, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="dataRow">参数值</param>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(string strSpName, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                return ExecuteScalar(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteScalar(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行存储过程, 返回第一行第一列对象,其他放弃
        /// </summary>
        /// <returns>返回对象</returns>
        public object ExecuteScalar(SqlCommand sqlCmd)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                object objRet = sqlCmd.ExecuteScalar();
                return objRet;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }
        #endregion ExecuteScalar

        #region ExecuteXmlReader
        /// <summary>
        /// 执行SQL命令,返回XmlReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(string strSQL)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            return ExecuteXmlReader(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令,返回XmlReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strSQL">SQL命令类型</param>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(string strSQL, CommandType commandType)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            return ExecuteXmlReader(sqlCmd);
        }
        /// <summary>
        /// 执行SQL命令,返回XmlReader
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strSQL">SQL命令类型</param>
        /// <param name="sqlParameters">SQL命令参数</param>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(string strSQL, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            return ExecuteXmlReader(sqlCmd);
        }
        /// <summary>
        /// 执行存储过程,返回XmlReader
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="parameterValues">参数名</param>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(string strSpName, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                return ExecuteXmlReader(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteXmlReader(strSpName, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程,返回XmlReader
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <param name="dataRow">参数值</param>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(string strSpName, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                return ExecuteXmlReader(strSpName, CommandType.StoredProcedure, sqlParameters);
            }
            return ExecuteXmlReader(strSpName, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行数据库命令,返回XmlReader
        /// </summary>
        /// <returns>XmlReader</returns>
        public XmlReader ExecuteXmlReader(SqlCommand sqlCmd)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                XmlReader dataReader = sqlCmd.ExecuteXmlReader();
                return dataReader;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }
        #endregion ExecuteXmlReader

        #region FillDataSet
        /// <summary>
        /// 执行SQL命令, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="dataSet">输出结果集</param>
        public void FillDataSet(string strSQL, DataSet dataSet)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            FillDataSet(sqlCmd, dataSet);
        }
        /// <summary>
        /// 执行SQL命令, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="dataSet">输出结果集</param>
        public void FillDataSet(string strSQL, CommandType commandType, DataSet dataSet)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            FillDataSet(sqlCmd, dataSet);
        }
        /// <summary>
        /// 执行SQL命令, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="strSQL">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="dataSet">输出结果集</param>
        /// <param name="commandParameters">SQL命令参数</param>
        public void FillDataSet(string strSQL, CommandType commandType, DataSet dataSet, params SqlParameter[] sqlParameters)
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL);
            sqlCmd.CommandType = commandType;
            PrepareCommand(sqlCmd, sqlParameters);
            FillDataSet(sqlCmd, dataSet);
        }
        /// <summary>
        /// 执行存储过程, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="strSpName">存储过程名称</param>
        /// <param name="dataSet">输出结果集</param>
        /// <param name="commandParameters">存储过程参数</param>
        public void FillDataSet(string strSpName, DataSet dataSet, params object[] parameterValues)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, parameterValues);
                FillDataSet(strSpName, CommandType.StoredProcedure, dataSet, sqlParameters);
            }
            FillDataSet(strSpName, CommandType.StoredProcedure, dataSet);
        }
        /// <summary>
        /// 执行存储过程, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="strSpName">存储过程名称</param>
        /// <param name="dataSet">输出结果集</param>
        /// <param name="dataRow">存储过程参数</param>
        public void FillDataSet(string strSpName, DataSet dataSet, DataRow dataRow)
        {
            if (strSpName == null || strSpName.Length == 0)
            {
                throw new Exception("存储过程名称为空");
            }

            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                SqlParameter[] sqlParameters = GetSpParameters(strSpName);
                AssignParameterValues(sqlParameters, dataRow);
                FillDataSet(strSpName, CommandType.StoredProcedure, dataSet, sqlParameters);
            }
            FillDataSet(strSpName, CommandType.StoredProcedure, dataSet);
        }
        /// <summary>
        /// 执行SQL命令, 按原DataSet表名顺序顺序填充DataSet
        /// </summary>
        /// <param name="sqlCmd">SQL命令</param>
        /// <param name="dataSet">输出结果集</param>
        public void FillDataSet(SqlCommand sqlCmd, DataSet dataSet)
        {
            try
            {
                Open();
                if (this.sqlTransaction != null)
                {
                    sqlCmd.Transaction = this.sqlTransaction.Transaction;
                }
                sqlCmd.Connection = this.Connection;
                using (SqlDataAdapter da = new SqlDataAdapter(sqlCmd))
                {
                    string tableName = "Table";
                    for (int index = 0; index < dataSet.Tables.Count; index++)
                    {
                        da.TableMappings.Add(tableName, dataSet.Tables[index].TableName);
                        tableName = "Table" + (index + 1).ToString();
                    }
                    da.Fill(dataSet);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //Close();
            }
        }
        #endregion
    }
}

 

转载于:https://www.cnblogs.com/andycai/archive/2009/07/08/1518930.html