# Classic ADO Conversion to ADO.NET ### Description The VB6 AI Migrator allows converting the Activex Data Objects (classic ADO) to [ADO.NET](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/) using the System.Data.Common namespace + Mobilize helper classes. This entry talks about this option. In the Upgrade Options section of the VB6 AI Migrator tool: ![ADODB Option](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F26c0e580884102566e18540739bf1531d3b8a2ed.png?generation=1597176135253569\&alt=media) ### 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. ![ADODB Components](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F1d61c66102710afdb6f84428001a687793bbd64e.png?generation=1597176132097283\&alt=media) ### 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. ![ADODB Objects](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2Faeb26b508c58445c9845654704f11aac740e8916.png?generation=1597176133818460\&alt=media) 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](https://www.oracle.com/database/technologies/appdev/dotnet/odp.html)) 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 VB6 AI Migrator 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](https://www.oracle.com/database/technologies/appdev/dotnet/odp.html)) technology developed by Oracle instead. #### How VB6 works with MSDAORA The following VB6 code shows a database connection through the MSDAORA provider: ```php 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 VB6 AI Migrator the code looks like this: ```csharp 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(); ``` {% hint style="info" %} The ADODB component is migrated to [ADO.NET](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/) using the System.Data.Common and helper classes. {% endhint %} 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](https://www.oracle.com/database/technologies/appdev/dotnet/odp.html) developed by Oracle. In this case, you need to go to the [Oracle Data Provider .NET download page](http://www.oracle.com/technetwork/topics/dotnet/index-085163.html) 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: ```csharp factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "Oracle.DataAccess.Client", DatabaseType.Oracle, false)); ``` and comment out this line: ```csharp factorySection.Add("Oracle", new FactoryConfigurationElement("Oracle", "System.Data.OracleClient", DatabaseType.Oracle, false)); ``` So, this method should look like this: ```csharp private static void LoadDefaultFactorySettings(Dictionary 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 GAPVelocity AI helper classes consists of using the App.Config file of the new .NET project: ```markup
** ** ``` 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: ```csharp 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 VB6 AI Migrator tool. ![ADODB Option](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F26c0e580884102566e18540739bf1531d3b8a2ed.png?generation=1597176135253569\&alt=media) **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. ```php Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.ConnectionString = "valid connection string replaced" conn.CommandTimeout = 900 conn.Open conn.Close ``` *C# code* ```csharp 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(); UpgradeHelpers.DB.TransactionManager.DeEnlist(conn); conn.Close(); ``` ```csharp 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 {% hint style="info" %} The VB6 AI Migrator sets OLEDB as the default factory, but it can be changed in the AdoFactoryManager.LoadDefaultFactorySettings() method. {% endhint %} 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 VB6 AI Migrator 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). ```csharp 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 VB6 AI Migrator, and it may need to be revised since in .NET connection strings may be different. ```csharp UpgradeHelpers.DB.TransactionManager.DeEnlist(conn); conn.Close(); ``` As the DBConnection object is closed, the TransactionManager.DeEnlist() method, injected by the VB6 AI Migrator, 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*** ```php 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*** ```csharp 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. ```csharp 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](https://stackoverflow.com/questions/6846836/rowcount-not-working-like-i-think) for additional info about how @@RowCount works in nested statements.
*Original code (nested SQL Statements):* ```sql 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: ```sql 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** ```csharp 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 VB6 AI Migrator 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** ```csharp 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](https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/begintrans-committrans-and-rollbacktrans-methods-ado?view=sql-server-ver15) 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 VB6 AI Migrator 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** ```php 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** ```csharp 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(); } ``` ```csharp 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) ```csharp UpgradeHelpers.DB.TransactionManager.Commit(conn); ``` Commits the Transaction associated (if any) to the specified connection. Once the Commit is performed the transaction is DeEnlisted ```csharp 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. ```csharp 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 VB6 AI Migrator tool. *VB6 code* ```php 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* ```csharp 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. ```csharp DbConnection conn = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection(); ``` The above sentence creates a DBConnection object using the desired factory. By default System.Data. OLEDB {% hint style="info" %} Those factories are defined in the AdoFactoryManager.LoadDefaultFactorySettings() method. {% endhint %} ```csharp 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. ```csharp 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 VB6 AI Migrator 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 VB6 AI Migrator: ```csharp UpgradeHelpers.DB.DbConnectionHelper.ResetCommandTimeOut(TempCommand); ``` This line, injected by the VB6 AI Migrator, retrieves the CommandTimeOut value set for the Connection Object and assigns that value to the DBCommand.CommandTimeOut property. ```csharp UpgradeHelpers.DB.TransactionManager.SetCommandTransaction(TempCommand); ``` If the Connection object started a Database Transaction, then this line, injected by the VB6 AI Migrator, will link the Transaction to the DBCommand object. If there's no Transaction associated to the Connection object, then this method will do nothing. ```csharp UpgradeHelpers.DB.TransactionManager.DeEnlist(conn); conn.Close(); ``` As the DBConnection object is closed, the DeEnlist() method, injected by the VB6 AI Migrator, 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 VB6 AI Migrator converts the Activex Data Objects (classic ADO) to a .NET alternative using the System.Data.Common namespace + GAPVelocity AI helper classes. Specifically, the Classic ADO Recordset object is converted to a GAPVelocity AI 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** GAPVelocity AI 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. ![ADORecordSetHelper](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F4ae217288ed702e290fcefadfefe07ba393a4c91.png?generation=1597176135742569\&alt=media) 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. * GAPVelocity AI 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 GAPVelocity AI RecordsetHelper class provides the logic to have a current record pointer and allow MoveNext, MoveFirst methods to achieve the same functionality as in VB6. ![Code RecordSetHelper](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F002ebf59385b8f5bc18990133946c1b983c4b9c8.png?generation=1597176135150576\&alt=media) #### 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. {% hint style="danger" %} **Concurrency violation: the UpdateCommand affected 0 of the expected 1 records** {% endhint %} *Example* Let's assume we have the following table in a database. ![Users Table](https://1564986598-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MEU5wF4akBZMw75SrqC%2Fsync%2F76eb42a6a53fb247bc58a0f5a37326c0b187d965.png?generation=1597176135137669\&alt=media) 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* ```php 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* ```csharp 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* ```csharp 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. ```csharp 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](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-overview) 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* ``` .Properties("Preserve on Commit") [= value] ``` | Value | Description | | ----- | ------------------------------------------------------------------------------------------------------------------------------------------------------ | | True | After committing a transaction, the recordset remains active. Therefore, it is possible to fetch new rows; update, delete, and insert rows; and so on. | | False | After committing a transaction, the only operations allowed on a recordset are to release rows and the recordset. | Preserve on Commit applies only to local transactions. Source: *C# Conversion* ```csharp //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 //.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.