Concurrency

Hi all, well im at the painful stage of moving to vb.NET from VB6 - and a little confused!!
What I want to know is how do I handle concurrency with ADO.NET. With ADO I used optimistic locking, if I got an error
from SQL Server then I knew that I had a lock etc, now I read that with ADO.NET we get a DataSet thats disconnected.
What I need to know is how does ADO.NET handle concurrency issues, ie, what when i go to write my disconnected
dataset to the database and I find someone else has changed the same fields while I was away with the Dataset??

LVL 1
ProgramVBAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
AlexFMConnect With a Mentor Commented:
Sorry, this is exact link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconOptimisticConcurrency.asp

This article contains both VB and C# code.

Some explanations.
DataSet is filled using SQL command:
SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID

Standard update command in this case is:
UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) WHERE CustomerID = @oldCustomerID

However, this update query doesn't resolve concurrency. To do this we need old company name which is kept from the time when we fill dataset. If it is changed, other user changed the record:

UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName


' Modify the DataSet contents.
This line is placeholder for any code which modifies dataset.

AddHandler custDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
This line subscribes to SqlRowUpdated event raised for each row updated in dataset. Inside of OnRowUpdated function code tests RecordsAffected value. It should be 1 in normal case. If it is 0, record is deleted or changed by other user from the time dataset was filled.
0
 
AlexFMCommented:
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

 SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn);

  // The Update command checks for optimistic concurrency violations in the WHERE clause.
  custDA.UpdateCommand = new SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " +
                                        "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn);
  custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
  custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName");

  // Pass the original values to the WHERE clause parameters.
  SqlParameter myParm;
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
  myParm.SourceVersion = DataRowVersion.Original;
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName");
  myParm.SourceVersion = DataRowVersion.Original;

  // Add the RowUpdated event handler.
  custDA.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

  DataSet custDS = new DataSet();
  custDA.Fill(custDS, "Customers");

  // Modify the DataSet contents.

  custDA.Update(custDS, "Customers");

  foreach (DataRow myRow in custDS.Tables["Customers"].Rows)
  {
    if (myRow.HasErrors)
      Console.WriteLine(myRow[0] + "\n" + myRow.RowError);
  }


protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
  if (args.RecordsAffected == 0)
  {
    args.Row.RowError = "Optimistic Concurrency Violation Encountered";
    args.Status = UpdateStatus.SkipCurrentRow;
  }
}
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ProgramVBAuthor Commented:
AlexFM, the code looks great but could you explain a little whats happening
0
 
ProgramVBAuthor Commented:
im also using VB not C#
0
 
AlexFMCommented:
0
All Courses

From novice to tech pro — start learning today.