Configure default Factory Database provider

Summary

Visual Basic 6 applications to be migrated use either OLEDB or ODBC drivers to open DataBase connections. To provide a database-provider-independent model, the VBUC ADO.NET code is based on the use of the "factory" design pattern. In this way upgraded applications to .NET could use database provider inheriting from the System.Data.Common namespace like OLEDB and ODBC ones. This post covers how to set or change the factory database provider in the upgraded code.

The AdoFactoryManager

"ADODB to ADO.NET conversion using System.Data.Common and Helper Classes" upgrade option provides a mechanism to upgrade ADODB classes to a independed database provider mechanism structure of suppport classes.

ADODB Upgrade Option

This will generate code that will open database connections independently of the driver used by the original application to connect to database. Once the application is upgraded, the OLEDB and ODBC drivers used by the VB6 application could be used as well, nevertheless, it's possible that the connection string has to be manually fixed.

Please visit ConnectionStrings.com for further info.

Other drivers rather than OLEDB and ODBC ones, while extending from the System.Data.Common .NET namespace could be used. To achieve this, the migrated code depends on DBProviderFactory .NET class, that represents a set of methods for creating instances of a provider's implementation of the data source classes.

Mobilize ADO.NET helper classes provide the AdoFactoryManager class that handles information about the factory used to create database connections. By default, OLEDB factory (System.Data.OleDb namespace) is used for this, but it can be change.

For instance, the below code in VB6 creates a ADODB.Connection object and open a database connection using a OLEDB driver.

Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb;Data Source=<SQLSERVER>;Initial Catalog=<DATABASE>;Integrated Security=SSPI;"

conn.Open

The C# migrated code will look like:

DbConnection conn = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection();

conn.ConnectionString = "Provider=MSOLEDBSQL;Server=<SQLSERVER>;Database=<DATABASE>;Integrated Security=SSPI;";

//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://docs.mobilize.net/vbuc/ewis#7010
conn.Open();

A generic DBConnection object is created and a default Factory is used at runtime to create the final object datatype by using UpgradeHelpers.DB.AdoFactoryManager.GetFactory() method.

In AdoFactoryManager class, LoadDefaultFactorySettings method, defines standard Factories available default one to be used along the application. As you can see below, OLEDB Database Factory is the one marked by default, but it can be changed by setting True/False value as wanted.

private static void LoadDefaultFactorySettings(Dictionary<string, FactoryConfigurationElement> factorySection)
{
     factorySection.Add("Access", new FactoryConfigurationElement("Access", "System.Data.OleDb", DatabaseType.Access, true));
     factorySection.Add("SQLServer", new FactoryConfigurationElement("SQLServer", "System.Data.SqlClient", DatabaseType.SQLServer, false));
     //New Changes
     //factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false));
     factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "System.Data.OracleClient", DatabaseType.Oracle, false));
     factorySection.Add("ODBC", new FactoryConfigurationElement("ODBC", "System.Data.Odbc", DatabaseType.Access, false));
}

Using more than one Database provided in the migrated application

If more than one Database Factory should be used in the same application (like ODBC or OLEDB), the initial recommendation is let's define a single provider; it will imply modifying Connection strings.

But if the above suggestion is not possible, then the upgraded code can be modified to use the wanted factory. For instance, in the below C# code, "ODBC" factory is forced to be used nevertheless the default factory:

DbConnection conn = UpgradeHelpers.DB.AdoFactoryManager.GetFactory("ODBC").CreateConnection();

conn.ConnectionString = "Provider=MSOLEDBSQL;Server=<SQLSERVER>;Database=<DATABASE>;Integrated Security=SSPI;";

//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://docs.mobilize.net/vbuc/ewis#7010
conn.Open();

You can use "Access" for OLEDB or "SQLServer" for .NET SQLClient.

Configuring Default Factory in App.Config

The .NET application App.Config file could be used to define default Database Factory to be used to open database connections. You can use the following draft:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="AdoFactories" type="UpgradeHelpers.DB.AdoFactoriesConfigurationSection, UpgradeHelpers.DB.Essentials" allowExeDefinition="MachineToApplication" allowLocation="true" />
    <section name="AdoIdentityColumns" type="UpgradeHelpers.DB.AdoIdentityColumnsConfigurationSection, UpgradeHelpers.DB.Essentials" allowExeDefinition="MachineToApplication" allowLocation="true" />
  </configSections>
  <connectionStrings>
  </connectionStrings>
  <AdoFactories>
    <!--
    
    The following section declares some of the most common factories. It can be modified in order to accomplish your needs.
	The factory declaration with the "isdefault" attribute set to true will be used by the upgraded application as the current provider factory.
    
    The database type attribute can take one of the following values
      * SQLServer: when the application interacts wiht Ms SQL Server
      * Oracle: when the application interacts wiht Oracle
      * Access: when the application interacts wiht Ms Access
      * Undefined: when none of the previous is being used
    
    -->
    <Factories>
      **<add name="SQLServer" factorytype="System.Data.SqlClient" isdefault="false" databasetype="SQLServer" />
      <!-- MS SQL Server -->
      <add name="Oracle" factorytype="System.Data.OracleClient" isdefault="true" databasetype="Oracle" />
      <!-- Oracle -->
      <add name="Oledb" factorytype="System.Data.OleDb" isdefault="false" databasetype="Access" />
      <!-- Any database through Oledb -->
      <add name="ODBC" factorytype="System.Data.Odbc" isdefault="false" databasetype="Access" />**
      <!-- Any database through ODBC -->
    </Factories>
  </AdoFactories>
  <AdoIdentityColumns>
  </AdoIdentityColumns>
</configuration>

In the factories section you can define the factory to use for each type of Database. If the application uses an Oracle driver just change the isdefault attribute to true and set to false the isdefault attributes of all other factories. In any other case, just change the respective isdefault flag to true and set the isdefault flag of other providers to false.