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