在.net / c#中写入大量记录(批量插入)到Access

本文关键字：记录 Access 插入 net | 更新日期: 2023-09-27 18:05:44

从。net执行批量插入到MS Access数据库的最佳方法是什么?使用ADO。. NET，写一个大数据集要花一个多小时。

请注意，在我"重构"它之前，我的原始帖子在问题部分同时包含了问题和答案。我采纳了Igor Turman的建议，并将其重写为两部分——上面的问题和我的回答。

在.net / c#中写入大量记录(批量插入)到Access

我发现以特定方式使用DAO大约比使用ADO.NET快30倍。我在这个答案中分享代码和结果。作为背景，在下面的测试中，要写出一个有20列的表的100,000条记录。

技术和时间的总结-从最好到最差:

  1. 02.8秒:使用DAO，使用DAO.Field 's引用表列
  2. 02.8秒:写入文本文件，使用自动化将文本导入Access
  3. 11.0秒:使用DAO，使用列索引来引用表中的列
  4. 17.0秒:使用DAO，按名称引用列
  5. 79.0秒:使用ADO。. NET，为每一行生成INSERT语句
  6. 86.0秒:使用ADO。. NET中，为"批处理"插入
    7. 使用DataTable作为数据适配器

作为后台，我偶尔需要对相当大的数据进行分析，我发现Access是最好的平台。该分析涉及许多查询，通常还涉及大量VBA代码。

由于各种原因，我想使用c#而不是VBA。典型的方法是使用OleDB连接到Access。我使用OleDbDataReader抓取了数百万条记录，它工作得非常好。但是当将结果输出到一个表时，需要很长很长的时间。一个多小时了

首先，让我们讨论从c#中向Access写入记录的两种典型方法。这两种方式都涉及到OleDB和ADO.NET。第一种方法是每次生成一条INSERT语句，然后执行它们，这10万条记录需要79秒的时间。代码是:

public static double TestADONET_Insert_TransferToAccess()
{
  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;
  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);
    for (int i = 0; i < 100000; i++)
    {
      StringBuilder insertSQL = new StringBuilder("INSERT INTO TEMP (")
        .Append(names)
        .Append(") VALUES (");
      for (int k = 0; k < 19; k++)
      {
        insertSQL.Append(i + k).Append(",");
      }
      insertSQL.Append(i + 19).Append(")");
      cmd.CommandText = insertSQL.ToString();
      cmd.ExecuteNonQuery();
    }
    cmd.Dispose();
  }
  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

请注意，我在Access中没有发现允许批量插入的方法。

我当时认为，也许使用数据表和数据适配器将被证明是有用的。特别是因为我认为我可以使用数据适配器的UpdateBatchSize属性进行批量插入。然而，显然只有SQL Server和Oracle支持，而Access不支持。用时最长的是86秒。我使用的代码是:

public static double TestADONET_DataTable_TransferToAccess()
{
  StringBuilder names = new StringBuilder();
  StringBuilder values = new StringBuilder();
  DataTable dt = new DataTable("TEMP");
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    dt.Columns.Add(fieldName, typeof(int));
    if (k > 0)
    {
      names.Append(",");
      values.Append(",");
    }
    names.Append(fieldName);
    values.Append("@" + fieldName);
  }
  DateTime start = DateTime.Now;
  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);
  OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM TEMP", conn);
  da.InsertCommand = new OleDbCommand("INSERT INTO TEMP (" + names.ToString() + ") VALUES (" + values.ToString() + ")");
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    da.InsertCommand.Parameters.Add("@" + fieldName, OleDbType.Integer, 4, fieldName);
  }
  da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
  da.InsertCommand.Connection = conn;
  //da.UpdateBatchSize = 0;
  for (int i = 0; i < 100000; i++)
  {
    DataRow dr = dt.NewRow();
    for (int k = 0; k < 20; k++)
    {
      dr["Field" + (k + 1).ToString()] = i + k;
    }
    dt.Rows.Add(dr);
  }
  da.Update(dt);
  conn.Close();
  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

然后我尝试了非标准的方法。首先，我将其写入一个文本文件，然后使用Automation将其导入。这个速度很快——2.8秒——并列第一。但我认为这很脆弱，原因有很多:输出日期字段很棘手。我必须对它们进行特殊的格式化(someDate.ToString("yyyy-MM-dd HH:mm"))，然后建立一个特殊的"导入规范"，以这种格式进行编码。导入规范还必须正确设置"quote"分隔符。在下面的示例中，只有整数字段，因此不需要导入规范。

文本文件对于"国际化"也很脆弱，其中使用逗号作为十进制分隔符，不同的日期格式，可能使用unicode。

请注意，第一个记录包含字段名，以便列顺序不依赖于表，并且我们使用Automation来执行文本文件的实际导入。

public static double TestTextTransferToAccess()
{
  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(Properties.Settings.Default.TEMPPathLocation);
  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();
  ACCESS.Application accApplication = new ACCESS.Application();
  string databaseName = Properties.Settings.Default.AccessDB
    .Split(new char[] { ';' }).First(s => s.StartsWith("Data Source=")).Substring(12);
  accApplication.OpenCurrentDatabase(databaseName, false, "");
  accApplication.DoCmd.RunSQL("DELETE FROM TEMP");
  accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
  TableName: "TEMP",
  FileName: Properties.Settings.Default.TEMPPathLocation,
  HasFieldNames: true);
  accApplication.CloseCurrentDatabase();
  accApplication.Quit();
  accApplication = null;
  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

最后，我尝试了DAO。很多网站都对使用DAO给出了巨大的警告。然而，事实证明，它只是在Access和。net之间进行交互的最佳方式，特别是当您需要写出大量记录时。此外，它还提供对表的所有属性的访问。我在某个地方读到，使用DAO而不是ADO.NET编写事务是最简单的。

注意，有几行代码被注释了。我很快就会解释的。

public static double TestDAOTransferToAccess()
{
  string databaseName = Properties.Settings.Default.AccessDB
    .Split(new char[] { ';' }).First(s => s.StartsWith("Data Source=")).Substring(12);
  DateTime start = DateTime.Now;
  DAO.DBEngine dbEngine = new DAO.DBEngine();
  DAO.Database db = dbEngine.OpenDatabase(databaseName);
  db.Execute("DELETE FROM TEMP");
  DAO.Recordset rs = db.OpenRecordset("TEMP");
  DAO.Field[] myFields = new DAO.Field[20];
  for (int k = 0; k < 20; k++) myFields[k] = rs.Fields["Field" + (k + 1).ToString()];
  //dbEngine.BeginTrans();
  for (int i = 0; i < 100000; i++)
  {
    rs.AddNew();
    for (int k = 0; k < 20; k++)
    {
      //rs.Fields[k].Value = i + k;
      myFields[k].Value = i + k;
      //rs.Fields["Field" + (k + 1).ToString()].Value = i + k;
    }
    rs.Update();
    //if (0 == i % 5000)
    //{
      //dbEngine.CommitTrans();
      //dbEngine.BeginTrans();
    //}
  }
  //dbEngine.CommitTrans();
  rs.Close();
  db.Close();
  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}
在这段代码中，我们创建了DAO。每个列的字段变量(myFields[k])，然后使用它们。花了2.8秒。或者，也可以像在注释行rs.Fields["Field" + (k + 1).ToString()].Value = i + k;中那样直接访问这些字段，这会将时间增加到17秒。将代码封装在事务中(请参阅注释行)将其缩短为14秒。使用整数索引rs.Fields[k].Value = i + k;将该时间缩短为11秒。使用DAO。字段(myFields[k])和一个事务实际上需要更长的时间，将时间增加到3.1秒。 最后，为了完整起见，所有这些代码都在一个简单的静态类中，using语句如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ACCESS = Microsoft.Office.Interop.Access; // USED ONLY FOR THE TEXT FILE METHOD
using DAO = Microsoft.Office.Interop.Access.Dao; // USED ONLY FOR THE DAO METHOD
using System.Data; // USED ONLY FOR THE ADO.NET/DataTable METHOD
using System.Data.OleDb; // USED FOR BOTH ADO.NET METHODS
using System.IO;  // USED ONLY FOR THE TEXT FILE METHOD

谢谢Marc，为了给你投票，我在StackOverFlow上创建了一个帐户…

下面是可重用的方法[在64位c#上测试- Win 7, Windows 2008 R2, Vista, XP平台]

性能细节:在4秒内导出120,000行。

复制下面的代码并传递参数…看演出

  • 只是传递你的数据表与相同的模式，作为目标Access Db表。
  • DBPath=访问的全路径Db
  • TableNm = Target Access Db表名。

代码:

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;
    }
}

您可以使用KORM，对象关系映射器，允许在MsAccess上进行批量操作。

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

或者如果你有源代码阅读器，你可以直接使用MsAccessBulkInsert class:

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

KORM可以从nuget krose .KORM. msaccess获得，它在GitHub上是开源的

感谢Marc提供的示例。
在我的系统上，DAO的性能并不像这里建议的那么好:

TestADONET_Insert_TransferToAccess(): 68秒
TestDAOTransferToAccess(): 29秒

由于在我的系统上使用Office互操作库不是一个选项，我尝试了一种涉及编写CSV文件的新方法，然后通过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;
    }

testadonet_insert_fromsv()的性能分析:1.9秒


与Marc的示例TestTextTransferToAccess()类似，由于使用CSV文件的一些原因，该方法也很脆弱。

希望对你有帮助。
Lorenzo

首先确保访问表列具有相同的列名和相似的类型。然后你可以使用这个函数，我相信它是非常快速和优雅的。

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);
}

另一个要考虑的方法，涉及通过DAO或ADOX链接表，然后执行这样的语句:

SELECT * INTO Table1 FROM _LINKED_Table1

请在这里看到我的完整答案:
MS Access批量更新通过ADO。Net和COM互操作性

Marc的回答是:

注意，在Main方法上面有[STAThread]属性。将使您的程序能够轻松地与COM对象通信，从而进一步提高速度。我知道它并不适用于每个应用程序，但如果您非常依赖DAO，我会推荐它。

进一步，使用DAO插入方法。如果您有一个不需要的列，并且您想要插入null，甚至不要设置它的值。设置该值需要花费时间，即使它为空。

注意DAO组件在这里的位置。这有助于解释效率的提高。

相关文章：