Access data from any database using Dbprovider class in .net

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=Nayab\\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: