Articles → ADO.NET → Writing Generic Data Access Code Using Dbproviderfactory Class

Writing Generic Data Access Code Using Dbproviderfactory Class

In this tutorial I will explain about how to write generic data access code.

Software Requirement

Before you start reading this tutorial you should make sure that visual studio 2005(or above) is installed on your machine.

Prerequisite Knowledge

Before you go ahead, it is required that you should know

How to create projects (or websites) in Visual Studio?
What is ADO.NET?
How to use ADO.NET to connect to different data sources?

What Is A Data Source?

Data source is an object that provides data. For example an XML file, a text file, access database tables are examples of data sources.

What Is A Data Provider In .Net?

In .net data provider is the set of libraries from which we can communicate with the data source. The term communication means that we can get data from the datasource or perform data manipulation operation (insert, update and delete) on the data source.

For example System.Data.dll is a library which helps the .net program to communicate with SQL Server.

What Is Generic Data Access Code?

Generic data access code means the code for accessing data is not changed if the data source changes.

In the real life scenario classes for accessing data changes if data source changes. For example A programmer uses SqlConnection, SqlCommand and SqlDataAdapter classes to get the data from SQL Server (data source). If the data source is changed to MSAccess then the classes will be changed to OledbConnection and OledbCommand.

Let’s go ahead and create data sources. For the sake of this tutorial I will create 2 tables one is SQL server and other in MS Access. Here I will write the same code for inserting the value first in the SQL Server table and then in the MSAcess table.

The table schema of both the tables is same. This is done to ensure that same query will work for both the data sources.

Creation Of Access Data Source

Access database has one table. Please see the figure below for table schema.

Picture showing the table schema of the access database

Click to Enlarge

Creation Of SQL Server Data Source

Like MSAccess SQL server also contains the same table with same schema.

Picture showing the table schema of the SQL server database

Click to Enlarge

Once we have done with the creation of tables, now our job is to add some configuration in web.config file.

Adding Provider And Connection String In Web.Config File

Add the following code in your web.config file.

<appSettings>
	<add key="ProviderName" value="System.Data.SqlClient"/>
	<!--<add key="ProviderName" value="System.Data.OleDb"/>-->
</appSettings>
<connectionStrings>
	<add name="connectionString" connectionString="Data Source=TestServer\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=true;"/>
	<!--<add name="connectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=d:\test.mdb"/>-->
</connectionStrings>

In this code I have commented the code related to MSAccess. The uncommented code is for SQL server.

Writing Generic Code

string commandText = "insert into books(BookName) values('test')";

DbProviderFactory factory = DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["ProviderName"]);

using(DbConnection con = factory.CreateConnection()) {
  using(DbCommand cmd = factory.CreateCommand()) {
    con.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
    cmd.Connection = con;
    cmd.CommandText = commandText;
    con.Open();
    cmd.ExecuteNonQuery();
  }
}

Explanation Of Code

In the above code we are using DbProviderFactory and DbProviderFactories classes to write the generic data access layer. As I have mentioned in the earlier section that we have set the provider name in web.config file, so that we can change it even if the system is deployed.

ConfigurationManager.AppSettings["ProviderName"] in the above code gets the provider. If database is SQL server then the provider should be System.Data.SqlClient, if the database is access then the provider should be System.Data.OleDb.

The DbProviderFactory Class exposes several methods that allow us to create classes for accessing database like CreateConnection(), CreateCommand() etc.

Now run the program.

Output

Picture showing the data inserted into the sql server table

Click to Enlarge

Change Provider And Connection String In Web.Config File

This code given above works well for SQL Server because in the provider and connection string in web.config we have specified the credentials for SQL Server. To make it work for MSAccess, just change the web.config values and it will work fine. So the web.config is changed to

<appSettings>
	<!--<add key="ProviderName" value="System.Data.SqlClient"/>-->
	<add key="ProviderName" value="System.Data.OleDb"/>
</appSettings>
<connectionStrings>
	<!--<add name="connectionString" connectionString="Data Source=TestServer\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=true;"/>-->
	<add name="connectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=d:\test.mdb "/>
</connectionStrings>

And rest of the code remains same. Now let’s run the program.

Output

Picture showing the data inserted into the access table

Click to Enlarge

Conclusion

The code which I have explained is the generic way of accessing data source. If you use this code and the datasource is changed after the application is deployed then you just need to change the connection string and provider in web.config file and this code will work for that data source as well.

Posted By -	Karan Gupta

Posted On -	Monday, February 13, 2012

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**