Home>

This article mainly explains the sqldatasource control in asp.net 2.0 through the cooperation of sql statements to achieve the purpose of controlling open concurrency,By extending the where clause in update and delete statements, sqldatasource can handle most situations.

preface:

In the previous tutorial,We looked at how to add insert, update, and delete functions to the sqldatasource control.in short,It is to assign corresponding insert, update, and deletesql statements to its insertcommand, updatecommand, and deletecommd attributes, and place the corresponding parameters in the,, andtags. We can write these codes by hand,You can also generate statements automatically by clicking the "Advanced" button in the Set Data Source Wizard and selecting "Automatically generate insert, update, and delete commands."

Have There is a "Use Open Concurrency" option in the "Advanced SQL Generation Options" dialog box (see Figure 1). when

When selected,The data in the database has not changed since the last successful save,To successfully perform the update or delete operation.

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

LingImplementing optimistic concurrencyIn the tutorial, we explored the basic principles of open concurrency control and how to use open concurrency for the objectdatasource control.In this tutorial we will see how to use optimistic concurrency for the sqldatasource control.

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 record modified by one person is accidentally overwritten by another modified record.In this tutorial on implementing optimistic concurrency we have given examples like this:

For example, suppose two users,jisun and sam, both visit a page in our application,This page allows visitors to update and delete product data through a gridview control.They both click the edit button in the gridview control at the same time. Jisun changed the product name to "chai tea" and clicked the update button. The actual result is to send an update statement to the database, which will update all modifiable fields of this product (though jisun actually only modified one field:productname). at this moment,The database contains detailed information about this product record "chai tea"-category foreverages, supplier for exotic liquids, etc.However, in the gridview in Sam's screen, the production name displayed in the current editing line is still "chai". A moment after jisun's changes were committed,Sam changed the type to "condiments" and clicked 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 corresponding to the category beverages, and so on. Jisun's changes to the product name were overwritten.

Figure 2 shows these consecutive events

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

In response to this possibility,We have to implement some kind of concurrency control.The focus of this article is open concurrency control.It's suitable for this situation:assuming that concurrent conflicts happen only occasionally,Most of the time it does not occur. When a conflict occurs,Just simply inform the user,His changes cannot be saved,Because other users have modified the same record.

Note:For applications,Assuming that concurrent conflicts often occur,And it cannot be tolerated.In this case, conservative concurrency control is used at the end.More discussion about conservative concurrency control,Please refer to the implementing optimistic concurrency tutorial.

The role of open 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 during the adding or deleting stage.For example, for example, when clicking the edit button in an editable gridview,The raw value of this record is read from the database and displayed in textbox and other web controls. These original values ​​are stored in the gridview. Then, when the user finishes his edits and clicks the update button,These original values ​​plus the modified new values ​​are sent to the business logic layer,Then go to the data access layer.The data access layer must issue a sql statement, which will only update those records whose values ​​in the root database are consistent when they start editing.Figure 3 depicts the sequence in which these events occur.

Figure 3:In order to update or delete successfully,The original value must match the corresponding value in the database

There are many ways to achieve open concurrency control (see peter a. Bromberg's article optmistic concurrency updating logic, see many options from the summary). The sqldatasource control uses this method (as used by ado.net-type datasets in the data access layer) to extend the where clause to include the original value used for comparison.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 started editing in gridview.The @productname and @unitprice parameters contain new values ​​entered by the user,The parameters @original_productname and @original_unitprice contain the values ​​that were loaded into the gridview when the edit button was first clicked:

update products set
 [email protected], [email protected]
where
 [email protected]_productid and
 [email protected]_productname and
 [email protected]_unitprice

As we will see in this tutorial,Enabling sqldatasource to implement open concurrency control is very simple.

Step 1:Create an sqldatasource control that supports open concurrency

Open the optimisticconcurrency.aspx page in the sqldatasource folder, drag a sqldatasource control from the toolbox to the page,Set its id to productsdatasourcewithoptimisticconcurrency. Click "Set Data Source" in its smart label, select "northwindconnectionstring" for the database, and 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 the productid, productname, unitprice, and discontinued columns from the products table, as shown in Figure 5:

Figure 5:Productid, productname, unitprice and discontinued columns returned from the products

Then, click the "Advanced" button to open the "advanced sql generation options" dialog, select "generate insert, update, and delete statements" and "use optimistic concurrency", and click "ok" (see Figure 1). Then click Next and Finish to complete the setting.

After completing the Set Data Source Wizard,Take a few minutes to review the deletecommand and updatecommand attributes, and the deleteparameters and updateparameters tags. The fastest way is to switch to "source mode" to view directly in the page code,You will see the value of updatecommand like this:

update [products] set
 [productname][email protected], [unitprice][email protected], [discontinued][email protected]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 [unitprice][email protected]_unitprice and
 [discontinued][email protected]_discontinued

At the same time there are 7 parameters in thetag:

<asp:sqldatasource
 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 attribute and thetag are as follows:

delete from [products]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 [unitprice][email protected]_unitprice and
 [discontinued][email protected]_discontinued
<asp:sqldatasource
 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>

After selecting the "use optimistic concurrency" option, not only the where clauses in the updatecommand and deletecommand attributes are extended (at the same time parameters are added to the related parameter set), and the following two attributes are adjusted:

1. Change the conflictdetection property from "overwritechanges" (default) to "compareallvalues"

2. Change the oldvaluesparameterformatstring attribute from "{0}" (the default value) to "original_ {0}"

data When the data web control calls the update () or delete () method of sqldatasource, it will pass the original value.When the conflictdetection property of sqldatasource is set to "compareallvalues", these raw values ​​are added to the command.The oldvaluesparameterformatstring attribute provides a naming convention for these primitive values,The wizard names the original values ​​in updatecommand and deletecommand and the parameters inandin the form "original_ {0}".

Note:Since we do not use the insert function of the sqldatasource control,It is therefore possible to clear the insertcommand attribute and thetag.

Handling null values ​​correctly

Unfortunately,When using optimistic concurrency,The update and delete commands automatically generated by the Set Data Source Wizard and expanded to include where clauses cannot process records that contain null values.why?Look at the updatecommand statement of sqldatasource:

update [products] set
 [productname][email protected], [unitprice][email protected], [discontinued][email protected]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 [unitprice][email protected]al_unitprice and
 [discontinued][email protected]_discontinued

ProductThe value of the unitprice column of the table products is allowed to be null. If the unitprice of a record is indeed null, then the "[unitprice][email protected]_unitprice" of the where clause is always false, and null=null always returns false. So any record where y contains a null value cannot be edited or deleted.Because the where clause in the update and delete commands cannot return records.

Note:This vulnerability was reported to Microsoft as early as June 2004.According to industry rumors,Microsoft will patch the vulnerability in the next version of asp.net.

repair To patch the vulnerability,We need to manually modify all columns that allow null values ​​in the updatecommand and deletecommand attributes. Generally speaking,Change [columnname][email protected]_columnname to:

(
 ([columnname] is null and @original_columnname is null)
 or
 ([columnname][email protected]_columnname)
)

You can modify it in the code statement of the updatequery or deletequery option in the properties window,Or modify in the "Update" and "Delete" tabs of the "Specify a custom SQL statement or stored procedure" option of 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][email protected], [unitprice][email protected], [discontinued][email protected]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 (([unitprice] is null and @original_unitprice is null)
 or ([unitprice][email protected]_unitprice)) and
 [discontinued][email protected]_discontinued
delete from [products]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 (([unitprice] is null and @original_unitprice is null)
 or ([unitprice][email protected]_unitprice)) and
 [discontinued][email protected]_discontinued

Step 2:Add edit and delete items to the gridview control

When setting the sqldatasource control to support optimistic concurrency,We need to add a data web control to the page in order to perform open concurrency control.In this chapter we add a gridview control that provides editing and deleting functions. Drag a gridview from the toolbox onto 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" functions.

Figure 6:Binding gridview to sqldatasource and enabling edit and delete functions

After adding the gridview control, optimize its interface.Remove the productid column;set the headertext property of the productname column to "product";also, set the unitprice column to "price". In addition, we better add a requiredfieldvalidator control for productname;add a comparevalidator control for unitprice (make sure it is a formatted numeric value). Refer to the tutorial customizing the data modification interface to see how to customize the gridview interface.

Note:You must ensure that the view state of the gridview control is activated, because when the gridview control passes the original value,Save the original value in the view state.

Done After making these modifications to the gridview control,The declaration code of the gridview control and sqldatasource control looks similar to the following:

<asp:sqldatasource
 runat="server" conflictdetection="compareallvalues"
 connectionstring="<%$connectionstrings:northwndconnectionstring%>"
 deletecommand =
 "delete from [products]
  where [productid][email protected]_productid
  and [productname][email protected]_productname
  and (([unitprice] is null and @original_unitprice is null)
  or ([unitprice][email protected]_unitprice))
  and [discontinued][email protected]_discontinued "
 oldvaluesparameterformatstring =
 "original_ {0}"
 selectcommand =
 "select [productid], [productname], [unitprice], [discontinued]
  from [products] "
 updatecommand =
 "update [products]
  set [productname][email protected], [unitprice][email protected],  [discontinued][email protected]
  where [productid][email protected]_productid
  and [productname][email protected]_productname
  and (([unitprice] is null and @original_unitprice is null)
  or ([unitprice][email protected]_unitprice))
 and [discontinued][email protected]_discontinued ">
 <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" 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>
<asp:gridview 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>

Let's actually experience open concurrency control.Open the optimisticconcurrency.aspx page in both browsers at the same time, and click the edit button of the first record.Change the product name in the first browser and click "Edit". The browser will post back,The gridview control returns to the "pre-edited" state and displays the new product name.

In the second browser,After changing the price of the product (don't change the product name), click "Edit". A postback occurs,The gridview control returns to the "pre-edited" state, which is the same as the result displayed in the first browser-the product name has changed but the price has not changedThe modification made by the second browser failed.However, everything happened so quietly,No hint that a concurrency conflict just happened!

Figure 7:Changes made by the second browser are quietly lost

of The reason for the failure of the second browser update is that all records in the update command are filtered out.Does not affect any one row of records (that is, no records that meet the conditions are found). Let's look at the update statement again:

update [products] set
 [productname][email protected], [unitprice][email protected], [discontinued][email protected]
where
 [productid][email protected]_productid and
 [productname][email protected]_productname and
 (([unitprice] is null and @original_unitprice is null) or
 ([unitprice][email protected]_unitprice)) and
 [discontinued][email protected]_discontinued

When the second 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 first browser changed chai to chai tea). So the expression "[productname][email protected]_productname" returns false, causing the update to fail.

Note:The principle of deletion is the same here.Open 2 browsers at the same time,The first is to make changes to a product,Delete the product in the second browser,Also because the original value does not match the updated value,failed to delete.

In the view of the end user (the one who failed to update), after he clicked the "Update" button, the gridview control returned to the "pre-edited" state, but the submitted changes were lost.However, there is no visual reminder that the modification failed.When a user's update fails due to a concurrent conflict,We better remind users,For example, keep the gridview control in the "edit" state. Let's see how to achieve this.

Step 3:Handling Concurrency Conflicts

Refuse Rejecting user changes due to concurrency conflicts,So it is best to prompt the user when a concurrent conflict occurs.Add a label control on the page with the id of concurrencyviolationmessage and set its text property to "you have attempted to update or delete a record that was simultaneously updated by another user. Please review the other user" s changes and then redo your update or delete ", set its cssclass attribute to" warning ", which is defined in styles.css. Finally, set the visible and enableviewstate attributes to" false ". This way the label control will not be visible,Unless some postback event occurs (we specify the visible property of the label control to true in these postback events)

Figure 8:Add a label control to the page to display reminder information

When performing update or delete operations,When the gridview data source control is updated or deleted,Only then started to execute the rowupdated and rowdeleted event handlers of the gridview control. We can calculate how many records were affected in these event handlers.If 0 records are affected, that is, the operation fails,We want to display the label control concurrencyviolation message.

Create handlers for 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 both event handlers, we must check the e.affectedrows property. If it is 0, set the visible property of the label control's concurrencyviolationmessage to true. In particular, in the rowupdated event handler,We keep the gridview control in edit mode by setting the keepineditmode property to true.In this case,The databind () method of gridview is used to display data that has been successfully updated by others in the editing state.

As shown in Figure 9, when a concurrent conflict occurs,Show prompt message

Figure 9:When a concurrent conflict occurs,Display prompt message:

to sum up:

When creating an application,When multiple people are editing the same data at the same time,Consider the issue of concurrent conflicts.By default,Asp.net data web controls and data source controls do not take concurrency control.As we saw in this chapter,It is relatively quick and easy to use open concurrency control for the sqldatasource control.By extending the where clause in update and delete statements, sqldatasource can handle most situations.But as discussed in the "Handling null values ​​properly" section,Vulnerability in handling columns containing null values.

This chapter is the end of a survey of sqldatasource.The next tutorial continues with the hierarchy and processing data with bjectdatasource.

Happy programming!

About the Author

  • Previous ThinkPHP 322 way to achieve persistent login (remember me) function
  • Next php + html5 + ajax method for uploading pictures