Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

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??

0
ProgramVB
Asked:
ProgramVB
  • 4
  • 2
1 Solution
 
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
 
ProgramVBAuthor Commented:
AlexFM, the code looks great but could you explain a little whats happening
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ProgramVBAuthor Commented:
im also using VB not C#
0
 
AlexFMCommented:
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:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now