The options in this section define how the data access components will be migrated.
1. ADODB
1.1. To ADO.NET using System.Data.Common and helper classes
This optional feature allows the VBUC to convert ADODB to ADO.NET by using the System.Data.Common libraries and some helpers.
This option converts ADODB to ADO.NET by using helper classes, achieving very high automation levels.
If the VB6 code is using data binding to grids, it is also recommended to convert all data grids to .NET components.
General Description:
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 (e.g. SQL Server, Oracle, MS Access, etc) through its ADO.NET 2.0 compliant providers with just minimal configuration effort.
Deployment Note:
This feature uses a .NET configuration file containing specific sections. An example file is created in the output path of the migration solution. This file should be added to the main executable assembly and configured according to your needs by selecting the proper provider factory.
Class
Maps To
ADODB.RecordSet
UpgradeHelpers.DB.ADO.ADORecordSetHelper
ADODB.Command
System.Data.Common.DbCommand
ADODB.CommandTypeEnum
System.Data.CommandType
ADODB.Connection
System.Data.Common.DbConnection
ADODB.DataTypeEnum
System.Data.DbType
ADODB.Field
System.Data.DataColumn
ADODB.Fields
System.Data.DataColumnCollection
ADODB.IsolationLevelEnum
System.Data.IsolationLevel
ADODB.ObjectStateEnum
System.Data.ConnectionState
ADODB.Parameter
System.Data.Common.DbParameter
ADODB.ParameterDirectionEnum
System.Data.ParameterDirection
ADODB.Parameters
System.Data.Common.DbParameterCollection
ADODB.Stream
System.IO.StreamWriter
ADODB.LockTypeEnum
UpgradeHelpers.DB.ADO.LockTypeEnum
ADODB.CursorLocationEnum
UpgradeHelpers.DB.ADO.CursorLocationEnum
ADODB.AffectEnum
UpgradeHelpers.DB.ADO.AffectEnum
ADODB.EventStatusEnum
UpgradeHelpers.DB.ADO.Events.EventStatusEnum
ADODB.EventReasonEnum
UpgradeHelpers.DB.ADO.Events.EventReasonEnum
ADODB.PositionEnum
UpgradeHelpers.DB.ADO.PositionEnum
MSAdodcLib.Adodc
UpgradeHelpers.DB.ADO.ADODataControlHelper
MSAdodcLib.EOFActionEnum
UpgradeHelpers.DB.Controls.EOFActionEnum
MSAdodcLib.BOFActionEnum
UpgradeHelpers.DB.Controls.BOFActionEnum
This optional feature also includes the conversion of ADOR to ADO.NET.
Class
Maps To
ADOR.RecordSet
UpgradeHelpers.DB.ADO.ADORecordSetHelper
Original VB6 code:
Dim conConnection AsNewADODB.ConnectionDim cmdCommand AsNewADODB.CommandDim rstRecordSet AsNewADODB.RecordsetconConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= NWIND.MDB;Persist Security Info=False;"
conConnection.OpenrstRecordSet.Open "SELECT*FROM Customers", conConnectionIf rstRecordSet.EOF =False Then MsgBox rstRecordSet.Fields(0).Name &"="& rstRecordSet.Fields(0)Else MsgBox "No records were returned using the query "& cmdCommand.CommandTextEnd If
C# code:
DbConnection conConnection =UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection();DbCommand cmdCommand =UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand();ADORecordSetHelper rstRecordSet =newADORecordSetHelper("");conConnection.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source= NWIND.MDB;Persist Security Info=False;";//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements.
conConnection.Open();rstRecordSet.Open("SELECT * FROM Customers", conConnection);if (!rstRecordSet.EOF){ MessageBox.Show(rstRecordSet.GetField(0).FieldMetadata.ColumnName + "=" + Convert.ToString(rstRecordSet[0]), AssemblyHelper.GetTitle(System.Reflection.Assembly.GetExecutingAssembly()));
}else{ MessageBox.Show("No records were returned using the query " + cmdCommand.CommandText, AssemblyHelper.GetTitle(System.Reflection.Assembly.GetExecutingAssembly()));
}
VB.NET code:
Dim conConnection As DbConnection = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection()Dim cmdCommand As DbCommand = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand()Dim rstRecordSet AsNewADORecordSetHelper("")conConnection.ConnectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source= NWIND.MDB;Persist Security Info=False;"'UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider connection string requirements. '
conConnection.Open()rstRecordSet.Open("SELECT*FROM Customers", conConnection)If Not rstRecordSet.EOF Then MessageBox.Show(rstRecordSet.GetField(0).FieldMetadata.ColumnName & "=" & rstRecordSet(0), My.Application.Info.Title)
Else MessageBox.Show("No records were returned using the query "& cmdCommand.CommandText, My.Application.Info.Title)End If
1.2. To ADO.NET using SqlClient
This optional feature allows the VBUC to Convert ADODB to plain ADO.NET by using the provider-specific System.Data.SqlClient libraries.
This approach converts ADODB to plain ADO.NET, but it might require several manual changes to achieve functional equivalence, especially if the application uses RecordSets extensively.
General Description:
This solution converts ADODB data access patterns to plain ADO.NET specifically to interact with a SQL Server database.
Class
Maps To
ADOBD.Command
System.Data.SqlClient.SqlCommand
ADOBD.Parameters
System.Data.SqlClient.SqlParameterCollection
ADOBD.DataTypeEnum
System.Data.DbType
ADOBD.ParameterDirectionEnum
System.Data.ParameterDirection
ADOBD.Parameter
System.Data.SqlClient.SqlParameter
ADOBD.RecordSet
System.Data.DataSet
ADOBD.CommandTypeEnum
System.Data.CommandType
ADOBD.Connection
System.Data.SqlClient.SqlConnection
ADOBD.Error
System.Data.SqlClient.SqlError
ADOBD.Errors
System.Data.SqlClient.SqlErrorCollection
ADOBD.IsolationLevelEnum
System.Data.IsolationLevel
ADOBD.ObjectStateEnum
System.Data.ConnectionState
This optional feature also includes the conversion of ADOR to ADO.NET.
Class
Maps To
ADOR.RecordSet
System.Data.DataSet
Original VB6 code:
Dim conConnection AsNewADODB.ConnectionDim cmdCommand AsNewADODB.CommandDim rstRecordSet AsNewADODB.RecordsetconConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= NWIND.MDB;Persist Security Info=False;"
conConnection.OpenrstRecordSet.Open "SELECT*FROM Customers", conConnectionIf rstRecordSet.EOF =False Then MsgBox rstRecordSet.Fields(0).Name &"="& rstRecordSet.Fields(0)Else MsgBox "No records were returned using the query "& cmdCommand.CommandTextEnd If
C# code:
SqlConnection conConnection =newSqlConnection();SqlCommand cmdCommand =newSqlCommand();DataSet rstRecordSet =newDataSet();//UPGRADE_TODO: (7010) The connection string must be verified to fullfill the .NET data provider conecction string requirements.
conConnection.Open();...cmdCommand.CommandText="SELECT * FROM Customers";...//UPGRADE_ISSUE: (2064) ADODB.LockTypeEnum property LockTypeEnum.adLockUnspecified was not upgraded. //UPGRADE_ISSUE: (2064) ADODB.CursorTypeEnum property CursorTypeEnum.adOpenUnspecified was not upgraded. //UPGRADE_ISSUE: (2064) ADODB.Recordset method rstRecordSet.Open was not upgraded. UpgradeStubs.ADODB_Recordset.Open(rstRecordSet, null, null, UpgradeStubs.ADODB_CursorTypeEnum.getadOpenUnspecified(), UpgradeStubs.ADODB_LockTypeEnum.getadLockUnspecified(), -1);
if (rstRecordSet.Tables[0].Rows.Count!=0){ //UPGRADE_WARNING: (2077) Change the default 0 index in the Rows property with the correct one. MessageBox.Show(Convert.ToString(rstRecordSet.Tables[0].Rows[0][0]),Application.ProductName);}else{MessageBox.Show("No records were returned using the query "+cmdCommand.CommandText,Application.ProductName);}
VB.NET code:
Dim conConnection AsNewSqlConnectionDim cmdCommand AsNewSqlCommandDim rstRecordSet AsNewDataSet'UPGRADE_TODO: (7010) The connection string must be verified to fulfill the .NET data provider connection string requirements.'
conConnection.Open()...cmdCommand.CommandText ="SELECT*FROM Customers"...'UPGRADE_ISSUE: (2064) ADODB.Recordset method rstRecordSet.Open was not upgraded. 'UpgradeStubs.ADODB_Recordset.Open(rstRecordSet)If Not (rstRecordSet.Tables(0).Rows.Count =0) Then'UPGRADE_WARNING: (2077) Change the default 0 index in the Rows property with the correct one.' MessageBox.Show(rstRecordSet.Tables(0).Rows(0)(0), Application.ProductName)Else MessageBox.Show("No records were returned using the query "& cmdCommand.CommandText, Application.ProductName)End If
1.3. To COM Interop
This feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control's functionality will remain the same since it will use the same binary, but the resulting application will depend on the legacy control.
Original VB6 code:
Dim conConnection AsNewADODB.ConnectionDim cmdCommand AsNewADODB.CommandDim rstRecordSet AsNewADODB.RecordsetconConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= NWIND.MDB;Persist Security Info=False;"
conConnection.OpenrstRecordSet.Open "SELECT*FROM Customers", conConnectionIf rstRecordSet.EOF =False Then MsgBox rstRecordSet.Fields(0).Name &"="& rstRecordSet.Fields(0)Else MsgBox "No records were returned using the query "& cmdCommand.CommandTextEnd If
C# code:
ADODB.Connection conConnection =newADODB.Connection();ADODB.Command cmdCommand =newADODB.Command();ADODB.Recordset rstRecordSet =newADODB.Recordset();...conConnection.Open("","","",-1);cmdCommand.CommandText="SELECT * FROM Customers";...rstRecordSet.Open(Type.Missing, Type.Missing, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1);
if (!rstRecordSet.EOF){ MessageBox.Show(rstRecordSet.Fields[0].Name + "=" + Convert.ToString(rstRecordSet.Fields[0].Value), Application.ProductName);
}else{MessageBox.Show("No records were returned using the query "+cmdCommand.CommandText,Application.ProductName);}
VB.NET code:
Dim conConnection AsNewADODB.ConnectionDim cmdCommand AsNewADODB.CommandDim rstRecordSet AsNewADODB.Recordset...conConnection.Open()cmdCommand.CommandText ="SELECT*FROM Customers"...rstRecordSet.Open()If Not rstRecordSet.EOF Then MessageBox.Show(rstRecordSet.Fields(0).Name &"="& rstRecordSet.Fields(0).Value, Application.ProductName)Else MessageBox.Show("No records were returned using the query "& cmdCommand.CommandText, Application.ProductName)End If
2. DAO
2.1. To ADO.NET with helpers based on System.Data.Commons
This optional feature allows the VBUC to convert DAO to ADO.NET by using the System.Data.Common libraries and some helper classes.
This option converts DAO to ADO.NET using helper classes, achieving high automation levels.
If the VB6 code is using data binding to grids, it is also recommended to convert all data-bound grids to .NET components.
General Description:
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 (e.g. SQL Server, Oracle, MS Access, etc) through its ADO.NET 2.0 compliant providers with just minimal configuration efforts.
Deployment Note:
This feature uses a .NET configuration file containing specific sections. An example file is created in the output path of the migration solution. This file should be added to the main executable assembly and configured according to your needs by selecting the proper provider factory.
Class
Maps to
DAO.Recordset
UpgradeHelpers.DB.DAO.DAORecordSetHelper
DAO.Field
System.Data.DataColumn
DAO.Fields
System.Data.DataColumnCollection
DAO.Database
System.Data.Common.DBConnection
DAO.ParameterDirectionEnum
System.Data.ParameterDirection
DAO.Parameters
System.Data.Common.DbParameterCollection
DAO.Parameter
System.Data.Common.DbParameter
DAO.QueryDef
System.Data.Common.DbCommand
DAO.Connection
System.Data.Common.DbConnection
DAO.Workspace
UpgradeHelpers.DB.DAO.WorkspaceHelper
DAO.DBEngine
UpgradeHelpers.DB.DAO.DBEngineHelper
DAO.RecordsetTypeEnum
UpgradeHelpers.DB.DAO.DAORecordsetTypeEnum
DAO.LockTypeEnum
UpgradeHelpers.DB.DAO.DAOLockTypeEnum
DAO.RecordsetOptionEnum
UpgradeHelpers.DB.DAO.DAORecordsetOptionEnum
Original VB6 code:
Dim ws As WorkspaceDim db As DatabaseDim strConnection AsStringSet ws = DBEngine.Workspaces(0)strConnection ="ODBC;DSN="&"DatabaseName"&";UID="&"UserName"&";PWD="&"UserPassword"Set db = ws.OpenDatabase("",False,False, strConnection)Dim rs As RecordsetSet rs = db.OpenRecordset("TableName")If Not rs.EOF Then MsgBox rs!IDFieldEnd If
C# code:
WorkspaceHelper ws =DBEngineHelper.Instance(UpgradeHelpers.DB.AdoFactoryManager.GetFactory())[0];string strConnection ="ODBC;DSN="+"DatabaseName"+";UID="+"UserName"+";PWD="+"UserPassword";//UPGRADE_WARNING: (2065) DAO.Workspace method ws.OpenDatabase has a new behavior. DAODatabaseHelper db =ws.OpenDatabase("<Connection-String>");DAORecordSetHelper rs =db.OpenRecordset("TableName");if (!rs.EOF){MessageBox.Show(Convert.ToString(rs["IDField"]),Application.ProductName);}
VB.NET code:
Dim ws As WorkspaceHelper = DBEngineHelper.Instance(UpgradeHelpers.DB.AdoFactoryManager.GetFactory())(0)Dim strConnection AsString="ODBC;DSN="&"DatabaseName"&";UID="&"UserName"&";PWD="&"UserPassword"'UPGRADE_WARNING: (2065) DAO.Workspace method ws.OpenDatabase has a new behavior.'Dim db As DAODatabaseHelper = ws.OpenDatabase("<Connection-String>")Dim rs As DAORecordSetHelper = db.OpenRecordset("TableName")If Not rs.EOF Then MessageBox.Show(rs("IDField"), Application.ProductName)End If
2.2. To COM Interop
This optional feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control’s functionality will remain the same since it will use the same binary, but the resulting application will depend on the legacy control.
Original VB6 code:
Dim ws As WorkspaceDim db As DatabaseDim strConnection AsStringSet ws = DBEngine.Workspaces(0)strConnection ="ODBC;DSN="&"DatabaseName"&";UID="&"UserName"&";PWD="&"UserPassword"Set db = ws.OpenDatabase("",False,False, strConnection)Dim rs As RecordsetSet rs = db.OpenRecordset("TableName")If Not rs.EOF Then MsgBox rs!IDFieldEnd If
Dim ws As dao.Workspace = DAO_DBEngine_definst.Workspaces(0)Dim strConnection AsString="ODBC;DSN="&"DatabaseName"&";UID="&"UserName"&";PWD="&"UserPassword"Dim db As dao.Database = ws.OpenDatabase("",False,False, strConnection)Dim rs As dao.Recordset = db.OpenRecordset("TableName")If Not rs.EOF Then MessageBox.Show(rs.Fields("IDField").Value, Application.ProductName)End If
3. MSAccessToNet
3.1. MSAccessToNet To ToolTip.NET
Conversion of Microsoft Access Object Library for Windows Forms using a helper class.
General Description:
Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.
Dim intUTCOffset As Integer, strSQL As String
DoCmd.DeleteObject acQuery, "qryFlowGagesToRun"
Set dbs = CurrentDb
DoCmd.SetWarnings False
intUTCOffset = DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" & intGID)
DoCmd.TransferText acImportDelim, "Rain_Auto_Load", "DatRain5MinData", strFileName, 0
DoCmd.OpenQuery ("qryAppendDailyfromServer")
'Provisional flags where there are no estimates'
strSQL = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));"
DoCmd.RunSQL (strSQL)
C# Code:
...object strFileName =null;string intGID ="";UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_DeleteObject(UpgradeHelpers.MSAccessToNet.Enumerations.AcObjectType.acQuery, "qryFlowGagesToRun");
DAODatabaseHelper dbs =UpgradeHelpers.MSAccessToNet.MSAccessApplicationHelper.CurrentConnection();UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_SetWarnings(false);//UPGRADE_WARNING: (1068) DLookup() of type Variant is being forced to int.int intUTCOffset = ReflectionHelper.GetPrimitiveValue<int>(UpgradeHelpers.MSAccessToNet.MSAccessApplicationHelper.DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" + intGID));
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_TransferText(UpgradeHelpers.MSAccessToNet.Enumerations.AcTextTransferType.acImportDelim, "Rain_Auto_Load", "DatRain5MinData", ReflectionHelper.GetPrimitiveValue<string>(strFileName), 0);
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_OpenQuery("qryAppendDailyfromServer");//Provisional flags where there are no estimatesstring strSQL = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));";
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_RunSQL(strSQL);...
VB.NET code:
...
Dim strFileName As Object
Dim intGID As String = ""
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_DeleteObject(UpgradeHelpers.MSAccessToNet.Enumerations.AcObjectType.acQuery, "qryFlowGagesToRun")
Dim dbs As DAODatabaseHelper = UpgradeHelpers.MSAccessToNet.MSAccessApplicationHelper.CurrentConnection()
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_SetWarnings(False)
Dim intUTCOffset As Integer = ReflectionHelper.GetPrimitiveValue(Of Integer)(UpgradeHelpers.MSAccessToNet.MSAccessApplicationHelper.DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" & intGID))
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_TransferText(UpgradeHelpers.MSAccessToNet.Enumerations.AcTextTransferType.acImportDelim, "Rain_Auto_Load", "DatRain5MinData", ReflectionHelper.GetPrimitiveValue(Of String)(strFileName), 0)
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_OpenQuery("qryAppendDailyfromServer")
'Provisional flags where there are no estimates'
Dim strSQL As String = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));"
UpgradeHelpers.MSAccessToNet.MSAccessCmdHelper.DoCmd_RunSQL(strSQL)
...
3.2. To COM Interop
This feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control's functionality will remain the same since it will use the same binary, but the resulting application will depend on the legacy control.
Original VB6 code:
Dim intUTCOffset As Integer, strSQL As String
DoCmd.DeleteObject acQuery, "qryFlowGagesToRun"
Set dbs = CurrentDb
DoCmd.SetWarnings False
intUTCOffset = DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" & intGID)
DoCmd.TransferText acImportDelim, "Rain_Auto_Load", "DatRain5MinData", strFileName, 0
DoCmd.OpenQuery ("qryAppendDailyfromServer")
'Provisional flags where there are no estimates'
strSQL = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));"
DoCmd.RunSQL (strSQL)
C# code:
object strFileName =null;string intGID ="";AccessVisualCmdHelper.DoCmd_DeleteObject(AcObjectType.AcQuery,"qryFlowGagesToRun");DAODatabaseHelper dbs =UpgradeHelpers.DB.Access.AccessApplicationHelper.CurrentConnection();UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_SetWarnings(false);//UPGRADE_WARNING: (1068) DLookup() of type Variant is being forced to int.int intUTCOffset = ReflectionHelper.GetPrimitiveValue<int>(UpgradeHelpers.DB.Access.AccessApplicationHelper.DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" + intGID));
UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_TransferText(UpgradeHelpers.DB.Access.Enumerations.AcTextTransferType.AcImportDelim, "Rain_Auto_Load", "DatRain5MinData", ReflectionHelper.GetPrimitiveValue<string>(strFileName), false);
AccessVisualCmdHelper.DoCmd_OpenQuery("qryAppendDailyfromServer");//Provisional flags where there are no estimatesstring strSQL = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));";
UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_RunSQL(strSQL);
VB.NET code:
Dim strFileName As Object
Dim intGID As String = ""
AccessVisualCmdHelper.DoCmd_DeleteObject(AcObjectType.AcQuery, "qryFlowGagesToRun")
Dim dbs As DAODatabaseHelper = UpgradeHelpers.DB.Access.AccessApplicationHelper.CurrentConnection()
UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_SetWarnings(False)
'UPGRADE_WARNING: (1068) DLookup() of type Variant is being forced to Integer.'
Dim intUTCOffset As Integer = ReflectionHelper.GetPrimitiveValue(Of Integer)(UpgradeHelpers.DB.Access.AccessApplicationHelper.DLookup("UTC_Offset", "dbo_tblFlowGageRun", "G_ID=" & intGID))
UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_TransferText(UpgradeHelpers.DB.Access.Enumerations.AcTextTransferType.AcImportDelim, "Rain_Auto_Load", "DatRain5MinData", ReflectionHelper.GetPrimitiveValue(Of String)(strFileName), 0)
AccessVisualCmdHelper.DoCmd_OpenQuery("qryAppendDailyfromServer")
'Provisional flags where there are no estimates'
Dim strSQL As String = "UPDATE DatRain5MinData SET DatRain5MinData.Estimated ='p' WHERE (((DatRain5MinData.Estimated)is null));"
UpgradeHelpers.DB.Access.AccessCmdHelper.DoCmd_RunSQL(strSQL)
4. OracleInProc
4.1. To System.DbConnection
Converts OracleInProcServer to ADO.NET by using the System.Data.Common libraries and some helpers.
This option converts OracleInProcServer to ADO.NET by using helper classes but with a high automation level.
If the VB6 code is using data binding to grids it is also recommended to convert all data grids to .Net components.
General Description:
This solution uses a set of helper objects to provide equivalent behavior in .NET and to encapsulate the ADO.NET machinery required in order to handle a set of data, more specifically for the RecordSet object, which is very powerful and flexible in VB6 and does not have a direct equivalence in .NET. The 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 (e.g. SQL Server, Oracle, MS Access, etc) through its ADO.NET 2.0 compliant providers with just a minimal configuration effort and proper dialect changes.
Deployment Note:
This feature needs to be configured using a .NET configuration file containing specific sections. An example file is created in the output path of the migration solution. This file should be added to the main executable assembly and configured according to your needs by selecting the proper provider factory.
Dim OraSession As OraSessionDim OraDatabase As OraDatabaseDim EmpDynaset As OraDynaset'OraSession'Set OraSession =NewOraSessionClass'OraDatabase'Set OraDatabase = OraSession.OpenDatabase("Database","username",0&)'OraDynaset'Set EmpDynaset = OraDatabase.CreateDynaset("SELECT id, nameFROM employee",0&)
C# code:
//OraSessionDbConnection OraSession =UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection();//OraDatabaseOraSession.ConnectionString="Database"+"username";OraSession.Open();DbCommand TempCommand =UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand();TempCommand.Connection= OraSession;DbCommand OraDatabase = (DbCommand) TempCommand;//OraDynasetADORecordSetHelper tempDynaset =newADORecordSetHelper();OraDatabase.CommandText="SELECT id, name FROM employee";OraDatabase.CommandType=CommandType.Text;tempDynaset.Open(OraDatabase);ADORecordSetHelper EmpDynaset = (ADORecordSetHelper) tempDynaset;
VB.NET code:
'OraSession'Dim OraSession As DbConnection = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateConnection()'OraDatabase'OraSession.ConnectionString ="Database"&"username"OraSession.Open()Dim TempCommand As DbCommand = UpgradeHelpers.DB.AdoFactoryManager.GetFactory().CreateCommand()TempCommand.Connection = OraSessionDim OraDatabase As DbCommand = TempCommand'OraDynaset'Dim tempDynaset AsNewADORecordSetHelper()OraDatabase.CommandText ="SELECT id, nameFROM employee"OraDatabase.CommandType = CommandType.TexttempDynaset.Open(OraDatabase)Dim EmpDynaset As ADORecordSetHelper = tempDynaset
4.2. To COM Interop
This optional feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control’s functionality will remain the same since it will use the same binary, but the resulting application will depend on the legacy control.
Original VB6 code:
Dim OraSession As OraSessionDim OraDatabase As OraDatabaseDim EmpDynaset As OraDynaset'OraSession'Set OraSession =NewOraSessionClass'OraDatabase'Set OraDatabase = OraSession.OpenDatabase("Database","username",0&)'OraDynaset'Set EmpDynaset = OraDatabase.CreateDynaset("SELECT id, nameFROM employee",0&)
'OraSession'Dim OraSession As OracleInProcServer.OraSession =NewOracleInProcServer.OraSessionClass()'OraDatabase'Dim OraDatabase As OracleInProcServer.OraDatabase = OraSession.OpenDatabase("Database","username",0)'OraDynaset'Dim EmpDynaset As OracleInProcServer.OraDynaset = OraDatabase.CreateDynaset("SELECT id, nameFROM employee",0)
5. RDO
5.1. To 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.
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.
General Description:
This solution converts RDO data access patterns to plain ADO.NET specifically to interact with a SQL Server database.
This feature also supports the MSRDC data control.
Class
Maps to
RDO.rdoConnection
System.Data.SqlClient.SqlConnection
RDO.rdoResultset
System.Data.DataSet
RDO.rdoColumns
The property “rdoColumns.item” is the only supported member of this class.
RDO.rdoError
System.Data.SqlClient.SqlError
RDO.Parameter
System.Data.SqlClient.SqlParameter
RDO.Parameters
System.Data.SqlClient.SqlParameterCollection
RDO.rdoPreparedStatement
System.Data.SqlClient.SqlCommand
RDO.rdoQuery
System.Data.SqlClient.SqlCommand
Original VB6 code:
Dim mobjRDOConn As rdoConnectionDim mobjRDORst As rdoResultsetDim mstrSQL AsStringSet mobjRDOConn = rdoEngine.rdoEnvironments(0).OpenConnection ("PropDB", rdDriverNoPrompt,,"UID=admin;PWD=")Set mobjRDORst = mobjRDOConn.OpenResultset(mstrSQL, rdOpenKeyset, rdConcurRowVer)If Not mobjRDORst.EOF Then MsgBox mobjRDORst("propno")End If
C# code:
string mstrSQL =String.Empty;//Connect to the Property database://UPGRADE_ISSUE: (2068) RDO.rdoEngine object was not upgraded. //UPGRADE_ISSUE: (2064) RDO.rdoEngine method RDO.rdoEngine was not upgraded.//UPGRADE_ISSUE: (2064) RDO.rdoEngine property rdoEngine.rdoEnvironments was not upgraded. SqlConnection mobjRDOConn = (SqlConnection) rdoEngine.rdoEnvironments[0].OpenConnection("PropDB", RDO.PromptConstants.rdDriverNoPrompt, Type.Missing, "UID=admin;PWD=", Type.Missing);
SqlDataAdapter tempAdapter =newSqlDataAdapter(mstrSQL, mobjRDOConn);DataSet mobjRDORst =newDataSet();tempAdapter.Fill(mobjRDORst);if (mobjRDORst.Tables[0].Rows.Count!=0){ //UPGRADE_WARNING: (2077) Change the default 0 index in the Rows property with the correct one.MessageBox.Show(Convert.ToString(mobjRDORst.Tables[0].Rows[0]["propno"]),Application.ProductName);}
VB.NET code:
Dim mstrSQL AsString=""'UPGRADE_ISSUE: (2068) RDO.rdoEngine object was not upgraded. ''UPGRADE_ISSUE: (2064) RDO.rdoEngine method RDO.rdoEngine was not upgraded. ''UPGRADE_ISSUE: (2064) RDO.rdoEngine property rdoEngine.rdoEnvironments was not upgraded. 'Dim mobjRDOConn As SqlConnection = rdoEngine.rdoEnvironments(0).OpenConnection("PropDB", RDO.PromptConstants.rdDriverNoPrompt, , "UID=admin;PWD=")
Dim tempAdapter As SqlDataAdapter =NewSqlDataAdapter(mstrSQL, mobjRDOConn)Dim mobjRDORst AsNewDataSettempAdapter.Fill(mobjRDORst)If Not (mobjRDORst.Tables(0).Rows.Count =0) Then'UPGRADE_WARNING: (2077) Change the default 0 index in the Rows property with the correct one.' MessageBox.Show(mobjRDORst.Tables(0).Rows(0).Item("propno"), Application.ProductName)End If
5.2. To ADO.NET using System.Data.Common and helpers classes
This optional feature allows the VBUC to convert RDO to ADO.NET by using the System.Data.Common libraries and some helpers.
This option converts RDO to ADO.NET using helper classes, achieving a higher automation level.
If the VB6 code is using data binding to grids, it is also recommended to convert all data-bound grids to .NET components.
Transformations to occurrences of MSRDC will also be applied when selecting this choice.
General Description:
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 (e.g. SQL Server, Oracle, MS Access, etc) through its ADO.NET 2.0 compliant providers with just minimal configuration effort.
This feature also supports the MSRDC data control.
Deployment Note:
This feature uses a .NET configuration file containing specific sections. An example file is created in the output path of the migration solution. This file should be added to the main executable assembly and configured according to your needs by selecting the proper provider factory.
Class
Maps to
RDO.rdoConnection
System.Data.Common.DbConnection
RDO.rdoResultset
UpgradeHelpers.DB.RDO.RDORecordSetHelper
RDO.rdoColumns
System.Data.DataColumnCollection
RDO.rdoParameter
System.Data.Common.DbParameter
RDO.rdoParameters
System.Data.Common.DbParameterCollection
RDO.rdoPreparedStatement
System.Data.Common.DbCommand
RDO.rdoQuery
System.Data.Common.DbCommand
RDO.rdoEngine
UpgradeHelpers.DB.RDO.RDOEngineHelper
RDO.rdoEnvironment
UpgradeHelpers.DB.RDO.RDOEnvironmentHelper
RDO.rdoColumn
System.Data.DataColumn
RDO.DataTypeConstants
System.Data.DbType
RDO.LockTypeConstants
UpgradeHelpers.DB.RDO.LockTypeConstants
RDO.QueryTypeConstants
System.Data.CommandType
Original VB6 code:
Dim mobjRDOConn As rdoConnectionDim mobjRDORst As rdoResultsetDim mstrSQL AsString'Connect to the Property database:'Set mobjRDOConn = rdoEngine.rdoEnvironments(0).OpenConnection ("PropDB", rdDriverNoPrompt,,"UID=admin;PWD=")Set mobjRDORst = mobjRDOConn.OpenResultset(mstrSQL, rdOpenKeyset, rdConcurRowVer)If Not mobjRDORst.EOF Then MsgBox mobjRDORst("propno")End If
C# code:
string mstrSQL =String.Empty;//Connect to the Property database:DbConnection mobjRDOConn = (DbConnection)RDOEngineHelper.Instance(UpgradeHelpers.DB.AdoFactoryManager.GetFactory()).Environments[0].OpenConnection("UID=admin;PWD=");
RDORecordSetHelper mobjRDORst =RDORecordSetHelper.Open(mstrSQL, mobjRDOConn,LockTypeConstants.rdConcurRowVer,"");if (!mobjRDORst.EOF){MessageBox.Show(Convert.ToString(mobjRDORst["propno"]),Application.ProductName);}
VB.NET code:
Dim mstrSQL AsString=""'Connect to the Property database:'Dim mobjRDOConn As DbConnection = RDOEngineHelper.Instance(UpgradeHelpers.DB.AdoFactoryManager.GetFactory()).Environments(0).OpenConnection("UID=admin;PWD=")
Dim mobjRDORst As RDORecordSetHelper = RDORecordSetHelper.Open(mstrSQL, mobjRDOConn, LockTypeConstants.rdConcurRowVer, "")
If Not mobjRDORst.EOF Then MessageBox.Show(mobjRDORst("propno"), Application.ProductName)End If
5.3. To COM Interop
This optional feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control’s functionality will remain the same since it will use the same binary, but the resulting application will depend on the legacy control.
Original VB6 code:
Dim mobjRDOConn As rdoConnectionDim mobjRDORst As rdoResultsetDim mstrSQL AsStringSet mobjRDOConn = rdoEngine.rdoEnvironments(0).OpenConnection ("PropDB", rdDriverNoPrompt,,"UID=admin;PWD=")Set mobjRDORst = mobjRDOConn.OpenResultset(mstrSQL, rdOpenKeyset, rdConcurRowVer)If Not mobjRDORst.EOF Then MsgBox mobjRDORst("propno")End If
Dim mstrSQL AsString=""Dim mobjRDOConn As RDO.rdoConnection = RDO_rdoEngine_definst.rdoEnvironments(0).OpenConnection("PropDB", RDO.PromptConstants.rdDriverNoPrompt, , "UID=admin;PWD=")
Dim mobjRDORst As RDO.rdoResultset = mobjRDOConn.OpenResultset(mstrSQL, RDO.ResultsetTypeConstants.rdOpenKeyset, RDO.LockTypeConstants.rdConcurRowVer)
If Not mobjRDORst.EOF Then MessageBox.Show(mobjRDORst.rdoColumns("propno").Value, Application.ProductName)End If
6. SQLDMO
6.1. To Microsoft.SqlServer.Smo
Converts SQLDMO to ADO.Net by using Microsoft.SqlServer.Management.Smo libraries and some helpers.
This option converts SQLDMO to ADO.NET by using helper classes but with a high automation level.
If the VB6 code is using data binding to grids, it is also recommended to convert all data grids to .NET components.
General Description:
This solution uses a set of helper objects to provide equivalent behavior in .NET and to encapsulate the ADO.NET machinery required in order to handle a set of data, more specifically for the RecordSet object, which is very powerful and flexible in VB6 and does not have a direct equivalence in .NET. The approach reduces the manual changes effort to achieve functional equivalence.
The usage of Microsoft.SqlServer.Management.Smo libraries provide the application with a collection of objects designed for Microsoft SQL Server management.
Deployment Note:
This feature needs to be configured using a .NET configuration file containing specific sections. An example file is created in the output path of the migration solution. This file should be added to the main executable assembly and configured according to your needs by selecting the proper provider factory.
//Declaring Object VariablesMicrosoft.SqlServer.Management.Smo.Server oSQLServer =newMicrosoft.SqlServer.Management.Smo.Server();Microsoft.SqlServer.Management.Common.ServerConnection TempConnection = new Microsoft.SqlServer.Management.Common.ServerConnection("Server", "username", "password");
TempConnection.LoginSecure=false;oSQLServer =newMicrosoft.SqlServer.Management.Smo.Server(TempConnection);Microsoft.SqlServer.Management.Smo.Database oDatabase =newMicrosoft.SqlServer.Management.Smo.Database();Microsoft.SqlServer.Management.Smo.DataFile oDBFileData =newMicrosoft.SqlServer.Management.Smo.DataFile();oDatabase.Name="Database";oDBFileData.Name="DatabaseFile";//UPGRADE_WARNING: (2074) DbFile property oDBFileData.PhysicalName was upgraded to oDBFileData.FileName which has a new behavior.
oDBFileData.FileName="c:\\program files\\microsoft sql server\\mssql\\data\\database.mdf";//UPGRADE_ISSUE: (2064) SQLDMO.DBFile property oDBFileData.PrimaryFile was not upgraded. oDBFileData.setPrimaryFile(true);
//UPGRADE_ISSUE: (2070) Constant SQLDMOGrowth_MB was not upgraded.oDBFileData.GrowthType=UpgradeStubs.Microsoft_SqlServer_Management_Smo_FileGrowthType.getSQLDMOGrowth_MB();//UPGRADE_WARNING: (2074) DbFile property oDBFileData.FileGrowth was upgraded to oDBFileData.Growth which has a new behavior.
oDBFileData.Growth=1;//UPGRADE_ISSUE: (2064) SQLDMO.DBFiles method FileGroups.DBFiles.Add was not upgraded. oDatabase.FileGroups["PRIMARY"].Files.Add(oDBFileData);
//UPGRADE_ISSUE: (2064) SQLDMO.Databases method Databases.Add was not upgraded. oSQLServer.Databases.Add(oDatabase);
VB.NET code:
'Declaring Object Variables'Dim oSQLServer AsNewMicrosoft.SqlServer.Management.Smo.Server()Dim TempConnection As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection("Server", "username", "password")
TempConnection.LoginSecure =FalseoSQLServer =NewMicrosoft.SqlServer.Management.Smo.Server(TempConnection)Dim oDatabase AsNewMicrosoft.SqlServer.Management.Smo.Database()Dim oDBFileData AsNewMicrosoft.SqlServer.Management.Smo.DataFile()oDatabase.Name ="Database"oDBFileData.Name ="DatabaseFile"'UPGRADE_WARNING: (2074) DbFile property oDBFileData.PhysicalName was upgraded to oDBFileData.FileName which has a new behavior. '
oDBFileData.FileName ="c:\program files\microsoft sql server\mssql\data\database.mdf"'UPGRADE_ISSUE: (2064) SQLDMO.DBFile property oDBFileData.PrimaryFile was not upgraded.'oDBFileData.setPrimaryFile(True)'UPGRADE_ISSUE: (2070) Constant SQLDMOGrowth_MB was not upgraded.'oDBFileData.GrowthType = UpgradeSolution1Support.UpgradeStubs.Microsoft_SqlServer_Management_Smo_FileGrowthType.getSQLDMOGrowth_MB()
'UPGRADE_WARNING: (2074) DbFile property oDBFileData.FileGrowth was upgraded to oDBFileData.Growth which has a new behavior.'
oDBFileData.Growth =1'UPGRADE_ISSUE: (2064) SQLDMO.DBFiles method FileGroups.DBFiles.Add was not upgraded.'oDatabase.FileGroups.Item("PRIMARY").Files.Add(oDBFileData)'UPGRADE_ISSUE: (2064) SQLDMO.Databases method Databases.Add was not upgraded.'oSQLServer.Databases.Add(oDatabase)
6.2. To COM Interop
This optional feature will take the legacy COM control and create an interoperability code wrapper to make it visible from the managed code. This means the control’s functionality will remain the same since it will use the same binary but the resulting application will depend on the legacy control.