我去年写的数据访问层（ADO篇）

这是我去年给公司写的一个EF/ADO数据访问层，可以实现对多种数据库操作，代码中包含了一般的查询修改、事务和分布式事务等操作。

这篇文章我们来介绍一下ADO部分的处理。

下一篇EF篇(敬请关注)

ADO部分一般的查询修改都比较简单，这里就不详细介绍了，只列出方法名（每个方法都进行了重载），方法如下：

public DataTable ExecuteDataTable(string commandText, CommandType commandType, string tableName, DbTransaction transaction, params DbParameter[] commandParameters)；

public DbDataReader ExecuteDataReader(DbConnection connection, string commandText, CommandType commandType, params DbParameter[] commandParameters)；

public DataSet ExecuteDataSet(string commandText, CommandType commandType, string datasetName, DbTransaction transaction, params DbParameter[] commandParameters)；

public Int32 ExecuteNonQuery(string commandText, CommandType commandType, DbTransaction transaction, params DbParameter[] commandParameters)；

public T ExecuteScalar<T>(string commandText, CommandType commandType, DbTransaction transaction, params DbParameter[] commandParameters)；

 public Object ExecuteScalar(string commandText, CommandType commandType, DbTransaction transaction, params DbParameter[] commandParameters)；

public IList<Object> ExecuteScalar(string commandText, CommandType commandType, DbTransaction transaction, IList<DbParameter[]> commandParameters)；

我们在这里详细说一下事务处理的核心部分 ：

通过Func和Action将业务处理层的函数传递到数据访问层的事务中进行执行并返回结果，下面来看一下具体的实现代码：

事务处理函数：

public List<TResult> ExecuteADOWithTransaction<T1, TResult>(params ADOTranParams<T1, TResult>[] adoParams)
        {
            DbConnection connection = GetConnection();
            DbTransaction transaction = connection.BeginTransaction();
            List<TResult> listObj = new List<TResult>();
            try
            {
                if (adoParams == null)
                {
                    throw new ArgumentNullException("adoParams is null");
                }
                Array.ForEach<ADOTranParams<T1, TResult>>(adoParams, t => listObj.Add(t.tupleParams.Item2(transaction, t.tupleParams.Item1)));
                transaction.Commit();
            }
            catch (Exception m)
            {
                transaction.Rollback();

                throw m;
            }
            finally
            {
                connection.Close();
            }
            return listObj;
        }

ADOTranParams类的代码：

public class ADOTranParams<TIn1, TOut>
    {
        /// <summary>
        /// 初始化 <see cref="T:System.Object"/> 类的新实例。
        /// </summary>
        /// <remarks></remarks>
        public ADOTranParams()
        {
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="ADOTranParams&lt;TIn1, TOut&gt;"/> class.
        /// </summary>
        /// <param name="transaction">The transaction.</param>
        /// <param name="tIn1">The t in1.</param>
        /// <param name="funcName">Name of the func.</param>
        /// <remarks></remarks>
        public ADOTranParams(DbTransaction transaction, TIn1 tIn1, Func<DbTransaction, TIn1, TOut> funcName)
        {
            this.tIn1 = tIn1;
            this.funcName = funcName;
        }

        /// <summary>
        /// Gets or sets the t in1.
        /// </summary>
        /// <value>The t in1.</value>
        /// <remarks></remarks>
        public TIn1 tIn1
        {
            get;
            set;
        }
        /// <summary>
        /// Gets or sets the name of the func.
        /// </summary>
        /// <value>The name of the func.</value>
        /// <remarks></remarks>
        public Func<DbTransaction, TIn1, TOut> funcName
        {
            get;
            set;
        }

        /// <summary>
        /// Gets the tuple params.
        /// </summary>
        /// <remarks></remarks>
        internal Tuple<TIn1, Func<DbTransaction, TIn1, TOut>> tupleParams
        {
            get
            {
                return Tuple.Create(tIn1, funcName);
            }
        }
    }

执行不带参数的事务函数：

/// <summary>
        /// ADO执行带事物
        /// </summary>
        /// <param name="action">实现函数的事件委托</param>
        /// <returns>返回事务中最后一个方法的返回值</returns>
        /// <example>
        /// eg. public void ActionRun()
        /// {
        /// Action&lt;DbTransaction&gt; a= ExcSqlTransDetail1;
        /// a+= ExcSqlTransDetail2;
        /// ... ...
        /// ExecuteADOWithTransaction(a);
        /// }
        /// protected Object ExcSqlTransDetail1(DbTransaction transaction)
        /// {
        /// ... ...
        /// return null;
        /// }
        ///</example>
        /// <remarks></remarks>
        public Object ExecuteADOWithTransaction(Action<DbTransaction> action)
        {
            DbConnection connection = GetConnection();
            DbTransaction transaction = connection.BeginTransaction();

            Object rValue = null;
            try
            {
                if (action == null)
                {
                    throw new ArgumentNullException("action is null");
                }
                action(transaction);
                transaction.Commit();
            }
            catch (Exception m)
            {
                transaction.Rollback();

                throw m;
            }
            finally
            {
                connection.Close();
            }
            return rValue;
        }

调用方法：

public List<String> ExecTran(Users user)
        {
            var t = new BTBaseDAL.ADOTranParams<Users, string>();
            t.tIn1 = user;
            t.funcName = InsertUserWithTran;

            var t1 = new BTBaseDAL.ADOTranParams<Users, string>();
            t1.tIn1 = user;
            t1.funcName = UpdateUserWithTran;

            return ExecuteADOWithTransaction(t, t1);
        }

        public string InsertUserWithTran(SqlTransaction tran, Users model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" INSERT INTO Users (UserName,Password,State,Creater_CreateDate,Creater_CreateUser,Creater_GUID,Creater_IsDelete) ");
            strSQL.Append(" VALUES (@username,@password,@state,getdate(),@creater_createuser,newId(),0); select @@identity; ");

            SqlParameter[] p ={
                                   new SqlParameter("@username",SqlDbType.VarChar,50),
                                   new SqlParameter("@password",SqlDbType.VarChar,50),
                                   new SqlParameter("@state",SqlDbType.Int),
                                   new SqlParameter("@creater_createuser",SqlDbType.VarChar,50)
                               };
            p[0].Value = model.UserName;
            p[1].Value = model.Password;
            p[2].Value = model.State;
            p[3].Value = model.Creater.CreateUser;

            object rValue=ExecuteScalar(strSQL.ToString(), CommandType.Text, tran, p);
            return "InsertUserWithTran UserId:" + rValue;
        }
        public string UpdateUserWithTran(SqlTransaction tran, Users model)
        {

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" UPDATE Users ");
            strSQL.Append(" SET UserName = @username,Email =@email, ");
            strSQL.Append("     Password = @password,State = @state, ");
            strSQL.Append("     Creater_CreateUser = @creater_createuser");
            strSQL.Append("     WHERE UserId=(SELECT top 1 UserId FROM Users ORDER BY UserId desc) ");

            SqlParameter[] p ={
                                   new SqlParameter("@username",SqlDbType.VarChar,50),
                                   new SqlParameter("@password",SqlDbType.VarChar,50),
                                   new SqlParameter("@state",SqlDbType.Int),
                                   new SqlParameter("@creater_createuser",SqlDbType.VarChar,50),
                                   new SqlParameter("@email",SqlDbType.VarChar,50)
                               };
            p[0].Value = model.UserName;
            p[1].Value = model.Password;
            p[2].Value = model.State;
            p[3].Value = model.Creater.CreateUser;
            p[4].Value = "UpdateUserWithTran@bt.com";

            ExecuteScalar(strSQL.ToString(), CommandType.Text,tran, p);
            return "UpdateUserWithTran";
        }

分布式事务更加简单，直接看代码（要使用分布式事务，请开启MSDTC服务）：

/// <summary>
        /// Scopes the trans action.
        /// </summary>
        /// <param name="action">The action.</param>
        /// <remarks></remarks>
        public void ScopeTransAction(Action action)
        {
            using (var scope = new TransactionScope())
            {
                try
                {
                    action();
                    scope.Complete();
                }
                catch (Exception s)
                {
                    throw s;
                }
            }
        }

文章出处：http://www.cnblogs.com/wmlunge/archive/2012/02/03/2336665.html

转载请留下原文地址。