protected virtual void OnUpdate(DataSet ds, IDbCommandBuilder dbCommandBuilder, DbTransaction dbTransaction, string tableName)
{
DbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(ds.Tables[tableName], dbTransaction);
DbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
try
{
using (var updateCommand = dbCommandBuilder.GetUpdateCommand(dbTransaction, tableName))
{
updateCommand.Connection = sqlTransaction.Connection;
updateCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = updateCommand;
((DbDataAdapter)sqlDataAdapter).Update(ds, tableName);
}
}
finally
{
var disposable = sqlDataAdapter as IDisposable;
if (disposable != null)
{
disposable.Dispose();
}
}
//EnableTableConstraints(ds.Tables[tableName], dbTransaction);
}
protected virtual void OnUpdate(DataSet ds, IDbCommandBuilder dbCommandBuilder, IDbTransaction dbTransaction, string tableName)
{
IDbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(ds.Tables[tableName], dbTransaction);
IDbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
sqlDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(tableName);
sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
((DbDataAdapter)sqlDataAdapter).Update(ds, tableName);
//EnableTableConstraints(ds.Tables[tableName], dbTransaction);
}
public void GetUpdateCommand_Creates_Correct_SQL_Commands()
{
IList <string> commandList = new List <string>();
DataSet ds = _commandBuilder.GetSchema();
foreach (DataTable dataTable in ds.Tables)
{
IDbCommand dbCommand = _commandBuilder.GetUpdateCommand(dataTable.TableName);
commandList.Add(dbCommand.CommandText);
Console.WriteLine("Table '" + dataTable.TableName + "' update command");
Console.WriteLine("\t" + dbCommand.CommandText);
}
Assert.AreEqual(EXPECTED_COUNT_OF_COMMANDS, commandList.Count, string.Format("Should be {0} commands", EXPECTED_COUNT_OF_COMMANDS));
Assert.That(ExpectedUpdateCommands, Is.EquivalentTo(commandList));
}
protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)
{
// generate commands by first row table schema
if (dataRows.Length > 0)
{
var tbl = dataRows[0].Table;
var changeset = GetChangeset(tbl);
InsertCommand = (DbCommand)CmdBuilder.GetInsertCommand(tbl.TableName, changeset);
InitDbCmd(InsertCommand, tbl);
if (tbl.PrimaryKey != null && tbl.PrimaryKey.Length > 0)
{
var pkQuery = new Query(tbl.TableName, ComposePkCondition(tbl));
UpdateCommand = (DbCommand)CmdBuilder.GetUpdateCommand(pkQuery, changeset);
InitDbCmd(UpdateCommand, tbl);
DeleteCommand = (DbCommand)CmdBuilder.GetDeleteCommand(pkQuery);
InitDbCmd(DeleteCommand, tbl);
}
}
return(base.Update(dataRows, tableMapping));
}
public static IDbCommand GetUpdateCommand(this IDbCommandBuilder cmdBuilder, Query q, object poco)
{
return(cmdBuilder.GetUpdateCommand(q, DataHelper.GetChangeset(poco, null)));
}
public static IDbCommand GetUpdateCommand(this IDbCommandBuilder cmdBuilder, Query q, IDictionary <string, object> data)
{
return(cmdBuilder.GetUpdateCommand(q, DataHelper.GetChangeset(data)));
}
protected virtual void OnRefresh(DataSet ds, IDbCommandBuilder dbCommandBuilder, IDbTransaction dbTransaction, string tableName)
{
IDbTransaction sqlTransaction = dbTransaction;
IDbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
sqlDataAdapter.SelectCommand = dbCommandBuilder.GetSelectCommand(tableName);
sqlDataAdapter.SelectCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.SelectCommand.Transaction = sqlTransaction;
DataSet dsDb = new DataSet();
// Query all records in the database table.
((DbDataAdapter)sqlDataAdapter).Fill(dsDb, tableName);
DataSet dsUpdate = dbCommandBuilder.GetSchema().Clone();
dsUpdate.EnforceConstraints = false;
DataTable dataTable = ds.Tables[tableName];
DataTable dataTableDb = dsDb.Tables[tableName];
// Iterate all rows in the table.
foreach (DataRow dataRow in dataTable.Rows)
{
bool rowDoesNotExist = true;
// Iterate all rows in the database table.
foreach (DataRow dataRowDb in dataTableDb.Rows)
{
// The row exists in the database.
if (IsPrimaryKeyValueEqual(dataRow, dataRowDb, dsUpdate.Tables[tableName].PrimaryKey))
{
rowDoesNotExist = false;
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
dataRowNew.AcceptChanges();
MarkRowAsModified(dataRowNew);
break;
}
}
// The row does not exist in the database.
if (rowDoesNotExist)
{
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
dataRowNew.AcceptChanges();
}
}
// Does not insert identity.
sqlDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand(tableName);
sqlDataAdapter.InsertCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(tableName);
sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
//DisableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
((DbDataAdapter)sqlDataAdapter).Update(dsUpdate, tableName);
//EnableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
}
protected virtual void OnUpdate(DataSet ds, IDbCommandBuilder dbCommandBuilder, DbTransaction dbTransaction, string tableName)
{
DbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(ds.Tables[tableName], dbTransaction);
DbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
try
{
using (var updateCommand = dbCommandBuilder.GetUpdateCommand(dbTransaction, tableName))
{
updateCommand.Connection = sqlTransaction.Connection;
updateCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = updateCommand;
((DbDataAdapter)sqlDataAdapter).Update(ds, tableName);
}
}
finally
{
var disposable = sqlDataAdapter as IDisposable;
if (disposable != null)
disposable.Dispose();
}
//EnableTableConstraints(ds.Tables[tableName], dbTransaction);
}
protected virtual void OnRefresh(DataSet ds, IDbCommandBuilder dbCommandBuilder, DbTransaction dbTransaction, string tableName, bool insertIdentity)
{
DbTransaction sqlTransaction = dbTransaction;
using (DbDataAdapter sqlDataAdapter = CreateDbDataAdapter())
{
using (var selectCommand = dbCommandBuilder.GetSelectCommand(dbTransaction, tableName))
{
selectCommand.Connection = sqlTransaction.Connection;
selectCommand.Transaction = sqlTransaction;
sqlDataAdapter.SelectCommand = selectCommand;
DataSet dsDb = new DataSet();
// Query all records in the database table.
((DbDataAdapter)sqlDataAdapter).Fill(dsDb, tableName);
DataSet dsUpdate = dbCommandBuilder.GetSchema().Clone();
dsUpdate.EnforceConstraints = false;
DataTable dataTable = ds.Tables[tableName];
DataTable dataTableDb = dsDb.Tables[tableName];
var schemaTable = dsUpdate.Tables[tableName];
if (dataTableDb.PrimaryKey.Length == 0)
dataTableDb.PrimaryKey = schemaTable
.PrimaryKey
.Select(c => dataTableDb.Columns[c.ColumnName])
.OrderBy(c => c.Ordinal)
.ToArray();
// Iterate all rows in the table.
foreach (DataRow dataRow in dataTable.Rows)
{
var pkValues = dataTableDb.PrimaryKey
.Select(c => dataRow[c.ColumnName])
.ToArray();
var dataRowDb = dataTableDb.Rows.Find(pkValues);
bool rowExists = dataRowDb != null;
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
// The row does not exist in the database.
if (rowExists)
{
dataRowNew.AcceptChanges();
if (dataTableDb.PrimaryKey.Length != dataTableDb.Columns.Count)
MarkRowAsModified(dataRowNew);
}
}
var hasAutoIncColumn = dataTable.Columns.Cast<DataColumn>().Any(x => x.AutoIncrement);
var identityInsertGuard = insertIdentity && hasAutoIncColumn ? ActivateInsertIdentity(dataTable.TableName, dbTransaction) : null;
using (identityInsertGuard)
{
DbCommand insertCommand =
insertIdentity && hasAutoIncColumn
? dbCommandBuilder.GetInsertIdentityCommand(dbTransaction, tableName)
: dbCommandBuilder.GetInsertCommand(dbTransaction, tableName);
using (insertCommand)
{
insertCommand.Connection = sqlTransaction.Connection;
insertCommand.Transaction = sqlTransaction;
sqlDataAdapter.InsertCommand = insertCommand;
using (var updateCommand = dbCommandBuilder.GetUpdateCommand(dbTransaction, tableName))
{
updateCommand.Connection = sqlTransaction.Connection;
updateCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = updateCommand;
//DisableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
sqlDataAdapter.Update(dsUpdate, tableName);
//EnableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
}
}
}
}
}
}
protected virtual void OnRefresh(DataSet ds, IDbCommandBuilder dbCommandBuilder, DbTransaction dbTransaction, string tableName, bool insertIdentity)
{
DbTransaction sqlTransaction = dbTransaction;
using (DbDataAdapter sqlDataAdapter = CreateDbDataAdapter())
{
using (var selectCommand = dbCommandBuilder.GetSelectCommand(dbTransaction, tableName))
{
selectCommand.Connection = sqlTransaction.Connection;
selectCommand.Transaction = sqlTransaction;
sqlDataAdapter.SelectCommand = selectCommand;
DataSet dsDb = new DataSet();
// Query all records in the database table.
((DbDataAdapter)sqlDataAdapter).Fill(dsDb, tableName);
DataSet dsUpdate = dbCommandBuilder.GetSchema().Clone();
dsUpdate.EnforceConstraints = false;
DataTable dataTable = ds.Tables[tableName];
DataTable dataTableDb = dsDb.Tables[tableName];
var schemaTable = dsUpdate.Tables[tableName];
if (dataTableDb.PrimaryKey.Length == 0)
{
dataTableDb.PrimaryKey = schemaTable
.PrimaryKey
.Select(c => dataTableDb.Columns[c.ColumnName])
.OrderBy(c => c.Ordinal)
.ToArray();
}
// Iterate all rows in the table.
foreach (DataRow dataRow in dataTable.Rows)
{
var pkValues = dataTableDb.PrimaryKey
.Select(c => dataRow[c.ColumnName])
.ToArray();
var dataRowDb = dataTableDb.Rows.Find(pkValues);
bool rowExists = dataRowDb != null;
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
// The row does not exist in the database.
if (rowExists)
{
dataRowNew.AcceptChanges();
if (dataTableDb.PrimaryKey.Length != dataTableDb.Columns.Count)
{
MarkRowAsModified(dataRowNew);
}
}
}
var hasAutoIncColumn = dataTable.Columns.Cast <DataColumn>().Any(x => x.AutoIncrement);
var identityInsertGuard = insertIdentity && hasAutoIncColumn?ActivateInsertIdentity(dataTable.TableName, dbTransaction) : null;
using (identityInsertGuard)
{
DbCommand insertCommand =
insertIdentity && hasAutoIncColumn
? dbCommandBuilder.GetInsertIdentityCommand(dbTransaction, tableName)
: dbCommandBuilder.GetInsertCommand(dbTransaction, tableName);
using (insertCommand)
{
insertCommand.Connection = sqlTransaction.Connection;
insertCommand.Transaction = sqlTransaction;
sqlDataAdapter.InsertCommand = insertCommand;
using (var updateCommand = dbCommandBuilder.GetUpdateCommand(dbTransaction, tableName))
{
updateCommand.Connection = sqlTransaction.Connection;
updateCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = updateCommand;
//DisableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
sqlDataAdapter.Update(dsUpdate, tableName);
//EnableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
}
}
}
}
}
}
protected virtual void OnUpdate(DataSet ds, IDbCommandBuilder dbCommandBuilder, IDbTransaction dbTransaction, string tableName)
{
IDbTransaction sqlTransaction = dbTransaction;
//DisableTableConstraints(ds.Tables[tableName], dbTransaction);
IDbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
sqlDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(tableName);
sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
((DbDataAdapter)sqlDataAdapter).Update(ds, tableName);
//EnableTableConstraints(ds.Tables[tableName], dbTransaction);
}
protected virtual void OnRefresh(DataSet ds, IDbCommandBuilder dbCommandBuilder, IDbTransaction dbTransaction, string tableName)
{
IDbTransaction sqlTransaction = dbTransaction;
IDbDataAdapter sqlDataAdapter = CreateDbDataAdapter();
sqlDataAdapter.SelectCommand = dbCommandBuilder.GetSelectCommand(tableName);
sqlDataAdapter.SelectCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.SelectCommand.Transaction = sqlTransaction;
DataSet dsDb = new DataSet();
// Query all records in the database table.
((DbDataAdapter)sqlDataAdapter).Fill(dsDb, tableName);
DataSet dsUpdate = dbCommandBuilder.GetSchema().Clone();
dsUpdate.EnforceConstraints = false;
DataTable dataTable = ds.Tables[tableName];
DataTable dataTableDb = dsDb.Tables[tableName];
// Iterate all rows in the table.
foreach (DataRow dataRow in dataTable.Rows)
{
bool rowDoesNotExist = true;
// Iterate all rows in the database table.
foreach (DataRow dataRowDb in dataTableDb.Rows)
{
// The row exists in the database.
if (IsPrimaryKeyValueEqual(dataRow, dataRowDb, dsUpdate.Tables[tableName].PrimaryKey))
{
rowDoesNotExist = false;
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
dataRowNew.AcceptChanges();
MarkRowAsModified(dataRowNew);
break;
}
}
// The row does not exist in the database.
if (rowDoesNotExist)
{
DataRow dataRowNew = CloneDataRow(dsUpdate.Tables[tableName], dataRow);
dsUpdate.Tables[tableName].Rows.Add(dataRowNew);
dataRowNew.AcceptChanges();
}
}
// Does not insert identity.
sqlDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand(tableName);
sqlDataAdapter.InsertCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
sqlDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(tableName);
sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
//DisableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
((DbDataAdapter)sqlDataAdapter).Update(dsUpdate, tableName);
//EnableTableConstraints(dsUpdate.Tables[tableName], dbTransaction);
}
