Saving DataTable to SQLite Database by Adapter.Update

Question

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

Answer 1

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

Answer 2

finally, I found the solution.

the problem came from rowstate of each row after I call Fill(DataTable), every DataRow in DataTable after Fill have "Added" in RowState.

so I can't call "Update(DataTable)" because it will Insert every rows in DataTable to Database. because of that, in my wrapper class I have to Delete all rows in Database before "Update(DataTable).

so, I have to change RowState in every DataRow after method Fill(DataTable) by "AcceptChanges" method.

   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;
        foreach (DataRow row in DT.Rows)
            {
            row.AcceptChanges();
            }
        return DT;
        }
    public void SaveDataTable(DataTable DT)
        {
        try
            {
            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);
            }
        }

Now I don't have to Delete all row before Update, and the problem gone.