Walkthrough: Using a Timestamp with the LinqDataSource Control to Check Data Integrity

In this walkthrough, you will learn how to use a timestamp to check for data conflicts when you update data by using a LinqDataSource control. The LinqDataSource control stores the timestamp value in the Web page. When the user updates or deletes data, the LinqDataSource control checks the timestamp value against the current timestamp value in the database. If the LinqDataSource control detects that the value in the timestamp column has changed, the control does not update or delete the record. In that case, the record has been changed by another process. Instead, the LinqDataSource control raises an exception that indicates that the record has changed.

A timestamp column is automatically updated by SQL Server every time that the record is modified. For more information, see Timestamp (Transact-SQL). In the entity class, a timestamp column is marked by having the IsVersion property set to true.

If you do not use a timestamp column, the LinqDataSource control checks data concurrency by storing values in the Web page. Concurrency values consist of columns that are used as the primary key in the table, or that are marked with UpdateCheck.Always or UpdateCheck.WhenUpdated in the UpdateCheck property of the ColumnAttribute attribute. LINQ to SQL checks those values against the database before it updates or deletes the data. This approach can create a large Web page if the data record contains many columns or large column values. It can also represent a security risk if the record contains data that you do not want to expose in the page.

You will use the Object Relational Designer to create a class that represents the database table. The LinqDataSource control will interact with this generated class to retrieve, update, insert, and delete the data.

Prerequisites

To implement the procedures in your own development environment you need:

• Visual Web Developer Express or Visual Studio 2008.

• SQL Server Express Edition installed on your computer. If you have SQL Server installed, you can use that instead, but you must make small adjustments to some of the procedures.

• An ASP.NET Web site.

Creating a Database Table

To perform the steps in this walkthrough, you must have a database table that contains a timestamp column. If you do not already have a table like that, you can create one by following the steps in the following procedure. If you use an existing table, the steps in some of the procedures will not match the database exactly. However, the concepts illustrated in the walkthrough will be the same.

To create a database table with a timestamp column

1. If the Web site does not already have an App_Data folder, in Solution Explorer, right-click the project, click Add ASP.NET Folder, and then click App_Data.

2. Right click the App_Data folder and then click Add New Item.

3. Under Installed Templates, select SQL Database, change the file name to Reviews.mdf, and then click Add.

4. In Server Explorer, open the Reviews.mdf node and then right-click the Tables folder.

5. Click Add New Table.

6. Create the following columns in the table:

   Column Name	Data Type	Properties
   BookID	int	IsIdentity = Yes Not null Primary Key
   Title	nvarchar(50)
   Author	nvarchar(50)
   RecommendToBookGroup	bit	Not null
   Review	nvarchar(1000)
   Timestamp	timestamp	Not null

   

7. Save the table and name it BookReviews.

8. Add several records to the BookReviews table with sample data.

   In Server Explorer, right-click the BookReviews table and click Show Table Data. You do not have to specify a value for BookID or Timestamp because those values are generated by the database.

Creating Classes to Represent Database Entities

To work with the LinqDataSource control, you work with classes that represent database entities. You can use a tool in Visual Web Developer Express or Visual Studio 2008 to create these classes.

To create a class for the BookReviews table

1. If the Web site does not already have an App_Code folder, in Solution Explorer, right-click the project, click to Add ASP.NET Folder, and then click App_Code.

2. Right-click the App_Code folder and then click Add New Item.

3. Under Installed templates, select Linq to SQL Classes, rename the file Reviews.dbml, and then click Add.

   The Object Relational Designer window is displayed.

4. In Server Explorer, drag the BookReviews table into the Object Relational Designer window.

   The BookReviews table and its columns are represented as an entity named BookReview in the designer window.

   

5. Save the Reviews.dbml file.

6. In Solution Explorer, open the Reviews.designer.cs or Reviews.designer.vb file.

   The entity now has classes named ReviewsDataContext and BookReview. The column attributes for all the columns are marked with UpdateCheck=UpdateCheck.Never. However, the column attribute for the timestamp column is marked with IsVersion=true.

7. Close the Reviews.dbml file.

Creating and Configuring a LinqDataSource Control

You now have a database table and classes that represent database entities. You can use a LinqDataSource control on an ASP.NET Web page to manage data to display and update.

To create and configure a LinqDataSource control

1. In Visual Studio, create a new ASP.NET Web page and switch to Source view.

2. From the Data tab of the Toolbox, drag a LinqDataSource control and drop it inside the form element on the Web page.

   You can leave the ID property as LinqDataSource1.

3. Set the ContextTypeName property to ReviewsDataContext.

4. Set the TableName property to BookReviews.

5. Set the EnableUpdate and EnableDelete properties to true.

   The following example shows the declarative code for the LinqDataSource control.

   <asp:LinqDataSource 
     ContextTypeName="ReviewsDataContext" 
     TableName="BookReviews" 
     EnableUpdate="true" 
     EnableDelete="true" 
     ID="LinqDataSource1" 
     runat="server">
   </asp:LinqDataSource>
   

Adding a Control to Display and Update Data

You can now add a DetailsView control and bind it to the LinqDataSource control. The DetailsView control enables users to display and update data that is managed by the LinqDataSource control.

To bind a DetailsView control to the data from the LinqDataSource control

1. In the Data tab of the Toolbox, double-click the DetailsView control to add it to the page.

   You can leave the ID property as DetailsView1.

2. Set the DataSourceID property to LinqDataSource1.

3. Set the DataKeyNames property to BookID.

   You must set the DataKeyNames property if you want to use the DetailsView control to update or delete data.

4. Set the AllowPaging property to true.

5. Set the AutoGenerateEditButton and AutoGenerateDeleteButton properties to true.

   The following example shows the declarative markup.

   <asp:DetailsView 
     DataSourceID="LinqDataSource1" 
     DataKeyNames="BookID" 
     AutoGenerateEditButton="true"
     AutoGenerateDeleteButton="true"
     AllowPaging="true" 
     ID="DetailsView1" 
     runat="server">
   </asp:DetailsView>
   

   In the BookReviews database, the BookID and Timestamp columns are automatically set by the database. Users cannot change their values.

6. Save your changes.

7. Press CTRL+F5 to view the page in a browser.

   The DetailsView control displays the columns for the current record from the BookReviews table.

8. Close the browser.

Checking for Data Conflicts

To see how the timestamp column prevents updates when the data has changed, you can perform a test. You will select a record to update in a Web page but change that same record outside the Web page. When you submit the update through the Web page, the LinqDataSource control will block the update.

To test data integrity

1. Press CTRL+F5 to view the page in a browser.

2. Select a record and then click the Edit link. Do not update the record yet.

3. In Server Explorer, right-click the BookReviews table and click Show Table Data.

4. Change a value in the same record that you opened in the Web browser.

5. Close the window to write the change to the database.

   When the change is saved, the timestamp column for the record is updated automatically by SQL Server.

6. In the Web browser, change a value in the record to update.

7. Click the Update link.

   You will see an error message that states that the row has changed. The timestamp saved in the page does not match the timestamp of the record in the database.

   If you want to perform an action when this error is displayed, you can create a handler for the Updating event.

Next Steps

In this walkthrough, you have seen how to optimize data integrity checks when you use the LinqDataSource control. You can take additional advantage of the capabilities of the LinqDataSource control in the following ways:

• Create an event handler for handling the exception that is raised when the data has changed. For more information, see the Updating or Deleting events.

• Filter which data records are returned. When you do not specify a value for the Select property, the LinqDataSource control retrieves all the columns in the database table. You might want to select a subset of columns when you are not displaying all the columns in the DetailsView control. For more information, see Walkthrough: Selecting and Filtering a Subset of Data with the LinqDataSource and GridView Controls.

• Group data and aggregate values, such as finding the sum or average of column values. For more information, see How to: Group and Aggregate Data Using the LinqDataSource Control.

See Also

Concepts

LinqDataSource Web Server Control Overview

Reference

LinqDataSource