none
Alternatives to using OleDbCommandBuilder - AOD.NET and Access RRS feed

  • Question

  • I have the following code used to update a simple database. It works well but performance is not great when the Access database is on a NAS drive (using a UNC name) vs local drive. I have read that OleDbCommandBuilder (here) in fact updates ALL the fields of my database and not only those I change. However, I haven't the faintest clue how to use OleDbCommand instead (aka, building my own command) and I think executing a ExecuteNonQuery.

    Can anyone suggest a more efficient update mechanism so that my transaction time is better ?

    TIA

    -----------------------

            Dim myDataAdapter As New OleDbDataAdapter(My.Settings.DBSelectSpecific & cr.m_iID, m_connectionString)
            Dim myDataSet As New DataSet()

            Try
                myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                myDataAdapter.Fill(myDataSet, My.Settings.DBTableName)

                Dim cmdBldr As New OleDb.OleDbCommandBuilder(myDataAdapter)

                Main.FormatEventString("CommandBuilder: " & cmdBldr.GetUpdateCommand.CommandText.ToString, "SYSTEM ERROR")

                ' Dataset should contain only 1 row which is the result of the query
                Dim tempRow As DataRow = myDataSet.Tables(0).Rows(0)

                '  This doesn't change anything right now, but this is the idea...
                If bFullRecordUpdate Then
                  tempRow("FirstName") = cr.m_strFirstName
                  tempRow("LastName") = cr.m_strLastName
                  tempRow("Telephone") = cr.m_strPhoneNumber
                  tempRow("EMail") = cr.m_strEMail
                  tempRow("Notes") = cr.m_strNotes
                End If

                tempRow("TuneUps") = cr.m_snTuneupCount
                tempRow("Log") = cr.m_strLog

                myDataAdapter.Update(myDataSet, My.Settings.DBTableName)

                tempRow = Nothing
                cmdBldr = Nothing

            Catch ex As Exception
                Main.FormatEventString("Unable to update database for customer: " & cr.GetName & " !", "SYSTEM ERROR")
            End Try

    Friday, October 5, 2012 1:59 AM

Answers

  • Looks good to me. Hopefully that will speed up your updates.

    Also, I would highly recommend using Command Parameters for the column values instead of inserting variables into your SQL statement (if that is what you are doing).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by pjg61 Saturday, October 6, 2012 3:05 PM
    Saturday, October 6, 2012 12:17 PM

All replies

  • Are you performing batch updates of rows? This will definitely be slower.

    If you are not updating multiple rows at one time then you may want to consider ExecuteNonQuery instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, October 5, 2012 12:00 PM
  • Paul,

    I am only updating 1 row. Sometimes, I update all the columns and sometimes only two columns. So, I have come up with the following code. It works fine.  Can you simply tell me if all is good or something better or more resilient?

    Thanks

    Pierre

              Using connection As New OleDbConnection(m_connectionString)
                  Dim command As New OleDbCommand(updateSQL)

                  ' Set the Connection to the new OleDbConnection.
                  command.Connection = connection

                  ' Open the connection and execute the update command.
                  Try
                    connection.Open()
                    command.ExecuteNonQuery()

                  Catch ex As Exception
                    Main.FormatEventString("Unable to update TuneUps Count for customer: " & cr.GetName & " !", "SYSTEM ERROR")
                  End Try
                  ' The connection is automatically closed when the code exits the Using block.
              End Using

    Saturday, October 6, 2012 1:23 AM
  • Looks good to me. Hopefully that will speed up your updates.

    Also, I would highly recommend using Command Parameters for the column values instead of inserting variables into your SQL statement (if that is what you are doing).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by pjg61 Saturday, October 6, 2012 3:05 PM
    Saturday, October 6, 2012 12:17 PM
  • Paul,

    Here is my final code which appears to work well.

    Thanks,

    Pierre

    --------------

              Dim updateSQL As String = "UPDATE " & My.Resources.DBTableName & " SET TuneUps = ?, Log = ? WHERE ID = ?"

              Using connection As New OleDbConnection(m_connectionString)
                  Dim command As New OleDbCommand(updateSQL)

                  command.Parameters.AddWithValue("TuneUps", cr.m_snTuneupCount)
                  command.Parameters.AddWithValue("Log", cr.m_strLog)
                  command.Parameters.AddWithValue("ID", cr.m_iID)

                  ' Set the Connection to the new OleDbConnection.
                  command.Connection = connection

                  ' Open the connection and execute the update command.
                  Try
                    connection.Open()
                    command.ExecuteNonQuery()

                  Catch ex As Exception
                    '  Handle exception...
                  End Try
                  ' The connection is automatically closed when the code exits the Using block.
              End Using

    Saturday, October 6, 2012 3:06 PM