Write a universal data access component
WillSound (willsound@163.com)
I have received a lot of email to ask me to access different data sources (Data Provider) to access different data sources (Data Provider) to access different data sources in the premise of the Native Data Provider. A young man even asks if I can write some code to specify the data provider (Data Provider) when the program is running.
introduction:
ADO.NET provides different data providers for different data sources, and three universal data providers are OLE DB, SQL, and ODBC, respectively. Use with different data providers is to provide the most powerful and stable data access technologies for different data sources. For example, when you access the Access database, it is the most effective way, but if you use ODBC Data Provider, it is built on the OLE DB DATA Provider, so efficiency will be discounted.
In fact, all data provider classes, such as connect, command, data adapter, and data readers, are inherited from a particular interface. . I hope to write these articles in-depth discussion, but this takes me a lot of time.
In short, the main problem with this article is how to write a generic class to access the data source according to the user's choice according to the user's selection, respectively.
Interface model:
Each data provider implements some interfaces that are defined in the System.Data name space (Namespace). For example, SqlConnection, OLEDBCONNECTION, the And OdbcConnection class is inherited from the IDBConnection interface. Similar to the Connection class, other ADO.NET components like DataAdapter, DataReader, and Command are also inherited from an interface.
You will use these interfaces to implement universal data access classes. I don't plan to write all these features, but I will give you how to expand these features to provide a good idea.
The following code 1 shows an GenericadonetComp class that provides two methods, getConnection and getDataAdapter. Both of these methods are information provided from the user based on the Connection read information, which will return the desired output. Below is the definition of these two methods
Public IDBCONNECTION GETCONNECTION (int ConNNTYPE, STRING CONNSTRING)
Public IDBDataAdapter getDataAdapter (int CONNTYPE, STRING CONNSTRING, STRING SQL)
As you can see, we have replaced the connection with the IDBConnection. The method will return to IDBConnection. From the code 1 below you will see the type type parameter we provide based on the user's running type (Connection Type Argument) To generate SqlConnection, OLEDBCONNECTION, OR or ODBCCONNECTION.
// Code 1
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; using Microsoft.Data.Odbc; namespace GenericDataAccessApp {public class GenericAdoNetComp {private IDbConnection idbConn = null; private IDbDataAdapter idbAdapter = null; private DbDataAdapter dbAdapter = null; private IDataReader iReader = null; public GenericAdoNetComp () {} // GetConnection returns IDbConnection public IDbConnection GetConnection (int connType, string connString) {switch (connType) {case 1: // OleDb Data ProvideridbConn = new OleDbConnection (connString); break; case 2: // Sql Data ProvideridbConn = new SqlConnection (connString); break; case 3: // ODBC Data ProvideridbConn = new OdbcConnection (connString); break; // case 3: // Add your custom data providerdefault: break;} return idbConn;} // GetDataAdapter returns IDbDataAdapterpublic IDbDataAdapter GetDataAdapter (int connType, string connString, string sql) {switch (connType) {case 1: // OleDb Data ProvideridbAdapter = new OleDbDat aAdapter (sql, connString); break; case 2: // Sql Data ProvideridbAdapter = new SqlDataAdapter (sql, connString); break; case 3: // ODBC Data ProvideridbAdapter = new OdbcDataAdapter (sql, connString); break; // case 3: // Add your Custom Data ProviderDefault: Break;}}}} User Application:
Now let's take a look at how to use this class in a Windows application. In order to test, we created a Windows application, the program interface is shown below:
On the form, we put three Radio Buttons controls, a Button control, a group box control, and a DataGrid control.
From the form of the form, we can guess, this program can determine which data provider used according to the user's choice. As you can see above, there are three options on the form, you can choose one, and then click the Connect button. Based on the selected connection type, join the data library gate and fill the data to the DataGrid.
In my app, I defined the following variables.
Private string connString, sql; idbconnection conn = null; idbdataadapter adapter = null; the following is the code of the Connect button, where to create an instance of a GenericadonetComp class, and call its getConnection and getDataAdapter method. One but you provide a DataAdapter, you only need to simply call the Fill and Update methods to read and write data.
private void ConnectBtn_Click (object sender, System.EventArgs e) {GenericAdoNetComp genDP = new GenericAdoNetComp (); sql = "SELECT * FROM Employees"; if (radioButton1.Checked) {connString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c: //Northwind.mdb "; conn = genDP.GetConnection (1, connString); adapter = genDP.GetDataAdapter (1, connString, sql);} else if (radioButton2.Checked) {connString =" Data Source = MCB; Initial catalog = northwind; user ID = sa; password =; "; conn = gendp.getConnection (2, connString); adapter = gendp.getdataadapter (2, connString, sql);} else if (radiobutton3.checked) {// construct your connection string hender = gendp.getConnection (3, connString); adapter = gendp.getdataadapter (3, connString, sql);} try {conn.open (); // Fill a DataSetDataSet DS = New DataSet ( Adapter.Fill (DS); DataGrid1.datasource = DS.TABLES [0] .defaultview;} catch (exception exp) {messagebox.show (exp. measure);} finally {conn.close ();}}
to sum up:
In this article, we discussed how to write a universal data access class. You can extend the functionality of this class with active group components in ADO.NET, I have been trying to make this article easy to understand.
