c# - 将大量记录(批量插入)写入 .NET/C# 中的 Access

Question

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

请注意，在我“重构”它之前，我的原始帖子在问题部分既有问题又有答案。我接受了 Igor Turman 的建议并将其分为两部分重新编写 - 上面的问题和我的回答。

Answer 1

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

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

02.8 秒:使用DAO，使用DAO.Field是指表列

02.8 秒:写出到文本文件，使用自动化将文本导入 Access

11.0 秒:使用DAO，使用列索引来引用表列。

17.0 秒:使用DAO，引用名称列

79.0 秒:使用 ADO.NET，为每一行生成 INSERT 语句

86.0 秒:使用 ADO.NET，使用 DataTable 到 DataAdapter 进行“批量”插入

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

由于各种原因，我想使用 C# 而不是 VBA。典型的方式是使用OleDB连接Access。我使用了 OleDbDataReader抓取数百万条记录，而且效果很好。但是将结果输出到表格时，需要很长时间。一个多小时。

首先，让我们讨论从 C# 向 Access 写入记录的两种典型方式。这两种方式都涉及 OleDB 和 ADO.NET。第一种是一次生成一个 INSERT 语句并执行它们，100 000 条记录需要 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;
}

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

文本文件对于“国际化”也很脆弱，其中使用逗号作为小数点分隔符、不同的日期格式、可能使用 unicode。

请注意，第一条记录包含字段名称，因此列顺序不依赖于表，并且我们使用自动化来执行文本文件的实际导入。

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 之间交互的最佳方式，尤其是当您需要写出大量记录时。此外，它还可以 Access 表的所有属性。我在某处读到使用 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;
}

在此代码中，我们为每一列 ( myFields[k] ) 创建了 DAO.Field 变量，然后使用它们。耗时 2.8 秒。或者，可以直接 Access 注释行 rs.Fields["Field" + (k + 1).ToString()].Value = i + k; 中找到的那些字段。这将时间增加到 17 秒。将代码包装在事务中(请参阅注释行)将其缩短到 14 秒。使用整数索引 rs.Fields[k].Value = i + k;将其降至 11 秒。使用 DAO.Field ( 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