HOW TO: Use a TimeStamp Column to Detect Update Collisions in ADO.NET with Visual Basic .NET (309489)

SUMMARY

This step-by-step article uses a Microsoft SQL Server TimeStamp field to detect update collisions in order to reduce the amount of data sent to the server. By default, the CommandBuilder object (both SQLClient and OLEDB) builds collision detection based on all field values. By using a custom UpdateCommand, we can specify only the columns that we wish to use.

back to the top

Create the SQL Server Tables

Use the following script to create the test table and insert some test records:

CREATE TABLE [tblQ309489] (
[ID] int IDENTITY (1,1) NOT NULL, 
[CharData] varchar (10) NOT NULL,
[TimeStampCol] timestamp NULL)
ALTER TABLE [tblQ309489] WITH NOCHECK ADD CONSTRAINT [PK_tblQ309489] PRIMARY KEY ([ID])
INSERT INTO tblQ309489 ([CharData]) VALUES ('AAA')
INSERT INTO tblQ309489 ([CharData]) VALUES ('BBB')
INSERT INTO tblQ309489 ([CharData]) VALUES ('CCC')
				

back to the top

Create the Visual Basic .NET Application

1. Create a new Visual Basic .NET Windows Application.
2. Add the following controls to the form from the Windows Forms tab in the toolbox:
   • Button: cmdUpdate.
   • TextBoxes (3): txtID, txtCharData, and txtTimeStamp. (Set txtID and txtTimeStamp to ReadOnly to show that these fields cannot be updated.)
   • DataGrid.
3. Double-click the form to go to the code window and then add the following code to the very top (above the "Public Class Form1" statement):

   Imports System.Data.SqlClient
   					

4. Add the following declarations to the class (under "Inherits System.Windows.Forms.Form"):

   Private con As New SqlConnection()
   Private da As SqlDataAdapter
   Private ds As New DataSet()
   Private daUpdateCommand As SqlCommand
   Private dt As DataTable
   					

5. Add the following code to the Form_Load event:

   con.ConnectionString = "Server=(local);Database=pubs;Trusted_Connection=yes;"
   con.Open()
   da = New SqlDataAdapter("SELECT * FROM tblQ309489 ORDER BY ID", con)
   daUpdateCommand = New SqlCommand("UPDATE tblQ309489 SET CharData = @pCharData WHERE TimeStampCol = @pTimeStamp", _
       da.SelectCommand.Connection)
   With daUpdateCommand
       'This is the field that we are updating.
       .Parameters.Add(New SqlParameter("@pCharData", SqlDbType.VarChar, 10))
       .Parameters("@pCharData").SourceVersion = DataRowVersion.Current
       .Parameters("@pCharData").SourceColumn = "CharData"
   
       'We will use the TimeStamp to locate our row.
       .Parameters.Add(New SqlParameter("@pTimeStamp", SqlDbType.Binary))
       .Parameters("@pTimeStamp").SourceVersion=DataRowVersion.Original
       .Parameters("@pTimeStamp").SourceColumn = "TimeStampCol"
   End With
   da.UpdateCommand = daUpdateCommand
   'Fetch the data.
   da.FillSchema(ds, SchemaType.Source, "tblQ309489")
   da.Fill(ds, "tblQ309489")
   dt=ds.Tables("tblQ309489")
   'Show the data in the text boxes.
   With dt.Rows(0)
       Me.txtID.Text = .Item(0)
       Me.txtCharData.Text =.Item(1)
       Me.txtTimeStamp.Text= .Item(2).ToString
   End With
   					

6. Modify the ConnectionString property (the first line of code in step 5) to correspond to your SQL Server computer connection information. Make sure that you are connecting to the database where you ran the SQL script to create the test table.
7. Add the following code to the cmdUpdate_Click event:

   dt.Rows(0)("CharData") = Me.txtCharData.Text
   Try
       da.Update(dt)
       MessageBox.Show("Update was successful")
   Catch dbException As System.Data.DBConcurrencyException
       MessageBox.Show(dbException.Message.ToString)
       Dim dsModified As DataSet
       DsModified = ds.GetChanges(DataRowState.Modified)
       DataGrid1.DataSource = dsModified.Tables(0)
       DataGrid1.CaptionText = "Modified Rows"
       ds.RejectChanges()
   Catch genException As Exception
       MessageBox.Show(genException.Message & vbcrlf & genException.GetType.ToString)
   End Try
   con.Close
   					

back to the top

Test the Visual Basic .NET Application

1. To run the application (the data is fetched in the form_load event), click Start on the Debug menu.
2. Go to Query Analyzer (or Enterprise Manager) and update the CharData field in record #1 to a new value.
3. Return to the application and change CharData to a different value.
4. Click the button on the form and note the exception.

back to the top

Additional Information

A TimeStamp field in SQL Server is a binary value that is updated every time that row is updated. This is guaranteed to be a unique value database-wide. The value is not related to the date and time that the changes occurred. For further information on TimeStamp fields, see the "Using Special Data" topic in SQL Server Books Online.

back to the top