/// <summary>
/// Determines the result of the attempt to update the source database with changes to the
/// data table.
/// </summary>
/// <param name="changedRows">The rows from the data table that were changed.</param>
/// <param name="numberRowsUpdatedInSource">The number of rows that were updated in the
/// source database.</param>
/// <param name="errorMessage">Output parameter: Any error message as a result of
/// attempting to update the source database.</param>
/// <param name="result">DatabaseUpdateResult indicating whether the update attempt on the
/// source database was a success, a failure, or a partial success (some rows were
/// successfully updated but others were not).</param>
private DatabaseUpdateResult GetUpdateResult(DataTable changedRows,
int numberRowsUpdatedInSource, out string errorMessage)
{
errorMessage = string.Empty;
if (!changedRows.HasErrors)
{
return(DatabaseUpdateResult.Success);
}
DatabaseUpdateResult result = DatabaseUpdateResult.Failed;
DataRow[] errorRows = changedRows.GetErrors();
StringBuilder sb = new StringBuilder();
sb.AppendLine("The following errors were encountered while attempting "
+ "to update the data source:");
foreach (DataRow row in errorRows)
{
sb.AppendLine(row.RowError);
}
errorMessage = sb.ToString();
// Partial Success: Some of the row updates have succeeded, some have failed.
if (numberRowsUpdatedInSource > 0 &&
numberRowsUpdatedInSource < changedRows.Rows.Count)
{
result = DatabaseUpdateResult.PartialSuccess;
}
return(result);
}
/// <summary>
/// Accepts the changes made to the data table after the source database has been updated.
/// </summary>
/// <param name="dataTable">Data table that was the source of the changes made to the
/// database.</param>
/// <param name="changedRows">The rows from the data table that were changed.</param>
/// <param name="databaseUpdateResult">The result of attempting to update the database
/// with the changes from the data table.</param>
/// <remarks>This method will only be called if the update was successful or if the
/// rollbackAllOnError parameter of the UpdateSource method is set to false. If there
/// is an error and rollbackAllOnError is set to true, all the changes would have been
/// rolled back and this method would never be called.
///
/// Changes will be accepted only for those rows in the data table where the update of
/// the source database succeeded. Rows where the database update failed will NOT have
/// their changes either accepted or rejected. ie the RowStates of the rows where the
/// database update failed will be left as either Added, Modified, or Deleted, as
/// appropriate. It will be the responsibility of the client code to deal with these
/// rows. The rows which failed will also have their HasErrors properties set.</remarks>
private void AcceptDataTableChanges(DataTable dataTable, DataTable changedRows,
DatabaseUpdateResult databaseUpdateResult)
{
// Merge the changed rows back into the original data table. For any rows that
// were added this will update the value of any identity column to match the value
// in the database.
dataTable.Merge(changedRows);
// After the merge any identity column values will have been updated so it is okay
// to AcceptChanges. This will reset the RowState of any changed rows.
if (databaseUpdateResult == DatabaseUpdateResult.Success)
{
dataTable.AcceptChanges();
}
else if (databaseUpdateResult == DatabaseUpdateResult.PartialSuccess)
{
// Cannot use foreach to iterate through the dataTable.Rows collection - fails
// on the next iteration of the loop after a deleted row has changes accepted
// (ie after deleted row is removed from the collection).
DataRow[] rows = new DataRow[dataTable.Rows.Count];
dataTable.Rows.CopyTo(rows, 0);
foreach (DataRow row in rows)
{
if (!row.HasErrors)
{
row.AcceptChanges();
}
}
}
// If all rows failed (DatabaseUpdateResult.Failed) would not want any of the rows
// accepted, so do nothing.
}
/// <summary>
/// Updates the data source with changes that have been made to a DataTable, using the
/// specified data adapter.
/// </summary>
/// <param name="dataTable">DataTable containing changes that are to be merged back into
/// the original data source.</param>
/// <param name="dataAdapter">Data adapter that will be used to update the data source.
/// </param>
/// <param name="rollbackAllOnError">If an error occurs this determines whether all
/// changes will be rolled back, or only those changes with errors.</param>
/// <param name="timeout">Timeout, in seconds, for commands used to update database.</param>
/// <param name="errorMessage">Output Parameter: Error message resulting from attempting
/// to update the data source. Will be an empty string if there is no error.</param>
/// <returns>DatabaseUpdateResult enumeration value.</returns>
/// <remarks>If the DataAdapter does not already have its InsertCommand, UpdateCommand
/// or DeleteCommand properties set they will be automatically created.
///
/// After a record in the data source is updated successfully the matching record in the
/// DataTable will have its AcceptChanges method called, to commit the changes.
///
/// If the rollbackAllOnError parameter is set and an error occurs then all changes,
/// including those that have succeeded, will be rolled back in the data table and in
/// the source database.
///
/// If an error occurs when the rollbackAllOnError parameter is cleared then rows where
/// the update has succeeded will be committed in the data table and in the source
/// database. Data table rows where the update failed will be left in their modified
/// states (ie, with RowState of Added, Deleted or Modified, as appropriate) for the
/// client code to handle. These rows will have their RowError and HasErrors properties
/// set.
///
/// This UpdateSource method will not abort if there is any error while updating a
/// row in the data source. It will continue to process all the changes in the data table.
///
/// The data adapter will not be disposed at the end of the method. The client code is
/// responsiible for diposing the data adapter.
///
/// REQUIREMENTS:
/// This method will fail if these requirements are not met.
///
/// 1) That the table being updated has a primary key column.
/// 2) That the name of the data table matches the name of the table in the source
/// database that will be updated, and the column names in the data table and database
/// table match.
/// </remarks>
public DatabaseUpdateResult UpdateSource(DataTable dataTable, SqlDataAdapter dataAdapter,
bool rollbackAllOnError, int timeout, out string errorMessage)
{
errorMessage = string.Empty;
lock (_lockObject)
{
if (_connection == null)
{
errorMessage = "Connection has not been set up.";
return(DatabaseUpdateResult.Failed);
}
if (dataTable == null)
{
errorMessage = "No DataTable, containing the rows to update, was supplied.";
return(DatabaseUpdateResult.Failed);
}
if (dataAdapter == null)
{
errorMessage = "No DataAdapter to perform the update on the data source.";
return(DatabaseUpdateResult.Failed);
}
if (dataAdapter.SelectCommand == null)
{
string columnListText = GetColumnListText(dataTable, true, false);
if (columnListText == null)
{
errorMessage = "Unable to get a list of the columns in the data table. "
+ "Cannot create a SelectCommand for the DataAdapter.";
return(DatabaseUpdateResult.Failed);
}
string selectQuery = string.Format("SELECT {0} FROM {1};",
columnListText, dataTable.TableName);
SqlCommand selectCommand = new SqlCommand(selectQuery, _connection);
dataAdapter.SelectCommand = selectCommand;
}
int initialSelectTimeout = dataAdapter.SelectCommand.CommandTimeout;
int initialInsertTimeout = 0;
int initialUpdateTimeout = 0;
int initialDeleteTimeout = 0;
dataAdapter.SelectCommand.CommandTimeout = timeout;
try
{
// Just get changed rows from DataTable - quicker to update the data source.
DataTable changedRows = dataTable.GetChanges();
if (changedRows == null || changedRows.Rows.Count == 0)
{
return(DatabaseUpdateResult.Success);
}
// Must set this to false otherwise AcceptChanges method of data table will be
// automatically called for each row as the Update method executes. If that
// happens any newly added rows with identity values will not merge back into
// the data table. Once AcceptChanges has been called the value of the
// identity column will reflect the value in the database, not the value in
// the original data table. When the Merge method is called the data adapter
// will not be able to match the added row back to the equivalent row in
// the original data table. The row will be added as a new row to the data
// table, instead of the original row having its identity value updated.
dataAdapter.AcceptChangesDuringUpdate = false;
dataAdapter.ContinueUpdateOnError = true;
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
// Connection must be open to create a transaction.
SqlTransaction transaction
= _connection.BeginTransaction(IsolationLevel.ReadCommitted);
// Build commands the data adapter uses to insert, delete and update records
// in the source database, if they are not already specified.
SetDataAdapterCommands(dataTable, dataAdapter, transaction);
initialInsertTimeout = dataAdapter.InsertCommand.CommandTimeout;
initialUpdateTimeout = dataAdapter.UpdateCommand.CommandTimeout;
initialDeleteTimeout = dataAdapter.DeleteCommand.CommandTimeout;
dataAdapter.InsertCommand.CommandTimeout = timeout;
dataAdapter.UpdateCommand.CommandTimeout = timeout;
dataAdapter.DeleteCommand.CommandTimeout = timeout;
int numberRowsUpdated = 0;
try
{
numberRowsUpdated = dataAdapter.Update(changedRows);
}
catch (ArgumentNullException ex)
{
RollbackException(transaction);
errorMessage = "The supplied DataTable is invalid. Exception message: "
+ ex.Message;
return(DatabaseUpdateResult.Failed);
}
catch (InvalidOperationException ex)
{
RollbackException(transaction);
errorMessage = "The table in the data source is invalid. Exception message: "
+ ex.Message;
return(DatabaseUpdateResult.Failed);
}
catch (DBConcurrencyException ex)
{
RollbackException(transaction);
errorMessage = "Failed when executing a command on the data source: "
+ "No records were affected by the command. Exception message: "
+ ex.Message;
return(DatabaseUpdateResult.Failed);
}
DatabaseUpdateResult result
= GetUpdateResult(changedRows, numberRowsUpdated, out errorMessage);
if (rollbackAllOnError && result != DatabaseUpdateResult.Success)
{
RollbackException(transaction);
dataTable.RejectChanges();
return(DatabaseUpdateResult.Failed);
}
transaction.Commit();
AcceptDataTableChanges(dataTable, changedRows, result);
return(result);
}
catch (SqlException ex)
{
errorMessage = ex.Message;
if (ex.Number == (int)SqlExceptionNumbers.TimeoutExpired ||
ex.Number == (int)SqlExceptionNumbers.UnableToConnect)
{
return(DatabaseUpdateResult.TimedOut);
}
return(DatabaseUpdateResult.Failed);
}
catch (Exception ex)
{
errorMessage = ex.Message;
return(DatabaseUpdateResult.Failed);
}
finally
{
// Close connection. Cannot use "using" block because do not want to
// dispose of the connection as it may be used by other code.
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
// Reset command timeouts to initial values.
if (dataAdapter.SelectCommand != null)
{
dataAdapter.SelectCommand.CommandTimeout = initialSelectTimeout;
}
if (dataAdapter.InsertCommand != null)
{
dataAdapter.InsertCommand.CommandTimeout = initialInsertTimeout;
}
if (dataAdapter.UpdateCommand != null)
{
dataAdapter.UpdateCommand.CommandTimeout = initialUpdateTimeout;
}
if (dataAdapter.DeleteCommand != null)
{
dataAdapter.DeleteCommand.CommandTimeout = initialDeleteTimeout;
}
}
}
}
