Create an Application Using Any Database Without Changing a Single Line of Code

I am back with an important idea and to begin I want to describe the situation. Imagine that you have developed an application using a Microsoft Access database and you have deployed it. What if you change your mind about your database, instead of Access you would like to use a SQL Server database. Wow! Well don't panic, the simple solution is to use the class "DbProviderFactory" from the namespace "System.Data.Common", so instead of fixing your provider you specify which provider you want to use at runtime; that's wonderful.

The code is the following:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.Data.Common;  
  10. namespace DDbProviderFactory  
  11. {  
  12.     public partial class Form1 : Form  
  13.     {  
  14.         //a methode receiving the provider we intend to use at run time MyProvider  
  15.         // and the connection string to the databde we are targeting  
  16.         public DataSet loadData(string Myprovider, string conStr)  
  17.         {  
  18.             DbProviderFactory connecteur; // create an instance of DbProviderFactory  
  19.             DbConnection conx; // create an instance of a connection whih we dont  
  20.             //know the driver yet it could be ole or odbc in this example  
  21.             DataSet ds = new DataSet();// dataset to collect data  
  22.             connecteur = DbProviderFactories.GetFactory(Myprovider); // here the DbProviderFactory gonna know  
  23.             // it's provider we use ole or odbc in this example  
  24.             conx = connecteur.CreateConnection(); // instance a connection depending on the provider  
  25.             conx.ConnectionString = conStr;     // assign the conection string  
  26.             DbCommand command = connecteur.CreateCommand(); // create a dbcommand depending on provider we specifyed MyProvider  
  27.             DbDataAdapter adap = connecteur.CreateDataAdapter(); // create a dataAdapter  
  28.             command.CommandText = "select * from student"// assign the query  
  29.             command.Connection = conx; // assign the connectio,  
  30.             adap.SelectCommand = command;  
  31.             adap.Fill(ds, "student"); // collect data  
  32.             return ds;  
  33.         }  
  34.         public Form1()  
  35.         {  
  36.             InitializeComponent();  
  37.             txconnection.Items.Add(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + Application.StartupPath);  
  38.         }  
  39.         private void Form1_Load(object sender, EventArgs e)  
  40.         {  
  41.             DataSet ds = new DataSet();  
  42.             txprovider.SelectedIndex = 0;  
  43.             txconnection.SelectedIndex = 0;  
  44.             ds = loadData(txprovider.Text, txconnection.Text);  
  45.             dataGridView1.DataSource = ds.Tables["student"];  
  46.         }  
  47.         private void txprovider_SelectedIndexChanged(object sender, EventArgs e)  
  48.         {  
  49.             txconnection.SelectedIndex = txprovider.SelectedIndex;  
  50.             DataSet ds = new DataSet();  
  51.             ds = loadData(txprovider.Text, txconnection.Text);  
  52.             dataGridView1.DataSource = ds.Tables["student"];  
  53.         }  
  54.     }  
  55. }   

The UI

Image1.jpg

So as you can see the same code works with two different databases but with one condition, the database structure is the same, be sure of that.

The source will be included, try to look much more to the "loadData()" method the secret lies there.

I was in front my computer for two hours just to make that look easy, I hope you find it like that, see you soon and try to leave a comment.