I have searched for this over and over and cannot find an answer. I want to read and lock a record in sqlserver, process the data in some way, then write the record back to the database and release the lock.
This is an example of the type of code I'm trying to use:
Imports System.Data.SqlClient
Public Module TestDB
Private Sub DoThis()
ProcessData(1, 1)
End Sub
Private Sub ProcessData(ID As Integer, Quantity As Integer)
Dim DBMS As New DB
Dim MyRow As DataRow = Nothing
DBMS.OpenDatabase()
MyRow = DBMS.GetRecord(ID) 'lock this record so nobody else can read it, but only the record, not the table
If MyRow IsNot Nothing Then
'----------
'do some processing
MyRow("Quantity") = MyRow("Quantity") + Quantity
'----------
DBMS.UpdateRecord(MyRow) 'unlock this record people can read it again
End If
DBMS.CloseDatabase()
End Sub
End Module
Public Class DB
Public m_oConnection As SqlConnection
Public m_oTransaction As SqlTransaction
Public m_oCommand As SqlCommand
Public m_oDataAdapter As SqlDataAdapter
Public m_oDataTable As DataTable
Public Shared m_sConnectionString As String = "Server=SQL01; Database=MyDB; uid=me; pwd=secret;"
Public Sub OpenDatabase()
m_oConnection = New SqlConnection
m_oConnection.ConnectionString = m_sConnectionString
m_oConnection.Open()
End Sub
Public Sub CloseDatabase()
m_oConnection.Close()
End Sub
Public Function GetRecord(RecordID As Integer) As DataRow
Dim Result As DataRow = Nothing
Dim SQL As String = ""
SQL &= "SELECT * FROM TempStock WHERE StockID = " & RecordID
m_oDataAdapter = New SqlDataAdapter
m_oDataTable = New DataTable
m_oCommand = New SqlCommand(SQL, m_oConnection)
m_oDataAdapter.SelectCommand = m_oCommand
m_oDataAdapter.Fill(m_oDataTable)
Dim iRows As Integer = m_oDataTable.Rows.Count
If iRows > 0 Then
Result = m_oDataTable.Rows(0)
End If
Return Result
End Function
Public Function UpdateRecord(Row As DataRow) As Integer
Dim Result As Integer = 0
Dim SQL As String = ""
SQL &= "UPDATE TempStock "
SQL &= "SET Quantity = " & Row("Quantity")
SQL &= "WHERE StockID = " & Row("StockID")
m_oCommand = New SqlCommand(SQL, m_oConnection)
Dim iRows As Integer = m_oCommand.ExecuteNonQuery()
Return Result
End Function
End Class
Very simple idea to my mind, and a pretty standard thing to write in a multiuser application - I don't want 2 users to read the same record and try to update it. I don't want to get into timestamps etc, and so far, using transactions I just cannot get it to work - unless I'm totally misunderstanding them.
Can anyone help? (my example is in VB, but C# answers will be just as helpful).
