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

Answer 2

The way to import SQL data to SQLite will take long time. When you want to import data in millions, It will take lot of time. So the shortest and easiest way to do that is just fill fetch the data from SQL database in a DataTable and insert all its rows to SQLite database.

public bool ImportDataToSQLiteDatabase(string Proc, string SQLiteDatabase, params object[] obj)
    {
        DataTable result = null;
        SqlConnection conn = null;
        SqlCommand cmd = null;
        try
        {
            result = new DataTable();
            using (conn = new SqlConnection(ConStr))
            {
                using (cmd = CreateCommand(Proc, CommandType.StoredProcedure, obj))
                {
                    cmd.Connection = conn;
                    conn.Open();
                    result.Load(cmd.ExecuteReader());
                }
            }

            using (SQLiteConnection con = new SQLiteConnection(string.Format("Data Source={0};Version=3;New=False;Compress=True;Max Pool Size=100;", SQLiteDatabase)))
            {
                con.Open();
                using (SQLiteTransaction transaction = con.BeginTransaction())
                {
                    foreach (DataRow row in result.Rows)
                    {
                        using (SQLiteCommand sqlitecommand = new SQLiteCommand("insert into table(fh,ch,mt,pn) values ('" + Convert.ToString(row[0]) + "','" + Convert.ToString(row[1]) + "','"
                                                                                                                              + Convert.ToString(row[2]) + "','" + Convert.ToString(row[3]) + "')", con))
                        {
                            sqlitecommand.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                    new General().WriteApplicationLog("Data successfully imported.");
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            result = null;
            return false;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }

It will take a very few time as compare to upper given answers.