Vb.net saveing records fast to access or SQL express

See more:

VB

.NET

database

DataAdapter

Hi
I have a need to insert records to either a database and currently using Microsoft Access.

I have done some code, and roughly 10,000 records takes 12secs (using Access), I am wondering if I can improve my code in any way. I believe you cannot do a bulk insert to Microsoft Access, I am not sure if you can with SQL Server Express.

My code is below and I am looking for/to see if there are ways to speed up the existing code and/or another way of acheieving a faster insert rate.

The code is currently adding static test data and I am aware that i should be using parameters for the fields.

Any suggestions please, will SQL Express be any quicker? My other option is MySQL.


Thanks


Neil

What I have tried:

<pre>Dim connetionString As String
        Dim connection As OleDbConnection
        Dim oledbAdapter As New OleDbDataAdapter
        Dim sql As String

        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Neil Cooley\Desktop\Database1.accdb"
        connection = New OleDbConnection(connetionString)

        Try
            connection.Open()
            Dim stopwatch_1 As New Stopwatch
            stopwatch_1.Start()
            Dim getDate As Date = Date.Now
            For i As Integer = 1 To 10 Step 1
                sql = "INSERT INTO Lines VALUES('TAG " & i.ToString & "','" & i.ToString & "','192','" & getDate & "')"
                oledbAdapter.InsertCommand = New OleDbCommand(sql, connection)
                oledbAdapter.InsertCommand.ExecuteNonQuery()
            Next

            stopwatch_1.Stop()

            MsgBox("Row(s) Inserted !! - " & stopwatch_1.ElapsedMilliseconds.ToString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try