Convert VB6 ADO to vs 2008 ADO.NET

Posted on 2009-02-11
Last Modified: 2012-05-06
I have an old VB6 application which works well.
For performance it runs on the server that where the database resides.
The server OS is Windows Server 2003 R2 x64
It processes log files and imports the data into an SQL 2005 database after validating the data in each log file. There can be over 50 files per day and each log file can contain more than 5000 records. The database grows at approx 5GB every six months. There are approx 45 tables in the database.
I connect to the database via code below.
The database is interogated by opening recordsets as needed and all insertions are done by building the sql statement in a string varaible and issuing a gcnADO.Execute command to insert/update a record on any given table table.
I want to convert this application to VS2008, visual basic.
What is the best practice to do this ?
Public gcnADO As ADODB.Connection

Public cmd As New ADODB.Command

Public Function ConnectToDatabase(ByVal strServer As String, ByVal strPassword As String, ByVal sDBName As String) As Boolean

On Error GoTo Error_ConnectToDatabase

   Dim strConnect    As String

   Dim strError      As String

   ConnectToDatabase = False

   Set gcnADO = New ADODB.Connection

   strConnect = "Data Source = " & strServer

   gcnADO.Provider = "SQLOLEDB.1"

   gcnADO.Open strConnect, gstrUserID, strPassword

   gcnADO.DefaultDatabase = sDBName

   ConnectToDatabase = True


   Exit Function


   strError = "Cannot Open Database Error No :" & Err.Number & " Description :" & Err.Description

   MsgBox strError, vbCritical, "Critical Error"

   Resume Exit_ConnectToDatabase

End Function

Open in new window

Question by:josephwalsh
    LVL 69

    Accepted Solution

    LVL 5

    Expert Comment

    What information are you looking for? There are many different ways to do what your asking. You could use a strongly typed dataset that contains all your tables. Then add rows to the tables and call the tables TableAdapter's update method to perform all the inserts for you. Or pass the datasets table to a SQLBulkCopy method.
    LVL 5

    Author Comment

    This is my first app. in the .NET environment.
    I would like to do it right, and am open to suggestions.
    I think that Bulkcopy might not be the way as there is an internal transactionID per transaction and there would be entries in other related tables per transaction. The transactionID does no come from the log file. Do you need more info ?
    LVL 69

    Expert Comment

    by:Éric Moreau
    the link I have provided gives you different ways of doing it

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now