c# - 从 MySQL 到 MS Access 的 1GB 数据

Question

情况: 我正在创建一个自动化任务，该任务查询 MySQL(通过 ODBC)并使用 OLEDB 将结果集插入到 MS Access 数据库 (.mdb)。

代码:

OleDbConnection accCon = new OleDbConnection();
OdbcCommand mySQLCon = new OdbcCommand();
try
{
    //connect to mysql
    Connect();                
    mySQLCon.Connection = connection;              

    //connect to access
    accCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
        @"Data source= " + pathToAccess;
    accCon.Open();
    var cnt = 0;

    while (cnt < 5)
    {
        if (accCon.State == ConnectionState.Open)
            break;
        cnt++;
        System.Threading.Thread.Sleep(50);
    }

    if (cnt == 5)
    {
        ToolBox.logThis("Connection to Access DB did not open. Exit Process");
        return;
    }
} catch (Exception e)
{
    ToolBox.logThis("Faild to Open connections. msg -> " + e.Message + "\\n" + e.StackTrace);
}
OleDbCommand accCmn = new OleDbCommand();
accCmn.Connection = accCon;
//access insert query structure
var insertAccessQuery = "INSERT INTO {0} values({1});";
// key = > tbl name in access, value = > mysql query to b executed
foreach (var table in tblNQuery)
{
    try
    {
        mySQLCon.CommandText = table.Value;
        //executed mysql query                        
        using (var dataReader = mySQLCon.ExecuteReader())
        {
            //variable to hold row data
            var rowData = new object[dataReader.FieldCount];
            var parameters = "";
            //read the result set from mysql query
            while (dataReader.Read())
            {
                //fill rowData with the row values
                dataReader.GetValues(rowData);
                //build the parameters for insert query
                for (var i = 0; i < dataReader.FieldCount; i++)
                    parameters += "'" + rowData[i] + "',";

                parameters = parameters.TrimEnd(',');
                //insert to access
                accCmn.CommandText = string.Format(insertAccessQuery, table.Key, parameters);
                try
                {
                    accCmn.ExecuteNonQuery();
                }
                catch (Exception exc)
                {
                    ToolBox.logThis("Faild to insert to access db. msg -> " + exc.Message + "\\n\\tInsert query -> " + accCmn.CommandText );
                }                              
                parameters = "";
            }
        }
    }
    catch (Exception e)
    {
        ToolBox.logThis("Faild to populate access db. msg -> " + e.Message + "\\n" + e.StackTrace);
    }
}
Disconnect();
accCmn.Dispose();
accCon.Close();

问题:

1. 内存使用率非常高 (300MB++)，而 MS Access 文件大小却没有经常变化!似乎插入缓存数据而不是将其保存到磁盘。

2. 速度很慢!我知道我的查询会在几秒钟内执行，但插入过程需要很长时间。

我尝试在 MS Access 中使用准备好的语句并将值作为参数而不是字符串连接插入来创建插入查询。但是我收到此异常消息:

Data type mismatch in criteria expression.

有人知道如何解决这个问题或有更好的方法吗？

Answer 1

您可以创建一个使用 DoCmd.TransferDatabase 的 VBA 宏通过 ODBC 将数据提取到 Access 数据库中的方法。它也可能会更快、更简单。

要从外部程序或计划任务运行 VBA 代码，只需启动 Access 以使用/x 命令行开关打开您的文件，它将在启动时运行导入宏。 1 GB 的数据仍然需要一段时间。我找到了一个 article by David Catriel that implemented this approach .

更好的选择是使用不同的数据库引擎后端，例如 SQL Server Express 的免费版本。然后你有更多的选择，它也更强大。如果您需要 MS Access 表格和报告，如果您使用 SQL Server，则可以创建一个 ADP 项目文件，或者您可以使用链接表来获取数据。你甚至可以 use Access as a front-end to your MySQL database如果可以满足您的要求，而不是复制所有数据。