Using C # generics to implement switching between databases

Recently done a project, I began to use the MYSQL 5.0 database, the project submitted, the other request to change to MS SQL Server 2000, fortunately, the data-tier operation is based on the standard SQL statements, also did not use stored procedures, so the original data access layer code to rewrite the generic class, Only changed less than 100 lines of code, the specific SQL operation statement is not moved, it realizes the switch between the database. Here's a brief description:

The database is not very complex, so I've taken two classes:

(1) Dataprovider generic class

public class DataProvider<ConnType, CmdType>
　　where ConnType : IDbConnection, new()
　　where CmdType : IDbCommand,new ()
{

}

Provides insert,update,select,delete operations for database tables.

Because IDbCommand can be obtained by idbconnection, only one generic parameter is required conntype, but since then, the specific code changes are relatively large, I used two generic parameters.

(2) ConnectionPool generic class

public class ConnectionPool<T>
　　　　where T : IDbConnection,new ()
{

}

In the beginning did not find in MySQL 5.0 to the database connection pool support, so I wrote a simple database connection pool.

Below, is using C # 's NB Syntax ――using:

(1) If the MYSQL database is used:

Using Dataprovider = Dataprovider<mysql.data.mysqlclient.mysqlconnection, MySql.Data.MySqlClient.MySqlCommand >;

(2) If you are using a SQL Server database:

Using Dataprovider = Dataprovider<system.data.sqlclient.sqlconnection, system.data.sqlclient.sqlcommand>;

In this way, the code in the other place is not moved.

Laziness is the virtue of a programmer. One of my computers is a SQL Server 2000 database, a computer installed MySQL database, I often work on this computer, one will work on another computer, two computer code should be consistent ah. Therefore, pre-compiled directives are used:

#if MSSQLSERVER
using System.Data.SqlClient;
using DataProvider = DataProvider<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlCommand>;
#elif MYSQL
using MySql.Data;
using DataProvider = DataProvider<MySql.Data.MySqlClient.MySqlConnection, MySql.Data.MySqlClient.MySqlCommand>;
#endif

This specifies the conditional compilation symbol "MySQL" at compile time to get the MySQL version of the code, specify the conditional compilation symbol "MSSQLSERVER", get the SQL Server version of the code.

Other:

(1) Theoretically, the solution with reflection is more perfect, but the duration will be longer, there is no need for La la la ...

(2) database design, I used the free software Toad data Modeler free version, which provides a database switching function, switch past, a little change changes.

(3) This development tool is full of open source software or free software, are super easy to use Dongdong, feel the development speed is not much slower than the huge fee software. The tools used are as follows:

Ide:vs (c + +, C # Edition, Web Development Edition). NET's ASPNET command-line compilation tool

Version management: SVN, Little Turtle svn Client

Shell:windows Power Shell (since this thing is out, we should make full use of it)

UML Modeling: STARUML (powerful open source UML, more powerful than I used to jude,argouml, support C #)

Database modeling: Toad data Modeler Free Edition

Database management tools: EMS SQL Manager Lite for MySQL

Http://www.cnblogs.com/xiaotie/archive/2007/01/02/610121.html