C# Generic methods for database driver access in ADO.NET

Question

I have to write a small C# program which will handle at least three differents database vendors (Oracle, Sybase ASE, SqlServer) in a dynamic way. (It will rely on customer choices to choose the database)

I decided to use "pure" managed drivers through ado.net data providers.

But, when I just try connecting, I expected code a la "One line to rule them all", just like JDBC does with :

DriverManager.getConnection(connection_string);

Instead of this, surprised, I have to write for each driver its specific code :

SqlConnection() for SqlServer 

AseConnection() for Sybase

OracleConnection(), etc.

Of course, I should encapsulate -by myself- all of this inside abstract methods and dynamic loadings, but I'm wondering why such a thing doesn't already exist in .net

Mmhhh, I've got the feeling that I'm missing something

Answer 1

Since you have the .Net Provider for he respective database installed on the machine, you can use the DbProviderFactory, for sample:

include

using System.Data.Common;

and try something like this:

// create the provider factory from the namespace provider
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); 

// you could create any other provider factory.. for Oracle, MySql, etc...


// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object, in this case, SqlConnection ...

connection.ConnectionString = "read connection string from somewhere.. .config file for sample";


try
{
   // open connection
   connection.Open();

   // create command to execute queries...
   DbCommand command = connection.CreateCommand(); // create a SqlCommand, OracleCommand etc... depende of the provider factory configured.

   // some logic 
}
catch 
{
}
finally 
{
   // close connection
   connection.Close();
}

To know, what providers your application can find, you can use the DbProviderFactories.GetFactoryClasses() method to get a DataTable with details of every provider installed on the machine.