(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

VB.NET	Implement Pessimistic Concurrency With a DataSet
Listing A	This code implements pessimistic concurrency, even though using a DataSet and DataAdapter doesn't lock rows in the database. Using a transaction at the repeatable read or serializable isolation level and keeping the underlying connection open in the SqlDataAdapter locks the rows for you. This type of concurrency management can slow down a multiuser system and is not recommended.

' Pessimistic locking - SQL Server
Public Sub DoPessimistic()
	Dim ds As New DataSet()

	Dim connstr as String
	connstr = "server=mysvr;database=mydb;"
	connstr += "integrated security=sspi"
	Dim conn As NewSqlConnection(connstr)

	Dim tx As SqlTransaction
	Dim cmd As New SqlCommand( _
		"select * from simple", conn, tx)
	Dim da As New SqlDataAdapter(cmd)

	Try
		conn.Open()
		tx = conn.BeginTransaction( _
			IsolationLevel.RepeatableRead)

		da.Fill(ds, "simple")
		' rows locked here

		' update one row
		ds.Tables(0).Rows(1)(1) = "Fred"

		Dim bld As New SqlCommandBuilder(da)
		da.Update(ds, "simple")
		tx.Commit()
	Catch e As Exception
		Console.WriteLine(e.Message)
		tx.Rollback()
	Finally
		' rows unlocked here
		da.Dispose()
		conn.Close()
	End Try
End Sub



VB.NET	Use a Timestamp Column to Implement Optimistic Concurrency
Listing 1	Using a timestamp column lets you use a simpler SQL UPDATE statement than comparing all the columns to their original values, This code implements a timestamp-based update.


'Using a timestamp to construct an update command
Sub UpdateWithTimestamp()
	Dim connstr as String
	connstr  = "server=mysvr;database=mydb;"
	connstr += "integrated security=sspi"

	Dim da As New SqlDataAdapter( _
		"select * from simple_with_ts", conn)
	Dim ds As New DataSet()

	da.Fill(ds, "simple_with_ts")
	ds.Tables(0).Rows(0)(1) = "newname"
	ds.Tables(0).Rows(0)(2) = 50

	'  generate update command for all columns
	'  check concurency by timestamp
	Dim updatecmd = New SqlCommand()
	updatecmd.Connection = _
		da.SelectCommand.Connection

	updatecmd.CommandText = _
		"update simple_with_ts set " & _
		"name = @newname, " & _
		"age = @newage " & _
		"where simpleid = @oldid and ts = @oldts"

	'  add parms
	Dim parms(4) As SqlParameter
	parms(0) = New SqlParameter( _
		"@newname", SqlDbType.VarChar, 20)
	parms(1) = New SqlParameter( _
		"@newage", SqlDbType.Int)
	parms(2) = New SqlParameter( _
		"@oldid", SqlDbType.Int)
	parms(3) = New SqlParameter( _
		"@oldts", SqlDbType.Binary)

	Dim i As Integer
	For i = 0 To 3
		updatecmd.Parameters.Add(parms(i))
	Next i

	For i = 0 To 1
		parms(i).SourceVersion = _
			DataRowVersion.Current
		parms(i).SourceColumn = _
			ds.Tables(0).Columns(_ 
			i + 1).ColumnName()
	Next i

	' we want original column for key and 
	' timestamp
	parms(2).SourceVersion = _
		DataRowVersion.Original
	parms(2).SourceColumn = "simpleid"
	parms(3).SourceVersion = _
		DataRowVersion.Original
	parms(3).SourceColumn = "ts"

	da.UpdateCommand = updatecmd
	da.Update(ds, "simple_with_ts")

End Sub


