SQLite，将数据集/数据表复制到数据库文件 [英] 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.

现在我想将所有这些记录(DataTable)保存到一个新创建的 SQLite 数据库文件中......

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

我该怎么做?

如果可能的话，我真的很想避免循环.

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

最好的答案是我 :) 所以我会分享它.这是循环，但会在 2-3 秒内写入 100k 个条目.

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.

推荐答案

• 从源数据库加载DataTable时，将数据适配器的AcceptChangesDuringFill属性设置为false，这样加载的记录就保存在已添加状态(假设源数据库为SQL Server)

  • 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 数据库中创建表，通过直接使用 SQLiteCommand.ExecuteNonQuery

    为 SQLite 数据库连接创建一个新的 DataAdapter，并用它来Update 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，将数据集/数据表复制到数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！