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