Legacy Data Access Modernization

The .NET platform offers a new and completely redesigned collection of classes for data access that takes into consideration modern application requirements for distribution, reliability and scalability, plus a provider-independent data access model (System.Data.Common) which enables developers to write ADO.NET code that works against any .NET data provider.

So in terms of the .NET framework, the way to access data has changed: .NET uses XML as its underlying data structure, and even records from database are represented internally as XML, which is then converted to the data type required by the client application. Data access classes and methods have been re-written by Microsoft, building a new version of ActiveX Data Objects (ADO) called ADO.NET.

This new data access model provides the following advantages:

  • Interoperability: All data in ADO.NET is transported in XML format. The data is provided as a structured text document that can be read by anyone on any platform.
  • Scalability: ADO.NET promotes the use of disconnected datasets, with automatic connection pooling bundled as part of the package.
  • Productivity: ADO.NET can improve overall development time. For example, typed DataSets help you work more quickly and allow you to produce more bug-free code.
  • Performance: Because ADO.NET provides disconnected datasets, the database server is no longer a bottleneck and application performance is improved.

The Visual Basic Upgrade Companion migrates the legacy data access models (ADO, RDO, DAO, ADOR) to .NET equivalents, employing special transformation rules and helper classes (for some specific source/target combinations).

The Visual Basic Upgrade Companion provides different alternatives to upgrade the data access model to ADO.NET. For example it is possible to upgrade ADO into ADO.NET using the SqlClient libraries. This works perfect when using SQL Server, but if you want your application to connect to any database using a compliant .NET data provider, our tool generates code that uses the classes introduced with version 2.0 of the .NET Framework in the System.Data.Common namespace.

Besides the Data access libraries, the tool is able to upgrade the ADODC and MSRDC data controls.

The possible data source-target combinations are described on this table:

Source technology ADO.NET ADO.NET using Common
ADO Yes Yes
RDO Yes Yes
DAO No Yes
ADOR Yes Yes
Data Controls    
MSRDC Yes Yes using RDO COMMON
ADODC No Yes using ADO COMMON

The following example demonstrates a simple usage of ADO for data access with a simple query execution.

Original VB6 source code

Public Sub DataAccessExample()
    Set db = New ADODB.Connection
    Set rs = New ADODB.Recordset
    db.Open "Provider=MSDASQL;DSN=TikkisDb;Password=Password1;"
    rs.Open "SELECT * FROM Table1", db, adOpenKeyset, adLockPessimistic
End Sub

The Upgrade Wizard generated source code utilizes the same ADO technology to interact with data sources via COM interop wrapper calls, as seen in this example.

VB.NET code generated by the Upgrade Wizard

Public Sub DataAccessExample()
    Dim rs As Object
    Dim db As Object
    db = New ADODB.Connection
    rs = New ADODB.Recordset
    'UPGRADE_WARNING: Couldn't resolve default property of object db.Open.        
	   db.Open("Provider=MSDASQL;DSN=TikkisDb;Password=Password1;")
    'UPGRADE_WARNING: Couldn't resolve default property of object rs.Open.
	  rs.Open("SELECT * FROM Table1", db, 
	  ADODB.CursorTypeEnum.adOpenKeyset, 
	  ADODB.LockTypeEnum.adLockPessimistic)
End Sub

The Visual Basic Upgrade Companion generated source code employs .NET data access constructions. The user can select from different available techniques to upgrade the data access methodology from the tools Profile Manager screen. Besides the target language, the user can also select the data access library to be employed (SqlClient or System.Data.Common).

VB.NET code generated by the Visual Basic Upgrade Companion using SQLClient libraries

Public Sub DataAccessExample()
    Dim db As New SqlConnection
    Dim rs As New DataSet
	  db.ConnectionString = "Provider=MSDASQL;DSN=TikkisDb;Password=Password1;"
    db.Open()
    Dim com As SqlCommand = New SqlCommand()
    com.Connection = db
    com.CommandText = "SELECT * FROM Table1"
	  Dim adap As SqlDataAdapter = New SqlDataAdapter(com.CommandText, com.Connection)
    rs = New DataSet("dsl")
    adap.Fill(rs)
End Sub

C#.NET code generated by the Visual Basic Upgrade Companion using SQLClient libraries

public void  DataAccessExample()
{
    SqlConnection db = new SqlConnection();
    DataSet rs = new DataSet();
    db.ConnectionString = "Provider=MSDASQL;DSN=TikkisDb;Password=Password1;";
    db.Open();
    SqlCommand com = new SqlCommand();
    com.Connection = db;
    com.CommandText = "SELECT * FROM Table1";
    SqlDataAdapter adap = new SqlDataAdapter(com.CommandText, com.Connection);
    rs = new DataSet("dsl");
    adap.Fill(rs);
}

C#.NET code generated by the Visual Basic Upgrade Companion using System.Data.Common namespace

public void  DataAccessExample()
{
	DbConnection db = AdoFactoryManager.GetFactory().CreateConnection();
	RecordSetHelper rs = new RecordSetHelper(AdoFactoryManager.GetFactory());
	db.ConnectionString = "Provider=MSDASQL;DSN=TikkisDb;Password=Password1;";
	db.Open();
	rs.Open("SELECT * FROM Table1", db, LockTypeEnum.adLockPessimistic);
}