开发自己的Data Access Application Block[下篇] -Artech

转载自http://www.cnblogs.com/artech/archive/2007/04/03/698762.html

4. Database

下面来介绍重中之重：Database，绝大部分的Data Access 操作都集中在这个Abstract Database中。这是一个相对庞大的Class，所以不得不采用Partial Class的方式来编写。

Part I：Field 和Property

这些Field 和Property基本上对应我们前面的Configuraiton。此为我们定义了三个Field 和Property：DbDataAdapter，Connection，_transaction。考虑到垃圾回收，使Database实现IDisposable接口。值得说明一点的是，我们通过Database的DatabaseProviderFactory创建了泛型的DbDataAdapter，DbConnection和Transaction。

• ConnectionString：string
• DatabaseProviderFactory：DbProviderFactory
• DefaultCommandType：CommandType
• UseCommandBuilder：bool
• DbParameterNameMapping：IDbParameterNameMapping
• StoredProcedureNameMapping：IStoredProcedureNameMapping
• DbDataAdapter：DbDataAdapter
• Connection: DbConnection
• Transaction: DbTransaction

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Artech.ApplicationBlock.DataMapping;

namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Database defines a series of database-based operations.
    /// </summary>
    public abstract partial class Database : IDisposable
    {
        private bool _isDisposed;

        The five private fields possess the corressponding pubic properties, and they are only allowed to be evaluated by Database Factory.#region The five private fields possess the corressponding pubic properties, and they are only allowed to be evaluated by Database Factory.
        private DbProviderFactory _dbProviderFactory;
        private string _connectionString;
        private CommandType _defaultCommandType;
        private bool _useCommandBuilder;
        private IDbParameterNameMapping _dbParameterNameMapping;
        private IStoredProcedureNameMapping _storedProcedureNameMapping;

        /**//// <summary>
        /// Database connection string which is specified by the database factory.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }

            set
            {
                this._connectionString = value;
            }
        }

        /**//// <summary>
        /// The concrete database specific provider factory.
        /// </summary>
        public DbProviderFactory DatabaseProviderFactory
        {
            get
            {
                return this._dbProviderFactory;
            }
            set
            {
                this._dbProviderFactory = value;
            }
        }

        /**//// <summary>
        /// The defaull command type to perform the database operations which do not specify the commanf type.
        /// </summary>
        public CommandType DefaultCommandType
        {
            get
            {
                return this._defaultCommandType;
            }
            set
            {
                this._defaultCommandType = value;
            }
        }

        /**//// <summary>
        ///Determine whether to use command builder or mapped stored procedures to execute database operations.
        /// </summary>
        public bool UseCommandBuilder
        {
            get
            {
                return this._useCommandBuilder;
            }
            set
            {
                this._useCommandBuilder = value;
            }
        }

        /**//// <summary>
        /// A string which indicates the type to perform mapping between stored procedure parameter and source column.
        /// </summary>
        public IDbParameterNameMapping DbParameterNameMapping
        {
            get
            {
                return this._dbParameterNameMapping;
            }
            set
            {
                this._dbParameterNameMapping = value;
            }
        }

        /**//// <summary>
        /// A string which indicates the type to perform mapping between table name and the related stored procedure names.
        /// </summary>
        public IStoredProcedureNameMapping StoredProcedureNameMapping
        {
            get
            {
                return this._storedProcedureNameMapping;
            }
            set
            {
                this._storedProcedureNameMapping = value;
            }
        }
        #endregion

        Connection & Database DataAdapter#region Connection & Database DataAdapter
        private DbDataAdapter _dbDataAdapter;
        private DbConnection _connection;

        /**//// <summary>
        /// A generic database data adapter which is responsible for save the changed data into database.
        /// </summary>
        private DbDataAdapter DatabaseAdapter
        {
            get
            {
                if (this._dbDataAdapter == null)
                {
                    this._dbDataAdapter = this._dbProviderFactory.CreateDataAdapter();
                    this._dbDataAdapter.AcceptChangesDuringUpdate = false;
                    this._dbDataAdapter.MissingSchemaAction = MissingSchemaAction.Add;
                }

                return this._dbDataAdapter;
            }
        }

        /**//// <summary>
        /// The database connection.
        /// </summary>
        private DbConnection Connection
        {
            get
            {
                if (this._connection == null)
                {
                    this._connection = this._dbProviderFactory.CreateConnection();
                    this._connection.ConnectionString = this._connectionString;
                }

                return this._connection;
            }
        }
        #endregion

        Constructor#region Constructor
        public Database()
        {

        }

        #endregion      

        IDisposable Members#region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this._isDisposed)
            {
                if (disposing)
                {
                    if (this._connection != null)
                    {
                        if (this._connection.State == ConnectionState.Open)
                        {
                            this._connection.Close();
                        }
                    }

                    if (this._transaction != null)
                    {
                        this._transaction.Dispose();
                    }
                }
            }
            this._isDisposed = true;
        }
        #endregion      
   }
}

 

Part II: Fill Dataset

很简单，基本上ADO.NET 的基本操作，没什么可值得说的。

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Artech.ApplicationBlock.DataMapping;

namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Database defines a series of database-based operations.
    /// </summary>
    public abstract partial class Database : IDisposable,
    {
       
        public void FillDataSet(DataSet dataInfo, string commandText, IDictionary<string, object> parameters)
        {
            if (dataInfo.Tables.Count == 0)
            {
                DataTable table = dataInfo.Tables.Add("Table1");
            }

            this.FillDataSet(dataInfo, dataInfo.Tables[0].TableName, this._defaultCommandType, commandText, parameters);
        }

        public void FillDataSet(DataSet dataInfo, string tableName, string commandText, IDictionary<string, object> parameters)
        {
            this.FillDataSet(dataInfo, tableName, this._defaultCommandType, commandText, parameters);
        }

        public void FillDataSet(DataSet dataInfo, string tableName, CommandType commandType, string commandText, IDictionary<string, object> parameters)
        {
            DbCommand selectComand = this.Connection.CreateCommand();
            selectComand.CommandText = commandText;
            selectComand.CommandType = commandType;
            this.DatabaseAdapter.SelectCommand = selectComand;
            this.DatabaseAdapter.Fill(dataInfo, tableName);
        } 
    }
}

 

Part III Execute 系列

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Artech.ApplicationBlock.DataMapping;

namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Database defines a series of database-based operations.
    /// </summary>
    public abstract partial class Database : IDisposable
    {

        ExecuteNonQuery#region ExecuteNonQuery
        public int ExecuteNonQuery(CommandType commandType, string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            DbCommand command = this.Connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            DbParameter parameter;
            foreach (string parameterName in inputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = inputParameters[parameterName];
                command.Parameters.Add(parameter);
            }

            foreach (string parameterName in outputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(parameter);
            }

            this.Connection.Open();
            int rowCount = command.ExecuteNonQuery();
            this.Connection.Close();

            foreach (string parameterName in outputParameters.Keys)
            {
                outputParameters[parameterName] = command.Parameters[parameterName];
            }

            return rowCount;
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteNonQuery(commandType, commandText, inputParameters, new Dictionary<string, object>());
        }

        public int ExecuteNonQuery(string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            return this.ExecuteNonQuery(this._defaultCommandType, commandText, inputParameters, outputParameters);
        }

        public int ExecuteNonQuery(string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteNonQuery(this._defaultCommandType, commandText, inputParameters, new Dictionary<string, object>());
        }
        #endregion

        ExecuteReader#region ExecuteReader
        public DbDataReader ExecuteReader(CommandType commandType, string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            DbCommand command = this.Connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            DbParameter parameter;
            foreach (string parameterName in inputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = inputParameters[parameterName];
                command.Parameters.Add(parameter);
            }

            foreach (string parameterName in outputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(parameter);
            }

            this.Connection.Open();
            DbDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            foreach (string parameterName in outputParameters.Keys)
            {
                outputParameters[parameterName] = command.Parameters[parameterName];
            }

            return dataReader;
        }

        public DbDataReader ExecuteReader(CommandType commandType, string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteReader(commandType, commandText, inputParameters, new Dictionary<string, object>());
        }

        public DbDataReader ExecuteReader(string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            return this.ExecuteReader(this._defaultCommandType, commandText, inputParameters, outputParameters);
        }

        public DbDataReader ExecuteReader(string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteReader(this._defaultCommandType, commandText, inputParameters, new Dictionary<string, object>());
        }
        #endregion

        ExecuteScalar#region ExecuteScalar
        public object ExecuteScalar(CommandType commandType, string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            DbCommand command = this.Connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;

            DbParameter parameter;
            foreach (string parameterName in inputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = inputParameters[parameterName];
                command.Parameters.Add(parameter);
            }

            foreach (string parameterName in outputParameters.Keys)
            {
                parameter = this._dbProviderFactory.CreateParameter();
                parameter.ParameterName = parameterName;
                parameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(parameter);
            }

            this.Connection.Open();
            object returnValue = command.ExecuteScalar();
            this.Connection.Close();

            foreach (string parameterName in outputParameters.Keys)
            {
                outputParameters[parameterName] = command.Parameters[parameterName];
            }

            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteScalar(commandType, commandText, inputParameters, new Dictionary<string, object>());
        }

        public object ExecuteScalar(string commandText, Dictionary<string, object> inputParameters, Dictionary<string, object> outputParameters)
        {
            return this.ExecuteScalar(this._defaultCommandType, commandText, inputParameters, outputParameters);
        }

        public object ExecuteScalar(string commandText, Dictionary<string, object> inputParameters)
        {
            return this.ExecuteScalar(this._defaultCommandType, commandText, inputParameters, new Dictionary<string, object>());
        }
        #endregion
    }
}

 

Part IV: Transaction

定义了3个基于Transaction的方法BeginTransaction，Commit和RollBack，使Developer显示地开始和结束一个Transaction，这样他可以很直观地把所需的操作纳入某个Transaction中。

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Artech.ApplicationBlock.DataMapping;
using Artech.ApplicationBlock.Common;
using Artech.ApplicationBlock.Common.Exceptions;

namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Database defines a series of database-based operations.
    /// </summary>
    public abstract partial class Database : IDisposable
    {
        DbTransaction _transaction;

Transaction based operation#region Transaction based operation
        public void BeginTransaction()
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();
            }
            this._transaction = this.Connection.BeginTransaction();
        }

       public void Commit()
        {
            if (this._transaction == null)
            {
                throw new DataAccessException(CommonConstant.DataAccess.Messages.NotBegunTransaction);
            }

            this._transaction.Commit();
        }
        public void RollBack()
        {
            if (this._transaction == null)
            {
                throw new DataAccessException(CommonConstant.DataAccess.Messages.NotBegunTransaction);
            }

            this._transaction.Rollback();
        }

        public DbTransaction Transaction
        {
            get
            {
                return this._transaction;
            }
            set
            {
                this._transaction = value;
            }
        }
        #endregion
}

 

Part V: Update

这一部分花了很多时间和精力，现看Code：

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Artech.ApplicationBlock.DataMapping;
using Artech.ApplicationBlock.Common;
using Artech.ApplicationBlock.Common.Exceptions;

namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Database defines a series of database-based operations.
    /// </summary>
    public abstract partial class Database : IDisposable
    {        
        Save the changed data hold in DataSet into database.#region Save the changed data hold in DataSet into database.

        /**//// <summary>
        /// Discovery all of the parameters of the command.
        /// </summary>
        /// <param name="command"></param>
        public abstract void DiscoverParameters(DbCommand command);

        /**//// <summary>
        /// Get the approprate source version based on the parameter name. With the help of the source version, the database data adapter can choose the  data of 
        /// appropriate version to save into underlying database.
        /// </summary>
        /// <param name="parameterName"></param>
        /// <returns>The source version corresponding to the parameter.</returns>
        public abstract DataRowVersion GetSourceVersion(string parameterName);

        /**//// <summary>
        /// Save all of the changed data hold in a dataset into database.
        /// </summary>
        /// <param name="dataInfo">The dataset which contains all the changed data.</param>
        public void UpdateData(DataSet dataInfo)
        {

            //Deleted data.
            if (dataInfo.GetChanges(DataRowState.Deleted) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Deleted);
                }
            }


            //Modified Data.
            if (dataInfo.GetChanges(DataRowState.Modified) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Modified);
                }
            }


            //Added Data.
            if (dataInfo.GetChanges(DataRowState.Added) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Added);
                }
            }
        }

        /**//// <summary>
        /// Save all of the changed data hold in a data table into database.
        /// </summary>
        /// <param name="dataInfo">The data table which contains all the changed data.</param>
        public void UpdateData(DataTable table)
        {
            if (this._useCommandBuilder)
            {
                this.UpdateDataUsingCommandBuilder(table);
            }
            else
            {
                this.UpdateDataUsingMappedStoredProcedure(table);
            }
        }

        /**//// <summary>
        /// Save the changed data stored in a data table by specifying the command text and parameter to a series of commands - InsertCommand, UpdateCommand and DeleteCommand.
        /// </summary>
        /// <param name="table">The data table which stores the changed data.</param>
        /// <param name="insertCommandText">The command text for insert command.</param>
        /// <param name="updateCommandText">The command text for update command.</param>
        /// <param name="deleteCommandText">The command text for delete command</param>
        /// <param name="insertParameters">The parameter list to the insert command.</param>
        /// <param name="updateParameters">The parameter list to the update command.</param>
        /// <param name="deleteParameters">The parameter list to the delete command.</param>
        public void UpdateData(DataTable table, string insertCommandText, string updateCommandText, string deleteCommandText, Dictionary<string, object> insertParameters, Dictionary<string, object> updateParameters, Dictionary<string, object> deleteParameters)
        {
            this.UpdateData(table, this._defaultCommandType, insertCommandText, updateCommandText, deleteCommandText, insertParameters, updateParameters, deleteParameters);
        }
       
        /**//// <summary>
        /// Save the changed data stored in a data table by specifying the command text and parameter to a series of commands - InsertCommand, UpdateCommand and DeleteCommand.
        /// </summary>
        /// <param name="table">The data table which stores the changed data.</param>
        /// <param name="commandType">The command type of all of the commands</param>
        /// <param name="insertCommandText">The command text for insert command.</param>
        /// <param name="updateCommandText">The command text for update command.</param>
        /// <param name="deleteCommandText">The command text for delete command</param>
        /// <param name="insertParameters">The parameter list to the insert command.</param>
        /// <param name="updateParameters">The parameter list to the update command.</param>
        /// <param name="deleteParameters">The parameter list to the delete command.</param>
        public void UpdateData(DataTable table, CommandType commandType, string insertCommandText, string updateCommandText, string deleteCommandText, Dictionary<string, object> insertParameters, Dictionary<string, object> updateParameters, Dictionary<string, object> deleteParameters)
        {
            DbCommand insertCommand = this.Connection.CreateCommand();
            DbCommand updateCommand = this.Connection.CreateCommand();
            DbCommand deleteCommand = this.Connection.CreateCommand();

            insertCommand.CommandType = commandType;
            updateCommand.CommandType = commandType;
            deleteCommand.CommandType = commandType;

            insertCommand.CommandText = insertCommandText;
            updateCommand.CommandText = updateCommandText;
            deleteCommand.CommandText = deleteCommandText;

            DbParameter parameter = this._dbProviderFactory.CreateParameter();
            foreach (string parameterName in insertParameters.Keys)
            {
                parameter.ParameterName = parameterName;
                parameter.Value = insertParameters[parameterName];
                insertCommand.Parameters.Add(parameter);
            }

            foreach (string parameterName in updateParameters.Keys)
            {
                parameter.ParameterName = parameterName;
                parameter.Value = updateParameters[parameterName];
                updateCommand.Parameters.Add(parameter);
            }

            foreach (string parameterName in deleteParameters.Keys)
            {
                parameter.ParameterName = parameterName;
                parameter.Value = deleteParameters[parameterName];
                deleteCommand.Parameters.Add(parameter);
            }

            this.UpdateData(table, insertCommand, updateCommand, deleteCommand);
        }

        /**//// <summary>
        /// Save the changed data which is stored in table into database by specifying the thress commands: Inser commmand, update command and delete command.
        /// </summary>
        /// <param name="table">The table which stores the changed data.</param>
        /// <param name="insertCommand">Insert command used for performing insertion operation.</param>
        /// <param name="updateCommand">Update commmnad usede for performing modification operation.</param>
        /// <param name="deleteCommand">Delete command used for performing deletion operation.</param>
        public void UpdateData(DataTable table, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
        {
            this.DatabaseAdapter.InsertCommand = insertCommand;
            this.DatabaseAdapter.UpdateCommand = updateCommand;
            this.DatabaseAdapter.DeleteCommand = deleteCommand;

            this.DatabaseAdapter.Update(table);
        }

        /**//// <summary>
        /// Save the changed which is stored in a table or data row array into database using the command builder.
        /// </summary>
        /// <typeparam name="T">The type of changed data container: data table or data row array</typeparam>
        /// <param name="instance">The data container holding the changed data.</param>
        private void UpdateDataUsingCommandBuilder<T>(T instance)
        {
            DataTable table = null;
            DataRow[] dataRows = null;

            if (instance is DataTable)
            {
                table = instance as DataTable;
            }

            if (instance is DataRow[])
            {
                dataRows = instance as DataRow[];
            }

            DbCommandBuilder commandBuilder = this._dbProviderFactory.CreateCommandBuilder();
            commandBuilder.DataAdapter = this.DatabaseAdapter;

            //Specify the select command of the data adapter.
            DbCommand selectComand = this._dbProviderFactory.CreateCommand();
            selectComand.CommandText = string.Format("SELECT * FROM dbo.{0}", table.TableName);
            selectComand.Connection = this.Connection;
            this.DatabaseAdapter.SelectCommand = selectComand;

            //Build the three commands of data adapter.
            DbCommand insertCommand = commandBuilder.GetInsertCommand();
            DbCommand updateCommand = commandBuilder.GetUpdateCommand();
            DbCommand deleteCommand = commandBuilder.GetDeleteCommand();

            this.DatabaseAdapter.InsertCommand = insertCommand;
            this.DatabaseAdapter.UpdateCommand = updateCommand;
            this.DatabaseAdapter.DeleteCommand = deleteCommand;

            //Specify the database connection for the thress commands.
            insertCommand.Connection = this.Connection;
            updateCommand.Connection = this.Connection;
            deleteCommand.Connection = this.Connection;

            if (this._transaction != null)
            {
                insertCommand.Transaction = this._transaction;
                updateCommand.Transaction = this._transaction;
                deleteCommand.Transaction = this._transaction;
            }


            if (instance is DataTable)
            {
                this.DatabaseAdapter.Update(table);
            }

            if (instance is DataRow[])
            {
                this.DatabaseAdapter.Update(dataRows);
            }

        }

        /**//// <summary>
        /// Save the changed which is stored in a table or data row array into database using the table-mapped stored procedure.
        /// </summary>
        /// <typeparam name="T">The type of changed data container: data table or data row array</typeparam>
        /// <param name="instance">The data container holding the changed data.</param>
        private void UpdateDataUsingMappedStoredProcedure<T>(T instance)
        {
            DataTable table = null;
            DataRow[] dataRows = null;

            if (instance is DataTable)
            {
                table = instance as DataTable;
            }

            if (instance is DataRow[])
            {
                dataRows = instance as DataRow[];
                if (dataRows.Length == 0)
                {
                    return;
                }
            }

            //Create the three commands of the database data adapter.
            DbCommand insertCommand = this.Connection.CreateCommand();
            DbCommand updateCommand = this.Connection.CreateCommand();
            DbCommand deleteCommand = this.Connection.CreateCommand();

            //Specify the command type.
            insertCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.CommandType = CommandType.StoredProcedure;

            insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

            string tableName = string.Empty;
            if (table != null)
            {
                tableName = table.TableName;
            }
            else
            {
                tableName = dataRows[0].Table.TableName;
            }

            //Specify the command text.
            insertCommand.CommandText = this._storedProcedureNameMapping.GetInsertStoredProcedureName(tableName);
            updateCommand.CommandText = this._storedProcedureNameMapping.GetModifyStoredProcedureName(tableName);
            deleteCommand.CommandText = this._storedProcedureNameMapping.GetDeleteStoredProcedureName(tableName);

            if (this._transaction != null)
            {
                insertCommand.Transaction = this._transaction;
                updateCommand.Transaction = this._transaction;
                deleteCommand.Transaction = this._transaction;
            }

            //Discover the parameters of the three commands.
            this.DiscoverParameters(insertCommand);
            this.DiscoverParameters(updateCommand);
            this.DiscoverParameters(deleteCommand);

            //Specify the Source column and source version.
            foreach (DbParameter parameter in insertCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            foreach (DbParameter parameter in updateCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            foreach (DbParameter parameter in deleteCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            //Evaluate the commands for the database adapter.
            this.DatabaseAdapter.InsertCommand = insertCommand;
            this.DatabaseAdapter.UpdateCommand = updateCommand;
            this.DatabaseAdapter.DeleteCommand = deleteCommand;

          
            if (instance is DataTable)
            {
                this.DatabaseAdapter.Update(table);
            }

            if (instance is DataRow[])
            {
                this.DatabaseAdapter.Update(dataRows);
            }
        }

        /**//// <summary>
        /// Save the changed data which stored in a table which connect to another data table.
        /// </summary>
        /// <param name="table">The table containing the chaned data.</param>
        /// <param name="rowState">The data row state which detemines which kinds of data will be saved.</param>
        private void UpdateDependentTable(DataTable table, DataRowState rowState)
        {
            DataViewRowState dataViewRowState = DataViewRowState.OriginalRows;

            switch (rowState)
            {
                case DataRowState.Added:
                    {
                        dataViewRowState = DataViewRowState.Added;
                        break;
                    }
                case DataRowState.Deleted:
                    {
                        dataViewRowState = DataViewRowState.Deleted;
                        break;
                    }
                case DataRowState.Modified:
                    {
                        dataViewRowState = DataViewRowState.ModifiedCurrent;
                        break;
                    }
            }

            if (table.GetChanges(rowState) == null)
            {
                return;
            }

            DataTable parentTable = null;
            DataTable childTable = null;

            switch (rowState)
            {
                // DataRowState.Added|DataRowState.Modified: Parent table =>child table.
                case DataRowState.Added:
                case DataRowState.Modified:
                    {
                        foreach (DataRelation relation in table.ParentRelations)
                        {
                            parentTable = relation.ParentTable;
                            if (parentTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(parentTable, rowState);
                        }

                        DataRow[] updatedRows = table.Select(string.Empty, string.Empty, dataViewRowState);
                        this.UpdateIndependentTable(updatedRows);

                        foreach (DataRow row in updatedRows)
                        {
                            row.AcceptChanges();
                        }


                        foreach (DataRelation relation in table.ChildRelations)
                        {
                            childTable = relation.ChildTable;
                            if (childTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(childTable, rowState);
                        }
                        break;
                    }
                // DataRowState.Deleted: Child table => Parent table.
                case DataRowState.Deleted:
                    {
                        //Child Tables
                        foreach (DataRelation relation in table.ChildRelations)
                        {
                            childTable = relation.ChildTable;
                            if (childTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(childTable, rowState);
                        }

                        //Itself
                        DataRow[] updatedRows = table.Select(string.Empty, string.Empty, dataViewRowState);
                        this.UpdateIndependentTable(updatedRows);

                        foreach (DataRow row in updatedRows)
                        {
                            row.AcceptChanges();
                        }

                        //Parent Table.
                        foreach (DataRelation relation in table.ParentRelations)
                        {
                            parentTable = relation.ParentTable;
                            if (parentTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(parentTable, rowState);
                        }
                    }
                    break;
            }
        }

        /**//// <summary>
        /// Save the changed data stored in an independent table.
        /// </summary>
        /// <param name="dataRows"></param>
        private void UpdateIndependentTable(DataRow[] dataRows)
        {
            if (this._useCommandBuilder)
            {
                this.UpdateDataUsingCommandBuilder<DataRow[]>(dataRows);
            }
            else
            {

                this.UpdateDataUsingMappedStoredProcedure<DataRow[]>(dataRows);
            }
        }

        #endregion
        
    }
}

 

我们来分析一下 public void UpdateData(DataSet dataInfo)，这个方法对Data Access操作进行了高度的封装，Developer所做就是把更新过的Dataset传入UpdateData方法，其它的所有操作交给AppBlock来做。要实现这样的功能其实是很麻烦的，要考虑的因素很多：

• 需要把分析Dataset中DataTable之间的关系，确定先对那个Table 进行操作。
• Dataset中的数据包含不同DataRowState的记录：Added，Modified，Deleted；需要和Dataset中DataTable之间的关系结合确定不同表，不同DataRowState数据的操作顺序。
• 使用Stored Procedure进行更新，需要考虑以下的Mapping：DataTable的Name和Stored Procedure Name；不同DataRowVersion的DataRow中的Field和Stored Procedure中的参数名。

我的解决方案是：

为了避免数据库中数据的冲突，我们数据更新的顺序是：Deleted Data->Modified Data->Added Data;考虑到表之间的主子关系，对于Added Data和Modified Data，我们应该先修改Parent Table，后修改Child，而对Deleted Data顺序却恰好相反。由于我们 不应该对DataSet中的Table的数量和关系做出任何假设，我们需要以一种递归的过程完成数据的更新。本着这样一个原则，我们来看我们的实现：

 

public void UpdateData(DataSet dataInfo)
        {
            //Deleted data.
            if (dataInfo.GetChanges(DataRowState.Deleted) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Deleted);
                }
            }


            //Modified Data.
            if (dataInfo.GetChanges(DataRowState.Modified) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Modified);
                }
            }


            //Added Data.
            if (dataInfo.GetChanges(DataRowState.Added) != null)
            {
                foreach (DataTable table in dataInfo.Tables)
                {
                    this.UpdateDependentTable(table, DataRowState.Added);
                }
            }
        }

 

整个过程分3步骤，更新Deleted data.-> 更新Modified data.-> 更新Added data.真正的数据更新集中在UpdateDependentTable这样一个方法中。

 

private void UpdateDependentTable(DataTable table, DataRowState rowState)
        {
            DataViewRowState dataViewRowState = DataViewRowState.OriginalRows;

            switch (rowState)
            {
                case DataRowState.Added:
                    {
                        dataViewRowState = DataViewRowState.Added;
                        break;
                    }
                case DataRowState.Deleted:
                    {
                        dataViewRowState = DataViewRowState.Deleted;
                        break;
                    }
                case DataRowState.Modified:
                    {
                        dataViewRowState = DataViewRowState.ModifiedCurrent;
                        break;
                    }
            }

            if (table.GetChanges(rowState) == null)
            {
                return;
            }

            DataTable parentTable = null;
            DataTable childTable = null;

            switch (rowState)
            {
                // DataRowState.Added|DataRowState.Modified: Parent table =>child table.
                case DataRowState.Added:
                case DataRowState.Modified:
                    {
                        foreach (DataRelation relation in table.ParentRelations)
                        {
                            parentTable = relation.ParentTable;
                            if (parentTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(parentTable, rowState);
                        }

                        DataRow[] updatedRows = table.Select(string.Empty, string.Empty, dataViewRowState);
                        this.UpdateIndependentTable(updatedRows);

                        foreach (DataRow row in updatedRows)
                        {
                            row.AcceptChanges();
                        }


                        foreach (DataRelation relation in table.ChildRelations)
                        {
                            childTable = relation.ChildTable;
                            if (childTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(childTable, rowState);
                        }
                        break;
                    }
                // DataRowState.Deleted: Child table => Parent table.
                case DataRowState.Deleted:
                    {
                        //Child Tables
                        foreach (DataRelation relation in table.ChildRelations)
                        {
                            childTable = relation.ChildTable;
                            if (childTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(childTable, rowState);
                        }

                        //Itself
                        DataRow[] updatedRows = table.Select(string.Empty, string.Empty, dataViewRowState);
                        this.UpdateIndependentTable(updatedRows);

                        foreach (DataRow row in updatedRows)
                        {
                            row.AcceptChanges();
                        }

                        //Parent Table.
                        foreach (DataRelation relation in table.ParentRelations)
                        {
                            parentTable = relation.ParentTable;
                            if (parentTable.GetChanges(rowState) == null)
                            {
                                continue;
                            }

                            this.UpdateDependentTable(parentTable, rowState);
                        }
                    }
                    break;
            }
        }

 

通过传入的DataRowState创建一个DataViewRowState变量。对于DataRowState.Added和DataRowState.Modified，通过ParentRelations属性递归地获得并修改Parent Table的数据，然后跟新本Table，最后通过ChildRelations属性递归地获得并更新Child Table，如果发现对应的表已经更新，忽略并进入下一步。对于DataRowState.Deleted，则是一种反向的方法进行操作。而最终的Data Access 又落在了UpdateIndependentTable(updatedRows)方法上面。

 

private void UpdateIndependentTable(DataRow[] dataRows)
        {
            if (this._useCommandBuilder)
            {
                this.UpdateDataUsingCommandBuilder<DataRow[]>(dataRows);
            }
            else
            {

                this.UpdateDataUsingMappedStoredProcedure<DataRow[]>(dataRows);
            }
        }

 

通过._useCommandBuilder 属性判断是通过使用CommandBuilder生成Command还是通过Mapped Stored Procedure来生成Command更新数据。

 

private void UpdateDataUsingCommandBuilder<T>(T instance)
        {
            DataTable table = null;
            DataRow[] dataRows = null;

            if (instance is DataTable)
            {
                table = instance as DataTable;
            }

            if (instance is DataRow[])
            {
                dataRows = instance as DataRow[];
            }

            DbCommandBuilder commandBuilder = this._dbProviderFactory.CreateCommandBuilder();
            commandBuilder.DataAdapter = this.DatabaseAdapter;

            //Specify the select command of the data adapter.
            DbCommand selectComand = this._dbProviderFactory.CreateCommand();
            selectComand.CommandText = string.Format("SELECT * FROM dbo.{0}", table.TableName);
            selectComand.Connection = this.Connection;
            this.DatabaseAdapter.SelectCommand = selectComand;

            //Build the three commands of data adapter.
            DbCommand insertCommand = commandBuilder.GetInsertCommand();
            DbCommand updateCommand = commandBuilder.GetUpdateCommand();
            DbCommand deleteCommand = commandBuilder.GetDeleteCommand();

            this.DatabaseAdapter.InsertCommand = insertCommand;
            this.DatabaseAdapter.UpdateCommand = updateCommand;
            this.DatabaseAdapter.DeleteCommand = deleteCommand;

            //Specify the database connection for the thress commands.
            insertCommand.Connection = this.Connection;
            updateCommand.Connection = this.Connection;
            deleteCommand.Connection = this.Connection;

            if (this._transaction != null)
            {
                insertCommand.Transaction = this._transaction;
                updateCommand.Transaction = this._transaction;
                deleteCommand.Transaction = this._transaction;
            }


            if (instance is DataTable)
            {
                this.DatabaseAdapter.Update(table);
            }

            if (instance is DataRow[])
            {
                this.DatabaseAdapter.Update(dataRows);
            }
        }

 

上面的是一个泛型的方法，我们可以对一个单独的Table的一个DataRow数组进行的更新，代码相对还算清晰，相信对大部分人没有难度：首先照例使用DatabaseProviderFactory创建泛型的DbCommandBuilder，指定SelectCommand的CommandText（Select * From TableName），通过DbCommandBuilder创建3个Command传递给DatabaseAdapter的3 个Command属性。如果用户开始了一个Transaction，则把创建的Transaction映射到3个Command上。最后调用DatabaseAdapter.Update方法实现 数据的跟新。通过DbCommandBuilder是一种很简单的方法，但是存在很大的性能问题。造成性能降低的主要原因有两个：他是使用纯文本的SQL；为了避免数据库的并发操作引起的数据不一致，它在作数据更新的时候，会逐个字段地把Dataset原始数据和数据库作比较。所以我们一边采用stored procedure来更新数据库。

 

private void UpdateDataUsingMappedStoredProcedure<T>(T instance)
        {
            DataTable table = null;
            DataRow[] dataRows = null;

            if (instance is DataTable)
            {
                table = instance as DataTable;
            }

            if (instance is DataRow[])
            {
                dataRows = instance as DataRow[];
                if (dataRows.Length == 0)
                {
                    return;
                }
            }

            //Create the three commands of the database data adapter.
            DbCommand insertCommand = this.Connection.CreateCommand();
            DbCommand updateCommand = this.Connection.CreateCommand();
            DbCommand deleteCommand = this.Connection.CreateCommand();

            //Specify the command type.
            insertCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.CommandType = CommandType.StoredProcedure;

            insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

            string tableName = string.Empty;
            if (table != null)
            {
                tableName = table.TableName;
            }
            else
            {
                tableName = dataRows[0].Table.TableName;
            }

            //Specify the command text.
            insertCommand.CommandText = this._storedProcedureNameMapping.GetInsertStoredProcedureName(tableName);
            updateCommand.CommandText = this._storedProcedureNameMapping.GetModifyStoredProcedureName(tableName);
            deleteCommand.CommandText = this._storedProcedureNameMapping.GetDeleteStoredProcedureName(tableName);

            if (this._transaction != null)
            {
                insertCommand.Transaction = this._transaction;
                updateCommand.Transaction = this._transaction;
                deleteCommand.Transaction = this._transaction;
            }

            //Discover the parameters of the three commands.
            this.DiscoverParameters(insertCommand);
            this.DiscoverParameters(updateCommand);
            this.DiscoverParameters(deleteCommand);

            //Specify the Source column and source version.
            foreach (DbParameter parameter in insertCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            foreach (DbParameter parameter in updateCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            foreach (DbParameter parameter in deleteCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.ReturnValue)
                {
                    continue;
                }
                parameter.SourceColumn = this._dbParameterNameMapping.GetSourceCoulmnName(parameter.ParameterName);
                parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
            }

            //Evaluate the commands for the database adapter.
            this.DatabaseAdapter.InsertCommand = insertCommand;
            this.DatabaseAdapter.UpdateCommand = updateCommand;
            this.DatabaseAdapter.DeleteCommand = deleteCommand;

          
            if (instance is DataTable)
            {
                this.DatabaseAdapter.Update(table);
            }

            if (instance is DataRow[])
            {
                this.DatabaseAdapter.Update(dataRows);
            }
        }

 

通过stored procedure的方式和上面通过Command Builder的步骤差不多。首先通过Conection创建3个Command，并指定Command type为CommandType.StoredProcedure。通过storedProcedureNameMapping根据Table name获得对应的stored procedure的名称，并赋值给3个Command的CommandText属性。如果开始了Transaction，与之关联。通过DiscoverParameters方法（这个一个Abstract方法，需要在具体的Database 类脂Override）给Command发现参数。接着我们为3个Command的parameter指定SourceColumn和SourceVersion，其中SourceColumn通过我们配置的dbParameterNameMapping来获得。SourceVersion通过方法GetSourceVersion（这个一个Abstract方法，需要在具体的Database 类脂Override；这个方法根据参数名称来无额定对应的Source Version：Original or Current）来获取。最后调用DatabaseAdapter.Update方法实现 数据的跟新。

5. SQLDatabase & OracleDatabase

由于ADO.NET 2.0提供的很多基于泛型编程的功能，使得我们把觉得大部分Data Access操作放在了上面这个Abstract Database中，所以SQLDatabase & OracleDatabase上的逻辑很少。我们只看SQLDatabase。上面我们提到Abstract Database提供两个Abstract方法需要在具体的Database中实现的：DiscoverParameters和GetSourceVersion。实现上SQLDatabase之包含这两个方法：

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace Artech.ApplicationBlock.DataAccess
{
    /**//// <summary>
    /// Microsoft SQL Server Database
    /// </summary>
    public class SqlDatabase:Database
    {
        public SqlDatabase()
        { 
            
        }

        /**//// <summary>
        /// Discover all of the parameters for the command realted stored procedure.
        /// </summary>
        /// <param name="command">Command</param>
        public override void DiscoverParameters(DbCommand command)
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            SqlCommandBuilder.DeriveParameters(command as SqlCommand);
        }

        /**//// <summary>
        /// Get the DataRowVersion for the source column corresponding to the parameter based on the parameter name.
        /// </summary>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public override DataRowVersion GetSourceVersion(string parameterName)
        {
           //p_abc_def=>DataRowVersion.Current
           //o_abc_default=>DataRowVersion.Original
            if(parameterName.StartsWith("@o"))
            {
                return DataRowVersion.Original;
            }
            return DataRowVersion.Current;
        }
    }
}