SQLite的，复制的DataSet / DataTable的数据库文件 [英] SQLite, Copy DataSet / DataTable to DataBase file

问题描述

我已经充满了从另一个数据库文件中创建一个表数据集。该表不是我希望能够将表复制到数据库文件。

I have filled a DataSet with a Table that was created from another database file. The table is NOT in the database file which I want to be able to copy the Table to.

现在我想保存所有这些记录（数据表）到新创建的SQLite数据库文件...

Now I want to save all those records (DataTable) to a newly created SQLite database file...

我怎样才能做到这一点？

How can i do that?

此外，我真的想避免环路，如果这是可能的。

Also I really want to avoid loops if this is possible.

最好的答案是我:)所以我将分享it.This的循环，但在2-3secs写10万项。

The best answer is by me :) so i'll share it.This is loop but writes 100k entries in 2-3secs.

using (DbTransaction dbTrans = kaupykliuduomConn.BeginTransaction())
{
  downloadas.Visible = true; //my progressbar
  downloadas.Maximum = dataSet1.Tables["duomenys"].Rows.Count;

  using (DbCommand cmd = kaupykliuduomConn.CreateCommand())
  {
    cmd.CommandText = "INSERT INTO duomenys(Barkodas, Preke, kiekis) VALUES(?,?,?)";
    DbParameter Field1 = cmd.CreateParameter();
    DbParameter Field2 = cmd.CreateParameter();
    DbParameter Field3 = cmd.CreateParameter();
    cmd.Parameters.Add(Field1);
    cmd.Parameters.Add(Field2);
    cmd.Parameters.Add(Field3);

    while (n != dataSet1.Tables["duomenys"].Rows.Count)
    {
      Field1.Value = dataSet1.Tables["duomenys"].Rows[n]["Barkodas"].ToString();
      Field2.Value = dataSet1.Tables["duomenys"].Rows[n]["Preke"].ToString();
      Field3.Value = dataSet1.Tables["duomenys"].Rows[n]["kiekis"].ToString();
      downloadas.Value = n;
      n++;
      cmd.ExecuteNonQuery();
    }
  }
  dbTrans.Commit();
}

在这种情况下，dataSet1.Tables [duomenys]已经摆满了数据我需要转移到另一个数据库。我用循环来填充数据集了。

In this case dataSet1.Tables["duomenys"] is already filled with all the data i need to transfer to another database. I used loop to fill dataset too.

推荐答案

• 在加载数据表从源数据库，将数据适配器假的 AcceptChangesDuringFill 属性，因此加载记录都保存在添加状态（假定源数据库是SQL服务器）

• When you load the DataTable from the source database, set the AcceptChangesDuringFill property of the data adapter to false, so that loaded records are kept in the Added state (assuming that the source database is SQL Server)

  var sqlAdapter = new SqlDataAdapter("SELECT * FROM the_table", sqlConnection);
  DataTable table = new DataTable();
  sqlAdapter.AcceptChangesDuringFill = false;
  sqlAdapter.Fill(table);
  




• 在SQLite数据库中创建表，通过执行 CREATE表语句直接与 SQLiteCommand.ExecuteNonQuery

  创建一个新的的DataAdapter 的SQLite数据库连接，并用它来更新的DB：

  Create a new DataAdapter for the SQLite database connection, and use it to Update the db:

  var sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM the_table", sqliteConnection);
  var cmdBuilder = new SQLiteCommandBuilder(sqliteAdapter);
  sqliteAdapter.Update(table);
  




如果源和目标表具有相同列名和兼容的类型，它应该做工精细...

If the source and target tables have the same column names and compatible types, it should work fine...

这篇关于SQLite的，复制的DataSet / DataTable的数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！