Right way to export multiple records to Access using OLEDB

Question

I don't know much about exporting data using OLEDB, I figured the following:

using (OleDbConnection conn = new OleDbConnection(connString))
{
   try
   {
      conn.Open();
      foreach (T t in rows)
      {
         using( OleDbCommand oleDbCommand = new OleDbCommand(insertString, conn))
         {
               OleDbParameter param = new OleDbParameter(.., ..);
               oleDbCommand.Parameters.Add(param);
               //add more parameters
               oleDbCommand.ExecuteNonQuery();
         }
      }
   }
   catch (Exception e)
   {
      //handle
   }
}

At first, I didn't use the using statement for the OleDbCommand (i.e., I didn't dispose of the OleDbCommand). But in that case, a record locking file remained on the database even though I was finished exporting. WITH the (inner) Using statement however, exporting seems slower. Why? And how to get both the fast exporting and the removal of the record locking at the end of exporting?

Answer 1

Since you do not close the Connection, the ldb file remains present as you're still connected to the DB.

So, you should put a using statement around the connection-declaration (or close the connection in the finally block of your try statement [which is exactly what the using statement does nb).

using( var conn = new OleDbConnection (connectionstring) )
{
    conn.Open();

    using( cmd = conn.CreateCommand() )
    {
         cmd.Parameters.Add (..);
         ...

         for( ... )
         {
            cmd.Parameters.Clear();
            cmd.CommandText = "";
            cmd.Parameters["@p_param"].Value = ...

            cmd.ExecuteNonQuery();
         }
    }
}

With the using clause in the for-loop, you're disposing the OleDbCommand for every record that you're going to insert. However, you can re-use the OleDbCommand instance for every insert. (See code above).

Next to that, you should start a transaction explicitly. Since, when you do not do that, an implicit transaction will be created for each insert statement. Also, by performing all the inserts inside one transaction, you'll be able to rollback all the changes (inserts) that you've done when you encounter an error.
(For instance, if inserting row nr 159 fails, you can rollback all the 158 inserts that you've done before).