ProgramVB
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??
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??
ASKER
AlexFM, the code looks great but could you explain a little whats happening
ASKER
im also using VB not C#
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Param
custDA.UpdateCommand.Param
// Pass the original values to the WHERE clause parameters.
SqlParameter myParm;
myParm = custDA.UpdateCommand.Param
myParm.SourceVersion = DataRowVersion.Original;
myParm = custDA.UpdateCommand.Param
myParm.SourceVersion = DataRowVersion.Original;
// Add the RowUpdated event handler.
custDA.RowUpdated += new SqlRowUpdatedEventHandler(
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
// Modify the DataSet contents.
custDA.Update(custDS, "Customers");
foreach (DataRow myRow in custDS.Tables["Customers"]
{
if (myRow.HasErrors)
Console.WriteLine(myRow[0]
}
protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
if (args.RecordsAffected == 0)
{
args.Row.RowError = "Optimistic Concurrency Violation Encountered";
args.Status = UpdateStatus.SkipCurrentRo
}
}