How to implement database vendor independent data access mechanism in C#
Access data from any database using Dbprovider
In this article we will see how to
implement uniform data access mechanism to support various database platforms. Before
going to discussion we will discuss little importance of that and few related
topic like factory design pattern.
Let’s discuss what the advantages of
uniform data access mechanism. OK, think about a product which your company is
developing for their customer. Now, in requirement analysis part customer did
not specify their preferred database software.
If you write code for SQLServer in
project and after that if client wanted to use Oracle? –To solve this problem
it is needed to implement data access mechanism in platform independent
Now, what is the fashion? Here we will
use concept of factory design pattern. In .NET framework one nice Interface
called Dbprovider has given to do this job.
In factory design pattern, we can
create object in run time. In run time user will supply some information
to factory class and depending on supplied information factory class will
Let’s see below code to implement same.
public partial class Form1 : Form
private void Form1_Load(object sender, EventArgs e)
private void button1_Click_1(object sender, EventArgs e)
DbProviderFactory provider = null;
DbConnection con = null;
DbCommand cmd = null;
DbDataReader rdr = null;
DataTable dt = new DataTable();
provider.CreateConnection(); //Create Connection according to Connection Class
con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE;Initial
provider.CreateCommand(); //Create command according to Provider
cmd.CommandText = "select * from name";
cmd.CommandType = CommandType.Text;
if (con.State == ConnectionState.Closed ||
con.State == ConnectionState.Broken)
cmd.Connection = con;
if (dt.Rows.Count > 0)
this.dataGridView1.DataSource = dt;
this.dataGridView1.DataSource = null;
This is the whole code of a windows
form. I will suggest you to look at button’s click event. If you look closely
you can find we are not creating any ADO.NET object for any specific database
provider. All objects are getting created depending on database provider.
Here we are supplying provider string
of SQLServer class. And if you want to access data from any other database just
change the provider name and it will work perfectly. In below the code portion
need to change.
Here is sample output:
Here we have seen how to access data from database using Dbprovider factory class. If you want to implement data save mechanism you can implement it in same way.