HOW TO: Use a TimeStamp Column to Detect Update Collisions in ADO.NET with Visual C# .NET (317095)

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 that is sent to the server. By default, the CommandBuilder object (both SqlClientCommandBuilder and OleDbCommandBuilder) builds collision detection based on all field values. By using a custom UpdateCommand, you can specify only the columns that you want to use.

back to the top

Create the SQL Server Tables

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

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

back to the top

Create the Visual C# .NET Application

1. Create a new Visual C# .NET Windows Application.
2. Add the following controls to the form from the Windows Forms tab in the toolbox:
   • Add one Button control as cmdUpdate.
   • Add three TextBox controls as txtID, txtCharData, and txtTimeStamp. (Set txtID and txtTimeStamp to ReadOnly to show that these fields cannot be updated.)
   • Add one DataGrid control.
3. Use the using statement on the System and System.Data.SqlClient namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:

   using System.Data;
   using System.Data.SqlClient;
   					

4. Add the following declarations to the class:

   SqlConnection con = new SqlConnection();
   SqlDataAdapter da;
   DataSet ds = new DataSet();
   SqlCommand daUpdateCommand;
   DataTable dt;
   					

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 tblQ317095 ORDER BY ID", con);
   daUpdateCommand = new SqlCommand("UPDATE tblQ317095 SET CharData = @pCharData WHERE TimeStampCol = @pTimeStamp",da.SelectCommand.Connection);
   
   //This is the field that you are updating.
   daUpdateCommand.Parameters.Add(new SqlParameter("@pCharData", SqlDbType.VarChar, 10));
   daUpdateCommand.Parameters["@pCharData"].SourceVersion = DataRowVersion.Current;
   daUpdateCommand.Parameters["@pCharData"].SourceColumn = "CharData";
   
   //Use the TimeStamp to locate your row.
   daUpdateCommand.Parameters.Add(new SqlParameter("@pTimeStamp", SqlDbType.Binary));
   daUpdateCommand.Parameters["@pTimeStamp"].SourceVersion=DataRowVersion.Original;
   daUpdateCommand.Parameters["@pTimeStamp"].SourceColumn = "TimeStampCol";
   
   da.UpdateCommand = daUpdateCommand;
   //Fetch the data.
   da.FillSchema(ds, SchemaType.Source, "tblQ317095");
   da.Fill(ds, "tblQ317095");
   dt=ds.Tables["tblQ317095"];
   //Show the data in the textboxes.
   
   txtID.Text = dt.Rows[0][0].ToString() ;
   txtCharData.Text =dt.Rows[0][1].ToString() ;
   txtTimeStamp.Text= dt.Rows[0][2].ToString();
   					

6. Modify the ConnectionString property (the first line of code in step 5) to correspond to your SQL Server 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"] = txtCharData.Text;
   try
   {
   	da.Update(dt);
   	MessageBox.Show("Update was successful");
   }
   catch (System.Data.DBConcurrencyException dbException)
   {
   	MessageBox.Show(dbException.Message.ToString());
   	DataSet DsModified;
   	DsModified = ds.GetChanges(DataRowState.Modified);
   	dataGrid1.DataSource = DsModified.Tables[0];
   	dataGrid1.CaptionText = "Modified Rows";
   	ds.RejectChanges();
   }
   catch (Exception genException)
   {
   	MessageBox.Show(genException.Message);
   }
   		
   con.Close();			
   					

back to the top

Test the Visual C# .NET Application

1. To run the application (the data is fetched in the Form_Load event), click Start on the Debug menu.
2. In Query Analyzer (or Enterprise Manager), 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. Notice that you receive an 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