I have another post about this topic, you can find it under the title of Create a Vendor-Independent Data Access Class. But let’s be clearer: the System.Data.Common namespace provides a set of interfaces and classes to build your provider-agnostic data access class. Using these classes, you can even build new data providers.
The .NET Framework comes with four providers installed and configured in Machine.config. These are OdbcProvider, OledbProvider, SqlClientProvider and OracleClientProvider. When you want to connect to a data source, you must at least know its type name, and have a valid connection string. Without these, you’re stuck. The good news is that you can store both of these in the same place: yes, in a .config file, in the connectionStrings section. An example:
<add name=”myString” connectionString=”Data Source=.SQLEXPRESS; Initial Catalog=Northwind; Integrated Security=true;” provider=”System.Data.SqlClient” />
Then you should instantiate a DbProviderFactory object, generate a DbConnection with it, and open the connection through that. The code is the following: