/// <summary>
/// Execute update table to data base by command with args.
/// </summary>
/// <param name="table"></param>
/// <param name="command"></param>
/// <param name="args"></param>
/// <returns>Number of rows affected.</returns>
public virtual int ExecuteNonQuery(DataTable table, string command, params SqliteParameter[] args)
{
try
{
using (var conn = new SqliteConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = command;
cmd.Parameters.AddRange(args);
using (var adapter = new SqliteDataAdapter(cmd))
{
using (var builder = new SqliteCommandBuilder(adapter))
{
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
return(adapter.Update(table));
}
}
}
}
}
catch (Exception ex)
{
LogUtility.LogException(ex);
return(0);
}
}
[Category("NotWorking")] // Requires newer sqlite than is on wrench
public void UpdateResetRowErrorCorrectly()
{
const string connectionString = "URI = file::memory:; Version = 3";
using (var dbConnection = new SqliteConnection(connectionString)) {
dbConnection.Open();
using (var cmd = dbConnection.CreateCommand()) {
cmd.CommandText = "CREATE TABLE data (id PRIMARY KEY, name TEXT)";
cmd.ExecuteNonQuery();
}
var ts = dbConnection.BeginTransaction();
var da = new SqliteDataAdapter("SELECT * FROM data", dbConnection);
var builder = new SqliteCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();
da.UpdateCommand.Transaction = ts;
var ds1 = new DataSet();
da.Fill(ds1, "data");
var table = ds1.Tables [0];
var row = table.NewRow();
row ["id"] = 10;
row ["name"] = "Bart";
table.Rows.Add(row);
var ds2 = ds1.GetChanges();
da.Update(ds2, "data");
Assert.IsFalse(ds2.HasErrors);
}
}
void InitData()
{
string cs = "URI=file:test.db";
_dataTable = new DataTable();
// createData();
string stm = "SELECT * FROM Cars";
con = new SqliteConnection(cs);
con.Open();
ds = new DataSet();
SqliteCommandBuilder cmdBuilder;
da = new SqliteDataAdapter(stm, con);
da.Fill(ds, "Cars");
cmdBuilder = new SqliteCommandBuilder(da);
// SqliteCommand insCmd = cmdBuilder.GetInsertCommand();
// INSERT INTO [Cars] ([Name], [Note]) VALUES (@param1, @param2)
dgv.DataSource = ds.Tables["Cars"];
//BindingSource bs=ds.Tables["Cars"];
//dgv.DataSource=bs;
addRow("audi", "another note");
addRowSQL("audi sql", "another note");
da.Update(ds.Tables[0]);
}
public DataAdapter ToDataAdapter()
{
SqliteDataAdapter adapter = new SqliteDataAdapter();
SqliteCommandBuilder builder = new SqliteCommandBuilder(adapter);
adapter.SelectCommand = _context.CreateCommand(ToSelect());
return(adapter);
}
public override int Update(String table, ContentValues values, String whereClause, params String[] whereArgs)
{
Debug.Assert(!table.IsEmpty());
Debug.Assert(values != null);
var builder = new SqliteCommandBuilder();
builder.SetAllValues = false;
var command = GetUpdateCommand(table, values, whereClause, whereArgs);
var resultCount = -1;
try {
resultCount = (Int32)command.ExecuteNonQuery();
} catch (Exception ex) {
Log.E(Tag, "Error updating table " + table, ex);
}
return(resultCount);
}
[Category("NotWorking")] // Requires newer sqlite than is on wrench
public void XimarinBugzillaBug853Test()
{
const string connectionString = "URI = file:./SqliteTest.db; Version = 3"; //will be in System.Data directory
SqliteConnection dbConnection = new SqliteConnection(connectionString);
dbConnection.Open();
SqliteCommand ClearTableEntry = new SqliteCommand("DELETE FROM Primus;", dbConnection);
ClearTableEntry.ExecuteNonQuery();
SqliteDataAdapter sqliteDataAdapter = new SqliteDataAdapter("SELECT * FROM primus", dbConnection);
SqliteCommandBuilder builder = new SqliteCommandBuilder(sqliteDataAdapter);
sqliteDataAdapter.InsertCommand = builder.GetInsertCommand();
sqliteDataAdapter.DeleteCommand = builder.GetDeleteCommand();
DataSet dataSet = new DataSet();
sqliteDataAdapter.Fill(dataSet, "Primus"); //reset
DataRow rowToBeAdded = dataSet.Tables["Primus"].NewRow();
rowToBeAdded["id"] = 123;
rowToBeAdded["name"] = "Name"; //not null primary key
rowToBeAdded["value"] = 777;
dataSet.Tables["Primus"].Rows.Add(rowToBeAdded);
sqliteDataAdapter.Update(dataSet, "Primus");
//This would fail with NULL constraint violation in bug
//report. Because before the patch, it would create
//a new record with all fields being null-- if the
//exception rises, test fails
sqliteDataAdapter.Update(dataSet, "Primus");
dbConnection.Close();
dbConnection = null;
}
public override int Update (String table, ContentValues values, String whereClause, params String[] whereArgs)
{
Debug.Assert(!table.IsEmpty());
Debug.Assert(values != null);
var builder = new SqliteCommandBuilder();
builder.SetAllValues = false;
var command = GetUpdateCommand(table, values, whereClause, whereArgs);
var resultCount = -1;
try {
resultCount = (Int32)command.ExecuteNonQuery ();
} catch (Exception ex) {
Log.E(Tag, "Error updating table " + table, ex);
}
return resultCount;
}
public DataAdapter ToDataAdapter()
{
SqliteDataAdapter adapter = new SqliteDataAdapter();
SqliteCommandBuilder builder = new SqliteCommandBuilder( adapter );
adapter.SelectCommand = _context.CreateCommand( ToSelect() );
return adapter;
}
