Import text file to access db with ado.net

[noccy]

Hello!

I have a vb6 application running with the following code to import a text file to an Access database. Can anyone tell me how to do the same import using ADO.NET?

Private Sub import()
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"


End Sub

 

[Diesel]

System.IO.FileStream fs = new System.IO.FileStream("data.txt", System.IO.FileMode.OpenOrCreate);
System.IO.StreamReader sr = new System.IO.StreamReader(fs);
string str;
string[] strLine;

System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("connection string");
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("", con);

con.Open();

while (str = sr.ReadLine())
{
strLine = str.Split(;

command.CommandText = "INSERT INTO table VALUES ("; + strLine[0] + "," + strLine[1] + "," + strLine[2];

command.ExecuteNonQuery();
}

con.Close();
tr.Close();
MessageBox.Show("File was imported");

 

[noccy]

Thank you, I see how to do it now using that method.

What if the number of records is 100k or more. Would using a parameterized query improve performance, and if so how would I set it up?

Edit:
I have run a test with 25000 records, and my old ADO method used about 3 seconds to import the file, while the ADO.NET with a loop and INSERT INTO commands used about 45 seconds...

Would a paramterized query be the best solution or are there other methods that are as/more efficient?

Here is the code I used to test:

Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click 

Dim cnn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=PC60380\VSDOTNET; Initial Catalog=****_;user id=******;password=*******") 

Dim cmd As New SqlClient.SqlCommand() 

Dim fs As New System.IO.FileStream(Me.txtFilePath.Text, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read) 

Dim sr As New System.IO.StreamReader(fs) 

Dim strLine As String 

Dim strField() As String 

Dim strF1 As String 

Dim strF2 As String 
cnn.Open()

cmd.Connection = cnn


Do While sr.EndOfStream = False 
strLine = sr.ReadLine()

strField = Split(strLine, ";")

strF1 = strField(0)


If strField.Length > 1 Then 
strF2 = strField(1)


Else 
strF2 = ""


End If 
cmd.CommandText = "INSERT INTO tblBrukere(Nummer,Bruker) VALUES (" & strF1 & ", " & strF2 & ")"

cmd.ExecuteNonQuery()


Loop 
cnn.Close()

sr.Close()

MsgBox("OK!")

End Sub

noccy

 

[Diesel]

45 seconds compared to 3! Huh.

You can try parameters...

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO tblBrukere(Nummer,Bruker) VALUES (@Nummer, @Bruker)", con);
cmd.Parameters.Add("@Nummer", System.Data.OleDb.OleDbType.Integer, 4);
cmd.Parameters["@Nummer"].Value = int.Parse(strField(1));


You should try using the Oledb class for access, and specifying the data provider.