How the general framework implements multi-database support

about usingC #the way to implement multi-database support, everyone will know how much, this article from Generalframework of the development of the perspective of the detailed introduction GeneralThe framework implements multi-database support in a way that enables more people to understand GeneralThe underlying implementation of the framework and get the relevant knowledge required. I was developing Generalframework, from the network to obtain a lot of knowledge, to otherORMframework has also been used for reference, in which the most reference isNbearFramework andNHibernateframework, I will integrate the development ideas from all over the place, forming my own way of development.

Simply put, The general Framework supports a multi-database implementation approach, which is nothing more than the following:

1, the majority of ADO support

2 . Majority support for SQL

3. Multi-database support for Data Manager

This will be explained in detail below.

1, the majority of ADO support

when we were beginnersC#, you will understandadoThere are five major objects, namelyConnection(Connection object),Command(command or Execute object),DataReader(Reader object),DataAdapter(Adapter object),DataSet(DataSet object), and we started the usualSqlHelper, the five major objects areSqlConnection,SqlCommand,SqlDataReader,SqlDataAdapter,DataSet, and may later be exposed to support for multi-database editionsSqlHelper, i.e.DBHelper, the five objects in this will becomeDbConnection,DbCommand,IDataReader,DbDataAdapter,DataSet, and then we found out in factSqlConnection,SqlCommand,SqlDataReader,SqlDataAdapterrespectively isDbConnection,DbCommand,IDataReader,DbDataAdaptersub-class, that is,adoabstract classes are used to accommodate multi-database support, so writing applications that support multiple databases requires only the use ofDbbegins withadothe object is ready. But the abstract class can not be directly instantiated what to do, in fact, there is a factory class used to create an instance of the abstract class, that is,DbProviderFactory, butDbProviderFactoryis an abstract class and still cannot be instantiated, we need to use different database types according to the specific database type .adoIn the driver'sDbProviderFactoryof theInstanceYes, the corresponding drive class libraries and factory classes for each database are as follows:

Serial number	Database type	The corresponding Driver class library	The corresponding factory class
1	Access	System.Data.OleDb(comes with)	Oledbfactory.instance
2	Sqlite	System.Data.Sqlite(download required)	Sqlitefactory.instance
3	Sql server	System.Data.SqlClient(comes with)	Sqlclientfactory.instance
4	Oracle	System.Data.OracleClient(comes with)	Oracleclientfactory.instance
5	Mysql	Mysql.data(download required)	Mysqlclientfactory.instance

in the there is a baseprovider in general.data, whichis an abstract class that is responsible for providing an instance of the database corresponding to the factory class, each of which needs to implement its own Provider and inherit from Baseprovider, and provides practical examples of factory classes. There is also a Dbcommon class, this class is responsible for dealing with ADO , using the Db version of the five objects, its constructor parameters are Baseprovider, That is , a specific Provider implementation is required to create the Dbcommon, and Dbcommon can create the factory class instance provided by the specific Provider implementation The five major objects of the Db edition, so that the Dbcommon can adapt to different types of databases.

2 . Multi-database support for SQL

in the after the implementation of multi-database support at the ADO level, since ADO does not involve the consolidation of SQL, and the database in the implementation of SQL will be how much difference, such as SQL Server 's parameter prefix is "@", while Oracle is ":",SQL Server uses brackets to denote tag names, and Oracle Use double quotation marks to denote tag names, and if Sqlite has no "Top" keyword with similar "Limit" keyword and soon, so to implement an ORM framework that supports multiple databases , you need to differentiate between different database types when generating Sql.

General.datahas aQueryBuilderclass, this class is used to generateSqlstatement, and this class is an abstract class, that is, each database type needs to implement its ownQueryBuilderto distinguish the differentSqlsyntax, while the general part of theSql, such asSelect,Insert,Update,DeleteEach database is the same, so it does not need abstraction and is directly implemented in the abstract class. QueryBuilderclass does not need to be created by itself, butBaseproviderthe abstract methodGetquerybuilderFor eachProviderimplementation to create, corresponding to theQueryBuildersimilar toSchemaManageralso byBaseproviderthe abstract methodGetschemamanagerFor eachProviderimplementation to create the. In this way, the upper level only masterProvider, you can master the support for each database type.

3. Multi-database support for Data Manager

General.data datamanager class is the interface for all database operations. The previous paragraph said: As long as the grasp of provider datamanager Provider dbcommon querybuilder, Schemamanager provider provider to create, and only need to add provider and its corresponding querybuilder, schemamanager

for ease of configuration, The DatabaseType enumeration type is added to General.data , which contains the database type support that has already been implemented. When configuring, you only need to specify databasetype and ConnectionString,and DataManager can automatically create instances of the corresponding Provider class. The initialization is completed.

There are some unexpected issues with long database support:

1. The Sqlite database reads the data times "the string is not recognized as a valid DateTime " Exception

This is due to Sqlite does not support the date-time format of the current system and requires the value of the DateTime type when the data is saved. ToString ("s"), in order to solve this problem, Add the Entity attribute formatting method to the general framework, add format = "S" to the Entity attribute mapping, and then DataManager.Default.UsePropertyValueFormat = Trueto automatically format.

2,Access database in the Save database Times "xxx field cannot be empty"

This is due to The text field of an Access database table does not turn on allow null characters, and you can set DataManager if you do not want to turn on this option . Accessconvertemptystringtonull = Trueto automatically convert null characters to DBNull values.

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

This is due to the building of the table SQL adds double quotation marks to the table name and the table name in the SQL statement is not cased correctly, or the table SQL does not have double quotation marks Oracle automatically capitalizes the table name and the table name in the SQL statement is not uppercase, it is recommended to create a table Sql do not double-quote the table name and set Datamanager.oracleconvertquotenametoupper = True.

How the general framework implements multi-database support