Correct/Best practice method of handling concurrency

[dilhanmail]

I am trying to develop a VB.Net windows application for a store (POS). In order to handle concurrency issues that could arise when multiple cashiers are accessing the same records, I am researching a good method to handle the issue. After some research (Internet), I have come up with a method which I think can identify the concurrency problems (I'm only concerned with whether the record has changed since the first read, rather than how many times it has changed since the first read). However, I'm not fully sure whether this is the best method to do it. Therefore, I would appreciate if some one can advice on following code. (This is just a sample code for testing)

Imports System.Data.SqlClient

Public Class Form2
    Private oldRowVersion As Byte()

    'CODE TO GET THE EXISITING ROW VERSION AND DISPLAY DATA IN A FORM
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Try

            'MsgBox(BitConverter.ToString(CType(oldRowVersion, Byte())))
            Dim searchQuery As String = "select * from tbStudents where ID=@id"
            Using sqlConn As New SqlConnection(connString)
                Using cmd As New SqlCommand(searchQuery, sqlConn)
                    Using da As New SqlDataAdapter(cmd)
                        Using table As New DataTable
                            cmd.Parameters.Add("@id", SqlDbType.Int).Value = Trim(txtId.Text)
                            da.Fill(table)
                            If table.Rows.Count > 0 Then
                                txtName.Text = Trim(table.Rows(0)(1).ToString)
                                txtClass.Text = Trim(table.Rows(0)(2).ToString)
                                txtAddress.Text = Trim(table.Rows(0)(3).ToString)
                                txtContact.Text = Trim(table.Rows(0)(4).ToString)
                                oldRowVersion = GetRowVersion("select RowVersion from tbStudents where ID=@id")
                                MsgBox("Record found")
                            Else
                                'clear()
                                MsgBox("Record not found")
                            End If
                        End Using
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MsgBox("Button4_Click: " + Err.Description)
        End Try
    End Sub


    'CODE TO UPDATE THE RECORD ONLY IF THE ROW VERSION HASN'T CHANGED 
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            If (BitConverter.ToString(CType(GetRowVersion("select RowVersion from tbStudents where ID=@id"), Byte()))) <> (BitConverter.ToString(CType(oldRowVersion, Byte()))) Then
                MsgBox("The record has been modified by another user. Please reload the data and try again.")
            Else
                Dim updatequery As String = "update tbStudents set name=@name,class=@stclass,address=@address,contact=@contact where ID=@id"
                RunQuery(updatequery)
                MsgBox("Record Updated successfully")
            End If
        Catch ex As Exception
            MsgBox("Button2_Click: " + Err.Description)
        End Try
    End Sub


    'CODE TO DO THE CRUD OPERATIONS
    Public Sub RunQuery(ByVal query As String)
        Try
            Using sqlConn As New SqlConnection(connString)
                Using cmd As New SqlCommand(query, sqlConn)
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = Trim(txtId.Text)
                    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Trim(txtName.Text)
                    cmd.Parameters.Add("@stclass", SqlDbType.VarChar).Value = Trim(txtClass.Text)
                    cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = Trim(txtAddress.Text)
                    cmd.Parameters.Add("@contact", SqlDbType.VarChar).Value = Trim(txtContact.Text)
                    sqlConn.Open()
                    cmd.ExecuteNonQuery()
                    sqlConn.Close()
                End Using
            End Using
        Catch ex As Exception
            MsgBox("RunQuery: " + Err.Description)
        End Try
    End Sub


    'FUNCTION TO GET THE CURRENT ROW VERSION
    Public Function GetRowVersion(ByVal query As String) As Byte()
        Try
            Using sqlConn As New SqlConnection(connString)
                Using cmd As New SqlCommand(query, sqlConn)
                    Using da As New SqlDataAdapter(cmd)
                        cmd.Parameters.AddWithValue("@id", txtId.Text)
                        Dim table As New DataTable
                        da.Fill(table)
                        Dim oldRowVersion As Byte() = table.Rows(0)(0)
                        cmd.Dispose()
                        da.Dispose()
                        table.Dispose()
                        Return oldRowVersion
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MsgBox("GetRowVersion: " + Err.Description)
        End Try
    End Function

End Class

[jmcilhinney]

Optimistic concurrency is already built into ADO.NET. You should read this and see whether you still have questions:

https://learn.microsoft.com/en-us/do...ic-concurrency

[dilhanmail]

Optimistic concurrency is already built into ADO.NET. You should read this and see whether you still have questions:

https://learn.microsoft.com/en-us/do...ic-concurrency

Thank you