Note: This answer is relevant if you decide to use basic ADO.NET 2 functionality instead of an ORM (such as Entity Framework or NHibernate) or LINQ to SQL.
Let's assume you've got a connection string defined in your app.config:
<connectionStrings>
<add name="SomeConnection"
providerName="System.Data.SqlClient"
connectionString="..." />
</connectionStrings>
Notice the presence of the providerName attribute and its value. You could also put in a value for another DB provider, e.g. System.Data.SQLite.
(Note that non-standard providers, i.e. those that are not in the .NET Framework by default, need to be registered first, either in app.config or in the client machine's machine.config.)
Now, you can work with the specified database in a completely provider-agnostic fashion as follows:
using System.Configuration; // for ConfigurationManager
using System.Data; // for all interface types
using System.Data.Common; // for DbProviderFactories
var cs = ConfigurationManager.ConnectionStrings["SomeConnection"];
// ^^^^^^^^^^^^^^^^
var factory = DbProviderFactories.GetFactory(cs.ProviderName);
// ^^^^^^^^^^^^^^^
using (IDbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = cs.ConnectionString;
// ^^^^^^^^^^^^^^^^^^^
connection.Open();
try
{
using (IDbCommand command = connection.CreateCommand())
{
... // do something with the database
}
}
finally
{
connection.Close();
}
}
Note how this code only works with interface types. The only place where you indicate a particular DB provider is through the providerName attribute value in the app.config file. (I've marked all the places where a setting from app.config is taken with ^^^s.)
Further reading:
