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> publicabstract partial class Database : IDisposable { privatebool _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; privatestring _connectionString; private CommandType _defaultCommandType; privatebool _useCommandBuilder; private IDbParameterNameMapping _dbParameterNameMapping; private IStoredProcedureNameMapping _storedProcedureNameMapping; /**////<summary> /// Database connection string which is specified by the database factory. ///</summary> publicstring ConnectionString { get { returnthis._connectionString; } set { this._connectionString = value; } } /**////<summary> /// The concrete database specific provider factory. ///</summary> public DbProviderFactory DatabaseProviderFactory { get { returnthis._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 { returnthis._defaultCommandType; } set { this._defaultCommandType = value; } } /**////<summary> ///Determine whether to use command builder or mapped stored procedures to execute database operations. ///</summary> publicbool UseCommandBuilder { get { returnthis._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 { returnthis._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 { returnthis._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; } returnthis._dbDataAdapter; } } /**////<summary> /// The database connection. ///</summary> private DbConnection Connection { get { if (this._connection ==null) { this._connection =this._dbProviderFactory.CreateConnection(); this._connection.ConnectionString =this._connectionString; } returnthis._connection; } } #endregion Constructor#region Constructor public Database() { } #endregion IDisposable Members#region IDisposable Members publicvoid Dispose() { Dispose(true); GC.SuppressFinalize(this); } privatevoid 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> publicabstract partial class Database : IDisposable, { publicvoid 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); } publicvoid FillDataSet(DataSet dataInfo, string tableName, string commandText, IDictionary<string, object> parameters) { this.FillDataSet(dataInfo, tableName, this._defaultCommandType, commandText, parameters); } publicvoid 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); } } }
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> publicabstract partial class Database : IDisposable { DbTransaction _transaction; Transaction based operation#region Transaction based operation publicvoid BeginTransaction() { if (this.Connection.State != ConnectionState.Open) { this.Connection.Open(); } this._transaction =this.Connection.BeginTransaction(); } publicvoid Commit() { if (this._transaction ==null) { thrownew DataAccessException(CommonConstant.DataAccess.Messages.NotBegunTransaction); } this._transaction.Commit(); } publicvoid RollBack() { if (this._transaction ==null) { thrownew DataAccessException(CommonConstant.DataAccess.Messages.NotBegunTransaction); } this._transaction.Rollback(); } public DbTransaction Transaction { get { returnthis._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> publicabstract 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> publicabstractvoid 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> publicabstract 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> publicvoid 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> publicvoid 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> publicvoid 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> publicvoid 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> publicvoid 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> privatevoid 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> privatevoid 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> privatevoid 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> privatevoid 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来做。要实现这样的功能其实是很麻烦的,要考虑的因素很多:
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> publicclass SqlDatabase:Database { public SqlDatabase() { } /**////<summary> /// Discover all of the parameters for the command realted stored procedure. ///</summary> ///<param name="command">Command</param> publicoverridevoid 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> publicoverride DataRowVersion GetSourceVersion(string parameterName) { //p_abc_def=>DataRowVersion.Current //o_abc_default=>DataRowVersion.Original if(parameterName.StartsWith("@o")) { return DataRowVersion.Original; } return DataRowVersion.Current; } } }