通用数据库访问代码SqlHelper

最新推荐文章于 2021-08-04 22:26:19 发布
最新推荐文章于 2021-08-04 22:26:19 发布
阅读量207 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/zfylzl/p/5952176.html
版权

通用数据库访问代码，此类为抽象类，不允许实例化，在应用时直接调用即可

数据库连接：

/// <summary>
    /// 数据库连接字符串
    /// </summary>
    /// <returns></returns>
    public static SqlConnection CreateConn()
    {
        return new SqlConnection(GetConnString());
    }

    /// <summary>
    /// 获取数据库连接字符串
    /// </summary>
    /// <returns></returns>
    public static string GetConnString()
    {
        return ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
    }

    /// <summary>
    /// 读数据库连接字符串
    /// </summary>
    public static SqlConnection GetReadDbConn() 
    {
        ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ReadDbSqlConnString"];
        //如果没有配置读数据库，就从主数据库中读取
        string readDdConnectionString = settings == null ? GetConnString() : settings.ConnectionString;
        return new SqlConnection(readDdConnectionString); ;
    }

/// <summary>
    /// 为执行命令准备参数
    /// </summary>
    /// <param name="cmd">SqlCommand 命令</param>
    /// <param name="conn">已经存在的数据库连接</param>
    /// <param name="trans">数据库事物处理</param>
    /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
    /// <param name="cmdText">Command text，T-SQL语句 例如 Select 列名 from 表名</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)
            {
                if (parm != null)
                {
                    if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }

/// <summary>
    /// 获取插入记录时返回Id的语句。SCOPE_IDENTITY是返回当前作用域（当前语句执行产生的ID），@@IDENTITY是返回全局的ID
    /// </summary>
    /// <returns></returns>
    public static string GetLastIdSql()
    {
        return " Select SCOPE_IDENTITY(); ";
    }

    /// <summary>
    /// 关闭SqlConnection
    /// </summary>
    /// <returns></returns>
    public static void CloseConn(SqlConnection conn)
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }

    /// <summary>
    /// 打开SqlConnection
    /// </summary>
    /// <returns></returns>
    public static void OpenConn(SqlConnection conn)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }

    #region 事务

    /// <summary>
    /// 安全的开启事务方法，不用提前开启数据库连接。
    /// </summary>
    /// <example>
    /// sqlTrans = SqlHelper.BeginTransaction(conn);
    /// </example>
    /// <param name="conn">数据库连接</param>
    /// <returns>如果成功返回SqlTransaction，否则返回为null</returns>
    public static SqlTransaction BeginTransaction(SqlConnection conn)
    {
        OpenConn(conn);
        return conn.BeginTransaction();
    }

    /// <summary>
    /// 安全的回滚事务方法，同时关闭数据库连接
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.RollbackTransaction(trans);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    public static void RollbackTransaction(SqlTransaction trans)
    {
        RollbackTransaction(trans,true);
    }

    /// <summary>
    /// 安全的回滚事务方法
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.RollbackTransaction(trans,true);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    /// <param name="closeConn">是否同时关闭数据库连接；为true时会同时关闭数据库连接。</param>
    public static void RollbackTransaction(SqlTransaction trans, bool closeConn)
    {
        if (trans != null && trans.Connection != null)
        {
            SqlConnection conn = trans.Connection;
            trans.Rollback(); //回滚事务
            trans.Dispose();
            trans = null;
            if (closeConn)
            {
                CloseConn(conn);
            }
        }
    }

    /// <summary>
    /// 安全的提交事务方法，同时关闭数据库连接
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.CommitTransaction(trans);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    public static void CommitTransaction(SqlTransaction trans)
    {
        CommitTransaction(trans, true);
    }
    
    /// <summary>
    /// 安全的提交事务方法
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.CommitTransaction(trans,true);
    /// </remarks>
    /// <param name="closeConn">是否同时关闭数据库连接；为true时会同时关闭数据库连接。</param>
    /// <param name="trans">一个存在的sql事务</param>
    public static void CommitTransaction(SqlTransaction trans, bool closeConn)
    {
        if (trans != null && trans.Connection != null)
        {
            SqlConnection conn = trans.Connection;
            trans.Commit(); //提交事务
            trans.Dispose();
            trans = null;
            if (closeConn)
            {
                CloseConn(conn);
            }
        }
    }
View Code

ExecuteDataTable：

public static DataTable ExecuteDataTable(SqlConnection connection, string commandText)
    {
        return ExecuteDataTable(connection, CommandType.Text, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(connection, commandType, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        DataSet ds = ExecuteDataset(connection, commandType, commandText, commandParameters);
        CloseConn(connection);
        return ds == null || ds.Tables.Count == 0 ? new DataTable() : ds.Tables[0];
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, string commandText)
    {
        return ExecuteDataTable(transaction, CommandType.Text, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(transaction, commandType, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        DataSet ds = ExecuteDataset(transaction, commandType, commandText, commandParameters);
        return ds == null || ds.Tables.Count == 0 ? new DataTable() : ds.Tables[0];
    }
View Code

ExecuteDataSet：

public static DataSet ExecuteDataset(SqlConnection connection, string commandText)
    {
        return ExecuteDataset(connection, commandText, null);
    }

    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
    }

    public static DataSet ExecuteDataset(SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataset(connection, CommandType.Text, commandText, commandParameters);
    }
    
    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Parameters.Clear();
        CloseConn(connection);
        return ds;
    }

    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
    }

    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Parameters.Clear();
        return ds;
    }

    public static DataSet RunProc(SqlConnection connection, string procName, out int returnValue, params SqlParameter[] commandParameters)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, connection, (SqlTransaction)null, CommandType.StoredProcedure, procName, commandParameters);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    returnValue = (int)cmd.Parameters["Returnvalue"].Value;
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
            catch
            {
                connection.Close();
                returnValue = 0;
                return new DataSet();
            }
        }
    }

    public static DataSet RunProc(SqlTransaction transaction, string procName, out int returnValue, params SqlParameter[] commandParameters)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, transaction.Connection, transaction, CommandType.StoredProcedure, procName, commandParameters);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    returnValue = (int)cmd.Parameters["Returnvalue"].Value;
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
            catch
            {
                transaction.Connection.Close();
                returnValue = 0;
                return new DataSet();
            }
        }
    }
View Code

ExecuteNonQuery：

public static int ExecuteNonQuery(SqlConnection connection, string cmdText)
    {
        return ExecuteNonQuery(connection, CommandType.Text, cmdText, null);
    }

    public static int ExecuteNonQuery(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQuery(connection, CommandType.Text, cmdText, commandParameters);
    }

    /// cmdType可以是语句，也可以是存储过程
    /// 语句：CommandType.Text，cmdText就是sql语句，存储过程：CommandType.StoredProcedure，cmdText就是存储过程的名字，commandParameter是参数
    /// 例子：Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = 3600;
        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        CloseConn(connection);
        return val;
    }

    public static int ExecuteNonQueryTrans(SqlTransaction trans, string cmdText)
    {
        return ExecuteNonQueryTrans(trans, cmdText, null);
    }

    public static int ExecuteNonQueryTrans(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQueryTrans(trans, CommandType.Text, cmdText, commandParameters);
    }

    public static int ExecuteNonQueryTrans(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;
    }
View Code

ExecuteScalar：

public static object ExecuteScalar(SqlConnection connection, string cmdText)
    {
        return ExecuteScalar(connection, cmdText, null);
    }
    
    public static object ExecuteScalar(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalar(connection, CommandType.Text, cmdText, commandParameters);
    }

    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();
        CloseConn(connection);
        return val;
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, string cmdText)
    {
        return ExecuteScalarTrans(trans, cmdText, null);
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalarTrans(trans, CommandType.Text, cmdText, commandParameters);
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
        object val = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        return val;
    }
View Code

分页：

/// <summary> 分页方法
    /// </summary>
    /// <param name="Conn">连接</param>
    /// <param name="Fields">返回字段</param>
    /// <param name="SqlDataTable">表名或视图名</param>
    /// <param name="strWhere">条件 eg. Id > 1 </param>
    /// <param name="strOrderBy">排序 eg. Order By Id</param>
    /// <param name="PrimaryKey">主键，本字段已弃用，留着只是为了保持兼容，传空即可</param>
    /// <param name="pageIndex">起始页</param>
    /// <param name="pageSize">页大小</param>
    /// <param name="recordCount">共几页</param>
    /// <returns></returns>
    public static DataTable Paging(string Fields, string SqlDataTable, string strWhere, string strOrderBy, string PrimaryKey, int pageIndex, int pageSize, out int recordCount)
    {
        using (SqlConnection Conn = CreateConn())
        {
            return Paging(Conn, Fields, SqlDataTable, strWhere, strOrderBy, PrimaryKey, pageIndex, pageSize, out  recordCount);
        }
    }
    
    public static DataTable Paging(SqlConnection Conn, string Fields, string SqlDataTable, string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        return Paging(Conn, Fields, SqlDataTable, strWhere, strOrderBy, string.Empty, pageIndex, pageSize, out recordCount);
    }

    public static DataTable Paging(SqlConnection Conn, string Fields, string SqlDataTable, string strWhere, string strOrderBy, string PrimaryKey, int pageIndex, int pageSize, out int recordCount)
    {
        if (Conn.State != ConnectionState.Open)
        {
            Conn.Open();
        }
        if (strWhere.Trim() != "" 
            && !strWhere.TrimStart().StartsWith("Where", StringComparison.CurrentCultureIgnoreCase)
            ) strWhere = " Where " + strWhere;
        SqlCommand Comm = new SqlCommand("sp_Sql_Paging", Conn);
        Comm.CommandType = CommandType.StoredProcedure;
        Comm.Parameters.Add("@SqlDataTable", SqlDbType.NVarChar).Value = SqlDataTable;
        Comm.Parameters.Add("@PrimaryKey", SqlDbType.NVarChar).Value = PrimaryKey;
        Comm.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = Fields;
        Comm.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
        Comm.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
        Comm.Parameters.Add("@strOrderBy", SqlDbType.NVarChar).Value = strOrderBy;
        Comm.Parameters.Add("@strWhere", SqlDbType.NVarChar).Value = strWhere;
        Comm.Parameters.Add("@recordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        SqlDataAdapter Sda = new SqlDataAdapter(Comm);
        DataTable dt_Paging = new DataTable();
        Sda.Fill(dt_Paging);
        recordCount = DataConverter.StrToInt(Comm.Parameters["@recordCount"].Value);
        return dt_Paging;
    }

    public static DataTable SQlPaging(SqlConnection conn, string cmdText, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        DataTable dt_Paging = Paging(conn, cmdText, strOrderBy, pageIndex, pageSize, out  recordCount);
        return dt_Paging;
    }

    public static DataTable Paging(SqlConnection conn, string cmdText, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        if (strOrderBy.Trim() == string.Empty) strOrderBy = " order by getdate() ";
        if (pageIndex <= 0) pageIndex = 1;
        if (pageSize <= 0) pageSize = 20;
        string sql = string.Format("select count(*) from ({0}) as t", cmdText);
        recordCount = ObjectToInt32(SqlHelper.ExecuteScalar(conn, sql));
        int startIndex = (pageIndex - 1) * pageSize + 1;
        int endIndex = startIndex + (pageSize - 1);

        sql = string.Format("select * from (select ROW_NUMBER() Over( " + strOrderBy + @") as rowId,* from ({0}) as t ) as t1 
                               Where rowId between " + startIndex + " and " + endIndex, cmdText);
        DataTable dt_Paging = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql);
        return dt_Paging;

    }
View Code

参数SqlParameter：

/// <summary>
    /// 添加参数，比如：SqlHelper.MakeInParam("@SkuId", SkuId)
    /// </summary>
    /// <param name="ParamName">参数名</param>
    /// <param name="Value">参数值</param>
    /// <returns>参数对象</returns>
    public static SqlParameter MakeInParam(string ParamName, object Value)
    {
        int size = 0;
        SqlDbType dbType = GetSqlDbType(Value, out size);
        return MakeInParam(ParamName, dbType, size, Value);
    }

    public static SqlParameter MakeInParam<T>(string ParamName, T Value)
    {
        int size = 0;
        SqlDbType dbType = GetSqlDbType(Value, out size);
        return MakeInParam(ParamName, dbType, size, Value);
    }

    public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }

    public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }

    public static 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;
    }

    /// <summary>
    /// 获取变量对应的SqlDbType和size.
    /// </summary>
    /// <param name="value">.net类型变量</param>
    /// <param name="size">Param size</param>
    /// <returns></returns>
    public static SqlDbType GetSqlDbType(object value, out int size)
    {
        SqlDbType t = SqlDbType.Text;
        if (value is string)
        {
            string v = value.ToString();
            if (v.Length > 3900)
            {
                t = SqlDbType.Text;
                size = 2147483640;
            }
            else
            {
                t = SqlDbType.NVarChar;
                size = 4000;
            }
        }
        else if (value is Nullable<bool>)
        {
            t = SqlDbType.Bit;
            size = 1;
        }
        else if (value is Nullable<sbyte>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<byte>)
        {
            t = SqlDbType.TinyInt;
            size = 1;
        }
        else if (value is Nullable<short>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<ushort>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<DateTime>)
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (value is Nullable<int>)
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (value is Nullable<uint>)
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (value is Nullable<long>)
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (value is Nullable<ulong>)
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (value is Nullable<float>)
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (value is Nullable<double>)
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (value is Nullable<decimal>)
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (value is Nullable<Guid>)
        {
            t = SqlDbType.UniqueIdentifier;
            size = 8;
        }
        else
        {
            if (value == DBNull.Value || value == null)
            {
                t = SqlDbType.VarChar;
                size = 1000;
            }
            else
            {
                t = SqlDbType.NText;
                size = 2147483640;
            }
        }
        return t;
    }

    /// <summary>
    /// 根据 type parameter Get SqlDbType
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="value"></param>
    /// <param name="size"></param>
    /// <returns></returns>
    public static SqlDbType GetSqlDbType<T>(T value, out int size)
    {
        SqlDbType t = SqlDbType.Text;
        Type type = typeof(T);
        if (type == typeof(string))
        {
            string v = value.ToString();
            if (v.Length > 3900)
            {
                t = SqlDbType.Text;
                size = 2147483640;
            }
            else
            {
                t = SqlDbType.NVarChar;
                size = 4000;
            }
        }
        else if (type == typeof(int))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(DateTime))
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (type == typeof(long))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(decimal))
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (type == typeof(float))
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (type == typeof(double))
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (type == typeof(uint))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(ulong))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<DateTime>))
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (type == typeof(Nullable<bool>))
        {
            t = SqlDbType.Bit;
            size = 1;
        }
        else if (type == typeof(Nullable<sbyte>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<byte>))
        {
            t = SqlDbType.TinyInt;
            size = 1;
        }
        else if (type == typeof(Nullable<short>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<ushort>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<int>))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(Nullable<uint>))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(Nullable<long>))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<ulong>))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<float>))
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (type == typeof(Nullable<double>))
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (type == typeof(Nullable<decimal>))
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (type == typeof(Nullable<Guid>))
        {
            t = SqlDbType.UniqueIdentifier;
            size = 8;
        }
        else
        {
            t = GetSqlDbType((object)value, out size);
        }
        return t;
    }
View Code

 整合所有代码：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Data.SqlClient;

public abstract class SqlHelper
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    /// <returns></returns>
    public static SqlConnection CreateConn()
    {
        return new SqlConnection(GetConnString());
    }

    /// <summary>
    /// 获取数据库连接字符串
    /// </summary>
    /// <returns></returns>
    public static string GetConnString()
    {
        return ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
    }

    /// <summary>
    /// 读数据库连接字符串
    /// </summary>
    public static SqlConnection GetReadDbConn() 
    {
        ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ReadDbSqlConnString"];
        //如果没有配置读数据库，就从主数据库中读取
        string readDdConnectionString = settings == null ? GetConnString() : settings.ConnectionString;
        return new SqlConnection(readDdConnectionString); ;
    }

    #region ExecuteNonQuery
    
    public static int ExecuteNonQuery(SqlConnection connection, string cmdText)
    {
        return ExecuteNonQuery(connection, CommandType.Text, cmdText, null);
    }

    public static int ExecuteNonQuery(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQuery(connection, CommandType.Text, cmdText, commandParameters);
    }

    /// cmdType可以是语句，也可以是存储过程
    /// 语句：CommandType.Text，cmdText就是sql语句，存储过程：CommandType.StoredProcedure，cmdText就是存储过程的名字，commandParameter是参数
    /// 例子：Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = 3600;
        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        CloseConn(connection);
        return val;
    }

    public static int ExecuteNonQueryTrans(SqlTransaction trans, string cmdText)
    {
        return ExecuteNonQueryTrans(trans, cmdText, null);
    }

    public static int ExecuteNonQueryTrans(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteNonQueryTrans(trans, CommandType.Text, cmdText, commandParameters);
    }

    public static int ExecuteNonQueryTrans(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 ExecuteScalar
    
    public static object ExecuteScalar(SqlConnection connection, string cmdText)
    {
        return ExecuteScalar(connection, cmdText, null);
    }
    
    public static object ExecuteScalar(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalar(connection, CommandType.Text, cmdText, commandParameters);
    }

    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();
        CloseConn(connection);
        return val;
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, string cmdText)
    {
        return ExecuteScalarTrans(trans, cmdText, null);
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
    {
        return ExecuteScalarTrans(trans, CommandType.Text, cmdText, commandParameters);
    }

    public static object ExecuteScalarTrans(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
        object val = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        return val;
    }

    #endregion

    #region ExecuteDataSet

    public static DataSet ExecuteDataset(SqlConnection connection, string commandText)
    {
        return ExecuteDataset(connection, commandText, null);
    }

    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
    }

    public static DataSet ExecuteDataset(SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
    {
        return ExecuteDataset(connection, CommandType.Text, commandText, commandParameters);
    }
    
    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Parameters.Clear();
        CloseConn(connection);
        return ds;
    }

    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
    }

    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Parameters.Clear();
        return ds;
    }

    public static DataSet RunProc(SqlConnection connection, string procName, out int returnValue, params SqlParameter[] commandParameters)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, connection, (SqlTransaction)null, CommandType.StoredProcedure, procName, commandParameters);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    returnValue = (int)cmd.Parameters["Returnvalue"].Value;
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
            catch
            {
                connection.Close();
                returnValue = 0;
                return new DataSet();
            }
        }
    }

    public static DataSet RunProc(SqlTransaction transaction, string procName, out int returnValue, params SqlParameter[] commandParameters)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                PrepareCommand(cmd, transaction.Connection, transaction, CommandType.StoredProcedure, procName, commandParameters);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    returnValue = (int)cmd.Parameters["Returnvalue"].Value;
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
            catch
            {
                transaction.Connection.Close();
                returnValue = 0;
                return new DataSet();
            }
        }
    }

    #endregion

    #region ExecuteDataTable

    public static DataTable ExecuteDataTable(SqlConnection connection, string commandText)
    {
        return ExecuteDataTable(connection, CommandType.Text, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(connection, commandType, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        DataSet ds = ExecuteDataset(connection, commandType, commandText, commandParameters);
        CloseConn(connection);
        return ds == null || ds.Tables.Count == 0 ? new DataTable() : ds.Tables[0];
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, string commandText)
    {
        return ExecuteDataTable(transaction, CommandType.Text, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(transaction, commandType, commandText, null);
    }

    public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        DataSet ds = ExecuteDataset(transaction, commandType, commandText, commandParameters);
        return ds == null || ds.Tables.Count == 0 ? new DataTable() : ds.Tables[0];
    }
    
    #endregion

    #region 分页

    /// <summary> 分页方法
    /// </summary>
    /// <param name="Conn">连接</param>
    /// <param name="Fields">返回字段</param>
    /// <param name="SqlDataTable">表名或视图名</param>
    /// <param name="strWhere">条件 eg. Id > 1 </param>
    /// <param name="strOrderBy">排序 eg. Order By Id</param>
    /// <param name="PrimaryKey">主键，本字段已弃用，留着只是为了保持兼容，传空即可</param>
    /// <param name="pageIndex">起始页</param>
    /// <param name="pageSize">页大小</param>
    /// <param name="recordCount">共几页</param>
    /// <returns></returns>
    public static DataTable Paging(string Fields, string SqlDataTable, string strWhere, string strOrderBy, string PrimaryKey, int pageIndex, int pageSize, out int recordCount)
    {
        using (SqlConnection Conn = CreateConn())
        {
            return Paging(Conn, Fields, SqlDataTable, strWhere, strOrderBy, PrimaryKey, pageIndex, pageSize, out  recordCount);
        }
    }
    
    public static DataTable Paging(SqlConnection Conn, string Fields, string SqlDataTable, string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        return Paging(Conn, Fields, SqlDataTable, strWhere, strOrderBy, string.Empty, pageIndex, pageSize, out recordCount);
    }

    public static DataTable Paging(SqlConnection Conn, string Fields, string SqlDataTable, string strWhere, string strOrderBy, string PrimaryKey, int pageIndex, int pageSize, out int recordCount)
    {
        if (Conn.State != ConnectionState.Open)
        {
            Conn.Open();
        }
        if (strWhere.Trim() != "" 
            && !strWhere.TrimStart().StartsWith("Where", StringComparison.CurrentCultureIgnoreCase)
            ) strWhere = " Where " + strWhere;
        SqlCommand Comm = new SqlCommand("sp_Sql_Paging", Conn);
        Comm.CommandType = CommandType.StoredProcedure;
        Comm.Parameters.Add("@SqlDataTable", SqlDbType.NVarChar).Value = SqlDataTable;
        Comm.Parameters.Add("@PrimaryKey", SqlDbType.NVarChar).Value = PrimaryKey;
        Comm.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = Fields;
        Comm.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
        Comm.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
        Comm.Parameters.Add("@strOrderBy", SqlDbType.NVarChar).Value = strOrderBy;
        Comm.Parameters.Add("@strWhere", SqlDbType.NVarChar).Value = strWhere;
        Comm.Parameters.Add("@recordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        SqlDataAdapter Sda = new SqlDataAdapter(Comm);
        DataTable dt_Paging = new DataTable();
        Sda.Fill(dt_Paging);
        recordCount = DataConverter.StrToInt(Comm.Parameters["@recordCount"].Value);
        return dt_Paging;
    }

    public static DataTable SQlPaging(SqlConnection conn, string cmdText, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        DataTable dt_Paging = Paging(conn, cmdText, strOrderBy, pageIndex, pageSize, out  recordCount);
        return dt_Paging;
    }

    public static DataTable Paging(SqlConnection conn, string cmdText, string strOrderBy, int pageIndex, int pageSize, out int recordCount)
    {
        if (strOrderBy.Trim() == string.Empty) strOrderBy = " order by getdate() ";
        if (pageIndex <= 0) pageIndex = 1;
        if (pageSize <= 0) pageSize = 20;
        string sql = string.Format("select count(*) from ({0}) as t", cmdText);
        recordCount = ObjectToInt32(SqlHelper.ExecuteScalar(conn, sql));
        int startIndex = (pageIndex - 1) * pageSize + 1;
        int endIndex = startIndex + (pageSize - 1);

        sql = string.Format("select * from (select ROW_NUMBER() Over( " + strOrderBy + @") as rowId,* from ({0}) as t ) as t1 
                               Where rowId between " + startIndex + " and " + endIndex, cmdText);
        DataTable dt_Paging = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql);
        return dt_Paging;

    }
    #endregion

    /// <summary>
    /// 为执行命令准备参数
    /// </summary>
    /// <param name="cmd">SqlCommand 命令</param>
    /// <param name="conn">已经存在的数据库连接</param>
    /// <param name="trans">数据库事物处理</param>
    /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
    /// <param name="cmdText">Command text，T-SQL语句 例如 Select 列名 from 表名</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)
            {
                if (parm != null)
                {
                    if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }

    #region Make SqlParameters

    /// <summary>
    /// 添加参数，比如：SqlHelper.MakeInParam("@SkuId", SkuId)
    /// </summary>
    /// <param name="ParamName">参数名</param>
    /// <param name="Value">参数值</param>
    /// <returns>参数对象</returns>
    public static SqlParameter MakeInParam(string ParamName, object Value)
    {
        int size = 0;
        SqlDbType dbType = GetSqlDbType(Value, out size);
        return MakeInParam(ParamName, dbType, size, Value);
    }

    public static SqlParameter MakeInParam<T>(string ParamName, T Value)
    {
        int size = 0;
        SqlDbType dbType = GetSqlDbType(Value, out size);
        return MakeInParam(ParamName, dbType, size, Value);
    }

    public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }

    public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }

    public static 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;
    }

    /// <summary>
    /// 获取变量对应的SqlDbType和size.
    /// </summary>
    /// <param name="value">.net类型变量</param>
    /// <param name="size">Param size</param>
    /// <returns></returns>
    public static SqlDbType GetSqlDbType(object value, out int size)
    {
        SqlDbType t = SqlDbType.Text;
        if (value is string)
        {
            string v = value.ToString();
            if (v.Length > 3900)
            {
                t = SqlDbType.Text;
                size = 2147483640;
            }
            else
            {
                t = SqlDbType.NVarChar;
                size = 4000;
            }
        }
        else if (value is Nullable<bool>)
        {
            t = SqlDbType.Bit;
            size = 1;
        }
        else if (value is Nullable<sbyte>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<byte>)
        {
            t = SqlDbType.TinyInt;
            size = 1;
        }
        else if (value is Nullable<short>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<ushort>)
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (value is Nullable<DateTime>)
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (value is Nullable<int>)
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (value is Nullable<uint>)
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (value is Nullable<long>)
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (value is Nullable<ulong>)
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (value is Nullable<float>)
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (value is Nullable<double>)
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (value is Nullable<decimal>)
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (value is Nullable<Guid>)
        {
            t = SqlDbType.UniqueIdentifier;
            size = 8;
        }
        else
        {
            if (value == DBNull.Value || value == null)
            {
                t = SqlDbType.VarChar;
                size = 1000;
            }
            else
            {
                t = SqlDbType.NText;
                size = 2147483640;
            }
        }
        return t;
    }

    /// <summary>
    /// 根据 type parameter Get SqlDbType
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="value"></param>
    /// <param name="size"></param>
    /// <returns></returns>
    public static SqlDbType GetSqlDbType<T>(T value, out int size)
    {
        SqlDbType t = SqlDbType.Text;
        Type type = typeof(T);
        if (type == typeof(string))
        {
            string v = value.ToString();
            if (v.Length > 3900)
            {
                t = SqlDbType.Text;
                size = 2147483640;
            }
            else
            {
                t = SqlDbType.NVarChar;
                size = 4000;
            }
        }
        else if (type == typeof(int))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(DateTime))
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (type == typeof(long))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(decimal))
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (type == typeof(float))
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (type == typeof(double))
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (type == typeof(uint))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(ulong))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<DateTime>))
        {
            t = SqlDbType.DateTime;
            size = 8;
        }
        else if (type == typeof(Nullable<bool>))
        {
            t = SqlDbType.Bit;
            size = 1;
        }
        else if (type == typeof(Nullable<sbyte>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<byte>))
        {
            t = SqlDbType.TinyInt;
            size = 1;
        }
        else if (type == typeof(Nullable<short>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<ushort>))
        {
            t = SqlDbType.SmallInt;
            size = 2;
        }
        else if (type == typeof(Nullable<int>))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(Nullable<uint>))
        {
            t = SqlDbType.Int;
            size = 4;
        }
        else if (type == typeof(Nullable<long>))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<ulong>))
        {
            t = SqlDbType.BigInt;
            size = 8;
        }
        else if (type == typeof(Nullable<float>))
        {
            t = SqlDbType.Float;
            size = 8;
        }
        else if (type == typeof(Nullable<double>))
        {
            t = SqlDbType.Real;
            size = 8;
        }
        else if (type == typeof(Nullable<decimal>))
        {
            t = SqlDbType.Decimal;
            size = 8;
        }
        else if (type == typeof(Nullable<Guid>))
        {
            t = SqlDbType.UniqueIdentifier;
            size = 8;
        }
        else
        {
            t = GetSqlDbType((object)value, out size);
        }
        return t;
    }

    /// <summary>
    /// 获取插入记录时返回Id的语句。SCOPE_IDENTITY是返回当前作用域（当前语句执行产生的ID），@@IDENTITY是返回全局的ID
    /// </summary>
    /// <returns></returns>
    public static string GetLastIdSql()
    {
        return " Select SCOPE_IDENTITY(); ";
    }

    /// <summary>
    /// 关闭SqlConnection
    /// </summary>
    /// <returns></returns>
    public static void CloseConn(SqlConnection conn)
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }

    /// <summary>
    /// 打开SqlConnection
    /// </summary>
    /// <returns></returns>
    public static void OpenConn(SqlConnection conn)
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }

    #region 事务

    /// <summary>
    /// 安全的开启事务方法，不用提前开启数据库连接。
    /// </summary>
    /// <example>
    /// sqlTrans = SqlHelper.BeginTransaction(conn);
    /// </example>
    /// <param name="conn">数据库连接</param>
    /// <returns>如果成功返回SqlTransaction，否则返回为null</returns>
    public static SqlTransaction BeginTransaction(SqlConnection conn)
    {
        OpenConn(conn);
        return conn.BeginTransaction();
    }

    /// <summary>
    /// 安全的回滚事务方法，同时关闭数据库连接
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.RollbackTransaction(trans);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    public static void RollbackTransaction(SqlTransaction trans)
    {
        RollbackTransaction(trans,true);
    }

    /// <summary>
    /// 安全的回滚事务方法
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.RollbackTransaction(trans,true);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    /// <param name="closeConn">是否同时关闭数据库连接；为true时会同时关闭数据库连接。</param>
    public static void RollbackTransaction(SqlTransaction trans, bool closeConn)
    {
        if (trans != null && trans.Connection != null)
        {
            SqlConnection conn = trans.Connection;
            trans.Rollback(); //回滚事务
            trans.Dispose();
            trans = null;
            if (closeConn)
            {
                CloseConn(conn);
            }
        }
    }

    /// <summary>
    /// 安全的提交事务方法，同时关闭数据库连接
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.CommitTransaction(trans);
    /// </remarks>
    /// <param name="trans">一个存在的sql事务</param>
    public static void CommitTransaction(SqlTransaction trans)
    {
        CommitTransaction(trans, true);
    }
    
    /// <summary>
    /// 安全的提交事务方法
    /// </summary>
    /// <remarks>
    /// 使用示例：
    /// SqlHelper.CommitTransaction(trans,true);
    /// </remarks>
    /// <param name="closeConn">是否同时关闭数据库连接；为true时会同时关闭数据库连接。</param>
    /// <param name="trans">一个存在的sql事务</param>
    public static void CommitTransaction(SqlTransaction trans, bool closeConn)
    {
        if (trans != null && trans.Connection != null)
        {
            SqlConnection conn = trans.Connection;
            trans.Commit(); //提交事务
            trans.Dispose();
            trans = null;
            if (closeConn)
            {
                CloseConn(conn);
            }
        }
    }

    #endregion
}
View Code

 

转载于:https://www.cnblogs.com/zfylzl/p/5952176.html

aibian0219
关注
C#知识|通用数据访问类SQLHelper的编写
做有价值的事，积累有价值的笔记！
05-31 244
哈喽，你好啊，我是雷工！前面学习了C#操作SQLServer数据库数据的增删改查，《C#知识|通过ADO.NET实现应用程序对数据库的增、删、改操作。其中实现的过程中有很多代码是重复的，此时作为高阶程序员为了使代码更优雅，避免重复，从而要考虑代码的复用技术，针对此案例中代码重复情况，结合项目中对数据库的操作还是挺普遍的，为了便于其他项目也能够复用该方法，可以将数据访问写成一个通用的类。
ASP.NET对SQLServer的通用数据库访问类
10-22
ASP.NET中的`SqlHelper`类是一个通用的数据库访问组件，设计用于简化与SQL Server数据库的交互。这个类封装了常见的数据库操作，如执行查询、存储过程等，使得开发者无需在每次需要与数据库通信时都重复编写相同的...
数据访问类代码
05-26
封装有数据库访问代码，很经典，过年使用，经过验证。。。
数据库的通用访问代码
VicSoft的专栏
09-06 1021
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Collections;using System.Data.SqlClient;/// /// 数据库的通用访问代码/// 此类为抽象类，不允许实例化，在应用时直接调用
简单通用数据库访问代码
aibian0219的博客
05-31 254
创建连接，执行语句，执行存储过程等等 using System; using System.Collections.Generic; using System.Text; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Configuration...
数据库操作通用代码!
夕阳(Zhanghongwen)的专栏
08-23 980
***************************************************************************/-->Dim objConn**********************************************************************DBBegin()** 函数名:DBBegin  ** 输
sql访问数据库的代码
yanghaitaohhh的专栏
06-26 1512
sql访问数据的步骤 1、定义数据库驱动器。 2、设置需要访问的数据库。 3、定义数据库语句实例。 4、将sql语句放到数据库执行，并获取执行结果。 5、处理执行结果。 访问数据库的源代码 package main; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Resu
SqlHelper: Web开发的通用数据库访问控制示例
在Web开发中，通用数据库访问控件（代码）是提高效率和简化数据操作的关键组成部分。本资源的核心是一个名为`SqlHelper`的抽象类，它主要负责与数据库进行交互，特别是通过ADO.NET连接管理器来执行SQL查询和事务处理...
web开发通用数据库访问控件（代码）
05-28
### Web开发通用数据库访问控件（代码）：深入解析与应用 #### 一、引言 在Web开发中，数据库访问是不可或缺的一部分，涉及到数据的读取、写入、更新和删除等操作。为了简化这一过程，提高代码的可重用性和效率，...
访问各种数据库代码
新新
09-04 2609
 1、Oracle8/8i/9i数据库（thin模式）       Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();       String   url="jdbc:oracle:thin:@localhost:1521:orcl";       //orcl为数据库的SID       String   user="
网站与数据库连接代码代码
01-18
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;
C#SqlHelper 数据库连接帮助类
08-18
C#SqlHelper 数据库连接帮助类
java访问数据库的代码
shmily_yang的专栏
08-19 1121
<br />java访问数据库的方式有很多种，这里我只说两种，一种是普通的访问方式，一种是用context的方式。下面分别就是这两种的代码片段：<br /><br />普通的访问方式：<br /><br />public class AccessDatabase {<br />  private static  Connection getConnection()throws SQLException, ClassNotFoundException{<br />   Class.forName("com.m
数据访问层的通用代码
qq_52426874的博客
08-04 339
public class BaseDAO { private static final String username="root"; //数据库帐号 private static final String password="root"; //数据库密码 private static final String url="jdbc:mysql://localhost:3306/kgcnews"; //数据地址 private static final String dr.
SqlHelper类源码
jishublog
01-30 233
SqlHelper是非常方便的数据库操作函数，功能强大，使用起来非常简单，免去很多复杂的数据库操作的代码，在多层应用及面向对象开发中都有很多应用 SqlHelper源码带有详细的说明，主要包括以下操作： 简单SQL语句(ExcuteSql) 带参数的简单SQL语句(ExcuteSQLParm) 简单存储过程 (ExcuteProc) 带参数的存储过程(ExcuteProcParm) ...
转 C# SqlHelper数据库的通用访问代码（sqlclient)
weixin_30367543的博客
12-12 86
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Collections; using System.Data.SqlClient; /// <summary> ...
sql数据库连接类SQLHelper(转)
weixin_30314631的博客
07-06 88
asp.net 项目连接SQL数据库详解代码(类) asp.net项目往往都要连接数据库，我一般习惯使用三层架构(Mode,DAL,BLL,Web)来创建项目， 今天我就把项目怎么连接SQL数据库的类写下来，仅供大家参考。 （注意：此教程是在三层架构的前提下） 1.首先是连接字符串：为了便于修改，我们把连接的字符串写在Web层下的Web.conf...
一步步写自己SqlHelper类库（三）：连接字符串
weixin_34358365的博客
04-19 164
1.连接字符串定义　　我们已经知道，ADO.NET类库为不同的外部数据源提供了一致的访问。这些数据源可以是本地的数据文件（如excel、txt、access，甚至是SQLite），也可以是远程的数据库服务器（如SQL Server、MySQL、DB2、Oracle等）。而我们可以选择一种既简单又容易操作的标识去连接不同的数据源，这时候连接字符串就起到了作用。　　连接字符串是标识ADO.NET用怎样...
利用SqlHelper.cs实现Web程序与数据库的连接
david专栏
03-20 694
利用SqlHelper.cs实现Web程序与数据库的连接  (2006-08-19 18:42:33) 转载▼   分类： 技术杂谈         SqlHelper.cs文件封装了所有的关于数据库的操作。鹏鹏推荐给我使用，我实验一下果然可行。防止以后忘记，特写此文章。         我的这个SqlHelper.cs是鹏鹏给我的版本，
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值