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:
String provider = ConfigurationManager.ConnectionStrings[“myString”].ConnectionString.Provider;
String connection = ConfigurationManager.ConnectionStrings[“myString”].ConnectionString;
DbProviderFactory myFactory = DbProviderFactory.GetFactory(provider);
DbConnection theConnection = myFactory.CreateConnection();
And we’re done. If you change the provider name and the connection string to any valid value, it will work as expected.
Some details about the System.Data.Common namespace. There are interfaces for each ADO.NET object, such as Connection, Command, DataAdapter, etc. They are called IDb… and they are living in the System.Data namespace. The generic (not that generic!) data access classes are called Db…, and they are living in System.Data.Common. They gain their functionality by implementing the generic IDb… interfaces. The provider-specific data objects are in turn inherit from the generic Db… classes.
It’s not that confusing, and it would be very impressive to show it on a graph, but I think there are many books filled with nice graphs about this topic.