效果很好的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;
            }

总结：
非常有效的数据库/配置文件访问模型。成功使用在几万流量的网站上。任何建议欢迎大家交流。