SQLite, copy DataSet / DataTable to database file

Question

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

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

How can i do this?

Also I really want to avoid loops if possible.

The best answer for me :), so I will share it. This is a cycle, but it records 100 thousand records in 2-3 seconds.

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 that I need to transfer to another database. I used a loop to populate a dataset.

Answer 1

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

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

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

• Create a new DataAdapter to connect the SQLite database and use it for Update 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, they should work fine ...

Answer 2

The way to import SQL data into SQLite will take a long time. When you want to import data into millions, it will take a long time. Thus, the shortest and easiest way to do this is to simply populate the data from the SQL database into the DataTable and insert all its rows into the 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(); } } 

This will take very little time compared to the upper given answers.