SqlHelper-猿圈-程序猿的知识社区

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>
/// Sql Server 操作基类
/// </summary>
///<author>dsq</author>
///<datetime>09-10-12</datetime>
public class SqlHelper
{
    public SqlHelper()
    {}

    #region 公共方法

    /// <summary>
    /// 验证字符串数组
    /// </summary>
    /// <param name="arr">字符串数组</param>
    /// <returns>验证结果:true or false</returns>
    private static bool ArrayIsNullOrEmpty(string[] arr)
    {
        if (arr == null || arr.Length < 1)
            return true;
        else
        {
            foreach (string parm in arr)
            {
                if (string.IsNullOrEmpty(parm))
                    return true;
            }
            return false;
        }
    }

    /// <summary>
    /// 检查参数
    /// </summary>
    /// <param name="parms">被检测的数组对象</param>
    /// <returns>检测结果:为空:true or false</returns>
    private static bool SqlParameterIsNullOrEmpty(SqlParameter[] parms)
    {
        if (parms == null || parms.Length <= 0)
        {
            return true;
        }
        else
        {
            foreach (SqlParameter parm in parms)
            {
                if (parm == null)
                    return true;
            }
            return false;
        }
    }

    /// <summary>
    /// 检查参数
    /// </summary>
    /// <param name="parms">被检测的数组对象</param>
    /// <returns>检测结果:为空:true or false</returns>
    private static bool SqlParameterIsNullOrEmpty(SqlParameter[][] parms)
    {
        if (parms == null || parms.Length <= 0)
        {
            return true;
        }
        else
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i] == null || parms[i].Length < 1)
                    return true;
                foreach (SqlParameter parm in parms[i])
                {
                    if (parm == null)
                        return true;
                }
            }
            return false;
        }
    }

    #endregion

    #region 执行存储过程

    #region 查询数据

    /// <summary>
    /// 带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="parms">参数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据表</returns>
    public static DataTable Proc_ExecuteDataTable(string procName, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    using (SqlDataReader dr = command.ExecuteReader())
                    {
                        dt.Load(dr);    
                    }
                    return dt;
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    /// <summary>
    /// 不带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据表</returns>
    public static DataTable Proc_ExecuteDataTable(string procName, string conString)
    {
        if (string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(conString))
            return null;
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(procName, conn))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                try
                {
                    adapter.Fill(dt);
                    return dt;
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    /// <summary>
    /// 带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="parms">参数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据集</returns>
    public static DataSet Proc_ExecuteDataSet(string procName, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(procName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adapter.Fill(ds);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="parms">参数</param>
    /// <param name="tbName">内存表表名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据集</returns>
    public static DataSet Proc_ExecuteDataSet(string procName, SqlParameter[] parms, string tbName, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(tbName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(procName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adapter.Fill(ds, tbName);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 不带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据集</returns>
    public static DataSet Proc_ExecuteDataSet(string procName, string conString)
    {
        if (string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(conString))
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    adapter.SelectCommand = command;
                    try
                    {
                        adapter.Fill(ds);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 不带参数的存储过程查询
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="tbName">内存表表名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据集</returns>
    public static DataSet Proc_ExecuteDataSet(string procName, string tbName, string conString)
    {
        if (string.IsNullOrEmpty(procName) || string.IsNullOrEmpty(tbName) || string.IsNullOrEmpty(conString))
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    adapter.SelectCommand = command;
                    try
                    {
                        adapter.Fill(ds, tbName);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    #endregion

    #region 插入数据、更新数据、删除数据

    /// <summary>
    ///  执行插入、删除、更新操作
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQuery(string proName, out int eCount, string conString)
    {
        if (string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString))
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    eCount = command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    ///  执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string proName, out int eCount, string conString)
    {
        if (string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString))
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    eCount = command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="parms">参数数组</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQuery(string proName, SqlParameter[] parms, out int eCount, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString);
        if (isNull)
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    eCount = command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="parms">参数数组</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string proName, SqlParameter[] parms, out int eCount, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString);
        if (isNull)
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    eCount = command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    transaction.Rollback();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行多次不同插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proNames">存储过程名数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string[] proNames, string conString)
    {
        bool isError = ArrayIsNullOrEmpty(proNames) || string.IsNullOrEmpty(conString);
        if (isError)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    foreach (string proName in proNames)
                    {
                        command.CommandText = proName;
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
            return true;
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQuery(string proName, string conString)
    {
        if (string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString))
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string proName, string conString)
    {
        if (string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString))
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQuery(string proName, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string proName, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(proName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(proName, conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="proNames">存储过程名数组</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool Proc_ExecuteNonQueryWithTrans(string[] proNames, SqlParameter[][] parms, string conString)
    {
        bool isNull = ArrayIsNullOrEmpty(proNames) || SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();
                for (int i = 0; i < proNames.Length; i++)
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = proNames[i];
                    command.Transaction = transaction;
                    foreach (SqlParameter parm in parms[i])
                        command.Parameters.Add(parm);
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();
                return false;
            }
            return true;
        }
    }

    #endregion

    #endregion

    #region 执行SQL语句

    #region 查询数据

    /// <summary>
    /// 不带参数的查询
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据表</returns>
    public static DataTable SQL_ExecuteDataTable(string selectSql, string conString)
    {
        if (string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(conString))
            return null;
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                command.CommandText = selectSql;
                try
                {
                    conn.Open();
                    using (SqlDataReader sr = command.ExecuteReader())
                    {
                        dt.Load(sr);
                    }
                    return dt;//返回一个表格
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    /// <summary>
    /// 带参的查询
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="parms">参数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>数据表</returns>
    public static DataTable SQL_ExecuteDataTable(string selectSql, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(selectSql, conn))
            {
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                using (SqlDataAdapter adpter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adpter.Fill(dt);
                        return dt;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 不带参数的查询
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>查询结果</returns>
    public static DataSet SQL_ExecuteDataSet(string selectSql, string conString)
    {
        if (string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(conString))
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlDataAdapter adpter = new SqlDataAdapter(selectSql, conn))
            {
                try
                {
                    adpter.Fill(ds);
                    return ds;
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    /// <summary>
    /// 不带参数的查询
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="tbName">内存表表名</param>
    /// <param name="conString">数据库连接字符串</param>
    /// <returns>查询结果</returns>
    public static DataSet SQL_ExecuteDataSet(string selectSql, string tbName, string conString)
    {
        if (string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(conString) || string.IsNullOrEmpty(tbName))
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlDataAdapter adpter = new SqlDataAdapter(selectSql, conn))
            {
                try
                {
                    adpter.Fill(ds, tbName);
                    return ds;
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    /// <summary>
    /// 带参查询数据
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>查询结果集</returns>
    public static DataSet SQL_ExecuteDataSet(string selectSql, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(selectSql, conn))
            {
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                using (SqlDataAdapter adpter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adpter.Fill(ds);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 带参查询数据
    /// </summary>
    /// <param name="selectSql">查询语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="tbName">内存表表名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>查询结果集</returns>
    public static DataSet SQL_ExecuteDataSet(string selectSql, SqlParameter[] parms, string tbName, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(selectSql) || string.IsNullOrEmpty(tbName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return null;
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(selectSql, conn))
            {
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                using (SqlDataAdapter adpter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adpter.Fill(ds, tbName);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        }
    }

    #endregion

    #region 插入数据、更新数据、删除数据

    /// <summary>
    ///  执行插入、删除、更新操作
    /// </summary>
    /// <param name="opSql">执行操作的SQL语句</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQuery(string opSql, out int eCount, string conString)
    {
        if (string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString))
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                try
                {
                    conn.Open();
                    eCount = command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    ///  执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSql">执行操作的SQL语句</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string opSql, out int eCount, string conString)
    {
        if (string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString))
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    eCount = command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="opSql">执行操作的sql语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQuery(string opSql, SqlParameter[] parms, out int eCount, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString);
        if (isNull)
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                command.CommandType = CommandType.Text;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    eCount = command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSql">执行操作的sql语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="eCount">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string opSql, SqlParameter[] parms, out int eCount, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString);
        if (isNull)
        {
            eCount = -2;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                command.CommandType = CommandType.Text;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    eCount = command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    eCount = -1;
                    transaction.Rollback();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行多次不同插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSqls">执行操作的sql语句数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string[] opSqls, string conString)
    {
        bool isNull = ArrayIsNullOrEmpty(opSqls) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    foreach (string opSql in opSqls)
                    {
                        command.CommandText = opSql;
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
            return true;
        }
    }

    /// <summary>
    /// 执行多次不同插入、删除、更新操作
    /// </summary>
    /// <param name="opSqls">执行操作的sql语句数组</param>
    /// <param name="effectRowCout">影响的行数</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string[] opSqls, out int effectRowCout, string conString)
    {
        bool isNull = ArrayIsNullOrEmpty(opSqls) || string.IsNullOrEmpty(conString);
        if (isNull)
        {
            effectRowCout = -1;
            return false;
        }
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    effectRowCout = 0;
                    foreach (string opsql in opSqls)
                    {
                        command.CommandText = opsql;
                        effectRowCout += command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch
                {
                    effectRowCout = -1;
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
            return true;
        }

    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="opSql">执行操作的sql语句</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQuery(string opSql, string conString)
    {
        if (string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString))
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();//可以进行插入
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSql">执行操作的sql语句</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string opSql, string conString)
    {
        if (string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString))
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作
    /// </summary>
    /// <param name="opSql">执行操作的SQL语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQuery(string opSql, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                command.CommandType = CommandType.Text;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSql">执行操作的SQL语句</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string opSql, SqlParameter[] parms, string conString)
    {
        bool isNull = SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(opSql) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            using (SqlCommand command = new SqlCommand(opSql, conn))
            {
                command.CommandType = CommandType.Text;
                foreach (SqlParameter parm in parms)
                    command.Parameters.Add(parm);
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                    
                catch
                {
                    if (transaction != null)
                        transaction.Rollback();
                    return false;
                }
               
            }
        }
    }

    /// <summary>
    /// 执行插入、删除、更新操作(附带事务)
    /// </summary>
    /// <param name="opSqls">sql 语句数组</param>
    /// <param name="parms">参数数组</param>
    /// <param name="conString">连接字符串</param>
    /// <returns>执行结果:true or false</returns>
    public static bool SQL_ExecuteNonQueryWithTrans(string[] opSqls, SqlParameter[][] parms, string conString)
    {
        bool isNull = ArrayIsNullOrEmpty(opSqls) || SqlParameterIsNullOrEmpty(parms) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            SqlTransaction transaction = null;
            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();
                for (int i = 0; i < opSqls.Length; i++)
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.Text;
                    command.CommandText = opSqls[i];
                    command.Transaction = transaction;
                    foreach (SqlParameter parm in parms[i])
                        command.Parameters.Add(parm);
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                    transaction.Rollback();
                return false;
            }
            return true;
        }
    }

    #endregion

    #endregion

    #region  更新DataGridview 中的 DataSet到数据库中相应的表

    /// <summary>
    /// 更新DataGridview中的DataSet到数据库中相应的表
    /// </summary>
    /// <param name="selectSql">sql select语句</param>
    ///<param name="ds">DataSet结果集</param> 
    /// <param name="tbName">DataGridView 中绑定的表名</param>
    /// <param name="conString">连接字符串</param>
    /// <returns></returns>
    public static bool UpdateDataSetToDB(string selectSql, DataSet ds, string tbName, string conString)
    {
        bool isNull = string.IsNullOrEmpty(selectSql) || ((ds == null || ds.Tables.Count < 1) ? true : false) || string.IsNullOrEmpty(tbName) || string.IsNullOrEmpty(conString);
        if (isNull)
            return false;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(selectSql, conn))
            {
                try
                {
                    using (SqlCommandBuilder sqlCB = new SqlCommandBuilder(adapter))
                    {
                        adapter.Update(ds, tbName);
                        ds.AcceptChanges();
                        return true;
                    }
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    #endregion

}

转载地址：http://blog.csdn.net/luxin10/article/details/7003095

SqlServerHelper类，整理微软提供的helper

微软在petShop中使用了SqlServerHelper类，通过调用静态方法实现对数据库的操作，使用起来非常简单顺手，下面整理了该类的代码，并加以说明：

//------------------------------------------------------------
// All Rights Reserved , Copyright (C) 2010 , lusens 
//------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Utility.Data
{
    /// <summary>
    /// SQLServer数据库操作类
    /// 本类主要包括SQLServer数据库的基本操作
    /// 
    /// 修改纪录
    /// 
    ///     2010.10.22 版本：1.1 lusens 新增了用户传入sql语句列表的事务操作
    ///        2010.09.05 版本：1.0 lusens 创建。
    /// 
    /// 版本：1.1
    /// 
    /// <author>
    ///        <name>lusens</name>
    ///        <date>2010.09.05</date>
    ///        <EMail>lusens@foxmail.com</EMail>
    /// </author> 
    /// </summary>
    public abstract class SQLServerHepler
    {
        //public static readonly string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;

        #region public static string GetConnectionStringByConfig()
        /// <summary>
        /// 读取配置文件的ConnectionString字符串
        /// </summary>
        /// <returns>Connection连接字符串</returns>
        public static string GetConnectionStringByConfig()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
        }
        #endregion

        #region 执行SQL，返回被操作的行数,public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

        /// <summary>
        /// 执行SQL语句，返回被操作的行数
        /// 使用using语句进行conn对象的释放
        /// </summary>
        /// <param name="connectionString">连接字符</param>
        /// <param name="cmdType">Command类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回被操作行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        #endregion

        #region 执行SQL，返回被操作的行数,public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL语句，返回被操作的行数
        /// 这里默认使用外面传入的conn对象，使用完成后不会对conn对象进行释放，需要自己在外面进行数据库连接释放
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">command命令类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region 执行SQL语句，返回被操作的行数,public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL语句，返回被操作的行数
        /// 这里默认使用外面传入的conn对象，使用完成后不会对conn对象进行释放，需要自己在外面进行数据库连接释放
        /// </summary>
        /// <param name="trans">SQL事务</param>
        /// <param name="cmdType">command类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        #endregion

        #region 执行SQL，返回SqlDataReader,public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL，返回SqlDataReader
        /// 返回一个连接，所以不能进行conn释放，在外界代码中使用完DataReader后，注意需要释放reader对象
        /// 当返回连接对象报错时，这里进行数据库连接的关闭，保证数据库连接使用完成后保持关闭
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">command命令类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception e)
            {
                conn.Close();
                throw e;
            }
        }
        #endregion

        #region 执行SQL，返回SqlDataReader, public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL，返回SqlDataReader
        /// 返回一个连接，所以不能进行conn释放，在外界代码中使用完DataReader后，注意需要释放reader对象
        /// 当返回连接对象报错时，这里进行数据库连接的关闭，保证数据库连接使用完成后保持关闭
        /// </summary>
        /// <param name="connection">SqlConnection数据库连接对象</param>
        /// <param name="cmdType">command命令类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception e)
            {
                connection.Close();
                throw e;
            }
        }
        #endregion

        #region 执行SQL，返回DataTable， public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL，返回DataTable
        /// 使用using语句进行conn对象的释放
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">command命令类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }

            return dt;
        }
        #endregion

        #region 执行SQL，返回DataTable,public static DataTable ExecuteDataTable(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 执行SQL，返回DataTable
        /// 这里默认使用外面传入的conn对象，使用完成后不会对conn对象进行释放，需要自己在外面进行数据库连接释放
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">Command命令类型，SQL语句或存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }

            return dt;
        }
        #endregion

        #region 返回第一行第一列，public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 返回第一行第一列
        /// 使用using语句进行conn对象的释放
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">Command命令类型，SQL语句还是存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        #endregion

        #region 返回第一行第一列，public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        /// <summary>
        /// 返回第一行第一列
        /// 使用外面传入的conn对象，使用完成后不会对conn对象进行释放，需要自己在外面进行数据库连接释放
        /// </summary>
        /// <param name="connection">SqlConnection数据库连接对象</param>
        /// <param name="cmdType">Command命令类型，SQL语句还是存储过程</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region 以事务执行sql语句列表，public static bool ExecuteTransaction(SqlConnection conn, List<string> cmdTextes, List<SqlParameter[]> commandParameterses)
        /// <summary>
        /// 以事务执行sql语句列表，返回事务执行是否成功
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="cmdTextes">sql语句列表</param>
        /// <param name="commandParameterses">sql语句列表对应的参数列表，参数列表必须与sql语句列表匹配</param>
        /// <returns>事务执行是否成功</returns>
        public static bool ExecuteTransaction(SqlConnection conn, List<string> cmdTextes, List<SqlParameter[]> commandParameterses)
        {
            bool flag = false;
            if (cmdTextes.Count == commandParameterses.Count)
            {
                SqlTransaction sqlTran = conn.BeginTransaction();
                try
                {
                    for (int i = 0; i < cmdTextes.Count; i++)
                    {
                        ExecuteNonQuery(sqlTran, CommandType.Text, cmdTextes[i], commandParameterses[i]);
                    }
                    sqlTran.Commit();
                    flag = true;
                }
                catch (Exception e)
                {
                    sqlTran.Rollback();
                }
            }
            return flag;
        }
        #endregion

        #region 以事务执行sql语句列表，返回事务执行是否成功，public static bool ExecuteTransaction(string connectionString, List<string> cmdTextes, List<SqlParameter[]> commandParameterses)
        /// <summary>
        /// 以事务执行sql语句列表，返回事务执行是否成功
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdTextes">sql语句列表</param>
        /// <param name="commandParameterses">sql语句列表对应的参数列表，参数列表必须与sql语句列表匹配</param>
        /// <returns></returns>
        public static bool ExecuteTransaction(string connectionString, List<string> cmdTextes, List<SqlParameter[]> commandParameterses)
        {
            bool flag = false;
            if (cmdTextes.Count == commandParameterses.Count)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlTransaction sqlTran = conn.BeginTransaction();
                    try
                    {
                        for (int i = 0; i < cmdTextes.Count; i++)
                        {
                            ExecuteNonQuery(sqlTran, CommandType.Text, cmdTextes[i], commandParameterses[i]);
                        }
                        sqlTran.Commit();
                        flag = true;
                    }
                    catch (Exception e)
                    {
                        sqlTran.Rollback();
                    }
                }
            }
            return flag;
        }
        #endregion

        #region 构造SQLCommand,private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        /// <summary>
        /// 构造SQLCommand
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">SqlConnection数据库连接对象</param>
        /// <param name="trans">SQL事务</param>
        /// <param name="cmdType">Command命令类型</param>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="cmdParms">参数数组</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, 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 = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion
    }
}

菜单 学习猿地 - LMONKEY

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练！告别无效练习

接私单赚外块

VIP优先接，累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

人生苦短我要学Python！

PHP开发工程师

PHP是世界上最好的编程语言!

GO开发工程师

想优雅的写程序，赶紧GO!

大数据开发工程师

弄大数据，就是在搞革命!

前端开发工程师

不仅最好，而且最全!

UI开发工程师

这个世界从来不缺少美，缺少你来创造美!

Linux运维工程师

不只是说说而已！

WEB前端1+X

不仅为考证，轻松做开发

计算机二级（C语言）

一切只为考证！

猿工手册

各种工具精挑细选

猿材料

各种工具精挑细选

猿代码

各种工具精挑细选

猿著课件

各种工具精挑细选

知识题库

知识闯关节节高，刷题涨知识！

阶段练习

单元测试知识学习状况秒掌握！

期末考试

期末测试，学习情况即刻知道！

面试题库

最新的企业技术人员招聘真题练习！

Java专区

2948篇 ｜ 24.5万人浏览

Python专区

2593篇 ｜ 19.5万人浏览

大数据专区

2948篇 ｜ 18.5万人浏览

PHP专区

3953篇 ｜ 27.5万人浏览

Go专区

1382篇 ｜ 12.5万人浏览

Web前端专区

1453篇 ｜ 31.5万人浏览

Linux云计算

1230篇 ｜ 4.5万人浏览

其他专区

53232篇文章 ｜ 11.5万人浏览

搜索

历史记录 清除记录

近期热搜

项目开发全程实录（电商EW_Shop）

8678 人 1年前

轻松学会Laravel-项目篇（商城API）

2022 人 1年前

Python数据分析2.0-金融

325 人 1年前

Python办公自动化2.0

241 人 1年前

使用账号登录

启用更安全省心的  微信登录

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

菜单学习猿地 - LMONKEY

尊享10项VIP特权持续新增

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览

历史记录清除记录

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览