[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); } }
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; }
private static DataTable SaveDynamicEntity(tgDataRequest request) { bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted; DataTable dataTable = CreateDataTable(request); using (SqliteDataAdapter da = new SqliteDataAdapter()) { da.AcceptChangesDuringUpdate = false; DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SqliteCommand cmd = null; switch (request.EntitySavePacket.RowState) { case tgDataRowState.Added: cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket); SetModifiedValues(request, request.EntitySavePacket, row); break; case tgDataRowState.Modified: cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket); SetOriginalValues(request, request.EntitySavePacket, row, false); SetModifiedValues(request, request.EntitySavePacket, row); row.AcceptChanges(); row.SetModified(); break; case tgDataRowState.Deleted: cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); SetOriginalValues(request, request.EntitySavePacket, row, true); row.AcceptChanges(); row.Delete(); break; } if (!needToDelete && request.Properties != null) { request.Properties["tgDataRequest"] = request; request.Properties["esEntityData"] = request.EntitySavePacket; dataTable.ExtendedProperties["props"] = request.Properties; } DataRow[] singleRow = new DataRow[1]; singleRow[0] = row; try { if (!request.IgnoreComputedColumns) { da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated); } tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Update(singleRow); } } finally { tgTransactionScope.DeEnlist(cmd); } if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null) { foreach (SqliteParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value; break; } } } cmd.Dispose(); } return dataTable; }
private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request) { SqliteCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (tgTransactionScope scope = new tgTransactionScope()) { using (SqliteDataAdapter da = new SqliteDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (tgEntitySavePacket packet in request.CollectionSavePacket) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SetOriginalValues(request, packet, row, true); row.AcceptChanges(); row.Delete(); singleRow[0] = row; #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace)) { try { da.Update(singleRow); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { da.Update(singleRow); } if (row.HasErrors) { request.FireOnError(packet, row.RowError); } dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow } } finally { tgTransactionScope.DeEnlist(cmd); cmd.Dispose(); } } scope.Complete(); } return request.Table; }
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]); }
private void UpdateDatabase() { try { if (File.Exists("data/db/players.db")) { File.Move("data/db/players.db", "data/db/players_old.db"); using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\players_old.db")) { old.Open(); DataTable table = new DataTable("players"); using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", old); adapter.Fill(table); } foreach (DataRow row in table.Rows) { row.SetAdded(); } using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", connection); adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true); adapter.Update(table); } } } if (File.Exists("data/db/hosts.db")) { File.Move("data/db/hosts.db", "data/db/hosts_old.db"); using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\hosts_old.db")) { old.Open(); DataTable table = new DataTable("hosts"); using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", old); adapter.Fill(table); } foreach (DataRow row in table.Rows) { row.SetAdded(); } using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", connection); adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true); adapter.Update(table); } } } if (File.Exists("data/db/comments.db")) { File.Move("data/db/comments.db", "data/db/comments_old.db"); using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\comments_old.db")) { old.Open(); DataTable table = new DataTable("comments"); using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", old); adapter.Fill(table); } foreach (DataRow row in table.Rows) { row.SetAdded(); } using (SqliteDataAdapter adapter = new SqliteDataAdapter()) { adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", connection); adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true); adapter.Update(table); } } } } catch(Exception e) { using (StreamWriter writer = new StreamWriter("migrate_error.txt")) { writer.WriteLine(e.Message); writer.WriteLine(e.StackTrace); } } }
/// <summary> /// 更新数据集中数据到数据库 /// </summary> /// <param name="insertCommand">insert语句</param> /// <param name="deleteCommand">delete语句</param> /// <param name="updateCommand">update语句</param> /// <param name="dataSet">要更新的DataSet</param> /// <param name="tableName">数据集中要更新的table名</param> public static void UpdateDataset (SqliteCommand insertCommand, SqliteCommand deleteCommand, SqliteCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) throw new ArgumentNullException ("insertCommand"); if (deleteCommand == null) throw new ArgumentNullException ("deleteCommand"); if (updateCommand == null) throw new ArgumentNullException ("updateCommand"); if (tableName == null || tableName.Length == 0) throw new ArgumentNullException ("tableName"); // Create a SQLiteDataAdapter, and dispose of it after we are done using (SqliteDataAdapter dataAdapter = new SqliteDataAdapter ()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update (dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges (); } }