Use ADO. NET to develop database-independent applications

Database independence (DB Independ) indicates that the application runs independently of a database (such as SqlServer) and can be switched to other databases (such as Oracle) simply (without compilation ). Database independence is a measure of the system.

There are many ways to implement database-independent applications, such as nhib.pdf and EntityFramework.

This article describes the most basic method. Next we will step through a simple database-independent applet.

Create a project
Create a Console Application in:

 

(For VS 2010, you can select different. net versions. The above version is 2.0 or another version, which has little impact on this example)

After the project is created, add a reference to System. Configuration as follows:

 

Add a configuration file to the project:

 

After the project is ready, the structure is as follows:

 

(I don't know why, the first letter of configuration in System. configuration is lowercase)

Add a connection string to the configuration file.
<? Xml version = "1.0" encoding = "UTF-8"?>
<Configuration>
<ConnectionStrings>
<Add name = "default"
ConnectionString = "server = localhost; user id = sa; password = ******; database = northwind"
ProviderName = "System. Data. SqlClient"/>
</ConnectionStrings>
</Configuration>
<? Xml version = "1.0" encoding = "UTF-8"?>
<Configuration>
<ConnectionStrings>
<Add name = "default"
ConnectionString = "server = localhost; user id = sa; password = ******; database = northwind"
ProviderName = "System. Data. SqlClient"/>
</ConnectionStrings>
</Configuration>

Note: The Connection characters are written in the connectionStrings section. It cannot be written in deleetaskbecause the providerName attribute is used in the program.

Write database-independent code
Reference the following three namespaces in Program. cs:
Using System. Data;
Using System. Data. Common;
Using System. Configuration;

First, read the connection string information in the configuration file:

1 ConnectionStringSettings settings = ConfigurationManager. ConnectionStrings ["default"];

The factory mode is used for data independence of ADO. NET. The factory can be obtained according to the providerName In the Configuration:

1 DbProviderFactory factory = DbProviderFactories. GetFactory (settings. ProviderName );

DbProviderFactory provides multiple methods to help us create the objects needed to access the database:

Public abstract class DbProviderFactory
{
//...
Public virtual DbCommand CreateCommand ();
Public virtual DbCommandBuilder CreateCommandBuilder ();
Public virtual DbConnection CreateConnection ();
Public virtual DbConnectionStringBuilder CreateConnectionStringBuilder ();
Public virtual DbDataAdapter CreateDataAdapter ();
Public virtual DbDataSourceEnumerator CreateDataSourceEnumerator ();
Public virtual DbParameter CreateParameter ();
}

DbConnection, DbCommand, DbDataAdapter, and DbParameter are all database-independent.

The following example demonstrates the use of these objects:

1. query the number of employees

Using (DbConnection connection = factory. CreateConnection ())
{
Connection. ConnectionString = settings. ConnectionString;

DbCommand command = connection. CreateCommand ();
Command. CommandType = CommandType. Text;
Command. CommandText = "select count (*) from employees ";

Connection. Open ();
Var obj = command. ExecuteScalar ();
If (obj is int) employeesCount = (int) obj;
Else employeesCount = (long) obj;
Connection. Close ();
}
Console. WriteLine ("Total employees: {0}", employeesCount );

Count (*) different database return value types (Int32 returned by SqlServer, Int64 returned by MySql), 10 ~ 12 rows are processed.

2. Use DbDataReader

Using (DbConnection connection = factory. CreateConnection ())
{
Connection. ConnectionString = settings. ConnectionString;

DbCommand command = connection. CreateCommand ();
Command. CommandType = CommandType. Text;
Command. CommandText = "select * from employees ";

Connection. Open ();
DbDataReader reader = command. ExecuteReader ();
While (reader. Read ())
Console. WriteLine ("{0} {1}", reader ["FirstName"], reader ["LastName"]);
Connection. Close ();
}

3. Fill in Dataset

DataSet dataSet = new DataSet ();
Using (DbConnection connection = factory. CreateConnection ())
{
Connection. ConnectionString = settings. ConnectionString;

DbCommand command = connection. CreateCommand ();
Command. CommandType = CommandType. Text;
Command. CommandText = "select * from employees ";

DbDataAdapter adapter = factory. CreateDataAdapter ();
Adapter. SelectCommand = command;

Connection. Open ();
Adapter. Fill (dataSet, "Employees ");
Connection. Close ();
}

Switch Database
You can simply modify the configuration file to use another database, and use MySql as follows:

<? Xml version = "1.0" encoding = "UTF-8"?>
<Configuration>
<ConnectionStrings>
<Add name = "default"
ConnectionString = "server = localhost; user id = root; password = ******; database = northwind"
ProviderName = "MySql. Data. MySqlClient"/>
</ConnectionStrings>
</Configuration>

(MySql Connector Net must be installed to run)

Summary
ADO. NET provides database-independent classes, simplifying the development of database-independent applications.

However, there are still many precautions for developing database-independent applications, such as the count (*) Return Value Type mentioned above, and writing database-independent SQL statements.

Finally, there are limits on Database independence. The method described in this article is only applicable to relational data.

Source code: ADO.NET.UniversalDataAccess.rar (19KB)