Manipulating data in asp.net 2.0 48: Using open concurrency _ self-study for SqlDataSource controls

Introduction:

In the previous tutorial, we examined how to add inserts, updates, and deletions to the SqlDataSource control. In short, the corresponding insert,update and DELETESQL statements are assigned to their nsertcommand, UpdateCommand, and DELETECOMMD attributes, and the corresponding arguments are placed in the <insertparameters , <UpdateParameters> and <DeleteParameters> tags. We can write the code manually, or you can automatically generate the statement by clicking the Advanced button in the Set Data Source Wizard and selecting automatically generate INSERT, update, and delete commands.

There is a "use optimistic concurrency" option in the Advanced SQL Build Options dialog box (see Figure 1). When
When this item is selected, the data in the database can be successfully performed without any changes since the last successful save.

Figure 1: Adding optimistic concurrency support in the Advanced SQL Generation Options dialog box

In implementing the open concurrent tutorial we explored the rationale of open concurrency control and how to use optimistic concurrency for ObjectDataSource controls. In this tutorial we see how to use optimistic concurrency for SqlDataSource Controls.

New optimistic concurrency

In an application that allows multiple people to edit or delete the same data at the same time, it is possible that a person's modified record is accidentally overwritten by another person's modified record. In the implementing optimistic concurrency this tutorial, we cite an example of this:

For example, suppose two users, Jisun, and Sam have access to a page in our application that allows visitors to update and delete product data through a GridView control. They both clicked the Edit button in the GridView control. Jisun Change the product name to "Chai Tea" and click the Update button, the real result is to send an UPDATE statement to the database, it will update all the modifiable fields of this product (although Jisun actually only modifies one field: ProductName). At this moment, the database contains this product record "Chai Tea"-Beverages, supplier for exotic liquids, and more. However, in the GridView in the Sam's screen, the name of the slice displayed in the current edit line is still "Chai". After the Jisun changes are submitted for a moment, the SAM changes the category to "condiments" and clicks on the Update button. The result of this UPDATE statement sent to the database is to change the product name to "Chai", the value of the CategoryID field is the ID of the kind beverages, and so on. Jisun's changes to the product name are overwritten.

Figure 2 shows these sequential events

Figure 2: When two users update a record at the same time, there is a possibility that one user's change overrides another change

To deal with this possibility, we have to perform some kind of concurrency control. The focus of this article--open concurrency control is one of the things that is appropriate for this scenario: it is assumed that concurrent conflicts occur only occasionally, and most of the time does not occur. When a conflict occurs, simply notifies the user that his changes cannot be saved because the other user has modified the same record.

Note: For applications, it is assumed that concurrent conflicts occur frequently and cannot be tolerated. In this case the conservative concurrency control is finally used. For more discussion on pessimistic concurrency control, refer to the implementing optimistic concurrency tutorial.

The purpose of optimistic concurrency control is to ensure that the value of the record to be updated or deleted is the same as the value of the record at the updating or deleting stage. For example, when you click the Edit button in an editable GridView, the original value of the record is read from the database and displayed in the TextBox and other Web controls. These original values are stored in the GridView. Then, when the user completes his changes and clicks on the Update button, the original values plus the modified values are sent to the business logic layer and then to the data access layer. The data access layer must emit an SQL statement that will only update records that are consistent in the original value root database when the edit was started. Figure 3 describes the order in which these events occur.

Figure 3: In order for the update or deletion to succeed, the original value must be the same as the corresponding value in the database

There are several ways to implement optimistic concurrency control (see Peter A. Bromberg's article optmistic concurrency updating Logic, see a number of choices from the summary). The SqlDataSource control expands the WHERE clause using the method, as it is used in the data set of the Ado.net type in the data access layer, to contain the original values used to make comparisons. For example, the following UPDATE statement updates the name and price of a product when the value in the current database is consistent with the original value that was edited in the GridView. The @ProductName and @UnitPrice parameters contain the new values entered by the user, while the parameters @original_productname and @original_UnitPrice contain the values loaded into the GridView when the Edit button was first clicked:

UPDATE products SET
 ProductName = @ProductName,
 UnitPrice = @UnitPrice
WHERE
 ProductID = @original_ ProductID and
 ProductName = @original_ProductName and
 UnitPrice = @original_UnitPrice

As we'll see in this tutorial, it's easy to make SqlDataSource realize open concurrency control.

First step: Create a SqlDataSource control that supports optimistic concurrency

Open the Optimisticconcurrency.aspx page in the SqlDataSource folder, drag a SqlDataSource control from the toolbox to the page, and set its ID to Productsdatasourcewithoptimisticconcurre Ncy. In its smart tag point "set data source", the database selected as "NorthwindConnectionString", click Next.

Figure 4: Selecting the "orthwindconnectionstring" database

In this example, we will add a GridView control to edit the table products. So in the "Configure the Select Statement" interface, choose to return ProductID, ProductName, UnitPrice and discontinued columns from the table products, as shown in Figure 5:

Figure 5: Returning ProductID, ProductName, UnitPrice and discontinued columns from the table products

Then, click the Advanced button to open the Advanced SQL Generation Options dialog box, select Generate INSERT, UPDATE, and DELETE statements and use optimistic Concurrency "2 items, point" OK "(see Figure 1). Then click Next, Finish, and end setup.

After you finish setting up the Data Source Wizard, take a few minutes to view the DeleteCommand and UpdateCommand properties, as well as the deleteparameters and updateparameters tags. The quickest way to switch to "source mode" is to view the page code directly, and you will see UpdateCommand values like this:

UPDATE [Products] SET
 [ProductName] = @ProductName,
 [UnitPrice] = @UnitPrice,
 [discontinued] = @ Discontinued
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 [ UnitPrice] = @original_UnitPrice and
 [discontinued] = @original_Discontinued

At the same time there are 7 parameters in the <UpdateParameters> label:

<asp:sqldatasource id= "productsdatasourcewithoptimisticconcurrency"
 runat= "Server" ...>
 < Deleteparameters> ...
 </DeleteParameters>
 <UpdateParameters>
 <asp:parameter name= "ProductName" type= "String"/ >
 <asp:parameter name= "UnitPrice" type= "Decimal"/> <asp:parameter name= "discontinued" Type= "
 Boolean "/>
 <asp:parameter name=" Original_productid "type=" Int32 "/> <asp:parameter name=
 " Original_productname "type=" String "/> <asp:parameter name=" Original_unitprice "type="
 Decimal "/>
 <asp:parameter name= "original_discontinued" type= "Boolean"/>
 </UpdateParameters>
 ...
</asp:SqlDataSource>

Similarly, the DeleteCommand properties and <DeleteParameters> tags are as follows:

DELETE from [Products]
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 [UnitPrice] = @original_UnitPrice and
 [discontinued] = @original_Discontinued

<asp:sqldatasource id= "productsdatasourcewithoptimisticconcurrency"
 runat= "Server" ...>
 < deleteparameters>
 <asp:parameter name= "Original_productid" type= Int32 "/>"
 <asp:parameter Name= "Original_productname" type= "String"/> <asp:parameter name= "Original_unitprice"
 Type= "Decimal"/ >
 <asp:parameter name= "original_discontinued" type= "Boolean"/>
 </DeleteParameters>
 <UpdateParameters> ...
 </UpdateParameters> ...
</asp:SqlDataSource>

When the use optimistic concurrency option is selected, not only extends the WHERE clause in the UpdateCommand and DeleteCommand properties, but also adds parameters to the set of related parameters, and adjusts the following 2 properties:

1. Change the ConflictDetection property from "OverwriteChanges" (default) to "CompareAllValues"
2. Change the OldValuesParameterFormatString property from ' {0} ' (default) to ' original_{0} '

When the data Web control invokes the SqlDataSource update () or delete () method, it passes the original value. When the SqlDataSource ConflictDetection property is set to "CompareAllValues", the original values are added to the command. The OldValuesParameterFormatString property provides a naming convention for these raw values, and the wizard takes the form "original_{0}" as the original value in UpdateCommand and DeleteCommand and the < Updateparameters> and <DeleteParameters> in the name of the parameter.

　　Note: Because we do not use the Insert feature of the SqlDataSource control, we can clear the InsertCommand properties and <InsertParameters> labels.

Handling Null values correctly

Unfortunately, when optimistic concurrency is used, the update and delete commands that are automatically generated by the Set Data Source wizard that extend to contain the WHERE clause cannot process records that contain null values. Why, then? First look at the SqlDataSource UpdateCommand statement:

UPDATE [Products] SET
 [ProductName] = @ProductName,
 [UnitPrice] = @UnitPrice,
 [discontinued] = @ Discontinued
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 [ UnitPrice] = @original_UnitPrice and
 [discontinued] = @original_Discontinued

The value of the UnitPrice column for table products is allowed to be null, and how the UnitPrice of a record is indeed null, then the "[UnitPrice] = @original_UnitPrice" of the WHERE clause is always false,null = null always returns FALSE. Therefore, any records y containing null values cannot be edited or deleted because the WHERE clause in the update and delete commands cannot return records.

　　Note: This vulnerability was first reported to Microsoft in June 2004 and, according to industry rumors, Microsoft will fix the vulnerability in the next version of ASP.net.

To fix this vulnerability, we need to manually modify all columns that allow null values in the UpdateCommand and DeleteCommand properties. In general, convert [ColumnName] = @original_ColumnName to:

(
 [ColumnName] is null and @original_ColumnName is null)
 OR
 ([ColumnName] = @original_ColumnName)
)

You can modify it in the code declaration for the UpdateQuery or DeleteQuery option of the Properties window, or in the Update and delete tabs of the Specify custom SQL statement or stored procedure option in the Set Data Source Wizard. Make sure to make the same changes in the WHERE clause of UpdateCommand and DeleteCommand. As follows:

UPDATE [Products] SET
 [ProductName] = @ProductName,
 [UnitPrice] = @UnitPrice,
 [discontinued] = @ Discontinued
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 ( ([UnitPrice] is null and @original_UnitPrice is null)
 OR ([UnitPrice] = @original_UnitPrice)) and
 [discontinued] = @original_Discontinued
DELETE from [Products]
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 ([UnitPrice] is NULL and @ Original_unitprice is NULL)
 OR ([UnitPrice] = @original_UnitPrice)) and
 [discontinued] = @original_ Discontinued

2nd step: Add Edit and delete items for the GridView control

When setting the SqlDataSource control to support optimistic concurrency, we need to add a data Web control to the page to perform optimistic concurrency control. In this chapter we add a GridView control that provides editing and deletion capabilities. Drag a GridView from the toolbox to the page, set its ID to products, and bind to the SqlDataSource control Productsdatasourcewithoptimisticconcurrency added in the first step, and finally enable its Edit and delete features.

Figure 6: Bind the GridView to SqlDataSource and enable Edit and delete features

After adding the GridView control, optimize its interface. Remove the ProductID column, set the HeaderText property of the ProductName column to "Product", and the UnitPrice column set to "Price". Also, we'd better add a RequiredFieldValidator control for ProductName and a CompareValidator control for UnitPrice (make sure it's a formatted numeric value). Reference tutorial Customizing the Data modification interface see how to customize the GridView interface.

　　Note: You must make sure that the GridView control's view state is activated because the GridView control, when it passes the original value, saves the original value in view states.

After these modifications are made to the GridView control, the declaration code for the GridView control and the SqlDataSource control looks similar to the following:

<asp:sqldatasource id= "productsdatasourcewithoptimisticconcurrency" runat= "Server" conflictdetection= " CompareAllValues "connectionstring=" <%$ connectionstrings:northwndconnectionstring%> "DeleteCommand=" DELETE From [Products] WHERE [ProductID] = @original_ProductID and [ProductName] = @original_ProductName and ([UnitPrice] Is null and @original_UnitPrice is null) OR ([UnitPrice] = @original_UnitPrice)) and [discontinued] = @original_Discon tinued "oldvaluesparameterformatstring=" original_{0} "selectcommand=" SELECT [ProductID], [ProductName], [UnitPrice] , [discontinued] from [Products] "updatecommand=" UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [discontinued] = @Discontinued WHERE [ProductID] = @original_ProductID and [ProductName] = @original_Pro Ductname and ([UnitPrice] is null and @original_UnitPrice is null) OR ([UnitPrice] = @original_UnitPrice)) and [Disc Ontinued] = @original_Discontinued "> &Lt;deleteparameters> <asp:parameter name= "Original_productid" type= "Int32"/> <asp:parameter "Name=" Original_productname "type=" String "/> <asp:parameter name=" Original_unitprice "type=" Decimal "/> <asp: Parameter name= "original_discontinued" type= "Boolean"/> </DeleteParameters> <UpdateParameters> < Asp:parameter name= "ProductName" type= "String"/> <asp:parameter "name=" UnitPrice "Decimal" type=/>: Parameter name= "discontinued" type= "Boolean"/> <asp:parameter name= "Original_productid" type= "Int32"/> < Asp:parameter name= "Original_productname" type= "String"/> <asp:parameter name= "Original_unitprice" Decimal "/> <asp:parameter name=" original_discontinued "type=" Boolean "/> </UpdateParameters> </asp :sqldatasource> <asp:gridview id= "Products" runat= "server" autogeneratecolumns= "False" datakeynames= " ProductID "datasourceid=" Productsdatasourcewithoptimisticconcurrency ">
 <Columns> <asp:commandfield showdeletebutton= "true" showeditbutton= "true"/> <asp:boundfield Datafield= "ProductName" headertext= "Product" sortexpression= "ProductName"/> <asp:boundfield "datafield=" UnitPrice "headertext=" Price "sortexpression=" UnitPrice "/> <asp:checkboxfield datafield=" Discontinued "
 headertext= "Discontinued" sortexpression= "discontinued"/> </Columns> </asp:GridView>

To actually feel the open concurrency control. Open the Optimisticconcurrency.aspx page in 2 browsers and click the Edit button on the first record. Change the product name in the first browser and click Edit. The browser will return, and the GridView control goes back to the pre-edit state, displaying the new product name.

In the 2nd browser, change the price of the product (do not change the product name), click "Edit". When a postback occurs, the GridView control returns to the "pre-edit" state, as shown in the 1th browser-the name of the product changed but the price did not change, and the 2nd browser's modification failed. However, everything happened so quietly, without any hint that a concurrency conflict had just happened!

Figure 7: The changes made by the 2nd browser are lost silently

The 2nd browser update failed because the WHERE clause in the UPDATE command filtered out the record and did not affect any one row (that is, no records were found to meet the criteria). Let's look at the UPDATE statement again:

UPDATE [Products] SET
 [ProductName] = @ProductName,
 [UnitPrice] = @UnitPrice,
 [discontinued] = @ Discontinued
WHERE
 [ProductID] = @original_ProductID and
 [ProductName] = @original_ProductName and
 ( ([UnitPrice] is null and @original_UnitPrice is null) OR
 ([UnitPrice] = @original_UnitPrice)) and
 [discontinued] = @original_Discontinued

When the 2nd browser updates the record, the original product name (that is, chai) in the WHERE clause does not match the product name of any current record (because the 1th browser changes the chai to chai tea). Therefore, the expression "[ProductName] = @original_ProductName" returns false, resulting in an update failure.

Note: The deletion principle is the same. Open 2 browsers at the same time, the 1th one to make changes to a product, and then delete the product in the 2nd browser, also because the original value does not match the updated value, the deletion failed.

At the end user (the one that failed to update) it appears that the GridView control returned to the pre-edit state, but the submitted modifications were lost, after he clicked the "Update" button. However, there is no intuitive reminder that the modification failed. When a user's update fails because of a concurrency violation, we'd better alert the user, for example, to keep the GridView control in edit state. Let's see how this can be achieved.

3rd Step: Handling of concurrent conflicts

Because concurrent conflicts deny user changes, it is a good idea to prompt the user when a concurrency violation occurs. Adds a label control to the page with an ID of concurrencyviolationmessage and sets its Text property to "Your have attempted to update or delete a record of that is Simul taneously updated by another user. Please review the other user ' s changes and then redo your update or delete to set its CssClass property to ' Warning ', which is defined in Styles.css. Finally, set the visible and EnableViewState properties to "false." The label control will not be visible unless some postback event occurs (we specified in these postback events that the Visible property of the Label control is true)

Figure 8: Add a Label control to the page to display the reminder message

When an update or a delete operation is performed, the RowUpdated and rowdeleted event handlers for the GridView control (event handler) are not started until the GridView data source control completes an update or deletion. We can calculate how many records are affected in these event handlers. If 0 records are affected, i.e. the operation fails, we want to display the label control Concurrencyviolationmessage.

To create a processor for the rowupdated and rowdeleted events, add the following code:

protected void products_rowupdated (object sender, GridViewUpdatedEventArgs e)
{
 if (e.affectedrows = 0)
 {
 concurrencyviolationmessage.visible = true;
 E.keepineditmode = true;
 Rebind the data to the GridView to show the latest changes
 products.databind ();
 }
protected void products_rowdeleted (object sender, Gridviewdeletedeventargs e)
{
 if (e.affectedrows = 0)
 concurrencyviolationmessage.visible = true;
}

In these 2 event handlers we have to examine E. The AffectedRows property, if 0, sets the Visible property of the Label control Concurrencyviolationmessage to True. In particular, in the RowUpdated event handler, we leave the GridView control's Keepineditmode property to True to keep it in edit state. In this way, the DataBind () method of the GridView is used to display the data that someone else has successfully updated in the editing state.

As shown in Figure 9, when a concurrency violation occurs, the message is displayed

Figure 9: When a concurrency violation occurs, a message is displayed:

Summarize:

When creating an application, when multiple people edit the same data at the same time, consider the problem of concurrent conflicts. By default, ASP. NET data Web controls and data source controls do not take concurrent control. As we have seen in this chapter, it is relatively quick and easy to use open concurrency control for SqlDataSource Controls. By extending the WHERE clause in the UPDATE and DELETE statements, SqlDataSource can handle most situations, but as discussed in the "properly handle NULL" section, there is a vulnerability to the handling of columns that contain null values.

This chapter is the end of the SqlDataSource Review, and the next tutorial continues to explore the hierarchy and process the data with Bjectdatasource.

I wish you a happy programming!

Introduction of the author

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.