将DataTable中的所有记录插入远程数据库

Question

我有一个DataTable，里面有几张唱片。我想将所有这些记录插入到远程数据库中。做这件事最简单的方法是什么？我发现大多数人都会遍历DataTable的各行，并逐条记录地插入记录。我只想建立一个到远程服务器的连接，并进行批量插入。有可能吗？我使用的是C#和MySQL。

Answer 1

尽管Kemal Taskin的答案是一个优雅的解决方案，但在使用大型DataTable时，它的性能很糟糕。

我试着插入了37500张唱片，花了15分钟。

它似乎是一次插入一条记录。

我发现，如果我生成一个包含1000条记录的MySQL insert语句字符串，并循环遍历数据，直到它完成，我就可以将插入时间减少到6秒。它不是批量加载，而是块加载。如果有人能想出更好的解决方案，请让我知道。

    public void writeToDBTable(DataTable dt)     
    {

        MySqlConnection conn = new MySqlConnection(globalClass.connString);
        conn.Open();
        String sql = null;
        String sqlStart = "insert into MyTable (run_id, model_id, start_frame,water_year, state_id, obligateCover, DTWoodyCover, perennialGrowth, clonalCover) values ";

        Console.WriteLine("Write to DB - Start. Records to insert  = {0}", dt.Rows.Count);
        int x = 0;            

        foreach (DataRow row in dt.Rows)
        {
            x += 1;
                if (x == 1)
                {
                    sql = String.Format(@"({0},{1},{2},{3},{4},{5},{6},{7},{8})",
                                          row["runId"],
                                          row["modelId"],
                                          row["startFrame"],
                                          row["waterYear"],
                                          row["currentFrame"],
                                          row["obligateCover"],
                                          row["DTWoodyCover"],
                                          row["perennialGrowth"],
                                          row["clonalCover"]
                                          );
                }
                else
                {
                    sql = String.Format(sql + @",({0},{1},{2},{3},{4},{5},{6},{7},{8})",
                                          row["runId"],
                                          row["modelId"],
                                          row["startFrame"],
                                          row["waterYear"],
                                          row["currentFrame"],
                                          row["obligateCover"],
                                          row["DTWoodyCover"],
                                          row["perennialGrowth"],
                                          row["clonalCover"]
                                          );

                }

                if (x == 1000)
                {
                   try
                   {
                       sql = sqlStart + sql;
                       MySqlCommand cmd = new MySqlCommand(sql, conn);
                       cmd.ExecuteNonQuery();
                       Console.WriteLine("Write {0}", x);
                       x = 0;
                   }
                   catch (Exception ex)
                    {
                        Console.WriteLine(sql);
                        Console.WriteLine(ex.ToString());
                    }
                }

        }
        // get any straglers
        if (x > 0)
        {
            try
            {
                sql = sqlStart + sql;
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                Console.WriteLine("Write {0}", x);
                x = 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(sql);
                Console.WriteLine(ex.ToString());
            }

        }

        conn.Close();
        Console.WriteLine("Write to DB - End.");
    }

Answer 2

我不知道这个答案是不是太晚了:)

你可以这样做：

// assume you have a table with one column;
string commandText = "insert into t_test1 (myid) values (@tempid)";

using (MySqlConnection cn = new MySqlConnection(myConnectionString))
{
    cn.Open();

    using (MySqlCommand cmd = new MySqlCommand(commandText, cn))
    {
        cmd.UpdatedRowSource = UpdateRowSource.None;

        cmd.Parameters.Add("?tempid", MySqlDbType.UInt32).SourceColumn = "tempid";

        MySqlDataAdapter da = new MySqlDataAdapter();
        da.InsertCommand = cmd;
        // assume DataTable dt contains one column with name "tempid"
        int records = da.Update(dt);
    }

    cn.Close();
}

Answer 3

对于Kemal Taşkın解，Kemal Ta set k set必须等于DataRowState.Added。如果不是这样，请执行以下操作：

foreach (DataRow row in dt.Rows)
    row.SetAdded();

对于Mr.Black，建议使用sql参数，不要直接使用数据值。

在将数据导入InnoDB时，请关闭自动提交模式，因为它会对每次插入执行一次日志刷新到磁盘。要在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句将其包围：

SET autocommit=0;
... SQL import statements ...
COMMIT;

性能测试:在2个表中插入5400行

从CSV文件插入

• ：3秒

使用Kemal Taşkın解决方案将数据INFILE 'data.csv‘加载到由'\t';

• insertion终止的表myTable中: 32秒

MySqlDataAdapter.Update (DataTable)

• 逐行插入()：41秒

INSERT INTO table (列)值(值)；

INSERT INTO table (列)值(值)；

...

• 在一个查询中插入所有行: 143秒

INSERT INTO table (列) VALUES (values)，(values)，...;

到目前为止，=> LOAD DATA是性能最好的！

你也可以查看这篇文章：https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html