1 Attachment(s)
Flexible data provider class
The class presented here provides developers with simplified code to switch from one database provider to another database provider regardless of the type of provider, unlike System.Data.Common.DbProviderFactories in the .NET Framework that may not provide you with a specific provider such as IBM DB2. Another benefit of this class when used with IDbConnection, IDbCommand and IDataReader interfaces allows developers to code for one data provider then switch to another data provider with very little effort. The main sections of code, which would be targets for change, would be SQL elements, which are specific to a particular provider.
Example, a solution used MS-Access as a backend and has been targeted to migrate to a IBM-DB2 backend. The code presented below needs only two elements changes, provider type and connection string.
Note: The attached project is VS2008 with an MS-Access 2007 database.
MS-Access
Code:
Dim MainConnection As IDbConnection = New DatasourceManager( _
DatasourceManager.EnumProviders.IDB2, _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Demo.accdb").GetConnection()
IBM-DB2
Code:
Dim MainConnection As IDbConnection = New DatasourceManager( _
DatasourceManager.EnumProviders.OLEDB, _
"datasource=Revenue3;DefaultCollection=DEV_DEMO").GetConnection()
Both would use the following to return data
Code:
If Not MainConnection Is Nothing Then
Dim cmd As IDbCommand
cmd = MainConnection.CreateCommand
cmd.CommandText = "SELECT * FROM Customer"
cmd.Connection = MainConnection
cmd.ExecuteNonQuery()
Dim dt As New DataTable
Dim dr As IDataReader = cmd.ExecuteReader
dt.Load(dr)
DataGridView1.DataSource = dt
End If
The next step would be to centralize the provider and connection string perhaps to a property or variable. One option is to store this information in a configuration file. In the attached demo project, I use an XML configuration file to store various configurations along with a class to make it easy to retrieve configuration information. Since good programming means not working with production data the configuration class coupled with the configuration file allow a developer to switch between test, quality assurance and production environments. Since in the attached demonstration I am dealing with one form all variables are marked as private. In a project that most access this information from various modules and forms you could create a Singleton class accessible to all modules and forms that need the connection information.
Code:
Private ConfigurationName As String
Private Sections As New AppSections("ApplicationSettings.xml")
Private DefaultEnvironment As String = ""
Private MainConnectionString As String = ""
Private MainConnection As IDbConnection = Nothing
Private Provider As DatasourceManager.EnumProviders
Private Sub frmMainForm_Load() Handles MyBase.Load
DefaultEnvironment = Sections.DefaultEnvironment
ConfigurationName = Sections.UseConfiguration
MainConnectionString = Sections.Configuration(ConfigurationName, _
DefaultEnvironment).ConnectionString
Provider = Sections.Configuration(ConfigurationName, DefaultEnvironment).Provider
End Sub
To load data
Code:
Private Sub LoadAllData()
MainConnection = New DatasourceManager(Provider, MainConnectionString).GetConnection()
If Not MainConnection Is Nothing Then
Dim cmd As IDbCommand
cmd = MainConnection.CreateCommand
cmd.CommandText = "SELECT FirstName, LastName, City FROM Customers ORDER BY LastName"
cmd.Connection = MainConnection
cmd.ExecuteNonQuery()
Dim dt As New DataTable
Dim dr As System.Data.IDataReader = cmd.ExecuteReader
dt.Load(dr)
DataGridView1.DataSource = dt
End If
End Sub
How the XML file is setting up. Under ApplicationSettings section are the configurations, one for each data provider. Each element under the configuration section store the environment (test/development, QAC and production), provider and several other items demoing that other information may be stored too such as a log file location and name.
HTML Code:
<Configuration Name="DB2_CUSTOMERS">
<Environment Name="Development">
<Connection>datasource=Revenue3;DefaultCollection=DEV_DEMO</Connection>
<Provider>IDB2</Provider>
<ImageFolder/>
<AuditFolder/>
<LogFile/>
</Environment>
<Environment Name="QAC">
<Connection>datasource=Revenue2;DefaultCollection=QAC_DEMO</Connection>
<Provider>IDB2</Provider>
<ImageFolder/>
<AuditFolder/>
<LogFile/>
</Environment>
<Environment Name="Production">
<Connection>datasource=Revenue1;DefaultCollection=PROD_DEMO</Connection>
<Provider>IDB2</Provider>
<ImageFolder/>
<AuditFolder/>
<LogFile/>
</Environment>
</Configuration>
The following indicates which configuration to use and which environment to work out from.
HTML Code:
<Use Default="Development" ConfigurationName="MS_ACCESS_CUSTOMERS"/>
Another thought is if you have to retrieve data from various data source. In the example below each section has an element for the main library from where application data resides along with an element for two other libraries/databases.
HTML Code:
<?xml version="1.0" encoding="utf-8"?>
<ApplicationSettings>
<Configuration Name="Main">
<Environment Name="Development">
<Connection></Connection>
<SecurityLibrary></SecurityLibrary>
<CountryLibrary></CountryLibrary>
<ImageFolder />
<AuditFolder />
<LogFile> </LogFile>
</Environment>
<Environment Name="QAC">
<Connection></Connection>
<SecurityLibrary></SecurityLibrary>
<CountryLibrary></CountryLibrary>
<ImageFolder />
<AuditFolder />
<LogFile></LogFile>
</Environment>
<Environment Name="Production">
<Connection></Connection>
<SecurityLibrary></SecurityLibrary>
<CountryLibrary></CountryLibrary>
<ImageFolder />
<AuditFolder />
<LogFile> </LogFile>
</Environment>
</Configuration>
<Use Default="Development" />
</ApplicationSettings>
How to configure the class DatasourceManager for providers other than OleDbConnection, SqlConnection and OdbcConnection is to review the code within the custom constrain “USE_DB2” which is defined under “My Project”, Compile, Advance compiler option.
Special note, EnumProviders order must match the order in the type array ConnectionTypes in DatasourceManager class.
