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:
In the recent posts, I’ve constantly referred to generics. The concept of generics is to simply provide classes (collections) with placeholders for types, and let them work with the given types. In this manner, type-safety can be enforced, and performance will be increased by evading the constant converts from a type to another.
Let’s consider the following example: we have a List collection, and we’d like to assign strings for it. But nothing prevents us from assigning for example an Exception, as a mistake. Even worse, we can create a useless pile of junk from our List collection (there is no reason why we should, the key point here is that we can). Now, by using the List<string> generic class, we can rest assured that our collection can only contain strings. It is beautiful, and even better, it works with everything, even your custom classes.