We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Convert VB6 ADO to vs 2008 ADO.NET

Medium Priority
546 Views
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_ConnectToDatabase:
   Exit Function
Error_ConnectToDatabase:
   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

Comment
Watch Question

Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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.

Author

Commented:
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 ?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
the link I have provided gives you different ways of doing it
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.