|
public Update ( |
||
| dataRows | ||
| return | int | |
/// <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;
}
}
