Writing large number of records (bulk insert) to A

Question

What is the best way to perform bulk inserts into an MS Access database from .NET? Using ADO.NET, it is taking way over an hour to write out a large dataset.

Note that my original post, before I "refactored" it, had both the question and answer in the question part. I took Igor Turman's suggestion and re-wrote it in two parts - the question above and followed by my answer.

Answer 1

Thanks Marc, in order to vote you I created an account on StackOverFlow...

Below is the reusable method [Tested on C# with 64 Bit - Win 7, Windows 2008 R2, Vista, XP platforms]

Performance Details: Exports 120,000 Rows in 4 seconds.

Copy the below code and pass the parameters... and see the performance.

• Just pass your datatable with the same schema, as of target Access Db Table.
• DBPath= Full path of access Db
• TableNm = Name of Target Access Db table.

The code:

public void BulkExportToAccess(DataTable dtOutData, String DBPath, String TableNm) 
{
    DAO.DBEngine dbEngine = new DAO.DBEngine();
    Boolean CheckFl = false;

    try
    {
        DAO.Database db = dbEngine.OpenDatabase(DBPath);
        DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm);
        DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Columns.Count];

        //Loop on each row of dtOutData
        for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++)
        {
            AccesssRecordset.AddNew();
            //Loop on column
            for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++)
            {
                // for the first time... setup the field name.
                if (!CheckFl)
                    AccesssFields[colCounter] = AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName];
                AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter];
            }

            AccesssRecordset.Update();
            CheckFl = true;
        }

        AccesssRecordset.Close();
        db.Close();
    }
    finally
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine);
        dbEngine = null;
    }
}

Answer 2

Another method to consider, involving linking tables via DAO or ADOX then executing statements like this:

SELECT * INTO Table1 FROM _LINKED_Table1

Please see my full answer here:
MS Access Batch Update via ADO.Net and COM Interoperability

Answer 3

First make sure that the access table columns have the same column names and similar types. Then you can use this function which I believe is very fast and elegant.

public void AccessBulkCopy(DataTable table)
{
    foreach (DataRow r in table.Rows)
        r.SetAdded();

    var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn);

    var cbr = new OleDbCommandBuilder(myAdapter);
    cbr.QuotePrefix = "[";
    cbr.QuoteSuffix = "]";
    cbr.GetInsertCommand(true);

    myAdapter.Update(table);
}

Answer 4

Thanks Marc for the examples.
On my system the performance of DAO is not as good as suggested here:

TestADONET_Insert_TransferToAccess(): 68 seconds
TestDAOTransferToAccess(): 29 seconds

Since on my system the use of Office interop libraries is not an option I tried a new method involving the writing of a CSV file and then importing it via ADO:

    public static double TestADONET_Insert_FromCsv()
    {
        StringBuilder names = new StringBuilder();
        for (int k = 0; k < 20; k++)
        {
            string fieldName = "Field" + (k + 1).ToString();
            if (k > 0)
            {
                names.Append(",");
            }
            names.Append(fieldName);
        }

        DateTime start = DateTime.Now;
        StreamWriter sw = new StreamWriter("tmpdata.csv");

        sw.WriteLine(names);
        for (int i = 0; i < 100000; i++)
        {
            for (int k = 0; k < 19; k++)
            {
                sw.Write(i + k);
                sw.Write(",");
            }
            sw.WriteLine(i + 19);
        }
        sw.Close();

        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            cmd.CommandText = "DELETE FROM TEMP";
            int numRowsDeleted = cmd.ExecuteNonQuery();
            Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);

            StringBuilder insertSQL = new StringBuilder("INSERT INTO TEMP (")
                .Append(names)
                .Append(") SELECT ")
                .Append(names)
                .Append(@" FROM [Text;Database=.;HDR=yes].[tmpdata.csv]");
            cmd.CommandText = insertSQL.ToString();
            cmd.ExecuteNonQuery();

            cmd.Dispose();
        }

        double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
        Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
        return elapsedTimeInSeconds;
    }

Performace analysis of TestADONET_Insert_FromCsv(): 1.9 seconds

Similar to Marc's example TestTextTransferToAccess(), this method is also fragile for a number of reasons regarding the use of CSV files.

Hope this helps.
Lorenzo

Answer 5

To add to Marc's answer:

Note that having the [STAThread] attribute above your Main method. will make your program easily able to communicate with COM objects, increasing the speed further. I know it's not for every application but if you heavily depend on DAO, I would recommend it.

Further more, using the DAO insertion method. If you have a column that is not required and you want to insert null, don't even set it's value. Setting the value cost time even if it's null.

Answer 6

You can use a KORM, object relation mapper that allows bulk operations over MsAccess.

database
  .Query<Movie>()
  .AsDbSet()
  .BulkInsert(_data);

or if you have source reader, you can directly use MsAccessBulkInsert class:

using (var bulkInsert = new MsAccessBulkInsert("connection string"))
{
   bulkInsert.Insert(sourceReader);
}

KORM is available from nuget Kros.KORM.MsAccess and it's opensource on GitHub