Workshop

for RuBoard

This workshop will help reinforce the concepts covered in today's lesson.

Quiz

1:	When would you set the MissingSchemaAction property to Error ?
A1:	Setting the MissingSchemaAction property to Error will cause the Fill method to throw an exception if the incoming schema (the one from the data store) doesn't exactly match that in the existing DataTable . You can use this to ensure that the DataTable you're reading into is correct for the data store.
2:	What is the purpose of table and column mappings?
A2:	Table and column mappings are used by the data adapter to map the incoming table and column names with DataTable and DataColumn names to be populated . They can be used both to match an existing schema and to specify what the new schema will look like depending on the value of the MissingSchemaAction property.
3:	What property controls if the data adapter throws an exception during the Update method?
A3:	When the ContinueUpdateOnError property is set to False , the data adapter will throw an exception when the first error is encountered . Any rows after that row won't be processed .
4:	What is the advantage to using optimistic concurrency?
A4:	By using optimistic concurrency and looking only at the primary key value, clients will ultimately encounter fewer conflicts when attempting to update rows. This is the case because optimistic concurrency entails a last-in-wins strategy in which the last client to submit its changes will overwrite other changes previously made, even if the first client wasn't aware they had been made.

Exercise

Q1:

Using the pattern shown in Listing 12.8, write a method that could be used to synchronize data in the Reviews table using optimistic concurrency.

A1:

One possible solution might be the following: public virtual DataSet SaveReview(DataSet ds) { //Make sure the DataSet has some changed data if (ds == null ds.HasChanges() == false) { // Can simply return null return null; } // Create the connection object SqlConnection con = new SqlConnection(_connect); // Create the data adapter (da) SqlDataAdapter da = new SqlDataAdapter("usp_GetReviews",con); // Create the data adapter commands and configure their parameters SqlCommand cmUpd = new SqlCommand("usp_UpdReview",con); SqlCommand cmIns = new SqlCommand("usp_InsReview",con); SqlCommand cmDel = new SqlCommand("usp_DelReview",con); // Associate the commands with the data adapter da.InsertCommand = cmIns; da.UpdateCommand = cmUpd; da.DeleteCommand = cmDel; //Select da.SelectCommand.Parameters.Add(new SqlParameter( "@reviewId",SqlDbType.UniqueIdentifier,16,"ReviewID")); //Update da.UpdateCommand.Parameters.Add(new SqlParameter( "@reviewId",SqlDbType.UniqueIdentifier,16,"ReviewID")); da.UpdateCommand.Parameters.Add(new SqlParameter( "@isbn",SqlDbType.NChar,10,"isbn")); da.UpdateCommand.Parameters.Add(new SqlParameter( "@reviewText",SqlDbType.NChar,0,"ReviewText")); da.UpdateCommand.Parameters.Add(new SqlParameter( "@stars",SqlDbType.TinyInt,2,"Stars")); //Insert da.InsertCommand.Parameters.Add(new SqlParameter( "@reviewId",SqlDbType.UniqueIdentifier,16,"ReviewID")); da.InsertCommand.Parameters.Add(new SqlParameter( "@isbn",SqlDbType.NChar,10,"isbn")); da.InsertCommand.Parameters.Add(new SqlParameter( "@reviewText",SqlDbType.NChar,0,"ReviewText")); da.InsertCommand.Parameters.Add(new SqlParameter( "@stars",SqlDbType.TinyInt,2,"Stars")); //Delete da.DeleteCommand.Parameters.Add(new SqlParameter( "@reviewId",SqlDbType.UniqueIdentifier,16,"ReviewID")); // Set the ContineUpdateOnError property to true da.ContinueUpdateOnError = true; try { // Call the Update method passing in results of the GetChanges method da.Update(ds); // Check for errors and return rows that were in error if (ds.HasErrors) { DataSet dsErrors; dsErrors = ds.GetChanges(DataRowState.Modified & DataRowState.Deleted); return dsErrors; } return null; } catch (Exception e) { // An exception occurred, probably in the connection // Wrap and throw a specific exception throw (new Exception("Could not update Review",e)); } }

for RuBoard