Share via

  • Article

Exercise 4: Updating Data

In this exercise, you will learn how to create an update method, how to bind it to the object data source, and modify the UI to change existing products. Before we begin, let us talk about some special circumstances that can arise in applications that support the ability for multiple users to change the same data.

Updates present an interesting challenge related to handling conflicts when users can change the same data. The classic scenario is the following:

  • User A reads record X from the database with the intent to change it.
  • User B also reads record X with the intent to change it.
  • User B updates record X, making User A’s copy out of date.
  • User A updates record X, resulting in a conflict.

There are three basic ways to handle conflicts:

  1. Ignore conflicts and permit lost updates.
  2. Use pessimistic concurrency which employs locking and transactions to avoid conflicts in the first place.
  3. Use optimistic concurrency to detect conflicts by comparing values to avoid lost updates and forcing the loser to re-read the source data and try their update again.

Each of these approaches has advantages and disadvantages. Permitting lost updates will probably not be an option for most serious applications. Pessimistic concurrency is fairly easy to implement but may require holding locks that require user intervention to release, which is contrary to a best practice we stated earlier in the Hands-on Lab. So that leaves optimistic concurrency.

Note:
Best Practice: Use optimistic concurrency to detect conflicts and prevent lost updates in applications that permit users to change the same data.

Optimistic concurrency requires the application to see if data has changed since it was read from the database before it is updated. There are many ways of implementing optimistic concurrency, including comparing the current values in the database with the ones that were originally read or using some kind of metadata like a row version or time stamp. If the data has changed the application usually reports an error to the loser, then re-reads the data and asks them to resubmit the request with fresh data.

Task 1 – Adding the Update Method to the Data Access Class

  1. In the File menu, choose Open | Project/Solution. In the Open Project dialog, browse to Ex4-UpdatingData\Begin in the Source folder of this lab, select Begin.sln and click Open. Alternatively, you may continue working with the solution obtained after completing the previous exercise.
  2. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.

  3. Create a new public static method to update an existing product. To do this, paste the following code (shown in bold) inside the ProductDataAccess class.

    (Code Snippet – First SQL ASP.NET Application Lab – Ex04 – UpdateProduct method)

    C#

    public static void UpdateProduct(int productId, string name, string originalName, decimal listPrice, decimal originalListPrice, string size, string originalSize) { var rows = 0; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString)) { connection.Open(); var commandText = "UPDATE Production.Product " + "SET Name=@Name, ListPrice=@ListPrice, Size=@Size " + "WHERE ProductID=@ProductID " + "AND Name=@OriginalName " + "AND ListPrice=@OriginalListPrice " + "AND " + "( " + " (Size IS NULL AND @OriginalSize IS NULL) " + " OR " + " (Size=@OriginalSize) " + "); "; var command = new SqlCommand(commandText, connection); command.Parameters.AddWithValue("@ProductID", productId); command.Parameters.AddWithValue("@Name", name); command.Parameters.AddWithValue("@OriginalName", originalName); command.Parameters.AddWithValue("@ListPrice", listPrice); command.Parameters.AddWithValue("@OriginalListPrice", originalListPrice); if (size == null) command.Parameters.AddWithValue("@Size", DBNull.Value); else command.Parameters.AddWithValue("@Size", size); if (originalSize == null) command.Parameters.AddWithValue("@OriginalSize", DBNull.Value); else command.Parameters.AddWithValue("@OriginalSize", originalSize); rows = command.ExecuteNonQuery(); } if (rows < 1) MessageBox.Show("Conflict detected. Please try again."); }
    FakePre-090ed9b35ce5488c8a666f1fcaf2ad44-f5e627424019465da34c7be693c6769aFakePre-0cf7925e70784a92b2733cc15047c602-87a867b08e734c959d99f76eba27310bFakePre-94f32b6e1f5e4c33b46ed42dc1d3d148-0164b93733fb4cf780c919e4c1f3468eFakePre-e58af1e498c147e7a9779827d7d1bba7-1b964c307c3a45c6b996c9c20f663e2eFakePre-2f33d965300740699485dd0868a268df-9e7286b5aae449afae776d339139f12dFakePre-d0187b245a0e4404932da0e36e0b6513-c60d93b060004a608dbee869effa1bcdFakePre-4a6428b60b00493497fb14c18d33d50d-f452b47998784ea0a12db29b8783b5a2

    Note:
    The WHERE clause of the UPDATE statement uses the original values to detect conflicts in the database. If no rows are affected by the update, that means that the original values changed or the row was deleted and there was a conflict. Take notice of the way the Size column must be handled because it supports NULL.

  4. Add the MessageBox.cs file to the solution. To do this, right-click the ListProducts project, point to Add, and click Existing Item. Browse to Assets in the Source folder of the lab, select the MessageBox.cs file and click Add.

    Note:
    The MessageBox is a helper class that allows displaying a message when the page is loaded in the browser (by writing a JavaScript alert in the Response).

  5. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI

In this task you will learn how to add the update form and bind it to the method created in the previous task.

  1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom left corner of the designer.
  2. Click the ObjectDataSource control in the designer surface to select it.
  3. Click the Smart Tag () to expand the ObjectDataSource tasks, and then click Configure Data Source.

  4. Click Next in the Choose a Business Object page. Click the Update tab and choose the UpdateProduct method and click Next. In the following window, click Finish.

    Figure 25

    Choosing the update method

  5. If the Refresh Fields and Keys for 'InsertProductsDetailsView' dialog displays, click No.

    Figure 26

    Refresh Fields and Keys dialog box

  6. A dialog asking whether you want to reconfigure ListView may show. Click No.

    Figure 27

    Dialog about reconfiguring ListView

  7. Open the Default.aspx page in Source view, locate the opening ListView tag, and add the DatakeyNames property as shown in the following code.

    HTML

    <asp:ListView ID="ProductsListView" runat="server" DataSourceID="ProductsDataSource" DataKeyNames="ProductId">

    The DataKeyNames property sets the names of the primary key fields in the data source bound to the ListView.

  8. Add the EditItemTemplate template to the ListView. To do this, add the following bolded code below the <LayoutTemplate> closing tag.

    HTML

    <asp:ListView ID="ProductsDataList" runat="server" DataSourceID="ProductsDataSource"
    FakePre-04d7179f8cd34498830fece32df7c88a-fac796bd07884f0388d566fb316eb1c9FakePre-5d8c1ecab7b747b98f931f0183ec097c-3a8e4785cfa448bf999cafbe99e44fd1 <EditItemTemplate> <div> <h3> <b>Name:&nbsp;</b> <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' /> <asp:TextBox ID="OriginalNameTextBox" runat="server" Visible="False" ReadOnly="True" Text='<%# Bind("OriginalName") %>' /> </h3> <p> <b>Product ID:&nbsp;</b> <%# Eval("ProductId")%><br /> <b>List price:&nbsp;</b> <asp:TextBox ID="ListPriceTextBox" runat="server" Text='<%# Bind("ListPrice") %>' /> <asp:TextBox ID="OriginalListPriceTextBox" runat="server" Visible="False" ReadOnly="True" Text='<%# Bind("OriginalListPrice") %>' /> <br /> <b>Size:&nbsp;</b> <asp:TextBox ID="SizeTextBox" runat="server" Text='<%# Bind("Size") %>' /> <asp:TextBox ID="OriginalSizeTextBox" runat="server" Visible="False" ReadOnly="True" Text='<%# Bind("OriginalSize") %>' /> </p> <p style="clear: both"> * Size field has a max length of 5</p> <img src="<%# "ProductImage.ashx?ProductId=" + Eval("ProductId") %>" /> <br style="clear: both" /> <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Save</asp:LinkButton>&nbsp;|&nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton> </div> </EditItemTemplate>FakePre-868db2f7ba4145e5943fa55a716f8bd7-99bce9546c6c44c9945b011455cb2588FakePre-bf1cc40461a64042abc3d87fa42072fb-cb348465a3774a21ac2e901153e33658

    The preceding code sets the template for a product when it is in edit mode.

    Note:
    the originalName, originalPrice and originalSize parameters are bound as hidden fields so their values are accessible from the ASP.NET ViewState. This makes it possible to check the original values to detect conflicts in an update operation.

  9. Add an Edit button to the ItemTemplate for entering edition mode. To do this, add the following bolded code inside the ItemTemplate tags, at the bottom of the <div> element.

    HTML

    <ItemTemplate>
    FakePre-2a81d8babdd144d78c01705ca31b8aed-ffd833a4db5a4a5cb14111347b5445e1FakePre-9ff30561af1c4eeda574dffce6d25c88-04c37ed287b541ef9aede57bba42f67b <br style="clear: both" /> <asp:LinkButton runat="server" CommandName="Edit">Edit</asp:LinkButton>FakePre-5f163d60c0fb4243a03a837d1522a972-65b097c92ee54244ae647c768597c695FakePre-56487a767c3f4f3bbe0d5711d75bebd4-f2dfccd7957143e3a1dbac9cca3c2a8f

  10. Save the changes and close the files.

Exercise 4: Verification

In order to verify that you have correctly performed the steps for exercise four, proceed as follows.

  1. Press CTRL+F5 to run the solution.

    Figure 28

    Listing the products

  2. Click Edit, in the product inserted in the previous exercise, to update it.

    Figure 29

    Updating a product

  3. Update the fields with the following data:
    • Name: XL Mountain Bike
    • List Price: 4000

  4. Click Save to update the products. The product listing will be reloaded and the updated product will be shown.

    Figure 30

    Updating the products

    Note:
    Next we will simulate a conflict by updating the same row from a different program.

  5. Click Edit on the same product edited in the previous step.
  6. Open the SQL Server Object Explorer from View | SQL Server Object Explorer
  7. Right click on the SQL Server item, and select Add SQL Server…
    Figure 1

    Adding a new SQL Server

  8. In the Connect to Server dialog, establish a new connection using the following values:
    • Server Type: Database Engine
    • Server Name: SqlServerTrainingKitAlias
    • Authentication: Windows Authentication
  9. In SQL ServerObject Explorer, go to Databases | AdventureWorks2012 and right click on it, and select New Query.
    Figure 2

    Creating a New Query

  10. Paste in the following query and press CTRL+Q to execute it:

    Transact-SQL

    UPDATE Production.Product SET ListPrice = 6000 WHERE Name = 'XL Mountain Bike';

    This query should generate the following output: (1 row(s) affected).

  11. Select File | Close and click No when asked about saving changes.
  12. Update the fields with the following data.
    • List Price: 5000

  13. Click Save. This will result in a conflict message being displayed.

    Figure 31

    Message shown when a conflict is detected

  14. Click OK.
  15. Finally, close the browser.

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents