locked
Various methods of DAO to ADO.NET conversion RRS feed

  • Question

  • Hi all,

    As part of a project, we are in the process of migrating DAO to ADO.NET. What are the various techniques available for the same which involves minimal manual effort.

     

    Friday, April 30, 2010 2:24 AM

Answers

  • The most minimal effort required would be to continue to use DAO from the .NET app via COM interop. Of course DAO Recordsets don't really bind directly to .NET data objects or visual controls if that is your current implementation. The less data binding you use the less effort will be required to adapt your existing data access code to .NET, and the more likely you will be able to migrate (rewrite) portions of your application in ADO.NET over time.

    There is no direct conversion path between DAO and ADO.NET since the object models are completely different.

    You may want to run the Visual Basic Code Advisor against your application first to see what changes are recommended prior to conversion. After you have performed any changes, try opening the VB 6.0 app from Visual Basic .NET to run the conversion process, or use the Upgrade Visual Basic 6 Code menu item under Tools to convert code snippets to .NET.

    There are also more comprehensive 3rd party tools and resources available to aid in the migration process:

    http://www.artinsoft.com/pr_vbcompanion.aspx

    http://vbmigration.codearchitects.com/
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 30, 2010 12:22 PM
  • There isn't really a good correlation between DAO objects and ADO.NET objects so creating a DAO wrapper in .NET may not be feasible and may in fact require more work than re-writing the code. For example, DAO can create different types of Recordsets all of which operate from a persistent connection.  ADO.NET has DataSet/DataTable classes that operate disconnected and do not have equivalent Move methods and Find or Seek. The DataReader is the only class that operates in a connected state but of course is read/forward only.

    Depending on your code, you may also find other features in DAO that have no direct support in ADO.NET (using the Jet or ACE OLEDB Provider).


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, May 3, 2010 1:36 PM

All replies

  • The most minimal effort required would be to continue to use DAO from the .NET app via COM interop. Of course DAO Recordsets don't really bind directly to .NET data objects or visual controls if that is your current implementation. The less data binding you use the less effort will be required to adapt your existing data access code to .NET, and the more likely you will be able to migrate (rewrite) portions of your application in ADO.NET over time.

    There is no direct conversion path between DAO and ADO.NET since the object models are completely different.

    You may want to run the Visual Basic Code Advisor against your application first to see what changes are recommended prior to conversion. After you have performed any changes, try opening the VB 6.0 app from Visual Basic .NET to run the conversion process, or use the Upgrade Visual Basic 6 Code menu item under Tools to convert code snippets to .NET.

    There are also more comprehensive 3rd party tools and resources available to aid in the migration process:

    http://www.artinsoft.com/pr_vbcompanion.aspx

    http://vbmigration.codearchitects.com/
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 30, 2010 12:22 PM
  • Hi Paul,

    Thanks a lot for sharing your valuable information...Just wanted to check out one more approach for converting DAO to ADO.NET...I was hoping to create wrapper classes for each of the DAO objects, which internally will have ADO.NET implementation. Is this approach feasible? I think by this way we can retain the same DAO code but internally exploiting ADO.NET capabilities...What do u think regarding this approach?

    Saturday, May 1, 2010 4:00 PM
  • There isn't really a good correlation between DAO objects and ADO.NET objects so creating a DAO wrapper in .NET may not be feasible and may in fact require more work than re-writing the code. For example, DAO can create different types of Recordsets all of which operate from a persistent connection.  ADO.NET has DataSet/DataTable classes that operate disconnected and do not have equivalent Move methods and Find or Seek. The DataReader is the only class that operates in a connected state but of course is read/forward only.

    Depending on your code, you may also find other features in DAO that have no direct support in ADO.NET (using the Jet or ACE OLEDB Provider).


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, May 3, 2010 1:36 PM
  • Thank you Paul for your good instruction and friendly help.

    Hi M Nair,

     

    Welcome to MSDN forums!

    By the way, here are some reference.

    In .NET, we recommend using ADO.NET service which is more convenient and efficient than the old ADO service.
    System.Data.OleDb namespace is used for MS Access database.
    System.Data.SqlClient namespace is used for SQL Server database.

    Code sample: Display MS Access database table records on DataGridView

    Imports System.Data.OleDb  

    Public Class Form1  

        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)  

            con.Open()  

            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)  

            Dim myDataSet As DataSet = New DataSet()  

            myDA.Fill(myDataSet, "MyTable")  

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView  

            con.Close()  

            con = Nothing 

        End Sub 

    End Class 


    Code sample: Display SQL Server database table records on DataGridView

    Imports System.Data.SqlClient  

    Public Class Form1  

        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  

            Dim con As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=True;AttachDbFilename=D:\SqlDatabase.mdf")  

            Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM AccessPage", con)  

            con.Open()  

            Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)  

            Dim myDataSet As DataSet = New DataSet()  

            myDA.Fill(myDataSet, "MyTable")  

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView  

            con.Close()  

            con = Nothing 

        End Sub 

    End Class 

    Further tutorial: Four methods to make simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) in VB.NET.
    http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/55170a2e-b4f8-46eb-8b6f-8040db334a39/


    Additionally, if you have to use classic/tradtional ADO service in VB.NET, you need to Add Reference this COM component "Microsoft ActiveX Data Object Library" to your project.

    Example: Using ADODB Services in .NET WinForms Applications in VB.NET
    http://www.vbdotnetheaven.com/UploadFile/ptailor/ADODBServices04082005081324AM/ADODBServices.aspx
    This article walks you through the usage of ADODB services in .NET application using VB.NET language. The example details the data access using ADODB, fetching recordset, filling ADO.NET dataset from the recordset and binding the same to datagrid for user display.

     

     

    Best regards,
    Martin Xie

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 7, 2010 4:03 AM
    Moderator