Since the 2.0 version of the .NET Framework, Microsoft incorporated the System.Data.Common namespace, which contains classes intended to be the base for the implementation of all data providers. This architectural decision allows application designers and programmers to use data provider-agnostic patterns on their data access layers.
VB6 offers several ways to access a database, all of them using COM libraries such as ADO, RDO and DAO. Many of the structures within these packages have their counterpart in the ADO.NET Common architecture, such as Connection and Command; in those cases most of their functionality is covered through our conversion approach.
The most common structure used to retrieve data from the database is the RecordSet object; in the case of ADO and DAO, and Resultset in RDO which are basically a collection of rows retrieved from the data source using a specific SQL command.
As counterpart in the .NET side, we have the DataSet object which also holds data retrieved from the database.
There are differences, though, between the DataSet in .NET and the RecordSet and Resultset; the most important of them, from a functional equivalence standpoint, is it’s capability to “remember” the current position and make all data-related operations affect that record. The importance of this feature increases when these kind of object is passed (as parameter) through the different tiers and components of the application.
To accomplish the same functionality in C# .NET, Mobilize has developed Helper classes to handle all these operations. Internally, this class has the necessary infrastructure to support all database requests such as DataAdapters, using the System.Data.Common namespace.
The RDO and ADODB technologies have as well visual controls to interact with the Recordset and Resultset and to bind visual controls to its data. Those controls ADODC and MSRDC are supported as part of the Data access feature to give more functional equivalence to our clients. Those controls are supported as well via helper implementations and some data binding patterns are supported and automatically upgraded.
The Helper classes are based on the specialization inheritance model, where the base class defines all common properties, methods, and the basic functionality. The derived classes overrides, adds new specific behavior to model the details. This design decision gives more functionality without breaking the natural ADO.NET architecture since the Recorset Helper classes are Datasets itself.
This structure is being use on the ADO, RDO, DAO common model as detailed on this diagram:
The following code is part of the helper code -which is periodically enhanced; specifically for the open methods that encapsulate the dataset population logic.
#region Open Operations
private void OpenRecordset()
{
operationFinished = false;
DbDataAdapter dbAdapter = CreateAdapter(activeConnection);
dbAdapter.Fill(this);
operationFinished = true;
currentView = Tables[0].DefaultView;
currentView.AllowDelete = true;
currentView.AllowEdit = true;
currentView.AllowNew = true;
if (Tables[0].Rows.Count == 0)
index = -1;
else
MoveFirst();
newRow = false;
foundRecordsCounter = 0;
OnAfterQuery();
}
public void Open()
{
if (activeConnection == null && activeCommand != null && activeCommand.Connection != null)
ActiveConnection = activeCommand.Connection;
else
throw new InvalidOperationException("The ActiveConnection property must be set before calling this method");
OpenRecordset();
}
public void Open(DbCommand command, String connectionString)
{
this.connectionString = connectionString;
Open(command, CreateConnection());
}
public void Open(DbCommand command, DbConnection connection)
{
ActiveConnection = connection;
activeCommand = command;
Open();
}
private void Open(String SQLstr, String connectionString)
{
this.connectionString = connectionString;
CommandType commandType = getCommandType(SQLstr);
DbCommand command = providerFactory.CreateCommand();
command.CommandText = SQLstr;
command.CommandType = commandType;
Open(command, connectionString);
}
#endregion
The Configuration file is an important component that supports this feature. This file contains the list of the providers that the application is capable to use and which are intended to be used as default, as well as the columns which values are calculated or retrieved automatically from the database when an insert occurs, such as identities or values set by triggers.
This tag of the configuration file details the custom configuration section handlers used by the application; in the case of an application converted to ADO .Net using Common, it must be as follows:
<configSections>
<section name="AdoIdentityColumns" type="Artinsoft.VB6.DB.AdoIdentityColumnsConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
<section name="AdoFactories" type="Artinsoft.VB6.DB. AdoFactoriesConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
</configSections>
This section details the providers that the application may use and which ones will be used as Default Provider.
This section has the following data to be filled:
<AdoFactories>
<Factories>
<add name="SQLServer" factorytype="System.Data.SqlClient" isdefault="true" databasetype=SQLServer/>
<add name="ODP" factorytype="Oracle.DataAccess.Client" isdefault="false" databasetype=Oracle/>
<add name="MSOracle" factorytype="System.Data.OracleClient" isdefault="false" databasetype=Oracle />
<add name="OracleOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Oracle />
<add name="AccessOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Access />
</Factories>
</AdoFactories>
This section details the columns that are calculated by the database when an Insert is executed, such as identities or sequence values set by a trigger.
This section has the following data to be filled:
<AdoIdentityColumns>
<IdentityColumns>
<add tablename="CR_Customreport">
<columns>
<add columnname="CR_ID" sequencename="CR_ID"/>
</columns>
</add>
</IdentityColumns>
The following example shows the configuration of an application which can connect to SQLServer and Oracle through the Microsoft provider as well as the ODP provider, and an OleDb Connection with the SQL Server provider as default.
<?xml version="1.0"?>
<configuration>
<configSections>
<section name="AdoIdentityColumns" type="Artinsoft.VB6.DB.AdoIdentityColumnsConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
<section name="AdoFactories" type="Artinsoft.VB6.DB. AdoFactoriesConfigurationSection, Artinsoft.VB6.DB" allowExeDefinition="MachineToApplication" allowLocation="true"/>
</configSections>
<AdoFactories>
<Factories>
<add name="SQLServer" factorytype="System.Data.SqlClient" isdefault="true" databasetype=SQLServer/>
<add name="ODP" factorytype="Oracle.DataAccess.Client" isdefault="false" databasetype=Oracle/>
<add name="MSOracle" factorytype="System.Data.OracleClient" isdefault="false" databasetype=Oracle />
<add name="OracleOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Oracle />
<add name="AccessOleDb" factorytype="System.Data.OleDb" isdefault="false" databasetype=Access />
</Factories>
</AdoFactories>
<AdoIdentityColumns>
<IdentityColumns>
<add tablename="CR_Customreport">
<columns>
<add columnname="CR_ID" sequencename="CR_ID"/>
</columns>
</add>
</IdentityColumns>
</configuration>
These three technologies has different ways to access the data stored on the local in memory data representation structures(Recordset, Resultset); such as Field and Fields classes on ADODB and DAO, and rdoColumn and rdoColumns on RDO.
These classes have particular characteristics since they are used to handle the metadata and the data itself. On the ADO.NET side, the metadata and the data stored on a dataset are handled by two different classes: DataColumn(For metadata), DataRow(For data).
Because of that, these classes are supported as follows:
These classes are intended to handle and report all the errors occurred during a database interaction using ADO, DAO and RDO.
These classes are not converted automatically and there is no helper provided to achieve this functionality. A manual solution to this would be to use the provider-specific collections contained in .NET, or to use the Try/Try/Catch pattern to handle the exceptions that database operation could generate.
The configuration file is automatically generated by the Visual Basic Upgrade Companion on the root of the migration solution’s output directory.
No code optimization patterns for the ADO Common feature have been implemented at this stage of the roadmap
There are data binding several patterns to implement data bindings using the visual controls MSRDC and ADODC; at this point of the roadmap we support the design time data binding pattern; it means that if you bind a control on design time with one of those controls the VBUC will generate the code that maintains the data binding functionality working.
This class exposes several events like BeginTransComplete and ConnectComplete; since this class is converted to the DbConnection class; some events are not supported.
The supported events are ConnectComplete and Disconnect even though a manual change is required to fix a compilation error:
this.lConn.Disconnect += new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
this.lConn.ConnectComplete += new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);
this.lConn.StateChange += new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
this.lConn.StateChange += new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);
this.lConn.Disconnect -= new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
this.lConn.ConnectComplete -= new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);
this.lConn.StateChange -= new System.Data.StateChangeEventHandler(this.lConn_Disconnect);
this.lConn.StateChange -= new System.Data.StateChangeEventHandler(this.lConn_ConnectComplete);
Private Sub cnnAccess_ConnectComplete(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.ConnectComplete
Private Sub cnnAccess_Disconnect(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.Disconnect
Private Sub cnnAccess_ConnectComplete(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.StateChange
Private Sub cnnAccess_Disconnect(ByVal sender As Object, ByVal e As StateChangeEventArgs) Handles cnnAccess.StateChange
From the .NET Framework 2.0 Common infrastructure perspectives, there are two native data providers to interact with Oracle; one of them is provided by Microsoft as part of the redistributable package of the Framework (System.Data.OracleClient) and the other one is provided by Oracle through its Oracle Data Provider (ODP).
This is a data provider implementation provided by Microsoft, included in the .NET Framework and compatible with the Common architecture.
This implementation tries to parse the SQL statements; issues have been detected with some PL/SQL constructs such as:
Select cust.Code ||’’|| cust.Name as Customer,
addr.First_Line ||‘ ‘||addr.Second_Line
From Customer cust, Address addr
WHERE TO_UPPER(cust.Address_Code (+)) = addr.Address_Code
This statement will throw an internal exception in the data adapter when it it is parsed, because of the combination of the old OUTER JOIN syntax and the call to the TO_UPPER function. Even though, this statement can be rewritten using the ANSI LEFT OUTER JOIN syntax:
Select cust.Code ||’’|| cust.Name as Customer,
addr.First_Line ||‘ ‘||addr.Second_Line
From Customer cust
Left Outer Join Address addr
ON TO_UPPER(cust.Address_Code) = addr.Address_Code
In summary some SQL statements have to be rewritten using this data adapter implementation.
This is a data provider implementation provided by Oracle, included on its client product; this is ADO Common–compatible since version 10.2.0.2, therefore this conversion approach requires this version or a later version of the ODP software installed on the computers that will run the application.
There are differences between some .NET Framework data types and Oracle database such as:
Oracle Type | Precision | .NET Type | Precision |
NUMBER | 38 | Decimal | 28 |
DATE TimeStamp |
Holds BC dates | DateTime | Holds Just AC dates |
INTERVAL DAY TO SECOND | 9 | TimeSpan | 7 |
To avoid this data loss issue, ODP provides a functionality called Safe Type Mapping, which consists of a property in the OracleDataAdapter class that needs to be set with a Hashtable containing the database column name and the type to be assigned to that column value.
This approach avoids data precision loss, however, from the conversion point of view it presents a problem, since the data types of the columns are replaced with the specified mappings, so data operations done through all the application may fail because of the data type substitution.
Here are some examples of the transformations needed with and without the Helper approach:
Dim mRS As ADODB.Recordset
SQL = ConvertOracleToSqlServer(dbc, SQL)
mRS.Open SQL, dbc, adOpenForwardOnly, adLockReadOnly
RecordSetHelper mRS = new RecordSetHelper();
SQL = ConvertOracleToSqlServer(dbc, SQL);
//A direct call to the helper class which takes care of the
//adapters and command creations
mRS.Open(SQL, dbc);
Set rsUpdate.ActiveConnection = dbc
rsUpdate.UpdateBatch
rsUpdate.ActiveConnection = dbc;
rsUpdate.UpdateBatch();
With prsChildRecordset
.Filter = adFilterPendingRecords
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
.Fields(psKey).Value = pvValue
.Update
.MoveNext
Loop
prsChildRecordset.Filter = adFilterPendingRecords;
if(!prsChildRecordset.BOF && ! prsChildRecordset.EOF)
{
prsChildRecordset.MoveFirst();
do{
prsChildRecordset [psKey] = pvValue;
prsChildRecordset.Update();
prsChildRecordset.MoveNext();
}
while(!prsChildRecordset.EOF)
}
9130 Jollyville Rd, Suite 175
Austin, TX 78759
Call us: +1 (425) 609-8458
info@wearegap.com