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;

Solution

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;

Comments

[Paul Bastide]

you might want to elaborate on the solution, providing text and reasons for the code.