当前位置:新注册送38元体验金 > 新注册送38元体验金编程 > DBHelper (支持事务与数据库变更),dbhelper事务

DBHelper (支持事务与数据库变更),dbhelper事务

文章作者:新注册送38元体验金编程 上传时间:2019-08-22

DBHelper (支持事务与数据库变更),dbhelper事务

1   概述

更新内容:添加 "支持数据分页"

这个数据库操作类的主要特色有

1>     事务操作更加的方便

2>     变更数据库更加的容易

3>   支持数据分页

最新的所有代码:

新注册送38元体验金 1using System; using System.Data; using System.Data.Common; using Project.BaseFramework; using System.Collections.Generic; using System.Configuration; namespace Project.BaseFramework.DataProvider { public class DBHelper { #region Constuctor public DBHelper() { } private static string ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"]; private static IDBClient DBClient = DBClientFactory.GetDBClient(ConfigurationManager.AppSettings["DBClient"]); [ThreadStatic] private static TransConnection TransConnectionObj = null; #endregion #region ExecuteNonQuery public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { int result = 0; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); OpenConn(cmd.Connection); result = cmd.ExecuteNonQuery(); if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteNonQuery #region ExecuteScalar public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { object result = 0; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); OpenConn(cmd.Connection); result = cmd.ExecuteScalar(); if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteScalar #region ExecuteReader public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DbDataReader result = null; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); try { OpenConn(cmd.Connection); if (mustCloseConn) { result = cmd.ExecuteReader(CommandBehavior.CloseConnection); } else { result = cmd.ExecuteReader(); } ClearCmdParameters(cmd); return result; } catch (Exception ex) { if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); throw ; } } #endregion ExecuteReader #region ExecuteDataset public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DataSet result = null; bool mustCloseConn = true; DbCommand cmd = PrepareCmd(cmdType, cmdText, parameterValues, out mustCloseConn); using (DbDataAdapter da = DBClient.GetDbDataAdappter()) { da.SelectCommand = cmd; result = new DataSet(); da.Fill(result); } if (mustCloseConn) CloseConn(cmd.Connection); ClearCmdParameters(cmd); cmd.Dispose(); return result; } #endregion ExecuteDataset #region ExecuteDataTable public static DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params DbParameter[] parameterValues) { DataSet ds = ExecuteDataSet(cmdType,cmdText, parameterValues); if (ds != null && ds.Tables.Count > 0) return ds.Tables[0]; else return null; } #endregion #region ExecutePaging public static DataTable ExecutePagingDataTable(CommandType cmdType, string cmdText,int pageIndex,int pageSize,string orderInfo, params DbParameter[] parameterValues) { cmdText = DBClient.GetPagingSql(cmdText, pageIndex, pageSize, orderInfo); return ExecuteDataTable(CommandType.Text, cmdText, parameterValues); } public static DbDataReader ExecutePagingReader(CommandType cmdType, string cmdText, int pageIndex, int pageSize, string orderInfo, params DbParameter[] parameterValues) { cmdText = DBClient.GetPagingSql(cmdText, pageIndex, pageSize, orderInfo); return ExecuteReader(CommandType.Text, cmdText, parameterValues); } #endregion #region Transaction public static void BeginTransaction() { if (TransConnectionObj == null) { DbConnection conn = DBClient.GetDbConnection(ConnectionString); OpenConn(conn); DbTransaction trans = conn.BeginTransaction(); TransConnectionObj = new TransConnection(); TransConnectionObj.DBTransaction = trans; } else { TransConnectionObj.Deeps = 1; } } public static void CommitTransaction() { if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Commit(); ReleaseTransaction(); } } public static void RollbackTransaction() { if (TransConnectionObj == null) return; if (TransConnectionObj.Deeps > 0) { TransConnectionObj.Deeps -= 1; } else { TransConnectionObj.DBTransaction.Rollback(); ReleaseTransaction(); } } private static void ReleaseTransaction() { if (TransConnectionObj == null) return; DbConnection conn = TransConnectionObj.DBTransaction.Connection; TransConnectionObj.DBTransaction.Dispose(); TransConnectionObj = null; CloseConn(conn); } #endregion #region Connection private static void OpenConn(DbConnection conn) { if (conn == null) conn = DBClient.GetDbConnection(ConnectionString); if (conn.State == ConnectionState.Closed) conn.Open(); } private static void CloseConn(DbConnection conn) { if (conn == null) return; if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); conn = null; } #endregion #region Create DbParameter public static DbParameter CreateInDbParameter(string paraName, DbType type, int size, object value) { return CreateDbParameter(paraName, type, size, value, ParameterDirection.Input); } public static DbParameter CreateInDbParameter(string paraName, DbType type, object value) { return CreateDbParameter(paraName, type, 0, value, ParameterDirection.Input); } public static DbParameter CreateOutDbParameter(string paraName, DbType type, int size) { return CreateDbParameter(paraName, type, size, null, ParameterDirection.Output); } public static DbParameter CreateOutDbParameter(string paraName, DbType type) { return CreateDbParameter(paraName, type, 0, null, ParameterDirection.Output); } public static DbParameter CreateReturnDbParameter(string paraName, DbType type, int size) { return CreateDbParameter(paraName, type, size, null, ParameterDirection.ReturnValue); } public static DbParameter CreateReturnDbParameter(string paraName, DbType type) { return CreateDbParameter(paraName, type, 0, null, ParameterDirection.ReturnValue); } public static DbParameter CreateDbParameter(string paraName, DbType type, int size, object value, ParameterDirection direction) { DbParameter para = DBClient.GetDbParameter(); para.ParameterName = paraName; if (size != 0) { para.Size = size; } para.DbType = type; if (value != null) { para.Value = value; } else { para.Value = DBNull.Value; } para.Direction = direction; return para; } #endregion #region Command and Parameter /// <summary> /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数 /// </summary> /// <param>要处理的DbCommand</param> /// <param>数据库连接</param> /// <param>一个有效的事务或者是null值</param> /// <param>命令类型 (存储过程,命令文本, 其它.)</param> /// <param>存储过程名或都T-SQL命令文本</param> /// <param>和命令相关联的DbParameter参数数组,如果没有参数为'null'</param> /// <param><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param> private static DbCommand PrepareCmd(CommandType cmdType,string cmdText, DbParameter[] cmdParams, out bool mustCloseConn) { DbCommand cmd = DBClient.GetDbCommand(cmdText); DbConnection conn = null; if (TransConnectionObj != null) { conn = TransConnectionObj.DBTransaction.Connection; cmd.Transaction = TransConnectionObj.DBTransaction; mustCloseConn = false; } else { conn = DBClient.GetDbConnection(ConnectionString); mustCloseConn = true; } cmd.Connection = conn; cmd.CommandType = cmdType; AttachParameters(cmd, cmdParams); return cmd; } /// <summary> /// 将DbParameter参数数组(参数值)分配给DbCommand命令. /// 这个方法将给任何一个参数分配DBNull.Value; /// 该操作将阻止默认值的使用. /// </summary> /// <param>命令名</param> /// <param>SqlParameters数组</param> private static void AttachParameters(DbCommand command, DbParameter[] commandParameters) { if (command == null) throw new ArgumentNullException("command"); if (commandParameters != null) { foreach (DbParameter p in commandParameters) { if (p != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } } private static void ClearCmdParameters(DbCommand cmd) { bool canClear = true; if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open) { foreach (DbParameter commandParameter in cmd.Parameters) { if (commandParameter.Direction != ParameterDirection.Input) { canClear = false; break; } } } if (canClear) { cmd.Parameters.Clear(); } } #endregion } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; namespace Project.BaseFramework.DataProvider { internal class TransConnection { public TransConnection() { this.Deeps = 0; } public DbTransaction DBTransaction { get; set; } public int Deeps { get; set; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using System.Data.SqlClient; namespace Project.BaseFramework.DataProvider { public interface IDBClient { DbConnection GetDbConnection(string connectionString); DbCommand GetDbCommand(string cmdText); DbDataAdapter GetDbDataAdappter(); DbParameter GetDbParameter(); string GetPagingSql(string cmdText, int pageIndex, int pageSize, string orderInfo); } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using System.Data.SqlClient; namespace Project.BaseFramework.DataProvider { public class SqlServerClient:IDBClient { public DbConnection GetDbConnection(string connectionString) { return new SqlConnection(connectionString); } public DbCommand GetDbCommand(string cmdText) { return new SqlCommand(cmdText); } public DbDataAdapter GetDbDataAdappter() { return new SqlDataAdapter(); } public DbParameter GetDbParameter() { return new SqlParameter(); } public string GetPagingSql(string cmdText, int pageIndex, int pageSize, string orderInfo) { int startIndex = (pageIndex - 1) * pageSize; int endIndex = startIndex pageSize 1; cmdText = string.Format(@";WITH T1 AS({0}),T2 AS(SELECT *,ROW_NUMBER()OVER ({1}) AS _RowNum FROM T1) SELECT *FROM T2 WHERE _RowNum>{2} AND _RowNum<{3}",cmdText,orderInfo,startIndex,endIndex); return cmdText; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using MySql.Data.MySqlClient; namespace Project.BaseFramework.DataProvider { public class MySqlClient:IDBClient { public DbConnection GetDbConnection(string connectionString) { return new MySqlConnection(connectionString); } public DbCommand GetDbCommand(string cmdText) { return new MySqlCommand(cmdText); } public DbDataAdapter GetDbDataAdappter() { return new MySqlDataAdapter(); } public DbParameter GetDbParameter() { return new MySqlParameter(); } public string GetPagingSql(string cmdText, int pageIndex, int pageSize, string orderInfo) { int startIndex = (pageIndex

  • 1) * pageSize; cmdText = string.Format(@"{0} {1} Limit {2}, {3}", cmdText, orderInfo, startIndex,pageSize); return cmdText; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Reflection; namespace Project.BaseFramework.DataProvider { public class DBClientFactory { private static readonly string path = "Project.BaseFramework"; public static IDBClient GetDBClient(string dbClientClassName) { if(string.IsNullOrEmpty(dbClientClassName)) dbClientClassName="SqlServerClient"; string className = string.Format("{0}.DataProvider.{1}", path, dbClientClassName); return (IDBClient)Assembly.Load(path).CreateInstance(className); } } } View Code

配置文件

<appSettings>
    <add key="DBConnectionString" value="Data Source=.;Initial Catalog=ProjectData;Persist Security Info=True;User ID=sa;Password=kjkj,911;"/>
    <add key="DBClient" value="SqlServerClient"/>
</appSettings>

 

 

2  事务操作

2.1 单个事务操作示例

新注册送38元体验金 2

try
{
    DBHelper.BeginTransaction();
    // add 
    DBHelper.ExecuteNonQuery(CommandType.Text, "INSERT INTO TRole(ID,RoleName) VALUES('R1','MKT')");

    //detele by pk
    DBHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM TRole WHERE ID='R1'");

    Console.WriteLine(string.Format("Success and Commited"));
    DBHelper.CommitTransaction();
}
catch (Exception ex)
{
    Console.WriteLine(string.Format("Exception and rollback"));
    DBHelper.RollbackTransaction();
}

新注册送38元体验金 3

 

用法是:只需要把相关联的代码放在BeginTransaction和CommitTransaction中间,如果发生异常调用RollbackTransaction即可。

实现事务的方法是:

首先,DBHelper维护一个TransConnection类型的字段,并添加ThreadStatic. ThreadStatic可以维护在线程级别上的唯一性。

[ThreadStatic]
private static TransConnection TransConnectionObj = null;

 

其次,TransConnection的作用是保存事务,并记录嵌套事务的嵌套级别。

新注册送38元体验金 4

internal class TransConnection
{
    public TransConnection()
    {
        this.Deeps = 0;
    }

    public DbTransaction DBTransaction { get; set; }

    public int Deeps { get; set; }
}

新注册送38元体验金 5

 

最后,当调用 BeginTransaction时创建TransConnection对象。之后的多个DbCommand命令都从这个事务上拿连接。因为TransConnectionObj添加了ThreadStatic属性,所以它是线程唯一的,不会影响其它线程上的事务;所有方法执行完后,调用CommitTransaction 就提交事务,并关闭连接;如果发生异常,则调用RollbackTransaction,就会回滚所有命令,并关闭连接。

2.2 嵌套事务示例

新注册送38元体验金 6

static void Main(string[] args)
{

    try
    {
        DBHelper.BeginTransaction();

        // add 
        DBHelper.ExecuteNonQuery(CommandType.Text, "INSERT INTO TRole(ID,RoleName) VALUES('R1','MKT')");

        Transaction2();

        //detele by pk
        DBHelper.ExecuteNonQuery(CommandType.Text, "DELETE FROM TRole WHERE ID='R1'");

        Console.WriteLine(string.Format("Success and Commited"));
        DBHelper.CommitTransaction();
    }
    catch (Exception ex)
    {
        Console.WriteLine(string.Format("Exception and rollback"));
        DBHelper.RollbackTransaction();
    }

    Console.ReadLine();
}

private static void Transaction2() 
{
    try
    {
        DBHelper.BeginTransaction();
        //update model
        DBHelper.ExecuteNonQuery(CommandType.Text, "UPDATE TRole SET RoleName='Marketer' WHERE ID='R1'");
        //throw new Exception("");

        DbParameter param = DBHelper.CreateInDbParameter("@ID", DbType.String, "R1");
        DbDataReader reader= DBHelper.ExecuteReader(CommandType.Text, "SELECT * FROM TRole WHERE [email protected]",param);
        while (reader.Read()) 
        {
            Console.WriteLine(reader["RoleName"]);
        }

        reader.Close();

        DBHelper.CommitTransaction();
    }
    catch(Exception ex)
    {
        Console.WriteLine(string.Format("Exception and rollback: {0}", ex.Message));
        DBHelper.RollbackTransaction();
        throw;
    }
}

新注册送38元体验金 7

 

2.2.1

当为嵌套事务时,首次调用BeginTransaction,同样会创建新的TransConnection对象,深度默认为0,并保存在TransConnectionObj字段上;

第n(n>1)次调用时方法时,仅会累加嵌套的深度,不会开起新的事务。

新注册送38元体验金 8

public static void BeginTransaction()
{
    if (TransConnectionObj == null) 
    {
        DbConnection conn = DBClient.GetDbConnection(ConnectionString);
        OpenConn(conn);
        DbTransaction trans = conn.BeginTransaction();
        TransConnectionObj = new TransConnection();
        TransConnectionObj.DBTransaction = trans;
    }
    else 
    {
        TransConnectionObj.Deeps  = 1;
    }
}

新注册送38元体验金 9

 

2.2.2

当CommitTransaction提交事务时,如果深度Deeps>0,那么表示此次提交的事务是内层事务,计数器减1即可;

新注册送38元体验金,当调用CommitTransaction提交事务,如果深度为0时,表示为最外层事务,刚做实际上的提交事务工作;

新注册送38元体验金 10

public static void CommitTransaction()
{
    if (TransConnectionObj == null) return;
    if (TransConnectionObj.Deeps > 0)
    {
        TransConnectionObj.Deeps -= 1;
    }
    else
    {
        TransConnectionObj.DBTransaction.Commit();
        ReleaseTransaction();
    }
}

新注册送38元体验金 11

 

2.2.3

当RollbackTransaction提交事务时,如果深度Deeps>0,那么表示此次提交的事务是内层事务,计数器减1即可;

当调用RollbackTransaction提交事务,如果深度为0时,表示为最外层事务,刚做实际上的回滚操作;

新注册送38元体验金 12

public static void RollbackTransaction()
{
    if (TransConnectionObj == null) return;
    if (TransConnectionObj.Deeps > 0)
    {
        TransConnectionObj.Deeps -= 1;
    }
    else
    {
        TransConnectionObj.DBTransaction.Rollback();
        ReleaseTransaction();
    }
}

新注册送38元体验金 13

 

3  变更数据库

同一个底层库,应用到不同项目时,数据库可能会不同。如果我们比较下不同数据库操作类之间的不同点,我们会发现所有的方法都是一致的,就是某些类型不同,如下表所示:

 

基类

SQL Server

MySql

DbConnection

SqlConnection

MySqlConnection

DbCommand

SqlCommand

MySqlCommand

DbDataAdapter

SqlDataAdapter

MySqlDataAdapte

DbParameter

SqlParameter

MySqlParameter

 

所以,根据子类出现的地方,可以用父类替换的原则,将SqlHeper中关于特定数据库的类,换成基类,并将创建特定数据库对象实例的代码统一到IDBClinet中

主要类有

DBHelper: 使用基类访问数据库,并聚合IDBClient来创建特定数据库对象的实例。

IDBClient: 定义创建特定数据库实例的接口,并转成基类型;

SqlServerClient:定义创建SqlServer对象的实例,并转成基类型;

MySqlCient:定义创建MySql对象的实例,并转成基类型;

DBClientFactory:根据类名动态创建IDBClient的实现类;

 

最后如果想要更换数据库时,只需要修改如下代码,并在配置文件中修改下连接字符串和具体的DBClient的类名:

<appSettings>
    <add key="DBConnectionString" value="Data Source=.;Initial Catalog=ProjectData;Persist Security Info=True;User ID=sa;Password=kjkj,911;"/>
    <add key="DBClient" value="SqlServerClient"/>
</appSettings>

 

4  支持数据分页

为了支持数据分页,在DBHelper添加了两个方法,分别返回DataTable和DbDataReader, DbDataReader用于在ORM中转成实体对象。

新注册送38元体验金 14

#region ExecutePaging
public static DataTable ExecutePagingDataTable(CommandType cmdType, string cmdText,int pageIndex,int pageSize,string orderInfo, params DbParameter[] parameterValues)
{
    cmdText = DBClient.GetPagingSql(cmdText, pageIndex, pageSize, orderInfo);
    return ExecuteDataTable(CommandType.Text, cmdText, parameterValues);
}

public static DbDataReader ExecutePagingReader(CommandType cmdType, string cmdText, int pageIndex, int pageSize, string orderInfo, params DbParameter[] parameterValues)
{
    cmdText = DBClient.GetPagingSql(cmdText, pageIndex, pageSize, orderInfo);
    return ExecuteReader(CommandType.Text, cmdText, parameterValues);
}
#endregion

新注册送38元体验金 15

 在分页时,虽然各种数据库的分页方法不一样,但它们都需要信息是:排序字段,开始索引位置,页大小,结束索引位置(可以通过开始索引位置和页大小计算出来)。至于它们最终分页的SQL不一样,可以放在实现了IDBClient的类中。

比如在SqlServerDBClient是用ROW_NUMBER做的:

新注册送38元体验金 16

public string GetPagingSql(string cmdText, int pageIndex, int pageSize, string orderInfo) 
{
    int startIndex = (pageIndex - 1) * pageSize;
    int endIndex = startIndex   pageSize   1;
    cmdText = string.Format(@";WITH T1 AS({0}),T2 AS(SELECT *,ROW_NUMBER()OVER ({1}) AS _RowNum FROM T1)
                               SELECT *FROM T2
                               WHERE _RowNum>{2} AND _RowNum<{3}",cmdText,orderInfo,startIndex,endIndex);
    return cmdText;
}

新注册送38元体验金 17

在MySqlDBClient中是用Limit 实现的:

新注册送38元体验金 18

public string GetPagingSql(string cmdText, int pageIndex, int pageSize, string orderInfo)
{
    int startIndex = (pageIndex - 1) * pageSize;
    cmdText = string.Format(@"{0} {1} Limit {2}, {3}", cmdText, orderInfo, startIndex,pageSize);
    return cmdText;
}

新注册送38元体验金 19

 

在下一次随笔中,将会用这个数据库操作类,以及上篇文章用T4 Template生成代码 来实现一个简单的ORM框架,支持CRUD。暂时不考虑用反射来实现这个ORM,暂时还hold不住反射的性能问题。

 

(支持事务与数据库变更),dbhelper事务 1 概述 更新内容:添加 " 支持数据分页 " 这个数据库操作类的主要特色有 1 事务操作更加的方...

本文由新注册送38元体验金发布于新注册送38元体验金编程,转载请注明出处:DBHelper (支持事务与数据库变更),dbhelper事务

关键词: