ADO to ADO.NET Common

Related Content

•   ADO to .NET Native SqlClient
•   How does the VBUC convert ADO
•   RDO to ADO.NET
•   RDO to ADO.Net System.Data.Common
•   RDO Special Conversions

Since the 2.0 version of the .NET Framework, Microsoft incorporated the System.Data.Common namespace, which contains classes intended to be the base for the implementation of all data providers. This architectural decision allows application designers and programmers to use data provider-agnostic patterns on their data access layers.

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 counterpart 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 in the .NET side, we have the DataSet object which also holds data retrieved from the database.

There are differences, though, between the DataSet in .NET and the RecordSet and Resultset; the most important of them, from a functional equivalence standpoint, is it’s capability to “remember” the current position and make all data-related operations affect that record. The importance of this feature increases when these kind of object is passed (as parameter) through the different tiers and components of the application.

To accomplish the same functionality in C# .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.

Helper Class Design

The Helper classes are based on the specialization inheritance model, where the base class defines all common properties, methods, and the basic functionality. The derived classes overrides, adds new specific behavior to model the details. This design decision gives more functionality without breaking the natural ADO.NET architecture since the Recorset Helper classes are Datasets itself.

This structure is being use on the ADO, RDO, DAO common model as detailed on this diagram:

The following code is part of the helper code -which is periodically enhanced; specifically for the open methods that encapsulate the dataset population logic.

 #region Open Operations
        private void OpenRecordset()
        {
            operationFinished = false;
            DbDataAdapter dbAdapter = CreateAdapter(activeConnection);
            dbAdapter.Fill(this);
            operationFinished = true;
            currentView = Tables[0].DefaultView;
            currentView.AllowDelete = true;
            currentView.AllowEdit = true;
            currentView.AllowNew = true;
            if (Tables[0].Rows.Count == 0)
                index = -1;
            else
                MoveFirst();
            newRow = false;
            foundRecordsCounter = 0;
            OnAfterQuery();
        }

        public void Open()
        {
            if (activeConnection == null && activeCommand != null && activeCommand.Connection != null)
               ActiveConnection = activeCommand.Connection;
            else
                throw new InvalidOperationException("The ActiveConnection property must be set before calling this method");
            OpenRecordset();
        }

        public void Open(DbCommand command, String connectionString)
        {
            this.connectionString = connectionString;
            Open(command, CreateConnection());
        }

        public void Open(DbCommand command, DbConnection connection)
        {
            ActiveConnection = connection;
            activeCommand = command;
            Open();
        }

        private void Open(String SQLstr, String connectionString)
        {
            this.connectionString = connectionString;
            CommandType commandType = getCommandType(SQLstr);
            DbCommand command = providerFactory.CreateCommand();
            command.CommandText = SQLstr;
            command.CommandType = commandType;
            Open(command, connectionString);
        }

        #endregion

Configuration file

The Configuration file is an important component that supports this feature. This file contains the list of the providers that the application is capable to use and which are intended to be used as default, as well as the columns which values are calculated or retrieved automatically from the database when an insert occurs, such as identities or values set by triggers.

Configuration Sections

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 Common, it must be as follows:

<configSections>
    <section name="AdoIdentityColumns" type="Artinsoft.VB6.DB.AdoIdentityColumnsConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
    <section name="AdoFactories" type="Artinsoft.VB6.DB. AdoFactoriesConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
  </configSections>

Factories Configuration Section

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

This section has the following data to be filled:

• 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.

<AdoFactories>
    <Factories>
      <add name="SQLServer" factorytype="System.Data.SqlClient" isdefault="true" databasetype=SQLServer/>
      <add name="ODP" factorytype="Oracle.DataAccess.Client" isdefault="false" databasetype=Oracle/>
      <add name="MSOracle" factorytype="System.Data.OracleClient" isdefault="false" databasetype=Oracle />
      <add name="OracleOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Oracle />
      <add name="AccessOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Access />
    </Factories>
  </AdoFactories>

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>  

Complete Configuration file example

The following example shows the configuration of an application which can connect to SQLServer and Oracle through the Microsoft provider as well as the ODP provider, and an OleDb Connection with the SQL Server provider as default.

<?xml version="1.0"?>
<configuration>
<configSections>
    <section name="AdoIdentityColumns" type="Artinsoft.VB6.DB.AdoIdentityColumnsConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
    <section name="AdoFactories" type="Artinsoft.VB6.DB. AdoFactoriesConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
  </configSections>

<AdoFactories>
    <Factories>
      <add name="SQLServer" factorytype="System.Data.SqlClient" isdefault="true" databasetype=SQLServer/>
      <add name="ODP" factorytype="Oracle.DataAccess.Client" isdefault="false" databasetype=Oracle/>
      <add name="MSOracle" factorytype="System.Data.OracleClient" isdefault="false" databasetype=Oracle />
      <add name="OracleOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Oracle />
      <add name="AccessOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Access />
    </Factories>
  </AdoFactories>
   <AdoIdentityColumns>
    <IdentityColumns>
      <add tablename="CR_Customreport">
        <columns>
          <add columnname="CR_ID" sequencename="CR_ID"/>
        </columns>
      </add>
    </IdentityColumns>  
</configuration>

Advantages

• Clearer and more readable C# code; by taking this approach the use of generated temporary variables is avoided and the generated code is a Helper class call.
• Due to the inheritance of the Helper class from DataSet, integration with ADO.NET-compliant data access frameworks will be easier.
• Minimal effort on manual changes for scenarios where the RecordSet is manipulated by more than one function or method, or passed between layers with dependency on the current position between the records.
• The Helper class source code will be included in Mobilize deliverables. This source code will belong to the client, allowing the client to modify it as necessary for new developments.

Limitations

Record accessor classes

These three technologies has different ways to access the data stored on the local in memory data representation structures(Recordset, Resultset); such as Field and Fields classes on ADODB and DAO, and rdoColumn and rdoColumns on RDO.

These classes have particular characteristics since they are used to handle the metadata and the data itself. On the ADO.NET side, the metadata and the data stored on a dataset are handled by two different classes: DataColumn(For metadata), DataRow(For data).

Because of that, these classes are supported as follows:

• The Field and rdoColumn classes, are type is converted as DataColumn to keep the metadata handling capability. Consequently the Fields and rdoColumns classes are converted as DataColumnCollection
• The “Value” property of the Field and rdoColumn classes are supported if and only if the invocation to that property is done under the context of a RecordSet instance; in that case, the conversion is an invocation to the indexer properties of the specific RecordSetHelper Class; otherwise the property is not converted.

Errors and Error Classes

These classes are intended to handle and report all the errors occurred during a database interaction using ADO, DAO and RDO.

These classes are not converted automatically and there is no helper provided to achieve this functionality. A manual solution to this would be to use the provider-specific collections contained in .NET, or to use the Try/Try/Catch pattern to handle the exceptions that database operation could generate.

The configuration file

The configuration file is automatically generated by the Visual Basic Upgrade Companion on the root of the migration solution’s output directory.

Code Optimization

No code optimization patterns for the ADO Common feature have been implemented at this stage of the roadmap

Data binding patterns

There are data binding several patterns to implement data bindings using the visual controls MSRDC and ADODC; at this point of the roadmap we support the design time data binding pattern; it means that if you bind a control on design time with one of those controls the VBUC will generate the code that maintains the data binding functionality working.

ADODB Connection Events

This class exposes several events like BeginTransComplete and ConnectComplete; since this class is converted to the DbConnection class; some events are not supported.

The supported events are ConnectComplete and Disconnect even though a manual change is required to fix a compilation error:

C#

• Find the delegate assignation code:

  this.lConn.Disconnect += new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
  this.lConn.ConnectComplete += new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);

• Change the name of the event like this:

  this.lConn.StateChange += new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
  this.lConn.StateChange += new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);

• Do the same with the delegate des assignation:

  this.lConn.Disconnect -= new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
  this.lConn.ConnectComplete -= new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);

• Change the name of the event like this:

  this.lConn.StateChange -= new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
  this.lConn.StateChange -= new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);

VB .Net

• Find the delegate method handlers:

  Private Sub cnnAccess_ConnectComplete(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.ConnectComplete
  
  Private Sub cnnAccess_Disconnect(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.Disconnect

• Change the handles section as follows:

  Private Sub cnnAccess_ConnectComplete(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.StateChange
  
  Private Sub cnnAccess_Disconnect(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.StateChange

Oracle Data Providers

From the .NET Framework 2.0 Common infrastructure perspectives, there are two native data providers to interact with Oracle; one of them is provided by Microsoft as part of the redistributable package of the Framework (System.Data.OracleClient) and the other one is provided by Oracle through its Oracle Data Provider (ODP).

System.Data.OracleClient

This is a data provider implementation provided by Microsoft, included in the .NET Framework and compatible with the Common architecture.

This implementation tries to parse the SQL statements; issues have been detected with some PL/SQL constructs such as:

Select cust.Code ||’’|| cust.Name as Customer, 
addr.First_Line ||‘ ‘||addr.Second_Line
    From Customer cust, Address addr
    WHERE TO_UPPER(cust.Address_Code (+)) = addr.Address_Code

This statement will throw an internal exception in the data adapter when it it is parsed, because of the combination of the old OUTER JOIN syntax and the call to the TO_UPPER function. Even though, this statement can be rewritten using the ANSI LEFT OUTER JOIN syntax:

    Select cust.Code ||’’|| cust.Name as Customer, 
addr.First_Line ||‘ ‘||addr.Second_Line
    From Customer cust
Left Outer Join Address addr 
ON TO_UPPER(cust.Address_Code) = addr.Address_Code

In summary some SQL statements have to be rewritten using this data adapter implementation.

Oracle Data Provider (ODP)

This is a data provider implementation provided by Oracle, included on its client product; this is ADO Common–compatible since version 10.2.0.2, therefore this conversion approach requires this version or a later version of the ODP software installed on the computers that will run the application.

There are differences between some .NET Framework data types and Oracle database such as:

To avoid this data loss issue, ODP provides a functionality called Safe Type Mapping, which consists of a property in the OracleDataAdapter class that needs to be set with a Hashtable containing the database column name and the type to be assigned to that column value.

This approach avoids data precision loss, however, from the conversion point of view it presents a problem, since the data types of the columns are replaced with the specified mappings, so data operations done through all the application may fail because of the data type substitution.

Examples

Here are some examples of the transformations needed with and without the Helper approach:

Opening a RecordSet

Source Code

Dim mRS As ADODB.Recordset
SQL = ConvertOracleToSqlServer(dbc, SQL)
mRS.Open SQL, dbc, adOpenForwardOnly, adLockReadOnly

Target Code

RecordSetHelper mRS = new RecordSetHelper();
SQL = ConvertOracleToSqlServer(dbc, SQL);
//A direct call to the helper class which takes care of the 
//adapters and command creations 
mRS.Open(SQL, dbc);

Updating a RecordSet

Source Code

Set rsUpdate.ActiveConnection = dbc
rsUpdate.UpdateBatch

Target Code

rsUpdate.ActiveConnection = dbc;
rsUpdate.UpdateBatch();

RecordSet Iteration

Source Code

With prsChildRecordset
  .Filter = adFilterPendingRecords
  If Not .BOF And Not .EOF Then
   .MoveFirst
        Do Until .EOF
      .Fields(psKey).Value = pvValue
       .Update
       .MoveNext
Loop

Target Code

prsChildRecordset.Filter = adFilterPendingRecords;
if(!prsChildRecordset.BOF && ! prsChildRecordset.EOF)
{
      prsChildRecordset.MoveFirst();
      do{
        prsChildRecordset [psKey] = pvValue;
        prsChildRecordset.Update();
        prsChildRecordset.MoveNext();
      }
  while(!prsChildRecordset.EOF)
}