SQLite, Copy DataSet / DataTable to DataBase file

Question

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.

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.

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();
}

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.

Answer 1

• 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);
  

• Create the table in the SQLite database, by executing the CREATE TABLE statement directly with SQLiteCommand.ExecuteNonQuery

• 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...