Concurrency control in ADO.NET.

Data Concurrency in ADO.NET:

Concurrency is a critical in any multi-user environment where data is to be updated. In classic client /server architectures, most programming languages and data base servers support multiple types of concurrency models such as optimistic ,pessimistic, and every thing in b/w (different isolation levels). but we use the windows application to show that problem and it's solving.

Optimistic concurrency control:

In a multi-user environment, optimistic concurrency. The Dataset object is designed to encourage the use of optimistic concurrency for long-running activities, such as remoting data and interacting with data.

In this Example  Changes the value of a column during time interval that is:

 Client first read data that is given bellow
 

 Client second change the no of product orders. now the product order is 22 then:

Client third change the no of product orders. now the product order is 11 then:

At this point, client first encounters an optimistic concurrency violation because the value in the database ("11 ") no longer matches the original value that Cient first was expecting ("12"). The concurrency violation simply lets you know that the update failed. The decision now needs to be made whether to overwrite the changes supplied by Client second with the changes supplied by Client first, or to cancel the changes by Client first.

Coding of optimistic concurrency control :

Step1: Connection coding

Imports System.Data.OleDb
Module Module1
    Public ds As DataSet
    Public Da As OleDbDataAdapter 
    Public constring As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\My_db12.accdb")
End Module

Step2: Form coding

Imports System.Data.OleDb    
Public Class Form1  
    Dim SqlString As String    
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim update As New update
        update.Show()  
    End Sub 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try           
            constring.Open()
            SqlString = "insert into info22 values("
            SqlString += """" & TextBox1.Text & """"
            SqlString += ","
            SqlString += """" & TextBox2.Text & """"
            SqlString += ","
            SqlString += """" & TextBox3.Text & """"
            SqlString += ")" 
            Dim Da As New OleDbDataAdapter()
            Da.InsertCommand = New OleDbCommand(SqlString, constring)
            Da.InsertCommand.ExecuteNonQuery()
            ds = New DataSet 
            Da.Fill(ds, "info22") 
            MessageBox.Show("data inserted")  
        Catch ex As Exception
            MessageBox.Show(ex.Message)  
        End Try             
End Sub 
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim Display As New Display
        Display.Show() 
    End Sub
End Class

Step3: coding for updating:

 Imports System.Data.OleDb 
Public Class update    
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim Display As New Display
        Display.Show() 
    End Sub 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
        Dim SqlString As String
        If RadioButton2.Checked Then
            Try
                constring.Open()  
                ds = New DataSet
                SqlString = "update info22 set product_order=TextBox2.Text where customer_name='ram'"
                Dim Da As New OleDbDataAdapter()
                Da = New OleDbDataAdapter(SqlString, constring)
                Da.Update(ds, "info22") 
            Catch ex As Exception
                MessageBox.Show(ex.Message)   
            End Try 
        ElseIf RadioButton1.Checked Then 
            constring.Open()  
            ds = New DataSet
            SqlString = "update info22 set product_order=TextBox1.Text where customer_name='ram'"
            Dim Da As New OleDbDataAdapter()
            Da = New OleDbDataAdapter(SqlString, constring)
            Da.Update(ds, "info22")  
        ElseIf RadioButton3.Checked Then
            constring.Open()  
            ds = New DataSet
            SqlString = "update info22 set product_order=TextBox3.Text where customer_name='ram'"
            Dim Da As New OleDbDataAdapter()
            Da = New OleDbDataAdapter(SqlString, constring)
            Da.Update(ds, "info22")  
        End If
    End Sub
End Class

Step4: Coding for display

Imports System.Data.OleDb 
Public Class Display
    Dim SqlString As String  
    Private Sub Display_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            constring.Open()
            ds = New DataSet
            SqlString = "select * from info22"
            Da = New OleDbDataAdapter(SqlString, constring)
            Da.Fill(ds, "info22")
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try    
    End Sub
End Class

Output: