C# way to achieve multi-database support, we will know more or less, this article from the General framework of the development of ideas point of view to introduce the General framework to achieve multi-database support in detail, so that more people understand the General framework of the underlying implementation and get the required knowledge. I am in the development of General framework, from the network to obtain a lot of knowledge, other ORM frameworks also have reference, which borrowed the most is the NBear framework and NHibernate framework, I will be from the development of ideas from all over the integration of ideas into the formation of my own development approach.

Simply put, the General framework to support the realization of multi-database ideas, are the following:

1, Ado.Net multi-data support

2, SQL multi-data support

3, data manager multi-database support

The following will be explained.

1, Ado.Net's multi-data support

When we first learn C#, we will understand that Ado.Net has five major objects, namely, Connection (Connection object), Command (Command or Execution object), DataReader (Reader object), DataAdapter (Adapter object), DataSet (dataset object), and we began to commonly used SqlHelper, these five objects are SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter, DataSet, and later may come into contact with support for the multi-database version of the SqlHelper, namely DbHelper, the five main objects in this will change to DbConnection, DbCommand, IDataReader, DbDataAdapter, DataSet, and then later we found that in fact SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter are DbConnection, DbCommand, IDataReader, DbDataAdapter subclasses, that is, Ado.Net uses abstract classes to adapt to the multi-database support, so write applications that support multiple databases, you only need to use the Db beginning of the Ado.Net object on the Net objects that begin with Db can be used. But the abstract class can not be directly instantiated how to do, in fact, there is a factory class used to create an instance of the abstract class, that is, DbProviderFactory, but DbProviderFactory is also an abstract class, and still can not be instantiated, this time we need to be based on the specific type of database, to use different databases corresponding to the Ado.Net driver for the Net driver Instance of DbProviderFactory can be used. The corresponding driver library and factory class of each database are as follows:

No.

Database type

Corresponding driver library

Corresponding factory class

1

Access

System Data.OleDb (comes with it)

OleDbFactory.Instance

2

Sqlite

System.Data.Sqlite (requires download)

SQLiteFactory.Instance

3

SqlServer

System.Data.SqlClient (comes with it)

SqlClientFactory.Instance

4

Oracle

System.Data. OracleClient (comes with it)

OracleClientFactory.Instance

5

MySql

MySql.Data (requires download)

MySqlClientFactory.Instance

In General.Data there is a BaseProvider, this class is an abstract class, responsible for providing the database corresponding to the factory class instances, each database type needs to implement its own Provider and inherit from BaseProvider, and provide the actual factory class instances. There is also a DbCommon class, this class is responsible for dealing with Ado.Net, using the Db version of the five objects, its constructor parameter is BaseProvider, that is, you need to use the specific Provider implementation to create DbCommon, and DbCommon can be used to implement the specific Provider to provide factory class instances to create the Db version of the five objects. instances to create the Db version of the five objects, which also allows DbCommon to adapt to different types of databases.

2, SQL multi-database support

After the implementation of multi-database support at the Ado.Net level, because Ado.Net does not actually involve the SQL collocation, and each database in the implementation of the SQL will have more or less different , such as SqlServer parameter prefixes are ? @? for SqlServer and ? :? SqlServer encloses the tag name in middle brackets while Oracle encloses the tag name in double quotes, and then Sqlite doesn't have the ?Top? keyword while it has the similar ?Limit? keyword and so on, so to implement an ORM framework that supports multiple databases, you need to differentiate between them according to the different database types when generating the Sql.

General.Data has a QueryBuilder class, this class is used to generate Sql statements, and this class is an abstract class, that is, each database type needs to implement its own QueryBuilder to differentiate between different Sql syntax, and the general part of the Sql, such as Select, insert, update, delete and other statements of each database type. The common part of the Sql, such as Select, Insert, Update, Delete and other statements are the same for each database, so they do not need to be abstracted and are directly implemented in the abstract class.The QueryBuilder class does not need to be created by itself, but through the BaseProvider's abstract method GetQueryBuilder to be created by the implementation of each Provider, and accordingly, with the QueryBuilder similar to the SchemaManager. The SchemaManager of BaseProvider is also created by the abstract method GetSchemaManager of BaseProvider, which is implemented by each Provider. In this way, the upper layer only need to master the creation of Provider, you can master the support of each database type.

3, DataManager multi-database support

General.Data Data in the DataManager class is the interface for all database operations, the previous paragraph said: as long as you master the creation of the Provider, you can master the support of each database type. So the initialization of DataManager, in fact, is Provider, DbCommon, QueryBuilder, SchemaManager creation process, and with Provider, the remaining three can be created by Provider, and only need to add Provider and its corresponding QueryBuilder, SchemaManager, you can add support for more database types.

For ease of configuration, the DatabaseType enumeration type has been added to General.Data to include the database type support that has been implemented. When configuring, you only need to specify DatabaseType and ConnectionString, and DataManager can automatically create instances of the corresponding Provider and other classes, which also completes the initialization.

When doing multi-database support, there are still some unexpected problems:

1, Sqlite database when reading data reported ? The string is not recognized as a valid DateTime?exception

This is due to Sqlite does not support the current system's date and time format, you need to save the data, the value of the DateTime type.ToString(?s?), in order to solve this problem, in the General framework to add the entity attribute formatting method, in the entity attribute mapping with Format = ?s? and then DataManager.Default.UsePropertyValueFormat = true for automatic formatting.

2, Access database in the Save Database Times ?xxx field can not be empty?

This is because the text field of the Access database table is not enabled? Allow Null Characters?

This is because the text field of the Access database table is not enabled to allow null characters, and if you don't want to enable this option, you can set DataManager.AccessConvertEmptyStringToNull = true, so that the null characters will be converted to the DBNull value automatically.

3, Oracle database in the execution of the operation reported ?xxx table or view does not exist?

This is due to build table Sql on the table name with double quotes and Sql statement in the table name case is not correct, or build table Sql without double quotes Oracle automatically capitalize the table name and Sql statement in the table name is not uppercase, it is recommended to build the table Sql do not double quotes on the table name and set the DataManager. OracleConvertQuoteNameToUpper = true.

How General Framework implements multi-database support

Tags: