Classic ADO Conversion to ADO.NET

Description

The VBUC allows converting the Activex Data Objects (classic ADO) to ADO.NET using the System.Data.Common namespace + Mobilize helper classes. This entry talks about this option.

In the Upgrade Options section of the VBUC tool:

Active-X Data Objects (ADO)

ADO is an object model for programmatically accessing, editing, and updating data from a wide variety of data sources through the OLEDB system interfaces.

OLEDB is a set of interfaces that expose data from a variety of sources using COM (Component Object Model).

ADO consists of objects and collections. Its main components are Connection, Command, and Recordset.

ADO to ADO.NET Common

Microsoft provides the System.Data.Common namespace, which contains classes intended to be the base for the implementation of all data providers: ADO.NET.

Many objects from ADO have a counterpart in ADO.NET.

As the counterpart of the Recordset in the .NET side is the System.Data.DataSet: an object which also holds data retrieved from the database.

Oracle Data provider (ODP.NET) in Upgraded projects

Description

Old Visual Basic 6 applications interacting with Oracle databases may rely on the MSDAORA driver (Microsoft OleDB provider for Oracle) in the connection string to establish such communication when that code is converted to .NET. The code migrated to .NET using the Visual Basic Upgrade Companion (VBUC) will keep the same connection string and therefore be using the old MSDAORA driver. But, in .NET we can take advantage of the Oracle Data Provider (ODP.NET) technology developed by Oracle instead.

How VB6 works with MSDAORA

The following VB6 code shows a database connection through the MSDAORA provider:

Dim oConn As ADODB.Connection

Set oConn = New ADODB.Connection

oConn.ConnectionString = "Provider=MSDAORA.1;Password=" & sPassword & ";User ID = " & sUser & "; Data Source= " & sServer & ";Locales Identifier=1033"

oConn.Open

When this code is converted to .NET using the VBUC the code looks like this:

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

oConn.ConnectionString = "Provider=MSDAORA.1;Password=" + sPassword + ";User ID = " + sUser + "; Data Source= " + sServer + ";Locales Identifier=1033";

//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010

oConn.Open();

As you can see, the migrated application is still using the MSDAORA provider.

Using a Native ODP for .NET

If your final goal is taking full advantage of the .NET technology, you may want to replace that provider for the ODP.NET developed by Oracle. In this case, you need to go to the Oracle Data Provider .NET download page and choose the required version of this .NET component.

After installing and configuring the ODP.NET component on your machine you will have to make some minor adjustments to the migrated code:

Add the Oracle.DataAccess.Client factory

Mobilize helper classes to use a DBProviderFactory to create the right ADO.NET object according to the database connection provider in use:

• OleDB providers will use the System.Data.OleDB namespace. This is valid for MS-Access files and any OleDB provider like the MSDAORA one.

• ODBC providers will use the System.Data.ODBC namespace.

• SqlServer can use the System.Data.SqlClient namespace

• Oracle providers for .NET will use Oracle.DataAccess.Client namespace that comes with the ODP.NET installer. If this assembly is not installed, an exception will raise at runtime.

To use the Oracle.DataAccess.Client, find the method LoadDefaultFactorySettings that comes in the AdoFactoryManager class from the UpgradeHelpers.DB.Essentials helper project and uncomment the line:

factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false));

and comment out this line:

factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "System.Data.OracleClient", DatabaseType.Oracle, false));

So, this method should look like this:

private static void LoadDefaultFactorySettings(Dictionary<string, FactoryConfigurationElement> factorySection)

{
    factorySection.Add("Access", new FactoryConfigurationElement("Access", "System.Data.OleDb", DatabaseType.Access, false));
    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, true));
    factorySection.Add("ODBC", new FactoryConfigurationElement("ODBC", "System.Data.Odbc", DatabaseType.Access, false));
}

With these changes, any ADO.NET object (DBCommands, DBConnections, etc) created using the UpgradeHelpers.DB.AdoFactoryManager.GetFactory() will be instantiated using the types defined in the Oracle.DataAccess.Client namespace.

Another approach instead of modifying Mobilize helper classes consists of using the App.Config file of the new .NET project:

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

Correct the Connection String

As illustrated in the VB6 code above, the connection string is using an OLEDB provider (MSDAORA), so we need to change that string to send the parameters required by the ODP.NET provider:

string conStr = "Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="+ sServer + ")(PORT="+ sPort + ")))(CONNECT_DATA=(SID="+ sSID + ")(SERVER=shared)))";

conStr = conStr + ";" + "User Id=" + sUser + ";Password=" + sPassword;

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

oConn.ConnectionString = conStr;

oConn.Open();

ADO Connection object

Description

ADO Connection objects are converted to their .NET System.Data.Common namespace equivalent. However, unlike ADO, .NET allows using different factories or methods to establish a Database Connection. This article is valid for conversion projects using the "ADODB to ADO.NET using System.Data.Common and helper classes" upgrade options of the Visual Basic Upgrade Companion (VBUC) tool.

System.Data.Common.DBConnection

System.Data.Common.DBConnection is the .NET equivalent for the ADODB.Connection object. It's an abstract class that defines the core behavior of database connections and provides a base class for database-specific connections.

.NET Database providers (DBProvider for short) offer their own implementation of the DBConnection class to establish a Database connection that allows working with specific data sources.

A DBProvider is a set of classes and methods that implement interfaces or abstract classes defined in System.Data.Common: System.Data. OLEDB, System.Data.Odbc and System.Data.SqlClient are DBproviders defined in the .NET Framework.

The next VB6 code shows how typically a Connection to a DB is established using classic ADO.

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.ConnectionString = "valid connection string replaced"
conn.CommandTimeout = 900
conn.Open
<some other code>
conn.Close

C# code

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

conn.ConnectionString = "valid connection string replaced";
//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010
conn.Open();
<some other code>
UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);
conn.Close();

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

The above statement creates a DBConnection object using the default factory:

1. ODBC (System.Data.ODBC namespace)

2. OLEDB (System.Data.OLEDB).

3. SqlClient (System.Data.SqlClient)

4. External factories like the Oracle.DataAccess.Client

In this way, the Provider can be changed at any time and only the connection string should be modified.

VB6 applications converted into .NET using the VBUC tool define a connection string that uses an OLEDB provider. By changing the Default factory in the AdoFactoryManager.LoadDefaultFactorySettings() method and the connection string, the migrated application can connect to a SQL Sever Database using a native client (System.Data.SqlClient) instead of an OLEDB driver (as it did in VB6), gaining performance (additional changes may be needed in migrated code to deal with Stored Procedures or other database-related topics).

conn.ConnectionString = "valid connection string replaced";
//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010
conn.Open();

The Connection string is not changed by the VBUC, and it may need to be revised since in .NET connection strings may be different.

UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);
conn.Close();

As the DBConnection object is closed, the TransactionManager.DeEnlist() method, injected by the VBUC, determines if there's a Transaction linked to the connection object and de-enlists it. If there's no Transaction, this method will do nothing.

Known Issues

ADO Connection.Execute to populate a recordset and executing Insert, Update or Delete operations

Description

This entry describes the scenario when the ADODB.Connection.Execute method is used for both populating a recordset and executing update/delete/insert operations when the affected record count is needed.

The issue

VB6 code

Dim conn As ADODB.Connection
conn.ConnectionString = "valid-connection-string"
conn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long

Set rs = conn.Execute("Insert into TableTest (ID,Options,Name) Values(1,'1','Test')", i)
Set rs = conn.Execute("Insert into TableTest (ID,Options,Name) Values(2,'2','Test2')", i)

Set rs = conn.Execute("Update TableTest Set ID=3 Where Options='2'", i)
MsgBox i
Set rs = conn.Execute("Delete from TableTest Where Options = '2'", i)
MsgBox i

C# code

DbConnection conn = null;
conn.ConnectionString = "valid-connection-string";
//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010
conn.Open();
ADORecordSetHelper rs = new ADORecordSetHelper("");
int i = 0;
rs = ADORecordSetHelper.Open("Insert into TableTest (ID,Options,Name) Values(1,'1','Test')", conn, out i, "");
rs = ADORecordSetHelper.Open("Insert into TableTest (ID,Options,Name) Values(2,'2','Test2')", conn, out i, "");

rs = ADORecordSetHelper.Open("Update TableTest Set ID=3 Where Options='2'", conn, out i, "");
MessageBox.Show(i.ToString(), Application.ProductName);
rs = ADORecordSetHelper.Open("Delete from TableTest Where Options = '2'", conn, out i, "");
MessageBox.Show(i.ToString(), Application.ProductName);

In this scenario, the Open() method will not return the real number of rows affected by the insert/delete method. The ADORecordsetHelper uses a DataAdapter.Fill() method to populate the underlying dataset, but it does not include the rows affected by statements that do not return rows (like Update/Delete statements).

Alternatives

1. Add Select @@RowCount to the Insert/Update SQL sentence to execute.

   Note: This is valid for SQLServer DBMS. For Oracle, you can try SQL%ROWCOUNT.

   Copy

    rs = ADORecordSetHelper.Open("Update TableTest Set ID=3 Where Options='2'; Select @@RowCount", conn, out i, "");
    i = rs.Tables[0]; //<-- this will have the @@RowCount value
    MessageBox.Show(i.ToString(), Application.ProductName);
   
    rs = ADORecordSetHelper.Open("Delete from TableTest Where Options = '2'; Select @@RowCount", conn, out i, "");
    i = rs.Tables[0]; //<-- this will have the @@RowCount value
    MessageBox.Show(i.ToString(), Application.ProductName);

   Bonus: @@RowCount not working? Check this for additional info about how @@RowCount works in nested statements.

   Original code (nested SQL Statements):

   Copy

    INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
    IF @@ROWCOUNT = 0
    INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
    IF @@ROWCOUNT = 0
    INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2

   Corrected Code (nested SQL Statements):

   Because the @@rowcount can only be checked once, the above script will skip one insert but then execute the next one. You must properly nest the IFs:

   Copy

    INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
    IF @@ROWCOUNT = 0
    begin
        INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
        IF @@ROWCOUNT = 0
        begin
            INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
        end
    end

2. Change the generated code

   C# suggestion

   Copy

    DbConnection conn = null;
    conn.ConnectionString = "valid-connection-string";
    //UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010
    conn.Open();
    ADORecordSetHelper rs = new ADORecordSetHelper("");
    int i = 0;
    rs = ADORecordSetHelper.Open("Insert into TableTest (ID,Options,Name) Values(1,'1','Test')", conn, out i, "");
    rs = ADORecordSetHelper.Open("Insert into TableTest (ID,Options,Name) Values(2,'2','Test2')", conn, out i, "");
   
    string str = "Update TableTest Set ID=3 Where Options='2'";  
    if(str.Trim().StartsWith("delete", StringComparison.InvariantCultureIgnoreCase) || str.Trim().StartsWith("update", StringComparison.InvariantCultureIgnoreCase))
    {
        DbCommand cmd = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand();
        cmd.Connection = conn;
        UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(cmd);
        UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(cmd);
        i = cmd.ExecuteNonQuery();
    }
    else
    {
        rs = ADORecordSetHelper.Open(str, conn, out i, "");​
    }
    MessageBox.Show(i.ToString(), Application.ProductName);
   
    str = "Delete from TableTest Where Options = '2'";  
    if(str.Trim().StartsWith("delete", StringComparison.InvariantCultureIgnoreCase) || str.Trim().StartsWith("update", StringComparison.InvariantCultureIgnoreCase))
    {
        DbCommand cmd = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand();
        cmd.Connection = conn;
        UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(cmd);
        UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(cmd);
        i = cmd.ExecuteNonQuery();
    }
    else
    {
        rs = ADORecordSetHelper.Open(str, conn, out i, "");​
    }
    MessageBox.Show(i.ToString(), Application.ProductName);

Summary

Using Connection.Execute to populate a recordset and at the same time getting the number of affected rows will not work in converted code when the SQL statement is a Delete/Update/Insert and Select @@RowCount (or its equivalent) is not part of the SQL sentence.

ADO Command.ActiveConnection using a connection string

Description

This entry describes the scenario when the ActiveConnection property of an ADO Command is initialized using a connection string rather than a connection object.

The issue

In this specific scenario, the VBUC creates a new connection object associated with the connection string, but this object is not opened, causing an error when the code is executed.

VB6 Code

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command​
cmd.CommandText = "select * from TableTest where Options = 3"​
cmd.ActiveConnection = "a-valid-connection-string"​
cmd.Execute  
Set cmd = Nothing

C# Code

DbCommand cmd = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand();
cmd.CommandText = "select * from TableTest where Options = 3";
cmd.Connection = ADORecordSetHelper.CreateConnetion("a-valid-connection-string");
cmd.Connection.Open();
UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(cmd);
UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(cmd);
cmd.ExecuteNonQuery();
cmd.Connection.Close();

Two lines of code were added: cmd.Connection.Open(); and cmd.Connection.Close();

The first one is mandatory to avoid the runtime exception and the second is needed to avoid having database connections opened.

Summary

Without the above manual changes, the C# code will not get the same behavior, therefore opening the connection object is needed, and closing mandatory to avoid leaving unused database connections opened.

Database Transactions

Description

Classic ADO allows creating Database Transactions to save or cancel a series of changes made to the source data as a single unit.

BeginTrans, CommitTrans, and RollbackTrans are methods provided by the ADO Connection object to create and handle Database Transactions.

TransactionManager

In .NET, DBConnection objects can handle Database Transactions with very important differences to classic ADO.

The DBConnection.BeginTransaction() method returns a Transaction object that must be used for Commit or Rollback operations. Unlike VB6, the .NET DBConnection does not keep a reference to the current Transaction.

To mitigate this different behavior, the VBUC introduces the TransactionManager class that manages all active Database Transactions created by DBConnection objects.

Let's analyze how the TransactionManager works by reviewing the following code:

VB6 Code

conn.BeginTrans

    On Error GoTo ErrTransaction

    Dim rs2 As Recordset
    Set rs2 = New Recordset
    rs2.Open "select * from providers where 1=2", conn

    rs2.AddNew
    rs2!ID = 123
    rs2!Name = "MyProvider"
    rs2.Update

    conn.CommitTrans
    GoTo ExitMethod

ErrTransaction:
    conn.RollbackTrans

ExitMethod:
   conn.Close

C# Code

UpgradeHelpers.DB.TransactionManager.Enlist(conn.BeginTransaction());

try
{

    ADORecordSetHelper rs2 = null;
    rs2 = new ADORecordSetHelper("");
    rs2.Open("select * from providers where 1=2", conn);

    rs2.AddNew();
    rs2["ID"] = 123;
    rs2["Name"] = "MyProvider";
    rs2.Update();

    UpgradeHelpers.DB.TransactionManager.Commit(conn);
}
catch
{

    UpgradeHelpers.DB.TransactionManager.Rollback(conn);
}
finally
{
    UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);
    conn.Close();
}

UpgradeHelpers.DB.TransactionManager.Enlist(conn.BeginTransaction());

The Connection object creates a Transaction and its info is stored in an internal Dictionary of the TransactionManager (the Connection object is used as a key)

UpgradeHelpers.DB.TransactionManager.Commit(conn);

Commits the Transaction associated (if any) to the specified connection. Once the Commit is performed the transaction is DeEnlisted

UpgradeHelpers.DB.TransactionManager.Rollback(conn);

Rolls back the Transaction associated (if any) to the specified connection. Once the Rollback is performed the connection is DeEnlisted.

UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);

Removes the Transaction object associated (if any) to the specified connection from the TransactionManager.

Connection objects executing SQL Statements

Description

This entry covers the case when Classic ADO Connection objects execute SQL statements in VB6 and how it's migrated by the VBUC tool.

VB6 code

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.ConnectionString = "valid connection string replaced"
'Connection with comandtimeout and executing query
conn.CommandTimeout = 900
conn.Open
conn.Execute "select * from providers where 1=2"
conn.Close

C# code

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

conn.ConnectionString = "valid connection string replaced";
//Connection with comandtimeout and executing query
UpgradeHelpers.DB.DbConnectionHelper.SetCommandTimeOut(conn, 900);
//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. More Information: https://www.mobilize.net/vbtonet/ewis/ewi7010
conn.Open();
DbCommand TempCommand = null;
TempCommand = conn.CreateCommand();
UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(TempCommand);
TempCommand.CommandText = "select * from providers where 1=2";
UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(TempCommand);
TempCommand.ExecuteNonQuery();
UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);
conn.Close();

Let's review some important information about the way that code was converted.

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

The above sentence creates a DBConnection object using the desired factory. By default System.Data. OLEDB

UpgradeHelpers.DB.DbConnectionHelper.SetCommandTimeOut(conn, 900);

DBConnection objects don't support the CommandTimeOut property. This helper method creates an internal structure to indicate the CommandTimeOut set for each DBConnection object.

DbCommand TempCommand = null;
TempCommand = conn.CreateCommand();
UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(TempCommand);
TempCommand.CommandText = "select * from providers where 1=2";
UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(TempCommand);
TempCommand.ExecuteNonQuery();

In .NET, DBConnection objects do not execute SQL commands; DBCommand objects do that. To solve this, the VBUC injects additional lines of code to create a DBCommand object to execute the CommandText.

In the above code, TempCommand is created by using the DBConnection.CreateCommand() method. Later, the TempCommand.CommandText property will have the SQL statement to execute, while TempCommand.ExecuteNonQuery() executes the statement.

Two additional lines are injected by the VBUC:

UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(TempCommand);

This line, injected by the VBUC, retrieves the CommandTimeOut value set for the Connection Object and assigns that value to the DBCommand.CommandTimeOut property.

UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(TempCommand);

If the Connection object started a Database Transaction, then this line, injected by the VBUC, will link the Transaction to the DBCommand object. If there's no Transaction associated to the Connection object, then this method will do nothing.

UpgradeHelpers.DB.TransactionManager.DeEnlist(conn);
conn.Close();

As the DBConnection object is closed, the DeEnlist() method, injected by the VBUC, determines if there's a Transaction linked to the connection object and De-enlists it. If there's no Transaction, this method will do nothing.

ADORecordSet Helper

Description

The VBUC converts the Activex Data Objects (classic ADO) to a .NET alternative using the System.Data.Common namespace + Mobilize helper classes. Specifically, the Classic ADO Recordset object is converted to a Mobilize class named ADORecordsetHelper. This entry talks about this.

Classic ADO Recordset

As stated before, ADO is an object model for programmatically accessing, editing, and updating data from a wide variety of data sources through OLEDB system interfaces.

For the Recordset counterpart in the .NET side is the System.Data.DataSet: an object which also holds data retrieved from the database.

However, there are differences between them:

• The capability of remembering the current position and performing all data-related operations on that record.

• The way to access the data: the ADO Field allows handling of both data and metadata. In ADO .NET this is handled by two different classes: System.Data.DataColumn for metadata and System.Data.DataRow for data.

ADORecordSetHelper

Mobilize provides the ADORecordSetHelper to accomplish the same functionality using the System.Data.Common namespace.

Inherits from the .NET System.Data.DataSet class and provides a set of properties and methods available in the RecordSet class in VB6, thus allowing a more direct migration of the VB6 code to .NET.

It follows the specialization inheritance model. The base class (RecordSetHelper) defines all common properties, methods, and basic functionality. The derived class (ADORecordSetHelper) overrides the properties and methods and adds specific behavior. The ADO .NET architecture is preserved because it inherits from DataSet.

Advantages

• Reduces manual work to achieve equivalence.

• Supports any kind of database engine.

• The migrated code is clearer and more readable since there are no additional variables or new code, just a call to the helper class.

• Mobilize .NET provides the source code in .NET as part of the migrated application.

• The client can modify these in any way they want once the migration process has been completed.

Differences

• ADO Recordsets are usually data-connected, while DataSets are always an in-memory representation of data thus disconnected from the database. Data manipulation is done through DataAdapters in .NET.

  1. For example, a query that retrieves data selects the values on-demand on the former, and queries all the records on the latter.

  2. On large queries, performance differences might occur.

• ADO Recordsets support updates when the source command contains a join. It is not supported with DataSets. In these cases, a manual update is needed.

• Some ADO Connection events like BeginTransComplete and ConnectComplete are not supported by System.Data.Common.DbConnection.

• DataSets have no current record pointer. For-Each-loops statements should be used to move through the data. Recordsets use pointers to move through them. Note: The Mobilize RecordsetHelper class provides the logic to have a current record pointer and allow MoveNext, MoveFirst methods to achieve the same functionality as in VB6.

Known Differences

ADORecordSetHelper update failed

Description

Sometimes a RecordSet will have the following error when trying to update the values when the method Update or MoveNext are used.

Example

Let's assume we have the following table in a database.

In this case, we have a user table, and its Primary Key consists of two columns: id and full_name.

Let's assume we have VB6 code that updates the role_id according to a SQL query.

VB6

set objRec = Server.CreateObject("ADODB.Recordset")
SQLQuery = "select role_id from users where country_code = 506"
'... setup RecordSet...
do while not objRec.EOF
  objRec("role_id") = 3
  objRec.Update()
  objRec.MoveNext()
loop

The migrated code would look something like this:

.NET

ADORecordSetHelper objRec = new ADORecordSetHelper("");
string SQLQuery = "select role_id from users where country_code = 506";
// ... (setup RecordSet)
while(!objRec.EOF)
{
    objRec["role_id"] = 3;
    objRec.Update();
    objRec.MoveNext();
}

It is very likely that this migrated code will have problems when calling the Update method.

Why does this happen?

When you create a recordset using a query in VBScript, it will retrieve a RowID for each DataRow. This allows VB6 to know which row to apply an update in the database. However, in .NET, this RowID isn't retrieved so it's necessary to modify the query to get all the primary keys of the table you are updating. This way, the RecordSet will know which row needs to be updated in the database.

Solution

As mentioned before, it is necessary to modify the SQL query to retrieve all the primary keys that are part of the table. Since id and full_name are part of the primary key of the user's table, it's necessary to retrieve them as well.

.NET

ADORecordSetHelper objRec = new ADORecordSetHelper("");
string SQLQuery = "select role_id, id, full_name  from users where country_code = 506";
// ... setup RecordSet...
while(!objRec.EOF)
{
    objRec["role_id"] = 3;
    objRec.Update();
    objRec.MoveNext();
}

Disconnected Recordsets - Issues in .NET

Summary

As indicated in previous sections, ADO Recordsets are usually data-connected, while .NET DataSets are always an in-memory representation of data thus disconnected from the database. Because of this, VB6 programmers may disconnect Recordsets from the Database Connection object to reduce the number of active connections, and reconnect when a database operation is needed.

This entry covers known issues in the ADORecordSetHelper when the ActiveConnection is set to null.

ADORecordSetHelper.AddNew()

The AddNew() method requires an active Database connection object to set columns' default values for the new row. If the ConnectionString is empty or null, the ADORecordSetHelper private method AssignDefaultValues(DataRow dbRow) will not set default values.

This situation will be visible when Update() or UpdateBatch() methods are executed to save data to the DataBase and default values are expected.

What to do

• Do not remove the ActiveConnection object from the ADORecordsetHelper if database operations are executed. This will not affect the number of database connections active.

• Or, restore the ActiveConnection object to the ADORecordsetHelper instance before calling the AddNew() method or any database operation.

Not Upgraded Elements

Description

Given the differences between classic ADO RecordSet and the ADO.NET DataSet (the RecordsetHelper is an extension of that .NET class), there are some Recordset class elements in the Recordset that don't have an equivalent in .NET.

This section covers those non-supported properties, methods, or events (PMEs) and provides more information about how to deal with related EWIs (Errors, Warning, and Issues) in migrated code.

ADODB.Property property was not upgraded. - "Preserve On Commit"

VB6 Syntax

<recordset>.Properties("Preserve on Commit") [= value]

Preserve on Commit applies only to local transactions.

Source: https://www.labath.org/docs/sys/mssql2000/adosql/adoprg04_20hl.htm

C# Conversion

//UPGRADE_ISSUE: (2064) ADODB.Recordset property lvrSEvents.Properties was not upgraded. More Information: https://www.mobilize.net/vbtonet/ewis/ewi2064
//UPGRADE_ISSUE: (2064) ADODB.Properties property Properties.Item was not upgraded. More Information: https://www.mobilize.net/vbtonet/ewis/ewi2064
//UPGRADE_ISSUE: (2064) ADODB.Property property Properties.Value was not upgraded. More Information: https://www.mobilize.net/vbtonet/ewis/ewi2064
//<recodsethelper>.getProperties().Item("Preserve On Commit").setValue(true);

Comments

The "Preserve on Commit" property specifies whether a recordset remains active after a transaction is committed. This is valid in Classic ADO and VB6. However, in .NET, this property is not supported. DataSets (RecordsetHelper is an extension of this) don't support this: The data is still maintained in the memory after data is committed or aborted.

Action

Keep commented this line of code. If a wrong behavior is observed when comparing the execution of this against the VB6 then some additional steps may be needed to execute, but it's unlikely to occur.