Saving DataTable to SQLite Database by Adapter.Update

c#, datagrid, datatable, sqlite, wpf

Solution

I know this is an old thread, but the extra code you wrote for your solution only works because it cancels out a bug in your code, where you set:

adapter.AcceptChangesDuringFill = false ; /* default is 'true' */

If you remove the above line, you no longer need to have the row accept the changes and your method becomes much cleaner:

public DataTable GetDataTable(string tablename)
{
    DataTable DT = new DataTable();
    con.Open();
    cmd = con.CreateCommand();
    cmd.CommandText = string.Format("SELECT * FROM {0}", tablename);
    adapter = new SQLiteDataAdapter(cmd);
    adapter.Fill(DT);
    con.Close();
    DT.TableName = tablename;
    return DT;
}

Problem

I wrote SQLite wrapper class like this ``` using System; using System.Data; using System.Data.SQLite; namespace SuPOS.Sources { public class SQLITE { private SQLiteConnection con; private SQLiteCommand cmd; private SQLiteDataAdapter adapter; public SQLITE(string databasename) { con = new SQLiteConnection(string.Format("Data Source={0};Compress=True;", databasename)); } public int Execute(string sql_statement) { con.Open(); cmd = con.CreateCommand(); cmd.CommandText = sql_statement; int row_updated; try { row_updated = cmd.ExecuteNonQuery(); } catch { con.Close(); return 0; } con.Close(); return row_updated; } public DataTable GetDataTable(string tablename) { DataTable DT = new DataTable(); con.Open(); cmd = con.CreateCommand(); cmd.CommandText = string.Format("SELECT * FROM {0}", tablename); adapter = new SQLiteDataAdapter(cmd); adapter.AcceptChangesDuringFill = false; adapter.Fill(DT); con.Close(); DT.TableName = tablename; return DT; } public void SaveDataTable(DataTable DT) { try { Execute(string.Format("DELETE FROM {0}", DT.TableName)); con.Open(); cmd = con.CreateCommand(); cmd.CommandText = string.Format("SELECT * FROM {0}", DT.TableName); adapter = new SQLiteDataAdapter(cmd); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); adapter.Update(DT); con.Close(); } catch (Exception Ex) { System.Windows.MessageBox.Show(Ex.Message); } } } } ``` I retrive DataTable from Database by "GetDataTable" and binding to control.itemsource in WPF such as DataGrid I can add new row on DataGrid and save to Database properly I can change any column and save to database properly but the problem is, when I insert new row to DataGrid and call "SaveDataTable" as usual I can save properly.. then I change some column on that row now when I use "SaveDataTable" it said like this picture after this message appear, when I call "SaveDataTable" it will always said like this picture

Original source