/// <summary> /// Calls the respective insert, update or delete command /// for each object in the specified collection to save the entity to the database. /// </summary> /// <param name="transaction">The transaction to save within.</param> /// <param name="entities">The entities to save.</param> /// <param name="batchSize">A value that enables or disables batch processing support, and specifies the number of commands that can be executed in a batch. /// <para>When the value is 0, the default, the adapter will use the largest batch size the server can handle.</para> /// <para>When the value is 1 batching is disabled.</para> /// <para>A value > 1 will send changes to the database using the specified batch size.</para></param> public virtual void Save(Transaction transaction, ICollection <T> entities, int batchSize = 0) { _batchSize = batchSize; if (_batchSize != 1) { InitializeDataAdapter(transaction); DataTable changes = CreateDataTable(entities); _dataAdapter.Update(changes); if (BatchComplete != null) { BatchComplete(this, new BatchCompleteEventArgs <T>(entities, changes.Rows)); } } else { foreach (T entity in entities) { Save(transaction, entity); } } }
public static int UpdateDataTable(DbDataAdapter dbDataAdapter, DataTable dataTable) { if (dbConnection.State != ConnectionState.Open) dbConnection.Open(); int iUpdate = dbDataAdapter.Update(dataTable); dbConnection.Close(); return iUpdate; }
/// <summary> /// /// </summary> /// <param name="dt"></param> /// <param name="strCmdText"></param> /// <returns></returns> public bool Update(DataTable dt, string strCmdText) { bool bResult = false; try { Database db = GetDataBase(); _cmd = db.DbProviderFactory.CreateCommand(); _cmd.Connection = db.CreateConnection(); _cmd.CommandText = strCmdText; _cmd.CommandTimeout = TimeOut; _da = db.DbProviderFactory.CreateDataAdapter(); _da.SelectCommand = _cmd; _da.Update(dt); } catch (Exception ex) { throw ex; } finally { Dispose(); } return bResult; }
public int UpdateDataBase(DbDataAdapter da, DataTable dt) { int updateCount = 0; try { DataTable dtChanges = dt.GetChanges(); if (dtChanges != null && dtChanges.Rows.Count >= 0) { updateCount = da.Update(dtChanges); dt.AcceptChanges(); } } catch (Exception) { dt.RejectChanges(); throw; } return updateCount; }
//---------------------------------------------------------------------------------------------------- /// <summary> /// 테이블 갱신(INSERT,UPDATE,DELETE...) /// </summary> /// <param name="table">테이블 객체</param> /// <param name="adapter">어답터</param> /// <returns>성공유무</returns> //---------------------------------------------------------------------------------------------------- public bool UpdateTable( DataTable table, DbDataAdapter adapter ) { DataTable changed_table = table.GetChanges(); if( changed_table==null ) return true; // 업데이트 DbTransaction tran = (DbTransaction)m_connection.BeginTransaction(); try { //adapter.SelectCommand.Transaction = tran; adapter.InsertCommand.Transaction = tran; adapter.UpdateCommand.Transaction = tran; adapter.DeleteCommand.Transaction = tran; adapter.Update(changed_table); tran.Commit(); } catch( Exception ex ) { tran.Rollback(); Log( ex.ToString() ); m_error = ex.Message; return false; } table.AcceptChanges(); return true; }
//--пока этот метод вызывается из CreateTable, проверку на _conn.Open делать не надо, но как только будет отдельно, надо будет делать! protected virtual void InsertDataToDb(DataTable table, string parameters_prefix) { string insert_sql = string.Format("insert into {0} values(", table.TableName); Array insert_params = new DbParameter[table.Columns.Count]; for (int i = 0; i < table.Columns.Count; i++) { insert_sql = string.Format("{0}{2}{1}", insert_sql, table.Columns[i].ColumnName + (i + 1 != table.Columns.Count ? "," : ")"), parameters_prefix); DbParameter par = CreateParameter(string.Format("{1}{0}", table.Columns[i].ColumnName, parameters_prefix), Utilites.SystemTypeToDbTypeConverter.Convert(table.Columns[i].DataType), table.Columns[i].MaxLength); par.SourceColumn = table.Columns[i].ColumnName; insert_params.SetValue(par, i); } _DA = CreateDataAdapter(""); var ins_cmd = CreateCommand(insert_sql); ins_cmd.Parameters.AddRange(insert_params); _DA.InsertCommand = ins_cmd; _DA.Update(table); }
private void DoSaveDataRow(DbDataAdapter adap, DbCommandBuilder dbCmdBuilder, string tablename, DataRow row) { if (row == null || row.RowState == DataRowState.Unchanged || row.RowState == DataRowState.Detached) { return; } try { dbCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges; dbCmdBuilder.DataAdapter = adap; adap.SelectCommand.CommandText = string.Format("SELECT TOP 1 * FROM [{0}]", tablename); adap.Update(new DataRow[] { row }); } catch (Exception ex) { throw new DbAccessException(adap.SelectCommand.Connection, "Save DataRow error.", ex, adap.SelectCommand, adap.InsertCommand, adap.UpdateCommand, adap.DeleteCommand); } }
/// <summary> /// /// </summary> /// <param name="dataAdapter"></param> /// <param name="dataTable"></param> /// <returns></returns> public Int32 Save(DbDataAdapter dataAdapter, DataTable dataTable) { var previousState = ConnectionState; int affectedRecordCount = -1; DateTimeOffset? startTime = null; DateTimeOffset? endTime = null; try { var e = Database.OnCommandExecuting(new CommandExecutingEventArgs(MethodName.Save, ConnectionString, dataAdapter)); if (e != null && e.Cancel == true) { return -1; } Open(); if (dataAdapter.InsertCommand != null) { dataAdapter.InsertCommand.Connection = this.Connection; dataAdapter.InsertCommand.Transaction = this.Transaction; } if (dataAdapter.UpdateCommand != null) { dataAdapter.UpdateCommand.Connection = this.Connection; dataAdapter.UpdateCommand.Transaction = this.Transaction; } if (dataAdapter.DeleteCommand != null) { dataAdapter.DeleteCommand.Connection = this.Connection; dataAdapter.DeleteCommand.Transaction = this.Transaction; } startTime = DateTimeOffset.Now; affectedRecordCount = dataAdapter.Update(dataTable); endTime = DateTimeOffset.Now; } catch (Exception ex) { this.CatchException(new CommandErrorEventArgs(MethodName.Save, ConnectionString, ex, dataAdapter)); } finally { if (previousState == ConnectionState.Closed && ConnectionState == ConnectionState.Open) { Close(); } } if (startTime.HasValue == true && endTime.HasValue == true) { Database.OnCommandExecuted(new CommandExecutedEventArgs(MethodName.Save, ConnectionString, startTime.Value, endTime.Value, dataAdapter)); } return affectedRecordCount; }
public void Update_DeleteRow() { m_cmd = m_conn.CreateCommand(); DataTable dt = new DataTable(); m_adapter = GetDataAdapter(); m_cmd.CommandText = "SELECT * FROM employee"; m_adapter.SelectCommand = m_cmd; DbCommandBuilder builder = GetCommandBuilder(m_adapter); m_adapter.Fill(dt); DateTime now = DateTime.Now; DateTime doj = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); DateTime dob = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0)); try { DataRow newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); // check row inserted DbCommand cmd = CheckNewEmployeeRow(6002, dob, doj); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row.Delete(); } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsFalse(m_reader.Read()); m_reader.Close(); // also try explicitly setting the DeleteCommand //m_adapter.DeleteCommand = builder.GetDeleteCommand(); newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); // check row inserted cmd = CheckNewEmployeeRow(6002, dob, doj); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row.Delete(); } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsFalse(m_reader.Read()); m_reader.Close(); // now with useColumnsForParameterNames as true //m_adapter.DeleteCommand = builder.GetDeleteCommand(true); newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); // check row inserted cmd = CheckNewEmployeeRow(6002, dob, doj); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row.Delete(); } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsFalse(m_reader.Read()); m_reader.Close(); // now explicitly set the DeleteCommand using GFXDCommandBuilder if (builder is GFXDCommandBuilder) { m_adapter.DeleteCommand = ((GFXDCommandBuilder)builder) .GetDeleteCommand(); newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); // check row inserted cmd = CheckNewEmployeeRow(6002, dob, doj); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row.Delete(); } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsFalse(m_reader.Read()); m_reader.Close(); // now with useColumnsForParameterNames as true m_adapter.DeleteCommand = ((GFXDCommandBuilder)builder) .GetDeleteCommand(true); newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); // check row inserted cmd = CheckNewEmployeeRow(6002, dob, doj); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row.Delete(); } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsFalse(m_reader.Read()); m_reader.Close(); } } finally { CleanEmployeeTable(); } }
public void Update_UpdateRow() { m_cmd = m_conn.CreateCommand(); DataTable dt = new DataTable(); m_adapter = GetDataAdapter(); m_cmd.CommandText = "SELECT * FROM employee"; m_adapter.SelectCommand = m_cmd; DbCommandBuilder builder = GetCommandBuilder(m_adapter); m_adapter.Fill(dt); DateTime now = DateTime.Now; DateTime doj = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); DateTime dob = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0)); try { DataRow newRow = dt.NewRow(); newRow["id"] = 6002; newRow["fname"] = "boston"; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "*****@*****.**"; dt.Rows.Add(newRow); m_adapter.Update(dt); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row["lname"] = "de Icaza"; } } m_adapter.Update(dt); DbCommand cmd = m_conn.CreateCommand(); cmd.CommandText = "SELECT id, fname, lname, dob, doj, email" + " FROM employee WHERE id = 6002"; m_reader = cmd.ExecuteReader(); Assert.IsTrue(m_reader.Read(), "#A1"); Assert.AreEqual(6002, m_reader.GetValue(0), "#A2"); Assert.AreEqual("boston", m_reader.GetValue(1), "#A3"); Assert.AreEqual("de Icaza", m_reader.GetValue(2), "#A4"); Assert.AreEqual(dob, m_reader.GetValue(3), "#A5"); Assert.AreEqual(doj, m_reader.GetValue(4), "#A6"); Assert.AreEqual("*****@*****.**", m_reader.GetValue(5), "#A7"); Assert.IsFalse(m_reader.Read(), "#A8"); m_reader.Close(); // also try explicitly setting the UpdateCommand //m_adapter.UpdateCommand = builder.GetUpdateCommand(); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row["lname"] = "none"; } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsTrue(m_reader.Read(), "#B1"); Assert.AreEqual(6002, m_reader.GetValue(0), "#B2"); Assert.AreEqual("boston", m_reader.GetValue(1), "#B3"); Assert.AreEqual("none", m_reader.GetValue(2), "#B4"); Assert.AreEqual(dob, m_reader.GetValue(3), "#B5"); Assert.AreEqual(doj, m_reader.GetValue(4), "#B6"); Assert.AreEqual("*****@*****.**", m_reader.GetValue(5), "#B7"); Assert.IsFalse(m_reader.Read(), "#B8"); m_reader.Close(); // now with useColumnsForParameterNames as true //m_adapter.UpdateCommand = builder.GetUpdateCommand(true); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row["lname"] = "de Icaza"; } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsTrue(m_reader.Read(), "#C1"); Assert.AreEqual(6002, m_reader.GetValue(0), "#C2"); Assert.AreEqual("boston", m_reader.GetValue(1), "#C3"); Assert.AreEqual("de Icaza", m_reader.GetValue(2), "#C4"); Assert.AreEqual(dob, m_reader.GetValue(3), "#C5"); Assert.AreEqual(doj, m_reader.GetValue(4), "#C6"); Assert.AreEqual("*****@*****.**", m_reader.GetValue(5), "#C7"); Assert.IsFalse(m_reader.Read(), "#C8"); m_reader.Close(); // now explicitly set the UpdateCommand using GFXDCommandBuilder if (builder is GFXDCommandBuilder) { m_adapter.UpdateCommand = ((GFXDCommandBuilder)builder) .GetUpdateCommand(); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row["lname"] = "none"; } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsTrue(m_reader.Read(), "#D1"); Assert.AreEqual(6002, m_reader.GetValue(0), "#D2"); Assert.AreEqual("boston", m_reader.GetValue(1), "#D3"); Assert.AreEqual("none", m_reader.GetValue(2), "#D4"); Assert.AreEqual(dob, m_reader.GetValue(3), "#D5"); Assert.AreEqual(doj, m_reader.GetValue(4), "#D6"); Assert.AreEqual("*****@*****.**", m_reader.GetValue(5), "#D7"); Assert.IsFalse(m_reader.Read(), "#D8"); m_reader.Close(); // now with useColumnsForParameterNames as true m_adapter.UpdateCommand = ((GFXDCommandBuilder)builder) .GetUpdateCommand(true); foreach (DataRow row in dt.Rows) { if (((int)row["id"]) == 6002) { row["lname"] = "de Icaza"; } } m_adapter.Update(dt); m_reader = cmd.ExecuteReader(); Assert.IsTrue(m_reader.Read(), "#E1"); Assert.AreEqual(6002, m_reader.GetValue(0), "#E2"); Assert.AreEqual("boston", m_reader.GetValue(1), "#E3"); Assert.AreEqual("de Icaza", m_reader.GetValue(2), "#E4"); Assert.AreEqual(dob, m_reader.GetValue(3), "#E5"); Assert.AreEqual(doj, m_reader.GetValue(4), "#E6"); Assert.AreEqual("*****@*****.**", m_reader.GetValue(5), "#E7"); Assert.IsFalse(m_reader.Read(), "#E8"); m_reader.Close(); } } finally { CleanEmployeeTable(); } }
public void BatchSizeMixedChangesTest() { m_adapter = GetDataAdapter("select * from employee"); GetCommandBuilder(m_adapter); Assert.AreEqual(1, m_adapter.UpdateBatchSize, "#A1 The default value should be 1"); m_adapter.UpdateBatchSize = 7; Assert.AreEqual(7, m_adapter.UpdateBatchSize, "#A2 The value should be 7 after setting the property" + " UpdateBatchSize to 7"); for (int times = 1; times <= 100; ++times) { DbCommand cmd = m_conn.CreateCommand(); DataTable dt = new DataTable(); m_adapter.Fill(dt); DateTime now = DateTime.Now; DateTime doj = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); DateTime dob = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0)); TrackRandom rnd = new TrackRandom(); int startInsertId = 1000, endInsertId = 1500; int startUpdateId = 1133, endUpdateId = 1437; int startDeleteId = 1251, endDeleteId = 1355; //int startInsertId = 100, endInsertId = 130; //int startUpdateId = 109, endUpdateId = 125; //int startDeleteId = 113, endDeleteId = 123; int rowId, endRowId; int insertRowId = startInsertId; int updateRowId = startUpdateId; int deleteRowId = startDeleteId; int sumRowIds; int currentBatch; bool changed; try { // the inserts/updates/deletes are performed in random batches of // sizes ranging from 3-15 while (insertRowId <= endInsertId || updateRowId <= endUpdateId || deleteRowId <= endDeleteId) { currentBatch = rnd.Next(3, 15); changed = false; endRowId = insertRowId + currentBatch; while (insertRowId <= endRowId && insertRowId <= endInsertId) { DataRow newRow = dt.NewRow(); newRow["id"] = insertRowId; newRow["fname"] = "gfxd" + insertRowId; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "test" + insertRowId + "@vmware.com"; dt.Rows.Add(newRow); ++insertRowId; changed = true; } if (changed && rnd.Next(5) == 1) { m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.AreEqual(0, m_adapter.InsertCommand.Parameters.Count, "#B1 Should have no parameters after batching"); // a rough check for successful inserts sumRowIds = 0; for (rowId = startInsertId; rowId < insertRowId; ++rowId) { if (rowId < startDeleteId || rowId >= deleteRowId) { sumRowIds += rowId; } } cmd.CommandText = "select sum(id) from employee where id >= " + startInsertId; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#B2 All inserts not done?"); } // now check for a set of updates changed = false; endRowId = updateRowId + rnd.Next(0, currentBatch); while (updateRowId <= endRowId && updateRowId < insertRowId && updateRowId <= endUpdateId) { foreach (DataRow row in dt.Rows) { if (row.RowState != DataRowState.Deleted) { rowId = (int)row["id"]; if (rowId == updateRowId) { row["lname"] = "gem" + rowId; changed = true; break; } } } ++updateRowId; } if (changed && rnd.Next(5) == 2) { m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.IsTrue(m_adapter.UpdateCommand == null || m_adapter.UpdateCommand.Parameters.Count == 0, "#C1 Should have no parameters after batching"); // a rough check for successful updates sumRowIds = 0; for (rowId = startUpdateId; rowId < updateRowId; ++rowId) { if (rowId < startDeleteId || rowId >= deleteRowId) { sumRowIds += rowId; } } cmd.CommandText = "select sum(id) from employee" + " where lname like 'gem%'"; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#C2 Updates not successful?"); } // lastly check for a set of deletes changed = false; endRowId = deleteRowId + rnd.Next(0, currentBatch); DataRow[] rowArray = new DataRow[dt.Rows.Count]; dt.Rows.CopyTo(rowArray, 0); foreach (DataRow row in rowArray) { if (row.RowState != DataRowState.Deleted) { rowId = (int)row["id"]; if (rowId >= deleteRowId && rowId < insertRowId && rowId <= endRowId && rowId <= endDeleteId) { row.Delete(); changed = true; } } } if (changed) { if (endRowId >= insertRowId) { endRowId = insertRowId - 1; } if (endRowId > endDeleteId) { endRowId = endDeleteId; } deleteRowId = endRowId + 1; } if (changed && rnd.Next(5) == 0) { m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.IsTrue(m_adapter.DeleteCommand == null || m_adapter.DeleteCommand.Parameters.Count == 0, "#D1 Should have no parameters after batching"); // a rough check for successful deletes sumRowIds = 0; for (rowId = startInsertId; rowId < insertRowId; ++rowId) { if (rowId < startDeleteId || rowId >= deleteRowId) { sumRowIds += rowId; } } cmd.CommandText = "select sum(id) from employee where id > 4"; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#D2 Some deletes not successful?"); } } // check that everything is finished properly at the end m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.AreEqual(0, m_adapter.InsertCommand.Parameters.Count, "#E1 Should have no parameters after batching"); Assert.IsTrue(m_adapter.UpdateCommand == null || m_adapter.UpdateCommand.Parameters.Count == 0, "#E2 Should have no parameters after batching"); Assert.IsTrue(m_adapter.DeleteCommand == null || m_adapter.DeleteCommand.Parameters.Count == 0, "#E3 Should have no parameters after batching"); // a rough check for successful updates sumRowIds = 0; for (rowId = startUpdateId; rowId <= endUpdateId; ++rowId) { if (rowId < startDeleteId || rowId > endDeleteId) { sumRowIds += rowId; } } cmd.CommandText = "select sum(id) from employee" + " where lname like 'gem%'"; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#E4 Updates not successful?"); // a rough check for successful deletes sumRowIds = 0; for (rowId = startInsertId; rowId <= endInsertId; ++rowId) { if (rowId < startDeleteId || rowId > endDeleteId) { sumRowIds += rowId; } } cmd.CommandText = "select sum(id) from employee where id > 4"; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#E5 Some deletes not successful?"); } catch (Exception ex) { Console.WriteLine("Failed with exception: " + ex.ToString()); Console.WriteLine("Failed for seed: " + rnd.Seed); throw; } finally { CleanEmployeeTable(); } } }
public void BatchSizeInsertUpdateDeleteTest() { m_adapter = GetDataAdapter("select * from employee"); GetCommandBuilder(m_adapter); Assert.AreEqual(1, m_adapter.UpdateBatchSize, "#A1 The default value should be 1"); m_adapter.UpdateBatchSize = 3; Assert.AreEqual(3, m_adapter.UpdateBatchSize, "#A2 The value should be 3 after setting the property" + " UpdateBatchSize to 3"); // some inserts that should be sent in batches DataTable dt = new DataTable(); m_adapter.Fill(dt); DateTime now = DateTime.Now; DateTime doj = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); DateTime dob = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0)); int startInsertId = 1000, endInsertId = 1500; int startUpdateId = 1273, endUpdateId = 1355; int startDeleteId = 1133, endDeleteId = 1437; int rowId; int sumRowIds = 0; try { for (rowId = startInsertId; rowId <= endInsertId; ++rowId) { DataRow newRow = dt.NewRow(); sumRowIds += rowId; newRow["id"] = rowId; newRow["fname"] = "gfxd" + rowId; newRow["dob"] = dob; newRow["doj"] = doj; newRow["email"] = "test" + rowId + "@vmware.com"; dt.Rows.Add(newRow); } m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.AreEqual(0, m_adapter.InsertCommand.Parameters.Count, "#B1 Should have no parameters after batching"); // a rough check for successful inserts DbCommand cmd = m_conn.CreateCommand(); cmd.CommandText = "select sum(id) from employee where id >= " + startInsertId; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#B2 All inserts not done?"); // now check for a set of updates int sumUpdatedRowIds = 0; for (int expectedId = startUpdateId; expectedId <= endUpdateId; ++expectedId) { sumUpdatedRowIds += expectedId; foreach (DataRow row in dt.Rows) { rowId = (int)row["id"]; if (rowId == expectedId) { row["lname"] = "gem" + rowId; break; } } } m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.AreEqual(0, m_adapter.UpdateCommand.Parameters.Count, "#C1 Should have no parameters after batching"); // a rough check for successful updates cmd.CommandText = "select sum(id) from employee" + " where lname like 'gem%'"; Assert.AreEqual(sumUpdatedRowIds, cmd.ExecuteScalar(), "#C2 Updates not successful?"); // lastly check for a set of deletes foreach (DataRow row in dt.Rows) { rowId = (int)row["id"]; if (rowId >= startDeleteId && rowId <= endDeleteId) { row.Delete(); sumRowIds -= rowId; } } m_adapter.Update(dt); // check that we have no parameters left after batch execution Assert.AreEqual(0, m_adapter.DeleteCommand.Parameters.Count, "#D1 Should have no parameters after batching"); // a rough check for successful deletes cmd.CommandText = "select sum(id) from employee where id > 4"; Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(), "#D2 Some deletes not successful?"); } finally { CleanEmployeeTable(); } }
public bool delete(DataRow dr, DataTable dt, DbDataAdapter da) { try { dt.Rows.Remove(dr); da.Update(dt); return true; } catch (Exception ex) { cError.mngError(ex, "delete", c_module, ""); return false; } }