I've filled a DataSet having a Table which was produced from another database file. The table isn't within the database file which I wish to have the ability to copy the Table to.

Now I wish to save all individuals records (DataTable) to some recently produced SQLite database file...

How do i do this?

Also I actually want to avoid loops if this sounds like possible.

The very best response is by me :) so i'll share it.This really is loop but creates 100k records by 50 percent-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();
}

Within this situation dataSet1.Tables["duomenys"] has already been filled with the data i have to transfer to a different database. I made use of loop to fill dataset too.

  • Whenever you load the DataTable in the source database, set the AcceptChangesDuringFill property from the data adapter to false, to ensure that loaded records are stored within the Added condition (presuming the source db is SQL Server)

    var sqlAdapter = new SqlDataAdapter("SELECT * FROM the_table", sqlConnection);
    DataTable table = new DataTable();
    sqlAdapter.AcceptChangesDuringFill = false;
    sqlAdapter.Fill(table);
    
  • Produce the table within the SQLite database, by performing the CREATE TABLE statement directly with SQLiteCommand.ExecuteNonQuery

  • Produce a new DataAdapter for that SQLite database connection, and employ it to Update the db:

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

When the source and target tables have a similar column names and compatible types, it will work fine...