DBHelper (支持事務與數據庫變更），dbhelper事務
1 概述
更新內容：添加 "支持數據分頁"
這個數據庫操作類的主要特色有
1> 事務操作更加的方便
2> 變更數據庫更加的容易
3> 支持數據分頁
最新的所有代碼：
![]()
using 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 單個事務操作示例
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();
}
用法是：只需要把相關聯的代碼放在BeginTransaction和CommitTransaction中間，如果發生異常調用RollbackTransaction即可。
實現事務的方法是：
首先，DBHelper維護一個TransConnection類型的字段，並添加ThreadStatic. ThreadStatic可以維護在線程級別上的唯一性。
[ThreadStatic]
private static TransConnection TransConnectionObj = null;
其次，TransConnection的作用是保存事務，並記錄嵌套事務的嵌套級別。
internal class TransConnection
{
public TransConnection()
{
this.Deeps = 0;
}
public DbTransaction DBTransaction { get; set; }
public int Deeps { get; set; }
}
最後，當調用 BeginTransaction時創建TransConnection對象。之後的多個DbCommand命令都從這個事務上拿連接。因為TransConnectionObj添加了ThreadStatic屬性，所以它是線程唯一的，不會影響其它線程上的事務；所有方法執行完後，調用CommitTransaction 就提交事務，並關閉連接；如果發生異常，則調用RollbackTransaction，就會回滾所有命令，並關閉連接。
2.2 嵌套事務示例
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 ID=@ID",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;
}
}
2.2.1
當為嵌套事務時，首次調用BeginTransaction，同樣會創建新的TransConnection對象，深度默認為0，並保存在TransConnectionObj字段上；
第n(n>1)次調用時方法時，僅會累加嵌套的深度，不會開起新的事務。
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;
}
}
2.2.2
當CommitTransaction提交事務時，如果深度Deeps>0，那麼表示此次提交的事務是內層事務，計數器減1即可；
