App.Config and Database Access

This document describes the necessary steps to successfully configure the app.config file that is generated by VBUC during the migration of Visual Basic 6.0 data access technologies to ADO.NET

Description

VB6 offers several ways to access a database, all of them using COM libraries such as ADO, RDO, and DAO. Many of the structures within these packages have their counterparts in the ADO.NET Common architecture, such as Connection and Command; in those cases, most of their functionality is covered through our conversion approach.

The most common structure used to retrieve data from the database is the RecordSet object; in the case of ADO and DAO, and Resultset in RDO which are basically a collection of rows retrieved from the data source using a specific SQL command.

As counterpart on the .NET side, we have the DataSet object which also holds data retrieved from the database.

Differences between the DataSet in .NET and the RecordSet and Resultset

The most important of them, from a functional equivalence standpoint, is its capability to “remember” the current position and make all data-related operations affect that record. The importance of this feature increases when this kind of object is passed (as a parameter) through the different tiers and components of the application.

To accomplish the same functionality in .NET, Mobilize has developed Helper classes to handle all these operations. Internally, this class has the necessary infrastructure to support all database requests such as DataAdapters, using the System.Data.Common namespace.

The RDO and ADODB technologies have as well visual controls to interact with the Recordset and Resultset and to bind visual controls to its data. Those controls ADODC and MSRDC are supported as part of the Data access feature to give more functional equivalence to our clients.

Those controls are supported as well via helper implementations and some data binding patterns are supported and automatically upgraded.

App.config file description

The App.config configuration file is an important component in the migration of VB6 legacy data access technologies to ADO.NET using System.Data.Commons. This file contains the list of the providers that the migrated application is capable to use and which are intended to be used as default. It also contains a definition of the columns in which values are calculated or retrieved automatically from the database when an insert occurs, such as identities or values set by triggers.

<?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="true" databasetype="SQLServer" />
      <!-- MS SQL Server -->
      <add name="Oracle" factorytype="System.Data.OracleClient" 
        isdefault="false" 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>

Configuration section

This tag of the configuration file details the custom configuration section handlers used by the application; in the case of an application converted to ADO.NET using System.Data.Commons.

ConnectionStrings section

This section specifies the connection strings that will be available for the migrated application.

Factories Configuration section

This section details the providers that the migrated application may use and which one will be used as Default Provider.

The following data has to be filled out:

• Name: The name of the provider; this name is the identifier of the provider. If the application needs to use a non-default provider, this is the parameter to be passed to the GetFactory method of the AdoFactorManager class.

• Factorytype: Is the namespace of the factory to be used.

• Isdefault: this marks the factory as default. Hence this factory is the one created when the parameter-less GetFactory method is called.

• Databasetype: this is the type of database that you are trying to interact with; this information is taken by the different recordset helpers to handle many subtle differences between the several database managers. Such as the way to handle the value returned by the database on insertions when there is an identity field involved.

<Factories>
      <add name="SQLServer" factorytype="System.Data.SqlClient" 
            isdefault="true" databasetype="SQLServer" />
      <!-- MS SQL Server -->
      <add name="Oracle" factorytype="System.Data.OracleClient" 
            isdefault="false" 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>

In the above example, the MS SQL Server provider will be used since its attribute isdefault is set to true.

Identity Columns Configuration section

This section details the columns that are calculated by the database when an Insert is executed, such as identities or sequence values set by a trigger.

This section has the following data to be filled:

• Tablename: The name of the table where the column belongs.

• Columnname: The name of the column

• Sequencename: This is the name of the sequence where the column name is taken.

<AdoIdentityColumns>
	<IdentityColumns>
		<add tablename="CR_CustomReport">
			<columns>
				<add columnname="CR_ID" sequencename="CR_ID"/>
			</columns>
		</add>
	</IdentityColumns>
</AdoIdentityColumns>

In the above example, an identity column was configured for table “CR_Customreport”.

How to configure the App.config file in your application

Once the migrated code is generated by the tool, it is necessary to do the following steps to configure the app.config file:

• Add the app.config file to the Visual Studio solution. To do this, right-click on the project name in the Solution Explorer, click on Add, and then “Existing item”. By default, the app.config file is generated on the root folder of the conversion as shown below:

App.config file location

• Open the app.config and change the configuration according to the database that the migrated application will be using. This includes:

  • Typing the application connection strings under the <connectionStrings> section

  • Setting the isdefault attribute to true for the Factory that the migrated application will use.

• Save the changes.