Writing changes to a SQLite database using SqliteDataAdapter

Question

What am I missing from the following code? In this code fragment, I am reading in a table from a SQLite database. I am then updating a cell, and then reading back the change.

This code is a simplified version of larger code, but it illustrates the problem.

The code reads the table perfectly, however AcceptChanges() does not write anything back. I verified that with the repeated read and by going to SQLiteAdmin and perusing the table.

I tried adding the "oLocalAdapter.Update(oLocalSet.Tables[0]);" line, however that did not make any difference. I saw that doing a search.

using System.Data.SQLite;

// DATABASE (Local): Formulate the SQL command.
String strSqlCommand = "SELECT * FROM [tblTest] ORDER BY [IdPrimary] ASC;";
SQLiteCommand oLocalCommand = new SQLiteCommand(strSqlCommand, ClassEngine.Connection);

// DATABASE (Local): Get the data records.
SQLiteDataAdapter oLocalAdapter = new SQLiteDataAdapter(oLocalCommand);
DataSet oLocalSet = new DataSet();
oLocalAdapter.Fill(oLocalSet, "tblTest");

// Try to write to some changes.
String strValue = oLocalSet.Tables[0].Rows[0][8].ToString();
oLocalSet.Tables[0].Rows[0][8] = 9;
oLocalSet.Tables[0].AcceptChanges();
oLocalAdapter.Update(oLocalSet.Tables[0]);

// Clean up.
oLocalSet.Dispose();
oLocalAdapter.Dispose();
oLocalCommand.Dispose();
oLocalCommand = null;

Sarah Weinberger · Answer 1 · 2013-05-10T20:16:59.553

Okay, got it.

One, I had to relocate/modify the AcceptChanges() line.

That includes the possible line

oLocalAdapter.AcceptChangesDuringUpdate = true;

I then had to add in

SQLiteCommandBuilder oBuilder = new SQLiteCommandBuilder(oLocalAdapter);
oLocalAdapter.UpdateCommand = oBuilder.GetUpdateCommand();

The last line is then the update and it works. That makes the code:

// DATABASE (Local): Formulate the SQL command.
String strSqlCommand = "SELECT * FROM tblTest ORDER BY IdPrimary ASC;";
SQLiteCommand oLocalCommand = new SQLiteCommand(strSqlCommand, ClassEngine.Connection);

// DATABASE (Local): Get the data records.
SQLiteDataAdapter oLocalAdapter = new SQLiteDataAdapter(oLocalCommand);
DataSet oLocalSet = new DataSet();
oLocalAdapter.Fill(oLocalSet, "tblTest");

// 
SQLiteCommandBuilder oBuilder = new SQLiteCommandBuilder(oLocalAdapter);

// Try to write to some changes.
String strValue = oLocalSet.Tables[0].Rows[0][8].ToString();
oLocalSet.Tables[0].Rows[0][8] = 9;
strValue = oLocalSet.Tables[0].Rows[0][8].ToString();
oLocalSet.AcceptChanges();
oLocalAdapter.UpdateCommand = oBuilder.GetUpdateCommand();
oLocalAdapter.Update(oLocalSet.Tables[0]);

// Clean up.
oLocalSet.Dispose();
oLocalAdapter.Dispose();
oLocalCommand.Dispose();
oLocalCommand = null;

score 0 · Answer 2 · answered Dec 29 '16 at 19:59

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

you might want to elaborate on the solution, providing text and reasons for the code. — Paul Bastide, Dec 29 '16 at 20:14

Writing changes to a SQLite database using SqliteDataAdapter

2 Answers2