Text file data to SQL-server

[noccy]

Hi!

 

I am currently upgrading a VB6 application to .NET, and I have some problems with a function that moves data from a text file into a SQL-server database.

 

With the "old" application I used Access, and ADO, and now I want to use SQL-server and ADO.NET.

 

I have tried different solutions with parameterized queries while looping through the text file, but its just to slow... 15 000 lines takes about 90 seconds to import, which is at least 80 seconds more than my "old" solution.

 

Has anyone god ideas on how to make the import as fast as possible?

Here is my VB6 code (that spent 5 seconds on the 15 000 lines)

 

Dim strFileToOpen As String
Dim i As Long
Dim strLine As String
Dim varFields As Variant
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
 
strFileToOpen = "c:\test.txt"

     
con.ConnectionString = "c:\test.mdb"
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open

rs.Open "SELECT * from tblMytable", con, adOpenStatic, adLockOptimistic
  
 i = FreeFile
 Open strFileToOpen For Input As #i
 'Line Input #i, strLine
 Do While Not EOF(i)
   
Line Input #i, strLine
   varFields = Split(strLine, ";")
      
   With rs
     .AddNew
     !Field1 = varFields(0)
     !Field2 = varFields(1)
     !Field3 = varFields(2)
     !Field4 = varFields(3)
     !Field5 = varFields(4)
     !Field6 = varFields(5)
     !Field7 = varFields(6)
     !Field8 = varFields(7)
     !Field9 = varFields(8)
     !Field10 = varFields(9)
     !Field11 = varFields(10)
     !Field12 = varFields(11)
     !Field13 = varFields(12)
    
     .Update
  End With

Loop
Close #i

MsgBox "File was imported"

 

 

Thanks in advance

 

 

noccy