Note: This answer is relevant if you decide to use basic ADO.NET 2 functionality instead of an ORM (Entity Framework, 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.)
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.)