Access data from any database using Dbprovider class

Sourav.Kayal
Posted by in C# category on for Beginner level | Points: 250 | Views : 2804 red flag
Rating: 5 out of 5  
 1 vote(s)

How to implement database vendor independent data access mechanism in C#

Access data from any database using Dbprovider class

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 fashion.

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 create object.

Let’s see below code to implement same.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using PIHelper;
using System.Data.Common;
 
namespace WindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        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 =DbProviderFactories.GetFactory("System.Data.SqlClient");
            con = provider.CreateConnection();   //Create Connection according to Connection Class
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE;Initial Catalog=test;Integrated Security=True";
            cmd = provider.CreateCommand();   //Create command according to Provider
       
            try
            {
                cmd.CommandText = "select * from name";
                cmd.CommandType = CommandType.Text;
                if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
                {
                    con.Open();
                    cmd.Connection = con;
                    using(con)
                    {
                        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        dt.Load(rdr);
 
                        if (dt.Rows.Count > 0)
                        {
                            //return dt;
                            this.dataGridView1.DataSource = dt;
                        }
                        else
                        {
                            this.dataGridView1.DataSource = null;
                        }
                         
                    }
                }
            }
        
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                //trn.Rollback();
                con.Dispose();
                cmd.Dispose();
            }
           
        }
       
    }
}

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.

DbProviderFactories.GetFactory("System.Data.SqlClient");

Here is sample output:


Conclusion:

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.



Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Posted by: Nandkishorre on: 8/5/2013 | Points: 25
Hi Sourav,

I face one problem when using DBProviderFactory class. which name space is used for DBProviderFactory class. it is not coming in my application. Could you help me how to get DBProviderFactory class and PIHelper namespace ?


Regards
Nanda Kishore.CH
Posted by: Sourav.Kayal on: 8/5/2013 | Points: 25
Hi Nanda,
Just use System.Data.common in your application. then you cab able to use dbProviderFactory classs
Posted by: Prabhukiran345 on: 8/7/2013 | Points: 25
Hi Sourav,

I am getting error at "DbProviderFactories". I have added system.Data.Common; using System.Data; using System.Collections.Generic; too..
Can u help me out why this is coming.

Thanks,
Prabhu Kiran

Login to post response

Comment using Facebook(Author doesn't get notification)