Update Database with current values if DBConcurrency occur in C# and VB.Net

Hi anvina,

Check this example. Now please take its reference and correct your code.

For updating conflict records, using Adapter RowUpdated event the row that is giving error fetched using the unique column and convert it to DataTable.

Thenn from the DataTable created the dynamic update query and finally update the records in the Database for that particular record.

SQL

CREATE TABLE TestDemo
(
	Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
	Name VARCHAR(50),
	Country VARCHAR(50)
)

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

SqlConnection con = new SqlConnection("Server=.;DataBase=Test;UID=sa;PWD=pass@123");
SqlDataAdapter mAdapter;
DataTable mTable;

public Form1()
{
    InitializeComponent();
    dataGridView1.DataSource = ReadRows();
}

private DataTable ReadRows()
{
    string sql = "SELECT * FROM TestDemo";
    mAdapter = new SqlDataAdapter(sql, con);
    SqlCommandBuilder builder = new SqlCommandBuilder(mAdapter);
    mAdapter.ContinueUpdateOnError = true;
    mAdapter.RowUpdated += new SqlRowUpdatedEventHandler(Adapter_RowUpdated);
    mAdapter.UpdateCommand = builder.GetUpdateCommand();
    mAdapter.DeleteCommand = builder.GetDeleteCommand();
    mAdapter.InsertCommand = builder.GetInsertCommand();
    mAdapter.SelectCommand = new SqlCommand(sql, con);
    mTable = new DataTable();
    mAdapter.Fill(mTable);
    return mTable;
}

private void Adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    if (e.Status == UpdateStatus.ErrorsOccurred && e.Errors.GetType() == typeof(DBConcurrencyException))
    {
        DataTable updatedData = e.Row.Table.AsEnumerable().Where(x => x.Field<Guid>("Id").ToString() == e.Row[0].ToString()).CopyToDataTable();
        string updateQuery = "UPDATE TestDemo SET ";
        for (int i = 1; i < updatedData.Columns.Count; i++)
        {
            updateQuery += updatedData.Columns[i].ColumnName + " = '" + updatedData.Rows[0][i] + "',";
        }
        updateQuery = updateQuery.TrimEnd(',');
        updateQuery += " WHERE Id = '" + e.Row[0].ToString() + "'";
        using (SqlConnection con = new SqlConnection("Server=.;DataBase=Test;UID=sa;PWD=pass@123"))
        {
            SqlCommand cmd = new SqlCommand(updateQuery);
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        e.Status = UpdateStatus.Continue;
    }
}

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        mAdapter.Update(mTable);
        mTable.AcceptChanges();
    }
    catch (Exception ex)
    {
    }
    finally
    {
        dataGridView1.DataSource = ReadRows();
    }
}

VB.Net

Private con As SqlConnection = New SqlConnection("Server=.;DataBase=Test;UID=sa;PWD=pass@123")
Private mAdapter As SqlDataAdapter
Private mTable As DataTable

Public Sub New()
    InitializeComponent()
    dataGridView1.DataSource = ReadRows()
End Sub

Private Function ReadRows() As DataTable
    Dim sql As String = "SELECT * FROM TestDemo"
    mAdapter = New SqlDataAdapter(sql, con)
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(mAdapter)
    mAdapter.ContinueUpdateOnError = True
    AddHandler mAdapter.RowUpdated, AddressOf Adapter_RowUpdated
    mAdapter.UpdateCommand = builder.GetUpdateCommand()
    mAdapter.DeleteCommand = builder.GetDeleteCommand()
    mAdapter.InsertCommand = builder.GetInsertCommand()
    mAdapter.SelectCommand = New SqlCommand(sql, con)
    mTable = New DataTable()
    mAdapter.Fill(mTable)
    Return mTable
End Function

Private Sub Adapter_RowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
    If e.Status = UpdateStatus.ErrorsOccurred AndAlso e.Errors.GetType() = GetType(DBConcurrencyException) Then
        Dim updatedData As DataTable = e.Row.Table.AsEnumerable().Where(Function(x) x.Field(Of Guid)("Id").ToString() = e.Row(0).ToString()).CopyToDataTable()
        Dim updateQuery As String = "UPDATE TestDemo SET "
        For i As Integer = 1 To updatedData.Columns.Count - 1
            updateQuery += updatedData.Columns(i).ColumnName & " = '" + updatedData.Rows(0)(i) & "',"
        Next
        updateQuery = updateQuery.TrimEnd(","c)
        updateQuery += " WHERE Id = '" & e.Row(0).ToString() & "'"
        Using con As SqlConnection = New SqlConnection("Server=.;DataBase=Test;UID=sa;PWD=pass@123")
            Dim cmd As SqlCommand = New SqlCommand(updateQuery)
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
        e.Status = UpdateStatus.Continue
    End If
End Sub
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Try
        mAdapter.Update(mTable)
        mTable.AcceptChanges()
    Catch ex As Exception
    Finally
        dataGridView1.DataSource = ReadRows()
    End Try
End Sub

Screenshot

As you can see the screenshot i binded the DataGridView from Database then updated the Database record and change the data in DataGridview for concurrency.

Then on button click database record got updated with the updated records.