The DbProviderFactories class, as mentioned previously, is responsible for returning concrete implementations of the DbProviderFactory class. In order to get a concrete DbProviderFactory, you first need to know the Invariant Provider Name that you will be targeting. This in itself is not difficult as the Invariant Names are simply System.Data.xxxx (where xxxx is OleDb, SqlClient, OracleClient, Odbc etc.), but instead of just relying on this piece of information, we can have a look at the GetFactoryClasses method which returns a DataTable containing information relating to all providers installed on your machine.
To demonstrate, create a new windows forms project and add a DataGridView to your form. Then use the following code to display the providers installed on your machine:
1 'Retrieves a list of available providers and binds this list to the DataGrid.
2 Dim availableProviders As DataTable = DbProviderFactories.GetFactoryClasses
3 availableProvidersGrid.DataSource = availableProviders |
Nb: This code assumes that you have imported the System.Data.Common namespace.
When you populate the grid, you will be presented with something similar to the following shortened table:
| Name |
Description |
InvariantName |
AssemblyQualifiedName |
| Odbc Data Provider |
.Net Framework Data Provider for Odbc |
System.Data.Odbc |
... |
| OleDb Data Provider |
.Net Framework Data Provider for OleDb |
System.Data.OleDb |
... |
The Name and Description fields are used to describe the provider in human readable terms. The InvariantName field however can be used to programmatically refer to the data provider you wish to target. The AssemblyQualifiedName field contains the fully qualified name of the factory class which is used to create a concrete implementation of the DbProviderFactory class you wish to target (omitted above due to space constraints).
Now that you know what providers are available on your system, the next task is to retrieve a concrete implementation of the required DbProviderFactory. The following example demonstrates how to retrieve the concrete implementation of the OleDbProviderFactory:
1 Dim provider As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
2 MessageBox.Show(provider.GetType.ToString) |
Assuming all went well, you should be presented with a message box stating "System.Data.OleDb.OleDbFactory". This is achieved by asking the DbProviderFactories to return a Factory (via the GetFactory method) based on the InvariantName string for System.Data.OleDb. If you change this InvariantName to System.Data.SqlClient, you should retrieve a message box stating "System.Data.SqlClient.SqlClientFactory". An important point to make at this time is that the InvariantName passed is case sensitive.
As mentioned previously, the GetFactory method also allows you to pass in a DataRow that contains this information via a call to the GetFactoryClasses method. If we combine the two examples above and pass in a DataRow to the GetFactory method you should get similar results. In the following example, I am passing row 2 of the availableProviders DataTable to return an instance of the OleDbFactory:
1 Dim availableProviders As DataTable = DbProviderFactories.GetFactoryClasses
2 Dim provider As DbProviderFactory = DbProviderFactories.GetFactory(availableProviders.Rows(1))
3 MessageBox.Show(provider.GetType.ToString) |
So, we now know how to retrieve the concrete provider factory that we want to target, the next step is in actually communicating with the provider (and hence the data store) without having to worry about which database we are actually using.