Using Microsoft Enterprise Library in database Access projects to support multiple databases

In many of our projects, data access is essential, with access to the regular databases of Oracle, SQL Server, MySQL, and possibly to SQLite, access, or some of the PostgreSQL, IBM DB2, or domestic dream database, and so on, the common characteristics of these databases are relational database, basically developed models are similar, but if we are based on the development of the basis of ADO, then all kinds of databases have their own different database operations objects, Microsoft Enterprise Library Enterprises The library is an abstract model based on the operations of these different databases and is suitable for multi-database support projects. This article describes the processing of various databases based on the Microsoft Enterprise Library 4.1 Foundation.

1. Selection of Enterprise Library version

In choosing Enterprise Library version, I have always been relatively cautious, because we develop projects involving a lot of different systems, some need XP support, some need Win7 support or WIN10, etc., need to consider the compatibility of the different system home, Because the database access module in the Microsoft Enterprise Library is relatively stable, it also basically uses a stable version, although the Enterprise Library version is 6.0, but previously used in the project is 3.1, this version can be run on the project of. NET 2.0, and the Extended class library is also quite good, so keep it.

With the upgrade of the framework version, you can run the. NET 4.0 version on XP, so consider using Enterprise Library 4.1 or 5.0 (Enterprise Library 6.0 requires. NET 4.5 support, Can no longer run on XP), relatively speaking, Enterprise Library4.1 Extended class library support is very good (http://entlibcontrib.codeplex.com/releases/view/38988), Supports SQL Server, DB2, MYSQL, ODP. NET (Oracle), PostgreSQL, SQLite, Sqlex and other databases, and the Enterprise Library version 5.0 extension Class Library support has not been fully provided, need to deal with their own.

Therefore, in order to take care of the compatibility of different systems such as XP and WIN7/WIN8/WIN10, we can upgrade from the current Enterprise Library 3.1 to Enterprise Library. 4.1, this can take advantage of better extension class library support (support SQL Server, DB2, MYSQL, ODP. NET (Oracle), PostgreSQL, SQLite, Sqlex, and other databases), and the version of the data access module is also relatively improved.

In the multi-database support I've drawn, I've tweaked the database as a data tier after we adopted Microsoft Enterprise Library, as shown below.

So basically the general relational database we all support, we need to develop any database application, is a unified data model, the development of a lot of convenience, but also convenient in different database management system configuration switch.

2, the use of Microsoft Enterprise Library architecture Design

Using the Microsoft Enterprise Library as our bottom database access module, for a variety of database access operations, will be unified adoption of this Enterprise library database Access objects, the operation is very consistent, For the purpose of simplifying the code, we can define a data access operation base class for each different database in order to implement some different database processing, but they still have a common data access base class for the further encapsulation of some operations such as the normal addition and deletion of different databases.

With different databases, we need to generate processing for the access layers of different databases, such as generating the relevant data access layer for the tables of SQL Server data, dalsql the contents of each Table object, but because of the related inheritance class processing and database-based code generation, There is little code to adjust.

For the data access layer, we need to design a corresponding inheritance relationship so that our base class can encapsulate most of the operations, and give the sub-class the relative elastic processing space, such as for the client customer's object, the data interface layer and the data Access implementation layer of the relationship is as follows.

With this consolidation of the underlying database support, the entire data access architecture is designed as follows.

About this architecture, I have in the previous many articles have elaborated, if we also need to extend some special database support, can refer to the essay "Enterprise Library-based WinForm development framework to support the Chinese Dream Database expansion operation" to carry out some extended custom operations.

3. Multi-database support processing based on Enterprise Library

As we mentioned earlier, the advantage of using Microsoft Enterprise Library Enterprise Libraries is that we can unify the programming model, realize the compatible processing of many databases, and the biggest feature of Microsoft Enterprise Library Enterprise database is the processing of multiple databases based on configuration items. By using different configuration items, you can quickly switch to the corresponding database, the code does not need to be modified.

For general Enterprise Library Configuration Processing, we add configuration items as shown below.

  < configsections >    <  name= "dataconfiguration"  type= " Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data "/>  </configsections  >

Then add a different connection string for the different databases

For the SQL Server database that is supported by default, its connection string is as follows.

<?XML version= "1.0"?><Configuration>  <configsections>    < Sectionname= "Dataconfiguration"type= "Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data "/>  </configsections>  <connectionStrings>    <!--connection string for SQL Server database -    <Addname= "SQL Server"ProviderName= "System.Data.SqlClient"connectionString= "Persist Security info=false;data source= (local); Initial catalog=winframework;integrated Security=sspi"/>  </connectionStrings>  <dataconfigurationDefaultDatabase= "SQL Server">  </dataconfiguration></Configuration>

However, for some extended supported databases, we also need to add some mapping processing, such as for MySQL support, we need to add a connection string:

    <!-- connection string for MySQL database  -    <  name= "MySQL"  providerName= "MySql.Data.MySqlClient"  connectionString= "server=localhost;database=winframework; Uid=root; pwd=123456; " />

You will also need to add support for providermappings, as shown in the following XML.

  <dataconfigurationDefaultDatabase= "MySQL">    <providermappings>      <AddDatabaseType= "EntLibContrib.Data.MySql.MySqlDatabase, EntLibContrib.Data.MySql"name= "MySql.Data.MySqlClient" />    </providermappings>  </dataconfiguration>

Below I list the connection strings for all the different databases and a full version of the mapping relationship for reference.

As we mentioned earlier, we can use the following simple case code to show the data in order to test the connection of different databases based on the configuration implementation of the unified processing of different databases.

The code that implements it is shown below.

We see that the above code is not specific to the database, so it is also very generic processing, we can directly get the data and show it, I above the case in SQL Server, Oracle, PostgreSQL, MySQL, SQLite, Access, IBM The DB2 database is tested and passed.

Specific development projects, different databases have a number of different processing, such as paging operations, to obtain the processing of the specified records, and so on, we need to play the above mentioned database base class functionality, through the encapsulation of the base class function, we can use all the database in addition to the commonality, It can also use some of its specific processing operations, so that we can take full advantage of the characteristics of a variety of different databases, but also unified to a development model, reducing the cost of development between different databases, but also reduce the difficulty of migration between different databases, improve code readability and extensibility.

The above framework built on the database access module has worked well for many of my WinForm projects, web development projects, and some back-end service projects, and has been used for many years as a contribution to the process of developing different databases or upgrading to different database versions.

Using Microsoft Enterprise Library in database Access projects to support multiple databases