CODEWITHSUNDEEP
sqlvb.netms-accessoledb
doovers
doovers

Reputation: 8675

Insert Multiple Records into Access DB

What is the most efficient way to insert multiple records into an Access DB with VB.net?

I have a list of objects with multiple properties which are the values for an INSERT query and I want to know can I insert them all together instead of looping through the list of objects, building the query string and executing the queries one by one which is very slow.

Rough example of what I have:

    For Each Val In ValueList

        ValueString = Val.X.ToString & ", "
        ValueString += Val.Y.ToString & ", "
        ValueString += Val.Z.ToString

        SQLValueList.Add(ValueString)

    Next

    Dim cmd As OleDb.OleDbCommand
    Dim strConnection As String
    Dim strSql As String = Nothing

    strConnection = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\db.accdb;" & _
            "User ID=Admin;Password=;"

    For Each ValueString As String In SQLValueList

        strSql = "INSERT INTO Results (FldX, FldY, FldZ)" &
                    "VALUES ( " & ValueString & ");"

        cmd = New OleDb.OleDbCommand(strSql)
        cmd.Connection = New OleDb.OleDbConnection(strConnection)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()

    Next

I'm assuming there is a much better and more efficient way of doing this but I haven't been able to find it!

Upvotes: 0

Views: 2237

Answers (1)

Steve
Steve

Reputation: 216243

Yes a parameterized query 

Imports System.Data.OleDb
.......

Dim strConnection As String
Dim strSql As String = Nothing

strConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\db.accdb;" & _
        "User ID=Admin;Password=;"

strSql = "INSERT INTO Results (FldX, FldY, FldZ) VALUES ( ?, ?, ?)"
using cn = new OleDbConnection(strConnection)
using cmd = new OleDbCommand(strSql, cn)
    cn.Open()
    ' HERE all the parameters are added with a string dummy value. '
    ' This should be changed if one of the underlying field is of different type '
    ' For example, if FldX is of type integer your need to write '
    '  cmd.Parameters.AddWithValue("@p1", 0) and then in the loop code '
    '  '
    '  cmd.Parameters(0).Value = val.X  or  '
    '  cmd.Parameters(0).Value = Convert.ToInt32(val.X) if val.X is not an integer but  convertible to... '


    cmd.Parameters.AddWithValue("@p1", "")
    cmd.Parameters.AddWithValue("@p2", "")
    cmd.Parameters.AddWithValue("@p3", "")
    For Each val In ValueList
        cmd.Parameters(0).Value = val.X.ToString()
        cmd.Parameters(1).Value = val.Y.ToString()
        cmd.Parameters(2).Value = val.Z.ToString()
        cmd.ExecuteNonQuery()
   Next
End Using
End Using

This is just an example because it is not clear what kind of data is stored in your ValueList (strings, integers, doubles dates?), but I hope that the idea is clear. Create a command object with 3 parameters (one for each field to insert), add every parameter to the command collection with dummy values (in the example, every parameter contains a string value but you need to add the correct datatype for the underlying field type). At this point just loop one time on your values and execute the query.

Please, stay away to string concatenation to build an sql command, expecially when the string values to concatenate are typed by your user. You risk an Sql Injection attack

Upvotes: 3

Related Questions