Develop your own DataAccessApplicationBlock [Part 1]

I often see discussions on ORM on the Internet. I also come to join in and talk about An orm I wrote. Recently, I am working on a task to abstract some of the business logic we often use and write them into Application blocks to apply them to different applications, such as DataAccess, Messaging, Auditing, and Databind.

I often see discussions on ORM on the Internet. I also come to join in and talk about An orm I wrote. Recently, I am working on a job to abstract some of the business logic that we often use and write them into Application blocks so that they can be applied to different applications, such as Data Access, Messaging, Auditing, data bind

I often see discussions on ORM on the Internet. I also come to join in and talk about An orm I wrote. Recently, I am working on a job to abstract some of the business logic that we often use and write them into Application blocks so that they can be applied to different applications, such as Data Access, Messaging, Auditing, data binding. Create a Data access application block first. Due to the rush of time, there was no optimization or a lot of tests. You don't have to go into the Code I provided. I just want to provide another idea for everyone's ORM.

The purpose of this application block:

• Encapsulate all Data Access operations.

• Suitable for mainstream DBMS: SQL Server (2000 and 2005), Oracle (9i and 10g), and DB2.

• To simplify Developer operations and provide maximum flexibility, you only need to specify SQL or Stored Procedure and corresponding parameters for Data Retrieval. In Data Update, you can call SQL and Stored Procedure directly, and update Data by calling Dataset containing multiple correlated Data tables at a time. In addition, you can choose whether to use SQL or Stored procedure. You can use Commander builder to generate commands or use commands generated based on Mapped stored procedure to update data.

• Implement generic programming so that the code using this AppBlock can be suitable for all databases.

• Implement Transaction.

• It provides configurability, including the configuration of different databases and the configuration of different Data Mapping.

The Entity used by the AppBlock is as follows:

• Database: Abstract Class, which encapsulates most Data Access operation logics irrelevant to a specific Database. Datasing between Dataset and Db is realized through two mappings: IDbParameterNameMapping and IStoredProcedureNameMapping. For example, Mapping of Data table name and Stored procedure name in Dataset, Mapping of parameter names in Field and Stored procedure in Data table. These two mappings can be configured. You only need to compile the Interface ing provider that suits you by implementing the provided Interface.

• SqlDatabase: encapsulate SQL Server-based operations. ADO. NET 2.0 has been greatly improved on the basis of 1.0. It mainly adds a large number of base classes, providing generic programming for us. It is very easy to write code unrelated to the specific database. Therefore, we can encapsulate most Data Access operations in the Abstract Database class, and the content in SqlDatabase is actually very small.

• OracleDatabase: encapsulate Oracle-based operations.

• IDbParameterNameMapping and IStoredProcedureNameMapping: I think everyone feels like this. The essence of implementing an ORM is to implement a ing between data in memory (mainly Dataset) and databases. In terms of datasing between Dataset and tables in databases, I don't think it is necessary to use special Mapping, directly and easily, the Data Table in the Table and Dataset exactly matches (the table name and field name exactly match ). Therefore, it is important to implement datasing of Dataset and Stored procedure: How the Table Name matches with the Stored procedure name for Insert, Update, and Delete, with different versions (original & current) how the Field matches the Parameter name of Stored procedure. Such a match should be configurable, because each Application has its own naming requirements during database design, so the Provider design pattern I use here. You can implement these two interfaces to write your own Mapping provider. The Configuration block provided by me can easily complete the Configuration. At the same time, I wrote a default Simple Mapping: SimpleDbParameterNameMapping and SimpleStoredProcedureNameMapping.

One thing to add is that to implement the above ing, there are high requirements for Stored Procedure naming, and manual writing is no longer suitable for our requirements, therefore, we need a Generator that generates Stored procedure. This Generator also uses these two configurable ing interfaces.

• DatabaseFactory: a static class that creates the specific Database object you need based on the configuration information to implement generic programming.

• DataAccessConfigurationCollection, DataAccessConfigurationElement, and DataAccessConfigurationp are three Configuration classes.

To make it clear that all the operations of this Application block are encapsulated in an IDatabase interface, note that, I am using Abstract class-based programming, rather than Interface-based programming. I believe you have discussed these two methods too much. I don't want to talk about the advantages and disadvantages of the two methods. This IDatabase interface only shows the use of all Operation and is not used in my code.

Namespace Artech. ApplicationBlock. DataAccess
{
Public interface IDatabase
{
Fill a System. Data. DataSet with retrieved data. # region Fill a System. Data. DataSet with retrieved data.
Void FillDataSet (DataSet dataInfo, string commandText, IDictionary Parameters );

Void FillDataSet (DataSet dataInfo, string tableName, string commandText, IDictionary Parameters );

Void FillDataSet (DataSet dataInfo, string tableName, CommandType commandType, string commandText, IDictionary Parameters );

# Endregion

Save the changed data which is stored in a dataset into database. # region Save the changed data which is stored in a dataset into database.
Void UpdateData (DataSet dataInfo );

Void UpdateData (DataTable table );

Void UpdateData (DataTable table, string insertCommandText, string updateCommandText, string deleteCommandText,
Dictionary InsertParameters, Dictionary UpdateParameters, Dictionary DeleteParameters );

Void UpdateData (DataTable table, CommandType commandType, string insertCommandText, string updateCommandText, string deleteCommandText,
Dictionary InsertParameters, Dictionary UpdateParameters, Dictionary DeleteParameters );

Void UpdateData (DataTable table, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand );

# Endregion

Execute a command and return the affect row count. # region Execute a command and return the affect row count.
Int ExecuteNonQuery (CommandType commandType, string commandText, Dictionary InputParameters, Dictionary OutputParameters );

Int ExecuteNonQuery (CommandType commandType, string commandText, Dictionary InputParameters );

Int ExecuteNonQuery (string commandText, Dictionary InputParameters, Dictionary OutputParameters );

Int ExecuteNonQuery (string commandText, Dictionary InputParameters );
# Endregion

Execute a command and return the data in the form of data reader. # region Execute a command and return the data in the form of data reader.
DbDataReader ExecuteReader (CommandType commandType, string commandText, Dictionary InputParameters, Dictionary OutputParameters );

DbDataReader ExecuteReader (CommandType commandType, string commandText, Dictionary InputParameters );

DbDataReader ExecuteReader (string commandText, Dictionary InputParameters, Dictionary OutputParameters );

DbDataReader ExecuteReader (string commandText, Dictionary InputParameters );
# Endregion

Execute a command and return a scalar value. # region Execute a command and return a scalar value.

Object ExecuteScalar (CommandType commandType, string commandText, Dictionary InputParameters, Dictionary OutputParameters );

Object ExecuteScalar (CommandType commandType, string commandText, Dictionary InputParameters );

Object ExecuteScalar (string commandText, Dictionary InputParameters, Dictionary OutputParameters );

Object ExecuteScalar (string commandText, Dictionary InputParameters );
# Endregion

Transaction based operation # region Transaction based operation
Void BeginTransaction ();
Void Commit ();
Void RollBack ();
# Endregion
}
}

This list is not much different from most ORM, and it is common to all of us, so it will not be too difficult to implement it. I will not detail most operations. Next, let's take a brief look at how such an AppBlock is implemented.
1. Data Mapping

First, let's take a look at Data Mapping: Mapping of Table name and Stored Procedure Name in Dataset, and Mapping of Parameter name of Field and Stored procedure in Dataset.

IDbParameterNameMapping

Using System;
Using System. Collections. Generic;
Using System. Text;

Namespace Artech. ApplicationBlock. DataMapping
{
/**////

/// IStoredProcedureNameMapping defines the mapping between the data table name and the name of stored procedures to perform insertion, modification and deletion operation.
///
Public interface IStoredProcedureNameMapping
{
/**////
/// Get the name of stored procedure to perform seletion operation.
///
/// The name of the database table.
/// The name of stored procedure to perform seletion operation
String GetSelectStoredProcedureName (string tableName );

/**////
/// Get the name of stored procedure to perform insert operation.
///
/// The name of the database table.
/// The name of stored procedure to perform insertion operation
String GetInsertStoredProcedureName (string tableName );

/**////
/// Get the name of stored procedure to perform modification operation.
///
/// The name of the database table.
/// The name of stored procedure to perform modification operation
String GetModifyStoredProcedureName (string tableName );

/**////
/// Get the name of stored procedure to perform deletion operation.
///
/// The name of the database table.
/// The name of stored procedure to perform deletion operation
String GetDeleteStoredProcedureName (string tableName );
}
}

IDbParameterNameMapping

Using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Data;
Namespace Artech. ApplicationBlock. DataMapping
{
/**////

/// IDbParameterNameMapping define the defult mapping between the source column name and the parameter name of the corresponding stored procedure.
///
Public interface IDbParameterNameMapping
{
/**////
/// Get the source column name based on the parameter name of the related stored procedure.
///
/// The parameter name of the corresponding stored procedure.
/// The source column name corresponding to the parameter name.
String GetSourceCoulmnName (string patameterName );

/**////
/// Get the source parameter name based on the source column name.
///
/// The source column name corresponding to the parameter name.
/// The data row version of the source solumn conressponding to the parameter.
/// The parameter name of the corresponding stored procedure.
String GetParameterName (string columnName, DataRowVersion rowVersion );
}
}

These two mappings are mainly used in Dataset and new database scenarios. Using IDbParameterNameMapping, we obtain the name of the Stored procedure that performs Insert, Update, and Delete operations on the Table name in Dataset. With IDbParameterNameMapping, we can specify the corresponding Source field for the Parameter of Stored procedure.

Note: The GetParameterName method is actually not required. I will use it in another AppBlock.

Next we will write two default mapping implementing the above Interface: SimpleStoredProcedureNameMapping and SimpleDbParameterNameMapping. He actually implemented the Mapping like this: for example, the Table name is T_ABC_DEF (I often use the naming method: starting with T to represent the Table, and the name is capitalized and connected with an underscore ), the corresponding Stored procedure names are: sp_abc_def_s (Select), sp_abc_def_ I (Insert), sp_abc_def_u (Update), sp_abc_def_d (delete ). If Field name is ABC_123, the Parameter name of Original version is o_abc_123 (o stands for Original), and the Parameter name of Current version is p_abc_123 (p stands for the Parameter in the general sense ).

Using System;
Using System. Collections. Generic;
Using System. Text;

Namespace Artech. ApplicationBlock. DataMapping
{
/**////

/// IStoredProcedureNameMapping defines the mapping between the data table name and the name of stored procedures to perform insertion, modification and deletion operation.
///
Public class SimpleStoredProcedureNameMapping: IStoredProcedureNameMapping
{