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.