ADO.NET - Generic Programming Model

We are usually writing code for handling databases, specific to any database like sql server, oracle etc. how we can write code with out specifying the database or code that suite for any database provider?. yes it is possible by using the class DbProviderFactory.

lets see how we can use dbproviderfatory to make database layer for our program.

for example :if we are using sql server and c# then our database specific code will be like this

//NameSpace

using System.Data.SqlClient;

//Code

SqlConnection con = new SqlConnection();

con.ConnectionString = "Your Connection String";

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Your Command Text";

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

we can write a database specific code for mysql like this

//NameSpace

using MySql.Data .MySqlClient ;

//Code

MySqlConnection con = new MySqlConnection();

con.ConnectionString = "Your Connection String";

MySqlCommand cmd = new MySqlCommand();

cmd.CommandText = "Your Command Text";

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

how we can generalize our code, by using dbproviderfatory . to better understand this class you should have an idea about provider factory and abstract factory design pattern.

Just read articles in references for more info

ok. then how we can implement this:

//NameSpace

using System.Data.Common;

//Code

DbProviderFactory oProviderFactory;

DbConnection con;

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

//oProviderFactory = DbProviderFactories.GetFactory("System.Data.MySqlClient") 4 MySQL

Con = oProviderFactory.CreateConnection;

Con.ConnectionString = "Your Connection String";

DbCommand cmd = con.CreateCommand();

cmd.CommandText ="Your Command Text";

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

It is always better to go for generic programming model you can easily switch to any database later.

References:
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
Writing Generic Data Access Code in ASP.NET 2.0 and ADO.NET 2.0
Abstract Factory Design Pattern in ADO.NET 2.0