效果很好的asp.net的数据库访问模型（优化，封装一体化）
非常有效的数据库/配置文件访问模型。成功使用在几万流量的网站上。任何建议欢迎大家交流。
在使用SqlCommand对象过程中，我们需要分配Connection对象。 通常，对于大型的Entity业务模型来说分配新的SqlConnection的操作非常频繁。要利用Pool的优化功能,就要想到保持Connection对象。由此想到可以把Connection和Transaction都保存到ConnectionProxy对象中。而此对象继承IDisposable仅仅存在于一个Request过程中。作为一个适用广泛的模型，我们建立ExecutionContext对象来封装对ConnectionProxy操作。
以下是ConnectionProxy代码：
internal class ConnectionProxy : IDisposable 
{ private string _connectionString = null; private SqlConnection _connection; private SqlTransaction _transaction; private int _tranCount = 0;
/**//// <summary>
/// Constructs a new ConnectionProxy instance, setting the connection string /// </summary> /// <param name="ConnectionString">A valid database connection string</param> internal ConnectionProxy(string connectionString) { _connectionString = connectionString; } /**//// <summary> /// Frees any connection related resources /// </summary> public void Dispose() { // ensure that the connection does not have a pending transaction if (_tranCount != 0) { // rollback the transaction stack until the rollback already occurs while (_tranCount > 0) this.RollbackTransaction(); throw new DataAccessException("Dispose was called on a connection with a pending transaction. The transaction will be aborted."); } // close the connection if it is open if ((_connection != null) && (_connection.State == ConnectionState.Open)) { _connection.Close(); } _connection = null; } /**//// <summary> /// Gets the current connection object /// </summary> internal SqlConnection Connection { get { // check that the connection string property has been set if (_connectionString == null) { //throw new DataAccessException("Connection string has not been set."); } // create new connection and open if one does not yet exist if (_connection == null) { _connection = new SqlConnection(_connectionString); _connection.Open(); //while (_connection.State == ConnectionState.Open) ; } return _connection; } } /**//// <summary> /// Gets the current transaction context object /// </summary> internal SqlTransaction Transaction { get { return _transaction; } } /**//// <summary> /// Begins a new transaction /// </summary> internal void BeginTransaction() { // only actually begin a new transaction if a transaction context does not yet exist if (_tranCount == 0) { // create new transaction context at the specified isolation level _transaction = Connection.BeginTransaction(IsolationLevel.Serializable); } _tranCount++; } /**//// <summary> /// Commits a pending transaction /// </summary> internal void CommitTransaction() { // check that a transaction context actually exists if (_tranCount <= 0) throw new DataAccessException("No transaction is pending"); _tranCount--; // check if an actual commit should occur if (_tranCount == 0) { // if trancount is zero, but we don't have a transaction then something is wrong if (_transaction == null) { throw (new DataAccessException("Transaction stack indicated a commit but no transaction exists!")); } // actually commit the transaction _transaction.Commit(); _transaction = null; } } /**//// <summary> /// Rolls back a pending transaction /// </summary> internal void RollbackTransaction() { // check that a transaction context actually exists if (_tranCount <= 0) throw new DataAccessException("No transaction is pending"); _tranCount--; // check if an actual rollback should occur if (_tranCount == 0) { // if trancount is zero, but we don't have a transaction then something is wrong if (_transaction == null) { throw (new DataAccessException("Transaction stack indicated a rollback but no transaction exists!")); } // actually rollback the transaction _transaction.Rollback(); _transaction = null; } } }
之后我们可以建立ExecutionContext.目的是使用这个Proxy.当然也可以保存和使用其他的实例化对象：
public sealed class ExecutionContext { private static string ConnProxy = "ConnProxy"; private static string ConfigProxy = "Config"; private static ConnectionProxy _ConnProxy; private static Config _Config; /**//// <summary> /// This class cannot be instantiated /// </summary> private ExecutionContext() { } /**//// <summary> /// /// </summary> private static ConnectionProxy ConnectionProxy { get { if (HttpContext.Current != null) //web app return (ConnectionProxy)HttpContext.Current.Items[ConnProxy]; else return _ConnProxy; } set { if(HttpContext.Current != null) //web app HttpContext.Current.Items.Add(ConnProxy, value); else _ConnProxy = value; } } private static Config Config { get { if (HttpContext.Current != null) //web app return (Config)HttpContext.Current.Items[ConfigProxy]; else return _Config; } set { if (HttpContext.Current != null) //web app HttpContext.Current.Items.Add(ConfigProxy, value); else _Config = value; } } /**//// <summary> /// Returns the connection object for the current execution context /// </summary> public static SqlConnection Connection { get { AssertInitialisation(); return ConnectionProxy.Connection; } } /**//// <summary> /// Returns the current transaction object for the current execution context /// </summary> public static SqlTransaction Transaction { get { AssertInitialisation(); return ConnectionProxy.Transaction; } } /**//// <summary> /// </summary> public static Config Configuration { get { if (Config == null) throw new Exception("Config.Xml cannot be loaded!"); return Config; } } /**//// <summary> /// Begins a new execution context /// </summary> public static void Begin() { // cleanup from any previous Begin calls End(); // create a configuration object Config = new Config(); // create a new database connection proxy ConnectionProxy = new ConnectionProxy(Config.ConnectionString); } /**//// <summary> /// Ends the current execution context and cleans up any resources used /// </summary> public static void End() { // clean up any objects that have not been cleaned up since the last Begin call on the thread if (ConnectionProxy != null) ConnectionProxy.Dispose(); if (HttpContext.Current != null) //web app { HttpContext.Current.Items.Remove(ConnProxy); HttpContext.Current.Items.Remove(ConfigProxy); } } /**//// <summary> /// Begins a new transaction /// </summary> public static void BeginTransaction() { AssertInitialisation(); ConnectionProxy.BeginTransaction(); } /**//// <summary> /// Commits the current transaction /// </summary> public static void CommitTransaction() { AssertInitialisation(); ConnectionProxy.CommitTransaction(); } /**//// <summary> /// Rolls back the current transaction /// </summary> public static void RollbackTransaction() { AssertInitialisation(); ConnectionProxy.RollbackTransaction(); } /**//// <summary> /// Asserts that the execution context has been correctly initialised /// </summary> private static void AssertInitialisation() { if (ConnectionProxy == null) { throw new ExecutionContextException("Execution Context has not been initialised."); } } }
使用的时候，要在Global.asax的Application_BeginRequest中加入：ExecutionContext.Begin();和Application_EndRequest中加入：ExecutionContext.End();也可以在WinForm程序的Application中加入这2行。
准备工作完成后我们就可以来测试了：
ExecutionContext.BeginTransaction(); try { cmd.Connection = ExecutionContext.Connection; cmd.Transaction = ExecutionContext.Transaction; cmd.ExecuteNonQuery(); ExecutionContext.CommitTransaction(); } catch { ExecutionContext.RollbackTransaction(); throw; }
