Avatar of ProgramVB
ProgramVBFlag for Ireland asked on

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

Programming

Avatar of undefined
Last Comment
AlexFM

8/22/2022 - Mon
AlexFM

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;
  }
}
AlexFM

ASKER
ProgramVB

AlexFM, the code looks great but could you explain a little whats happening
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
ProgramVB

im also using VB not C#
ASKER CERTIFIED SOLUTION
AlexFM

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AlexFM