| [ Team LiB ] |
|
Recipe 6.11 Resolving Data ConflictsProblemYou need to effectively resolve data conflicts and prevent overwriting of existing data when attempting to update changes in a DataSet to a database where the underlying data has changed. SolutionHandle the DBConcurrencyException within the RowUpdated event of the DataAdapter. The schema of table TBL0611 used in this solution is shown in Table 6-13.
The sample code contains seven event handlers:
The C# code is shown in Example 6-29. Example 6-29. File: ResolveDataConflictsForm.cs// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;
private const String TABLENAME = "TBL0611";
private SqlDataAdapter daA, daB;
private DataSet dsA, dsB;
// . . .
private void ResolveDataConflictsForm_Load(object sender,
System.EventArgs e)
{
// Tables A and B are filled from the same data source table.
String sqlText = "SELECT * FROM " + TABLENAME;
// Create the DataAdapter for table A.
daA = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]);
daA.ContinueUpdateOnError = true;
// Handle the RowUpdated event.
daA.RowUpdated += new SqlRowUpdatedEventHandler(daA_RowUpdated);
// Get the schema and data for table A in the DataSet.
dsA = new DataSet("A");
daA.FillSchema(dsA, SchemaType.Source, TABLENAME);
daA.Fill(dsA, TABLENAME);
// Create the command builder.
SqlCommandBuilder cbA = new SqlCommandBuilder(daA);
// Bind the default view for table A to the grid.
dataGridA.DataSource = dsA.Tables[TABLENAME].DefaultView;
// Create a DataAdapter to retrieve original rows
// for conflicts when updating table A.
conflictDaA = new SqlDataAdapter(sqlText + " WHERE Id = @Id",
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
conflictDaA.SelectCommand.Parameters.Add("@Id", SqlDbType.Int, 0);
// Create a DataSet with the conflict table schema.
conflictDsA = new DataSet( );
daA.FillSchema(conflictDsA, SchemaType.Source, TABLENAME);
// Bind the default view for conflict table B to the grid.
conflictDataGridA.DataSource =
conflictDsA.Tables[TABLENAME].DefaultView;
// Create the DataAdapter for table B.
daB = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]);
daB.ContinueUpdateOnError = true;
// Handle the RowUpdated event.
daB.RowUpdated += new SqlRowUpdatedEventHandler(daB_RowUpdated);
// Get the schema and data for table A in the DataSet.
dsB = new DataSet("B");
daB.FillSchema(dsB,SchemaType.Source,TABLENAME);
daB.Fill(dsB, TABLENAME);
// Create the command builder.
SqlCommandBuilder cbB = new SqlCommandBuilder(daB);
// Bind the default view for table A to the grid.
dataGridB.DataSource = dsB.Tables[TABLENAME].DefaultView;
// Create a DataAdapter to retrieve original rows
// for conflicts when updating table B.
conflictDaB = new SqlDataAdapter(sqlText + " WHERE Id = @Id",
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
conflictDaB.SelectCommand.Parameters.Add("@Id", SqlDbType.Int, 0);
// Create a DataSet with the conflict table schema.
conflictDsB = new DataSet( );
daB.FillSchema(conflictDsB,SchemaType.Source,TABLENAME);
// Bind the default view for conflict table B to the grid.
conflictDataGridB.DataSource =
conflictDsB.Tables[TABLENAME].DefaultView;
}
private void refreshAButton_Click(object sender, System.EventArgs e)
{
// Clear the conflict table and reload the data.
conflictDsA.Clear( );
dsA.Clear( );
daA.Fill(dsA, TABLENAME);
}
private void refreshBButton_Click(object sender, System.EventArgs e)
{
// Clear the conflict table and reload the data.
conflictDsB.Clear( );
dsB.Clear( );
daB.Fill(dsB, TABLENAME);
}
private void updateAButton_Click(object sender, System.EventArgs e)
{
// Clear the conflict table and update table A to data source.
conflictDsA.Clear( );
daA.Update(dsA, TABLENAME);
}
private void updateBButton_Click(object sender, System.EventArgs e)
{
// Clear the conflict table and update table B to data source.
conflictDsB.Clear( );
daB.Update(dsB, TABLENAME);
}
private void daA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
// Check if a concurrency exception occurred.
if (e.Status == UpdateStatus.ErrorsOccurred &&
e.Errors.GetType( ) == typeof(DBConcurrencyException))
{
// If the row was deleted, reject the delete.
if(e.Row.RowState == DataRowState.Deleted)
e.Row.RejectChanges( );
// Get the row ID.
conflictDaA.SelectCommand.Parameters["@Id"].Value =
e.Row["ID"];
// Get the row from the data source for the conflicts table.
if(conflictDaA.Fill(conflictDsA, TABLENAME) == 1)
e.Row.RowError =
"Row has been changed in the database.";
else
e.Row.RowError =
"Row has been deleted from the database.";
}
}
private void daB_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
// Bheck if a concurrency exception occurred.
if (e.Status == UpdateStatus.ErrorsOccurred &&
e.Errors.GetType( ) == typeof(DBConcurrencyException))
{
// If the row was deleted, reject the delete.
if(e.Row.RowState == DataRowState.Deleted)
e.Row.RejectChanges( );
// Get the row ID.
conflictDaB.SelectCommand.Parameters["@Id"].Value =
e.Row["ID"];
// Get the row from the data source for the conflicts table.
if(conflictDaB.Fill(conflictDsB, TABLENAME) == 1)
e.Row.RowError =
"Row has been changed in the database.";
else
e.Row.RowError =
"Row has been deleted from the database.";
}
}
DiscussionThe RowUpdated event of the DataAdapter occurs during the Update( ) method after the command to update a row is executed against the data source. The event fires with each row update attempt. The RowUpdated event handler receives an argument of type RowUpdatedEventArgs that provides information specifically related to the event as described in Table 6-14.
Table 6-15 describes the values in the UpdateStatus enumeration used by the Status property of the RowUpdatedEventArgs object.
The Update( ) method of the DataAdapter raises two events for every row in the data source that is updated. The order of the events is:
The DBConcurrencyException is raised during the update operation on a row if zero rows are affected. This usually indicates a concurrency violation. |
| [ Team LiB ] |
|