Microsoft.VisualBasic Uses

Visual Basic 6 inherited features in VB.NET which C# lacks.

Most can be modeled with different constructions to achieve functional equivalence:

• C# equivalent instructions.

• Helper classes.

The rest maps to classes, structures and enumerations on the Microsoft.VisualBasic namespace.

Microsoft.VisualBasic namespace

The Microsoft.VisualBasic namespace contains types that support the Visual Basic Runtime in Visual Basic. For a complete reference check: Microsoft.VisualBasic.

The Visual Basic Runtime is written entirely in .NET. Please refer to: Visual Basic Runtime.

• "Visual Basic developers have long associated the term "Visual Basic Runtime" with a set of core library files, such as msvbvm60.dll, that are required for Visual Basic 6.0 (and prior) programs to run. In Visual Basic .NET, the term "Visual Basic Runtime" refers to the set of classes in the Microsoft.VisualBasic namespace. The Visual Basic Runtime provides the underlying implementation for global Visual Basic functions and language features such as Len, IsDate, and CStr. And though the new Visual Basic Runtime provides similar facilities as its predecessors, it is entirely managed code (developed in Visual Basic .NET) that executes on the common language runtime. Furthermore, the Visual Basic Runtime is part of the .NET Framework, so it is never something separate that your application has to carry or deploy.".

• "Many of the methods in the Visual Basic Runtime actually use methods and properties from the System namespace (for example, Len() returns String.Length). In some cases you can achieve equivalent results by accessing .NET Framework class library classes directly, but typically you will be more productive using the Visual Basic Runtime when authoring your code. In many cases the Visual Basic Runtime wrappers provide additional functionality that you would have to code yourself if using the System namespace directly. In other cases, such as IsDate, there is no directly equivalent functionality in the System namespace.".

The idea behind the VBUC is to use the minimum possible elements from the Microsoft.VisualBasic namespace. However, you don't have to worry about the remaining ones as they are written in .NET.

Safe and Unsafe Methods Layer

Win-API

The Microsoft Windows application programming interface (Win API):

• Allows calls to Windows OS functions directly.

• All programs interact with the Windows API directly or through some other API.

Many system resources can be managed using this API.

Applications employ them to perform special functions not available from the programming language natively:

• Working with the Windows registry.

• Interacting directly with the user interface.

Implemented in several DLLs that reside on the Windows folder, such as kernel32.dll, advapi32.dll, user32.dll, etc.

These libraries contain what is called unmanaged code in .NET: code that is compiled directly to a binary that can be executed directly in the CPU.

The .NET platform provides the Platform Invocation Services also known as "PInvoke" to interact with unmanaged code from a managed environment.

The VBUC (migration tool) identifies all Windows API calls in the original VB6 code and translates them into their corresponding "PInvoke" signature:

Original VB6 Code

Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As Long, lpcbData As Long) As Long

Migrated C# Code

[DllImport("advapi32.dll", EntryPoint = "RegQueryValueExA", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
extern public static int RegQueryValueEx(int hKey, [MarshalAs(UnmanagedType.VBByRefStr)] ref string lpszValueName, int dwReserved, ref int lpdwType, System.IntPtr lpbData, ref int cbData);

Code Organization

All the signatures found in the original code are organized by library, resulting in a class per library.

• The code is better organized.

• The original definitions are commented out.

• The required interoperability data-type marshalling is generated.

• The error handling is added for the upgraded API calls.

• The correct data types for pointer-type parameters are generated.

Example for the advapi32 class

namespace CompendiaSupport.PInvoke.UnsafeNative {
  [System.Security.SuppressUnmanagedCodeSecurity]
  public static class advapi32 {
    [DllImport("advapi32.dll", EntryPoint = "RegQueryValueExA", 
    CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
    extern public static int RegQueryValueEx(int hKey, 
    [MarshalAs(UnmanagedType.VBByRefStr)] ref string 
    lpszValueName, int dwReserved, ref int lpdwType, System.IntPtr lpbData, ref int cbData);
  }
}
​
namespace CompendiaSupport.PInvoke.SafeNative {
  public static class advapi32 {
    public static int RegQueryValueEx(int hKey, ref string 
    lpszValueName, int dwReserved, ref int lpdwType, ref int lpbData, ref int cbData) {
      int result = 0;
      GCHandle handle = GCHandle.Alloc(lpbData, GCHandleType.Pinned);
      try {
        IntPtr tmpPtr = handle.AddrOfPinnedObject();
        CompendiaSupport.PInvoke.UnsafeNative.advapi32.RegQueryValueEx(hKey, ref lpszValueName, 
        dwReserved, ref lpdwType, tmpPtr, ref cbData);
        lpbData = Marshal.ReadInt32(tmpPtr);
      }
      finally {
        handle.Free();
      }
      return result;
    }
  }
}

Looking for alternatives?

Microsoft provides an article with a list of Windows API functions and their mappings to .NET.

In the summary, they explicitly say that: "This article identifies the Microsoft .NET Framework version 1.0 or 1.1 APIs that provide similar functionality to Microsoft Win32 functions."

The replacement of API functions with .NET alternatives should be done on a case-by-case basis.

• Imports of the required namespaces are needed.

• Can require modification on the method's logic.

• Creation of new objects

• Calling of the new method(s).

• How to retrieve the return value.

Example for the RegQueryValueEx method:

Third Party Components

Component One Classes Extensions

The Compendia projects consume ComponentOne's VSPrinter component.

There is no direct equivalent in the ComponentOne control collection for .NET.

For that matter, extension classes were created:

• To avoid changing logic in the migrated code.

• Maintainability.

The ComponentOne documentation provides information on which components to use for printing and previewing. You can check here: Reports for WinForms Overview​

Textually, the ones that serve our purpose are:

• "The C1PrintDocument component provides a rich object model which allows you to create arbitrarily complex documents in code."

• "The integrated components (the C1PrintPreviewControl control and the C1PrintPreviewDialog dialog box) make adding a professional-looking preview to your applications a snap."

Extensions

The extensions classes were created with the required elements that need to be exposed in mind (properties, methods, events, etc.).

A deep investigation of both controls is needed to code equivalent behavior.

Testing is crucial to find and correct bugs.

Fortunately, the VSPrinter control documentation is well detailed. It is the primary source of information.

Let's review the Action property. On the surface, it just assigns a value to the property:

Original VB6 code example

m_objVSPrinter1.Action = paStartDoc
​
m_objVSPrinter1.Footer = ""
strTabs = "^" & CStr(m_objVSPrinter1.PageWidth) & "~"
m_objVSPrinter1.MarginLeft = 650 ' was 0 AA: FW 6542
m_objVSPrinter1.CurrentY = (m_objVSPrinter1.PageHeight / 2) - 1000
m_objVSPrinter1.FontSize = 14
m_objVSPrinter1.Table = strTabs & "NO RECORDS FOUND~~"

Migrated C# code example

m_objVSPrinter1.Action = UpgradeHelpers.Gui.C1PrintDocumentExtension.
ActionSettings.paStartDoc;
​
m_objVSPrinter1.Footer = "";
strTabs = "^" + m_objVSPrinter1.PageWidth.ToString() + "~";
m_objVSPrinter1.MarginLeft = 650; // was 0 AA: FW 6542
m_objVSPrinter1.CurrentY = Convert.ToInt32((m_objVSPrinter1.PageHeight / 2) - 1000);
m_objVSPrinter1.FontSize = 14;
m_objVSPrinter1.Table = strTabs + "NO RECORDS FOUND~~";

Looking at the documentation, it says the following regarding that property: "Action Property (VSPrinter) Executes an action such as 'StartDoc' or 'EndDoc'.".

Detailed information is given about what it does with each value. The code should work accordingly.

Code snippet with the implementation

public ActionSettings Action
{
    get
    {
        return _action;
    }
​
    set
    {
        _action = value;
        ExecuteDocumentAction();
    }
}
private void ExecuteDocumentAction()
{
    switch (Action)
    {
        case ActionSettings.paStartDoc:
            IsDocumentEnded = false;
            _docStarted = true;
            InitializeDocument();
            break;
        case ActionSettings.paEndDoc:
            if (_docStarted)
            {
                EndDocument();
            }
            break;
            //...more code omitted
        default:
            break;
    }
}

TrueDbGrid ActiveX Conversion

Description

The VBUC allows converting the TrueDbGrid Actvex to .NET equivalents: C1 TrueDbGrid .NET version or to a Mobilize HelperClass extending the .NET DataGridView.

Component One (C1) TrueDBGrid (.NET version)

This conversion targets the Activex migration to their .NET equivalent of ComponentOne.

There are important differences between the Activex and the .NET controls.

​

TrueDbGrid Columns

For the Activex version, design properties can be accessed from the grid's Columns collection, but in the .NET version, design properties can only be accessed via the DisplayColumn collection of the grid's Splits collection.

In VB6, code like the following:

<truedbgrid_instance>.Columns(0).Alignment = dbgGeneral

This instruction affects the alignment of all displayed columns in the grid.

On the other hand, when using the C1 version of the TrueDbGrid, that visual property can only be accessed by indicating explicitly the Split containing the column.

The above VB6 column would be converted as follows:

//UPGRADE_WARNING: (2081) Getting a DataColumn not from a split has a new behavior. More Information: https://www.mobilize.net/vbtonet/ewis/ewi2081
<truedbgrid_instance>.Splits[0].DisplayColumns[this.tgd_Fam.Columns[0]].Style.HorizontalAlignment = C1.Win.C1TrueDBGrid.AlignHorzEnum.General;

However, this only affects the columns in Split[0].

​

.NET Component that extends the DataGridView

All instances of the TrueDbGrid are converted to an extension of the DataGridView. Design limitations of the .NET DataGridView affect the capabilities of the grid.

​

Getting a DataColumn not from a split has a new behavior

Description

This entry describes the different behavior observed in .NET applications when the TrueDbGrid Activex component is migrated to the C1 TrueDbGrid .NET version when column properties are accessed for getting/setting purposes. This entry covers the UPGRADE_WARNING: (2081) Getting a DataColumn not from a split has a new behavior EWI.

TrueDbGrid Columns

For the Activex version, design properties can be accessed from the grid's Columns collection, but in the .NET version, design properties can only be accessed via the DisplayColumn collection of the grid's Splits collection.

In VB6, code like the following:

<truedbgrid_instance>.Columns(0).Alignment = dbgGeneral

This instruction affects the alignment of all displayed columns in the grid.

On the other hand, when using the C1 version of the TrueDbGrid, that visual property can only be accessed by indicating explicitly the Split containing the column.

The above VB6 column would be converted as follows:

//UPGRADE_WARNING: (2081) Getting a DataColumn not from a split has a new behavior. More Information: https://www.mobilize.net/vbtonet/ewis/ewi2081
<truedbgrid_instance>.Splits[0].DisplayColumns[this.tgd_Fam.Columns[0]].Style.HorizontalAlignment = C1.Win.C1TrueDBGrid.AlignHorzEnum.General;

However, the above line only affects the columns in Split[0].

If the source grid contains multiple Splits, then the code must be adapted to reflect that situation.

A possibility would be creating a for-each loop to iterate through all Splits in the grid.

foreach(C1.Win.C1TrueDBGrid.Split split in <truedbgrid_instance>.Splits)
{
   split.DisplayColumns[0].Style.HorizontalAlignment = C1.Win.C1TrueDBGrid.AlignHorzEnum.General;
}

This EWI in previous VBUC versions

Starting with the public version of the VBUC 8.2, a for-each loop was created for a limited set of mapped elements of this library, making the code hard to read for an unexperienced user, seeing a lot of new code that was not present in VB6 code. Therefore, starting on VBUC 8.2.50602 this was modified in mappings for this component.

Migration Process

DataAccess related Upgrade Options

Description

The VBUC allows converting DataAccess related classes like the classic ADODB, Microsoft DAO, RDO or OracleInProc Activex components to .NET equivalents, or even keep them using the original components via COM interop.

In this post, we are not covering the migration to COM interop for those Activex components.

System.Data.Common and HelperClasses

This solution uses a set of helper objects to provide equivalent behavior in .NET and to encapsulate the ADO.NET machinery required to handle a set of data, more specifically, for the RecordSet object, which is very powerful and flexible in Visual Basic 6 and does not have a direct equivalent in .NET. This approach reduces the manual changes effort to achieve functional equivalence.

The usage of System.Data.Common libraries provide the application with the ability to interact with different Database Manager Systems (such as SQL Server, Oracle, MS Access, etc) through its ADO.NET 2.0 compliant providers with just minimal configuration efforts.

More info is found at Classic ADO Conversion to ADO.NET​

ADO.NET Using SqlClient

This optional feature allows the VBUC to convert RDO to plain ADO.NET by using the provider-specific System.Data.SqlClient libraries.

Remarks:

• This approach converts RDO to plain ADO.NET, but it might require several manual changes to achieve functional equivalence.

• Transformations to occurrences of MSRDC will also be applied when selecting this choice.

Known Issues with Upgrade Options

• Do not mix Upgrade options in a single migration even if source components are different: such as ADODB and RDO in the same VB6 project.

• Due to design in VBUC 8.2 and older, choosing System.Data.Common and HelperClasses to migrate ADODB will cause conflicts if SqlClient is chosen for RDO.

If System.Data.Common + Helper is used for some classes and SqlClient for others, the generated code will be hard to modify in order to achieve functional equivalence.

Make sure to be consistent, and just pick either only the SqlClient or only the System.Data.Common + Helper classes options.

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 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 Migrated 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 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 the 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 in 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 SqlSever 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.

    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):

    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:

    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

    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 to 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 assign that value to the DBCommand.CommandTimeOut property.

UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(TempCommand);