Do you find the Support WebCast transcripts helpful?
Let us know!

Note This document is based on the original spoken Support WebCast transcript. It has been edited for clarity.

Ken Robison: My name is Ken Robison. I specialize in data access technologies here with Microsoft. The subject at hand here is ADO.NET concurrency using Microsoft® Visual Basic® Windows® Forms. We are going to focus on Windows Forms today, just due to the amount of information we'd have to cover to try to do both Windows and Web Forms.

Just to quickly go over the agenda (slide 2), we're going to be covering an intro to concurrency, what concurrency is, why we need it and why would be concerned with it, what types of concurrency control are available in Microsoft Visual Studio® .NET, the retrieving data after we've submitted data to our back-end database. So basically we send an UPDATE statement to our database, and now we would like to update our data set with the current data so it reflects the current data on the back-end data source.

We will also be covering the command builder and the Data Adapter Configuration Wizard, and the options available in them for concurrency options. So we will be focusing on the concurrency options, not the actual builders themselves, but we will cover a number of topics with regards to those. And last, but not least, how do we actually handle these concurrency exceptions? We'll get into fairly decent details on those.

I have also included a supplemental lab that basically covers two different scenarios here that we will be getting into in a little bit. But the hope of this was due to the limited amount of information that I could provide to you in a slide deck, I wanted to provide something that you could go through and implement step-by-step, and actually get to touch this stuff and see what it does, and have the commands in front of you rather than trying to put something together on your own.

Without further ado, I'll pass you on to Ayax Vargas and he'll continue on, and I'll pick up a little bit later in the presentation. Thanks.

Ayax Vargas: Hello, everybody. This is Ayax Vargas. I'm a Support Professional with the VB WebData teams, supporting XML and C# technologies as well. Today I'll be talking to you about the introductory part of this presentation. We're going to discuss, real quickly, what concurrency control is and why we need it (slide 3).

Basically, in a situation where we have multiple users trying to modify data from the same database of the same nature at the same time, we encounter the need to do concurrency control. Over time there have been many approaches on how to deal with the fact that more than one user tries to change the same bits of data in the same database at the same time. The overall objective that we are pursuing with these techniques is that the changes made by one user do not cause unfavorable results to other users. That's what we define as concurrency control.

Over the course of this chat, we're going to discuss some of those controls that we would use to, first of all, see when the changes are made and, also, how to deal with them.

So as we move to the next slide (4), we are going to discuss what types of concurrency control we can perform in a typical application. I'll go ahead and mention those two available methods right now.

The first one is the pessimistic concurrency control. In a pessimistic type of scenario, a user would lock a row of data from the time it obtains that row until the time the changes have committed into the database. This works fine in some scenarios but, as with any approach, it has drawbacks and it has advantages. Basically, the situation is that no other users would be able to even see the data logged by that user pessimistically, until those changes have been committed.

On the other hand, we have the optimistic concurrency control where multiple users are able to obtain the same row of data and potentially make changes to it. Only in this time, when the user has committed the changes to the row, then the row of data would not be available to the other user. So basically the main difference here is the span of time when data is not available to the other user.

As we go to the next slide (5), we are going to go into a little bit more detail for pessimistic concurrency. The first thing we're going to mention, as a refresher to most of our audience, is that ADO.NET doesn't have a built-in facility to achieve pessimistic locking. Instead, the model is designed to submit changes through optimistic updates.

As we will see in the next slide, there are some ways in which we can get around this slight limitation and achieve some kind of pessimistic concurrency. So we continue with pessimistic concurrency on the next slide.

As we were saying, we can basically get pessimistic concurrency on the ADO application. This is what we will have to do: we will have to use the transaction object, which would basically allow us to do the three steps (on slide 6) without justifying in a high-level type of place. We're not putting any "pseudocode" here or anything, but just describing these steps.

First of all, we will have to connect to the database — that's a given — and then create a transaction object to enlist in a transaction. Now that we are in the middle of a transaction and, by the way, this example is more specific to Microsoft SQL Server™. This is kind of the back end that we're focusing on for the entire workshop here. Basically, after we have enlisted into a transaction, then we would do a query to lock a row of data within that transaction, meaning that we're doing a SELECT. We're getting that row of data, but we're putting a locking key in that query so that — even though we're doing a SELECT, we're just updating data here — SQL Server knows that it has to lock that data.

So let's quickly take a look at the example query, so we can get into that in more detail. We basically say SELECT * FROM Customers WITH (UPDLOCK) WHERE CustomerID = value. The important issue here is that, again, we're using the locking key UPDLOCK.

Basically, from SQL Server Books Online: what UPDLOCK is going to do is use update locks instead of shared locks when you read the table. The reason why we need a transaction with this approach is because that lock is going to be held until the end of the statement or the end of the transaction. So that way we basically enable our transaction, we do the SELECT, and potentially we could do other changes to the data. We could do deletes or we could do updates. Again, this row is going to be locked by SQL Server until the transaction is released.

Also, this option, UPDLOCK, has the advantage of allowing you to read data without blocking the other readers, and then update it later with the assurance that the data has not changed since you last read it. In other words, other people will be able to read the data, but they wouldn't be able to lock it for updating it until we have committed our transaction. So this is a good way to achieve pessimistic concurrency but, again, this is a custom solution. This is not something that we can get built in with ADO.NET, or we can just tell ADO.NET to do this for us. We actually have to write some code and be specific in these queries to do it.

Also, one of the major issues here is that, as a refresher, the ADO.NET is a disconnected model. So we don't really have a cursor going all the way to the server. So that's why, on a disconnected model, there is really no good way of doing pessimistic concurrency without actually running your queries with these kinds of locks and creating a transaction that is going to read data and potentially not update it.

So we go to the next slide (7), and we're going to talk about optimistic concurrency; this is what most of our discussion today is going to be about. As I mentioned, since ADO.NET is a disconnected model, most of the stuff we're going to be doing, both how to do deal with concurrency and how to handle the exceptions, is going to be towards an optimistic concurrency scenario.

What we're going to do right now is discuss the options for achieving optimistic concurrency. Basically, the options are based on what kind of information we used in the WHERE clause for UPDATE statements where we tried to commit the changes to the back end.

Obviously, I'm just saying UPDATE here, but naturally, we can do DELETEs as well, so that's a way of updating. So I'm just going to use them kind of interchangeably here. When I say an UPDATE, that doesn't necessarily mean a literal UPDATE statement; it also could also mean a change via a GUI.

But we mentioned those options, and the first option that we have for optimistic concurrency is to include only the primary key columns. We're going to have examples of each of these options and explain them in more detail. The next option that we have available for us is to include all columns of data in the UPDATE (or, as we said, the DELETE) statement.

Also, something else that we can do is to include the primary key and timestamp columns. As we go into more details, we're going to explain a little bit further about the timestamp. This also serves as a refresher, and gives a little bit more background on what SQL Server does. Finally, we're going to describe the classic ADO default behavior, which is to include the primary key columns and the modified columns. So as we go on, we're going to discuss those options and, as with any approach, what the advantages are and what the disadvantages are, and how ADO.NET is doing its default behavior.

So we'll go to the next slide (8) and we'll talk about the optimistic concurrency and what happens when we include only the primary key columns in our statements. This approach is very straightforward. We have a table with a primary key on it. What we want to do when we do this kind of optimistic concurrency, we try to update the row and we just tell SQL Server to change that row based on the primary key.

What happens here is a kind of "last in wins" approach, meaning that we have two users retrieving the same row of data based on the primary key. For example, if we retrieve Customer, and all we have is Customer 1. So both users retrieve the row for Customer 1. User A makes a change and User B makes the change. Both of them try to write the changes back based on only the primary key, which is the customer number. Both of them apply it to Customer 1. One of the issues here with this approach is that you don't have a way of knowing that the User B made a change in the first place, or when User A made it.

So we're going to take a look at the next slide (9), which has the example of what the UPDATE statement looks like, and we'll discuss a little bit more about this. Here's our statement: UPDATE Customers, SET CustomerID = <so-and-so>, CompanyName = <so-and-so>, which in this case, we just changed the company name, WHERE CustomerID = <CustomerID I read>.

So again, the whole issue here is that one user is going in and changing the company name for that customer, whereas another user may have potentially changed either that company name or any other field on that record. What's going to happen is that, as we said, this is a "last in wins" approach, so only the changes that the second user made are going to be prevailing, and one user doesn't have any way of knowing that the other user even made any changes, much less be aware of and have to deal with the whole concurrency problem that could potentially arise.

Let's move to the next slide (10) so we can take a look at the next approach, which is to include all the columns. In this scenario in our WHERE clause, for either the UPDATE or the DELETE statement, we will include all the fields that we originally read.

In this case, what we are doing is we are comparing the original values that we retrieved from the database against the current values in the database. Basically we want to make sure that the row is exactly identical to what we read initially, before we attempt to write any changes. This basically is an approach exactly the opposite of the first one, where we don't make any changes unless the row remains identical.

So let's go ahead and take a look at the example of what that statement would look like. In this slide (11) we're going to take a look at it. We're just saying

We could potentially go, I mean of course, if we have many other fields, this WHERE clause is going to grow to include a lot of fields and a lot of parameters to do this data selection.

So what's going to happen is that if, potentially, User A changed that record before User B ran this statement, we are not going to find a matching row for this statement, because the primary key hasn't changed, as it did in the previous example, but other fields may have potentially changed. So what we're going to see is one of those situations where we run this query and we don't find any matching rows, and that would tell us that the Original_CustomerID has changed, and somebody else did something.

At this point, we don't have a way of telling who did it or what changed, but at least we know that we don't have a matching row, and there is a concurrency issue there that we couldn't make those changes. But, at this point, we're going to discuss further on how to deal with these process errors and what to do about it, but we're just illustrating what the WHERE clause would look like where we want to compare all the columns before doing any kind of update. Again, this is also valid if we wanted to delete that row. We want to make sure that a user didn't change that row before we attempt to delete it.

So let's move on to the next scenario (slide 12), where we include the primary key and the timestamp columns. The first thing we're going to mention here is that this would use a combination of the primary key and the timestamp columns in the WHERE clause. What is the advantage of this? Well, first of all, the timestamp column is a binary value that is generated by SQL Server, which actually changes when SQL Server generates a new value for the timestamp column every time the row gets updated. This gives us a few advantages.

First of all, if any change is made to the row, we can know about it because the timestamp column has changed. We also have the advantage that we don't have to include so many columns, because there are going to be situations where the table design has too many columns, and we don't want to basically build a very, very long stream in our WHERE clause just to make sure that the user didn't change any values.

I'm going to go ahead and go to the example and continue talking about this approach with the primary key and timestamp columns (slide 13). Basically, one of the issues here is that there are the text and blob columns in SQL Server or many other back ends, that are not quite suitable to be compared in the WHERE clause. An example of this would include text or memo fields, basically fields that don't have a fixed length, or even binary or BLOB columns. So in that approach, in the case that we have an application that is updating images or long strings of text, it would not only not be practical, but it would not be possible to include such fields in a WHERE clause because we wouldn't be able to compare binary values in a WHERE clause.

So the two main advantages of including the primary key in timestamp columns, as we can see here in the example, we can say UPDATE TestCustomers SET CompanyName = <New_CompanyName>. So we're doing the example where the User B changes the company name, WHERE CustomerID = Original_CustomerID AND CompanyName = Original_CompanyName AND TimeStampCol = TimeStamp. We basically want to make sure that the timestamp column is the same timestamp column we read initially and that it hasn't changed. So if the timestamp column remains the same, it means that nothing else in that row has changed.

Again, the major difference in this approach gives us two advantages, shorter WHERE clauses where we could use the comparing a few columns that say we have a multipart primary key. We have shorter WHERE clauses, in which the primary key may be two or three fields, at most, and the timestamp column, and we have the other advantage that this will work with other fields that are not suitable to include all columns. Again, those would be BLOB columns or text columns in SQL Servers, among others. As new data types come into the picture, we can also take advantage of them and use the timestamp column as a very valuable option to handle concurrency, and it helps us to know that another user changed something when we didn't want them to.

So we're going to go to the last option we have in dealing with optimistic concurrency (slide 14), where we use a combination of the primary key fields and which columns were modified, and we will include those in the WHERE clause. This, by the way, is the default behavior used in the classic ADO, meaning not ADO.NET. The classic ADO cursor engine will actually do that for you when you do updates in the Recordset object. The structure of the ADO.NET DataAdapter doesn't lend itself to this updating strategy.

Basically, the reason for that — and Ken is going to mention more details about it — is that when you build a command, there is no way that you can know up front which fields are going to be changed by one particular user. You cannot know in advance that User A is going to come in and change just the company name, whereas User B is going to change, for example, the main contact for that company. So ADO.NET doesn't really lend itself well to this type of structure, but this is, again, just one strategy that we could potentially use for optimistic concurrency.

We don't have an example of this, but as I said, if we just change the company name we would just try to retrieve a row where the primary key and that column are identical. The only potential failure for this, the only reason why we wouldn't be able to make that update, is because User A changed the company name and User B changed the company name too, and then the row wouldn't be located in the WHERE clause. But if, potentially, User A changed, let's say, the company telephone number, and User B changed the company name, then those changes would be easily overwritten because the WHERE clause would still match, even though something else was changed in the record by a different user.

So in order to go on further, I'm going to turn it over to Ken, who is going to continue with today's presentation.

Ken: Okay. Thank you, Ayax. Basically, now that we have a better understanding of how we can issue updates to the back end and what our possibilities are, and we can see that the key here is our WHERE clause. That's actually going to be taking the values that we originally pulled from our back-end database and comparing those with what's in there currently. When we send that WHERE clause back, you can tell that that is controlling how the concurrency options are going to be affected.

I'll go ahead and move on to the next slide here (slide 15). What we're going to be looking at, now that we know how to send the updates and we know what options are available to us, is how we actually see these changes in our data set. We've sent the changes that were in our data set to the back end, but do we actually have those changes that other users may have made? Let's say I changed the first name field; somebody else changed the last name field; both of our changes were successful; how do I get to see that other user's changes?

So what we have here are basically three different options available to us. One of these options is to use the output parameters, and we'll be going through examples of each one of these. Another option is to use a batch query, and our final option is to use a RowUpdated event of the DataAdapter object (DataAdapter.RowUpdated event).

So we'll go ahead and move to the next slide (16) and show you an example of the first one, which is using the output parameters. As you can see, we create a stored procedure in this particular case, and we use the TS column (TSCol), which is a timestamp column in this case. The key to this thing is it's a normal UPDATE statement, just like we saw on the other ones, but you'll notice that we have — at the end I highlighted it — we have a semicolon and then we have another statement.

In this case, we're saying if we've actually returned records here, that we would like to get an update from our back-end data source. So we issue that second SELECT statement there, which basically pulls back data for us, so that now our data set has the current information in it.

Let's move on and take a look at the batch query option (slide 17). It's fairly similar. It's actually a little bit simpler. As you can see here, once again we just append a semicolon and then a SELECT statement to pull back the data again, so a very simple approach. One of the keys to this thing is that the database must support batch queries. Many databases such as Access and Oracle simply don't have support for this, so it's not an available option. That's why I wanted to cover all your options here, so if, in fact, you do need to hit one of these databases, such as a Jet database like Access or Oracle, you'll know what options are available to you here.

I kind of skipped ahead a little bit. As far the output parameter's options, Oracle does support that. Access does not support that. So output parameters are a viable option with Oracle. I just wanted to make that point clear.

Now let's go ahead and move on to our next slide here (slide 18). Now we're going to get into a little bit more of a complicated scenario. In this particular case, this works against just about any back-end database to include Access. What we're doing here is we are coding off the DataAdapter.RowUpdated event. What's really nice about this event is that we're able to send a parameterized query to the back-end database to get the concurrency exception that has actually occurred.

The other thing about the RowUpdated event is when we send an update to the database, the UPDATE statement gets applied first, and then this event fires after that update has taken place or it has attempted to take place. So it's a perfect place for us to go ahead and trap things if we've actually run into an exception.

Now to give you a little example of what the RowUpdated event looks like (slide 19). Most of you are probably familiar with coding of event handlers, but I at least wanted to give you a quick refresher on what an update event would look like. Basically here what we're doing is the AddHandler DataAdapter update (da.Update), and it then has the arguments in here. So pretty normal. Most people who are familiar with VB — and most of you all probably are — would be very familiar with us.

Now let's take a look at the actual event itself (slide 20). That was the definition for it. Now this is the event that would get fired after we've issued our update. Like I said, this is the RowUpdated, meaning that the update has tried to take place and now this event fires.

In this event, obviously we can add as much code as we need to, to properly handle the problem at hand here. So in this particular example here, we weren't really trying to show major functionality, just trying to give you an idea of how you'd handle this type of an error. One of the key things in here is the fact that we are going in and we're actually retrieving, using the e.Row, OrderID, the ProductID, and our TSCol. We're using the Errors collection that was passed into this event and we're able to go out and retrieve values from our database based on those original values.

So this is very useful for gathering, "Hey, what's in the database now? What did I have originally? Was this row deleted? Was this row modified?" And we'll get into these different possibilities a little bit later in the presentation.

Moving on to the next slide (21), I just wanted to kind of go over some of the performance considerations, now that we've talked about our three options, and just give you an idea. One thing that I do want to make very clear here is that when we talk about performance, it is not across the board. You need to test in your environment because, depending on your environment, performance can change. So I don't want to make this a hard and fast rule that this is always the case. You always need to test in your environment with your code to see what kind of performance you get.

So our first option here, using output parameters, is always going to be our best option. Generally we're talking about stored procedures here, passing a parameter to it, and that's going to generally give us the best performance. Obviously the database must support batch queries in order to handle this type of a scenario.

Using batch queries, as it says in the slide, pretty much "middle of the road" as far as performance goes and, once again, the database must support the batch query.

Then finally, to sum up the performance considerations (slide 22), the last option we have available is the RowUpdated event. Of all of our choices, this generally gives us the worst performance. Batch queries are kind of middle of the road, like I said, usually about 35 percent slower. Then finally, output parameters, which are generally your best choice, probably about 50 percent performance as compared to our worst-case scenario here, which is using the Updated event. And we already discussed databases that support and do not support this, so I'll move on.

Next (slide 23), kind of switching gears just a bit here, we're going to talk about some of the tools available to us, as far as building update queries. We have some really nice tools. In this case, a lot of you are probably familiar with the Command Builder if you've worked with previous versions of our products. In ADO.NET, the Command Builder basically will generate update logic for you at run time automatically.

In order to achieve this goal, we can see in the example down below, what we're doing is filling up a DataAdapter and then we just create a command object and pass it in the DataAdapter, and it will automatically create our INSERT, UPDATE, and DELETE statements for us, which is very nice. One of the problems that we run into with that is that we are doing this at run time. The queries are being generated at run time; obviously you're going to take a hit at run time to be able to use such an option. So just kind of be cautious with that.

At this point, as far as the options with concurrency in the Command Builder, we only support all columns in the WHERE clause and we kind of already went over what disadvantages and advantages that brings to the table.

Moving on to the next slide here (24), we're going to take a look at the Data Adapter Configuration Wizard. This tool offers us a little bit more control over our optimistic concurrency options. It gives us a design-time control over creating our queries so we can, at design time, go in to take a look at our queries and see what the WHERE clauses look like. We can modify those by hand if we choose to do so. One caution in that area is that if you do actually go in and modify the entire query to include the WHERE clause and then you run the wizard again, it will overwrite all of your changes. The moral of the story there is that the wizard is going to win. As I said, it includes all columns by default. It also supports an option that is "include only the primary key" in the concurrency option.

Let's move on to the next slide (25), and we'll get a better idea of what the interface looks like for this. What I've done is taken a snapshot of one of the steps as you're going through the DataAdapter Configuration Wizard and noted the options in here. The intent here is not to cover the whole, entire wizard. It was to more stay focused on the concurrency options that this adapter will bring to the table.

As you can see in the snapshot here, we can choose to generate our Update, Insert, and Delete statements, which is very powerful as far as optimistic concurrency goes. If I use the default, you can see the box is checked. I have the default set here. What this is going to do for us, when we generate the query, is it's going to include all columns by default. If we uncheck this box, it will include only the primary key. So basically what we're looking at in that case is a "last person wins" scenario. So we talked about some of the advantages and disadvantages of that.

Finally, we have the Refresh the DataSet option, which is basically going to write the batch query that we had talked about earlier, saying, "Hey, I've issued my update. Now can I get the changes other users made?" So it adds that additional Select parameter to the end our query, and pulls back the latest and greatest data.

Now let's move into actually handling some of these concurrency exceptions (slide 26). Before we really get into the nitty-gritty, I just kind of want to go over the model for handling exceptions in general. We have, basically, the choice of doing nothing, and we'll take a look at that in a second here, what it would look like. If we choose to do no handling, obviously it's going to throw a pretty hard error on the screen, and we're going to end or terminate our application when we're finished, by saying yes or no to the dialog box that comes up. Our application goes away, we've lost our changes, and, generally, users aren't too happy.

Our next option available to us is to just use a general exception handler. Many people are familiar with creating a general exception handler. It handles any error that might pop up, and it's just going to throw a message up and give us, as a developer, the opportunity to go ahead and handle exceptions, and maybe provide the users with a yes or no choice — do you really want this update to take place, yes or no; do you want to terminate the application; do you want to recover — things of that nature.

Last but not least, we've brought a new object to the table here, our handler, which is the DBConcurrencyException handler. We'll be taking a look at that in a few moments here. But what's nice with this is it gives us yet another option as far as handling exceptions. In a moment we'll take a look at that, but this would kind of precede the general exception handler. We step in and we try doing our updates. If it fails, we'll drop into our Concurrency Exception handler. If, in fact, it is not a Concurrency Exception handler, then we would have fallen through to our general exception.

So now let's take a look at the kind of information that we'd be looking at should we throw an exception and not handle the exception (slide 27). As you can see here, we get a nice dialog box that kind of tells us what's going on. We do at least know that we've got a concurrency violation. It was on an Update Command. So it does at least provide us with some information but, unfortunately, we don't have a lot of choices as to what to do with it when it occurs. So in this case, our application is going to terminate when we close the screen and, as I alluded to earlier, users generally don't like to see their app just go away.

Obviously, we like to put in exception handlers, so here's an example of just a general Try Catch block that you'd see in .NET (slide 28). Here we're catching a general exception. We would just put in some kind of informational message to maybe just warn the user, or we could actually act upon that and try to recover from it.

If we put in the general exception handler (slide 29), this dialog box is the type of message that we'd come back with. Once again, not extremely informative, but it does let you know what's going on and, hopefully, as a developer, you would have handled the exception a little bit better, maybe given your users some choices and not terminated the application when it was all said and done. Generally, when you catch this exception, you're going to try not to throw this to the users. You're probably going to catch that exception and then present them with a little better interpretation of what happened, and then offer them choices as to how to deal with it.

Our next and final option here is to add a DBConcurrencyException handler (slide 30). As you can see, it's fairly straightforward. All we're doing is adding an extra Catch block in the middle of our Try Catch. In this case, we're catching an exception as a DBConcurrencyException. We'll get into this and show you examples of how to deal with it. Because the topic here is concurrency, we'll be getting into this object fairly extensively and showing you some code that shows how to deal with these exceptions as we move forward.

One of the key things to note here is the DBConcurrencyException has an extra property on it that most rows do not have, and that is the Row property that allows us to do a comparison of our original data with our current data. It's very useful.

Now let's take a little bit closer look at it (slide 31). We're going to just go over the basics of the DBConcurrencyException. As we kind of step down in a little bit further, we'll show you some examples of it. So the DataAdapter, in the case of the DBConcurrencyException, is going to determine how the exceptions are processed. We'll get into some of the objects that affect this as well and explain those in detail. Examining the DataRow object properties helps us determine the cause of this failure, and as we move forward we'll get a good look at that as well.

One of the key players in deciding on how the DataAdapter is going to act is the ContinueUpdateOnError property of the DataAdapter object (slide 32). Basically we have two choices here: it's either true or false.

But going back to the ContinueUpdateOnError = True, normally the default option is going to be False, meaning when we get an error we are going to throw an exception. If we set the flag equal to True, we're going to say if a concurrency error condition occurs, an exception is not thrown in this case; it's going to continue. The update for this row will be skipped. The error information gets thrown into our RowError property of the offending row, and we'll look at this in a little bit, and then it continues updates on subsequent rows, and the process kind of continues through the same loop. Should we encounter another concurrency exception, once again, the exception is not thrown. We write the error into the RowError object for the offending row and move on to the next one.

As I alluded to a couple moments ago, if we set ContinueUpdateOnError = True, an exception is thrown, and we go directly into that exception handler and would need to deal with it in there.

Now let's take a look at some of the properties on the DataRow object (slide 33), so we can get a better understanding about what we're dealing with here to handle a concurrency exception. Basically, the DataRow.RowState property indicates the operation that has been performed on our last row.

We can take a look at the options here. We basically have five options available to us. The DataRow that we're sitting on is going to be in one of these current states. This has been added to the DataTable. This has been deleted from our DataTable. This has been detached, which a lot of people have a little confusion over, so I just want to cover this in a little more depth. Detached basically means we've created a DataRow, but we actually have not added that to the DataTable — so just a little clarification on that. I know I've had several questions on that in the past, so I wanted to clear that up right up front here.

Then we move on to the modified state, which basically says we have modified this row. And last but not least, the state when we first retrieve our data, which is going to be unmodified.

Now let's take a look at some of the things that actually control our RowState (slide 34). Basically AcceptChanges and RejectChanges are going to affect this, and we'll take a look at it in the next slide, a much better diagram kind of outlining this scenario for us. Deletes are also going to affect our RowState. The rows being added are going to affect it, and then when we decide to EndEdit or CancelEdit, all these are players in how our RowState is going to be sitting.

Let's take a good look at an example here (slide 35), which, to me, was very useful in understanding it, in really understanding what the objects were doing. The first thing we do is call our DataAdapter Fill, and we can see what's sitting in our buckets here. We basically have an Original bucket, a Current, and then our RowState object, which is what we're looking at here. We want to see how our RowState is going to be affected as we move down and do different commands on the particular object.

So when we do our DataAdapter.Fill we say, "Hey, go get my data," and it places it into our row object. In this case, our original and current are going to be the same. We pull back, in this case, White, which is probably a first name or a last name, and we can see that our RowState is unchanged. We haven't done anything to this data, obviously; we just changed it.

Now moving down to the next section here, we see we're issuing a DataRow change for the name. In this case, we're going to change our current value to Jones. We can see we still hold our original value that we pulled from the database. We now update our Current bucket with the name Jones, and we see that our RowState changes to a Modified state saying, "Hey, a change has occurred in here."

Now, should we move over to the right of the screen here and call our RejectChanges, basically what happens is the RowState will go back to Unmodified, meaning no changes have taken place, and we will take the data sitting in our Original bucket and move it into our Current, because we've said, "Forget these changes." So we want to overwrite our current data with what we pulled from the database originally, basically putting us back to where we started.

Now should we choose to actually accept these changes or in this case, issue the Update, what we do, is send those changes to the back end via the DataAdapter.Update. RowState does remain in the Modified state. Then in order to set our RowState flag back to Unmodified and basically tell our row that, "Hey, we're back. We've submitted the changes. We were successful," what we do is we call our DataSet.AcceptChanges method. This basically sets all of our RowState flags back to Unmodified, and now we should be in a state where our current data set reflects the back-end database and vice versa.

Now let's take a look at a little chart here that compares the RowState with the DataAdapter.Update (slide 36) What I wanted to cover here is if we've added a row, we execute an InsertCommand and then we replace our current version with the new values returned from the command, and I covered that a little bit in the last slide.

A delete, all were going to be doing is issuing the command. In the case of a modified RowState, we would be issuing the UpdateCommand, and then we would replace the current RowVersion with the new values if they were returned.

Last but not least, if the RowState is in an unmodified state, we're simply going to ignore it and move past that row. No changes have been made and no changes need to be submitted.

Now let's take a look at the DataRowVersion Enum (slide 37). Basically, what this is for is to let you know what version the DataRow is sitting in at this particular point in time. We can take a look at, basically, four different states the DataRowVersion can be sitting in. The first one is Original; it's the value that we read from the database originally. The Current RowState is the one that the user is modifying as he's going in and issuing updates against the data set. The next is the Default value. And then we have the Proposed, which is the value that we're proposing to send to the back-end database.

Now, the Proposed is only an option when we're enlisted in a transaction. So I just wanted to let you know that that flag sometimes is not available, sometimes is. That is contingent on whether we're in a transaction or not. Basically, it adds an extra bucket and sets that flag accordingly.

Now let's take a closer look at some of the properties that we have available to us to actually dig in and handle an error (slide 38). Basically, what the HasError property does for us is return a value indicating whether an error has occurred. All tables and all rows in our data set are going to be evaluated. You can see the little example here. It gives you a very brief example of how we would handle this.

The supplemental example (available at http://support.microsoft.com/default.aspx?scid=kb;EN-US;817281) gets into this and really shows you how to handle it, actually getting down to the level where you can tell the users, "This has been deleted by another user. This has been modified by another user. These actual rows have been changed by the other user," so much, much, much more finite control over this. But just due to lack of space here on the slides, we had to keep it fairly short.

I wanted to get the point across that here's how we would the HasErrors property. We just basically say, "Hey, does this table have any errors in it?" If so, drop into here and then write some code to reconcile these errors, whether it just be inform the user and reject the changes, or whether it be the choice to let the user handle the changes; show them what the problems were and let them say yea or nay to actually perform the update when it's all said and done.

Next take a little closer look on the ContiueUpdateOnError property (slide 39). As we talked about earlier, this has a big effect on how we're going to drop through, or how we're going to handle the errors. If we set this property equal to False, we're saying that we do not want to continue updates. We are going to throw an exception when it occurs, and when that does occur, now we want to do something about it. So let's talk a little bit more about handling this error.

One of the first things we're going to want to do is find out what the conflicting row is. We retrieve the current data from the database, so we can compare it with the data we have sitting in our current row. In this case, it's an exception row (ex.Row) that got passed in. We obtain the original value and then we start comparing this against what's sitting in our exception row, as far as the original value pulled from the database, what's sitting in our exception row as far as currently. Then in the last case here, we would say rowInDB. We're comparing what is really in the back-end database right now.

Here we take a little bit closer look at some of the code we would use to actually handle this (slide 40). The slide 38 we used the HasErrors to say, "Yes, we have had errors," and then we've maybe gone through and created a statement showing the users where the offending rows were. And then we may have asked the user, or prompted the user, how they would like to proceed. Do they want these changes to take effect and overwrite that other user's changes, or do we want to abort these changes and not overwrite that other user's changes? And then we set my current DataSet equal to what's in the back-end database, so we're synced up on the back end and in our current copy.

As you can see here, if the user would choose to overwrite the changes, what we'd be doing is basically merging our DataAdapter, the current data that we have with the data that we pulled from our back-end database. As I had alluded to in the last slide, we go and we pull the information from our back end, so we have what's sitting in there now, and we also have the stuff sitting in our error row here. So what we're doing here is we're merging these data sets back in.

In the case of saying we want to overwrite, we're saying that we want to preserve the changes, and that is going to overwrite the changes in our data set with what's in the back end. Then we call the AcceptChanges method to basically set our flag saying, "Hey, we have no more changes in our database. Everything is synced up, and we're back to a synced state and ready to move forward."

In the case of the user choosing No, they would like to cancel, what we're going to do is the DataSet.Merge and we're not going to preserve changes. That basically would take us back to where we originally started and not overwrite the person's changes.

Now let's take a look at the ContinueUpdateOnError = true (slide 41). In this case, as we said earlier, we are not going to throw an exception. We are going to go ahead and process all rows, and the ones that successfully make it through, great. The ones that don't make it through are going to be handled in the RowUpdate event.

As you can see, we have a little example here. We showed you this earlier in the slide deck, but just wanted to bring it up once again. This is how we make a handler, actually an example handler.

Then moving on to the next slide here (slide 42), the event handler basically supplies the SqlRowUpdatedEventArgs. You can see, in this case, we call it "Args." We could have called it anything. What we do in there is test the args.Errors to see if there are any errors. And secondly, is this error a DBConcurrencyException? If it is, then we can do pretty much anything we'd like, programmatically, to deal with that error, as far as how we want to present it or deal with it. In this case, I just showed you a brief example of setting the RowError so it would indicate that this row has been deleted by another user.

Generally, what you're going to be doing in there is comparing the back-end database with the current database, deciding, "Hey, this thing doesn't even exist in the back-end database; therefore, I want to set my RowError properly and then indicate that to the user."

Here's a little bit better rendition of the HasErrors property (slide 43), showing that we have a data set, in this case called Cust. We're checking if it has errors. Then we go in and create a DataRow object. For each row in our table we want to step down through it, see if it has errors, and create a string, and present that string to the user at some point, indicating which rows actually threw errors on this.

As I said earlier, I did include a supplemental step-through lab that should basically take you through each and every one of these options, provide you a hands-on experience with all of these particular options. And I actually give you a lot more code so that you'll have working code that you can go in, try it, get the feel for what's going on, actually see it happening, and look at the variables. I thought that would be a very good value-add for you, and maybe reduce the confusion and bring you up to speed a little bit better.

Finally, we get into the additional resources that are available to you (slide 44). All of these are very good links. I tried to provide you with ones that would give you the most information. Most of these actually have links to other articles, other WebCasts, things of that nature, that can help bring you up to speed as well. You guys have the list of that. I'm not going to go in and cover these but, like I said, each and every one of these is very good. The walkthrough is an excellent one, because it actually takes you by the hand and shows you how to deal with these once again.

I guess now we would move on to the Q&A session here.

Otto Cate: Excellent. Thank you very much for the presentation, guys. I'd like to go ahead and jump into the Q&A portion of the Support WebCast here today. I'd like to share a couple of quick program notes with our listeners. If you'd like to have a copy of the PowerPoint® slides, they are available for download now from the Support WebCast Web site.

The Q&A portion of the Support WebCast is intended to encourage further discussion of the topic that we addressed today. In addition, one-on-one product support issues are really outside the scope of what we're able to address. So if you do find that you need some more complex technical assistance, feel free to submit an incident on the Web, or contact Product Support Services directly and speak to a Support Professional.

The first question that we've got here, as follows: Regarding master/detail inserts, how do you recommend to handle identity columns so that order detail rows can include the order ID? Can we do this without using GUIDs?

Ken: As far as doing a master/detail insert, I know it says, "Please don't suggest GUIDs," but that is one possibility. I know that is kind of a pain in some respects but, generally, if we want to get an identity column back from the database, what we're going to do is do a batch query or something of that nature, so that once we submit that change that query will pull back that identity column and we'll have that available to us. So that's probably the easiest way to handle it.

One good reference that I can point you to on that particular subject would be David Sceppa's ADO.NET book (Microsoft ADO.NET (Core Reference), ISBN: 0-7356-1423-7). Actually, I should have included that on the last slide. It covers almost all of this information in very good depth, and also includes a lot of information about the Web services as well. So that is a good place to take a look and to get you up to speed. It also comes with a companion CD. A lot of these examples are on there. It provides a lot more details, above and beyond what I've provided to you in the slides or in my lab example.

I hope that answers that for you.

Otto: The next question here, regarding optimistic concurrency: If the first user does a delete, does the second user's change have any impact?

Ayax: I'll go ahead and address this. Basically, if both users open the same data set and the first user performs a delete, absolutely there is going to be an impact on the second user — we were talking about the WHERE clauses. We mentioned several ways, which were: include only the primary key, include all columns, and include the timestamp column. So in this case, the whole situation boils down to the fact that with these WHERE clauses, if the row has been deleted, no matter what kind of WHERE clause we're going to use, we're not going to be able to find that row when we try to commit that update.

So let's say that we try to modify a row. So we try to do an update, and regardless of what we put in there, whether we put just the primary key or we put all the columns, that row doesn't exist any more. So yes, there is going to be a concurrency issue there that needs to be handled appropriately. In this case, as we saw further down in the lab, we have the means in ADO.NET to determine that this row has been deleted and then warn the user appropriately saying, "This row has been deleted," and just write code to deal with those kinds of situations.

Again, in the case of row deletion, that would be regardless of what kind of WHERE clause we use to try to locate the row we're trying to update.

Otto: The next question here: In the primary key and timestamp columns example, why did we include the company name in the WHERE clause?

Ken: That one there would be a mistake on my part. I do apologize. That should only have the primary key, which, in this case, is the original customer ID, and the timestamp column, which, in this case, is called TimeStampCol. I apologize for that one.

Otto: Thanks for the clarification there. I'm not sure exactly which slide we're referring to here, but it says: Why the difference between Unchanged and Unmodified?

Ken: I think, in that case there, we're basically talking about the same thing. I believe that's slide 35 that we are referring to. In that case there, we're actually just talking about the same thing. The True value for that thing should be Unmodified. That's just a typo; that's all it was. So, once again, we do apologize; it should be Unmodified.

Otto: Next question here: Does a data set continue to keep all changes in the states until AcceptChanges is called? If so, does that state continue even if the data set has been persisted and rehydrated?

Ayax: The answer to that is yes. Just to clarify a little bit further, we don't necessarily keep a history of all changes that have been made. We just keep, basically, what the original value we read from the database is, and then we know what the current value is. We do keep the flag all the time that says, "Yes, this row has been modified." So as long as AcceptChanges has not been called, we are still going to keep the original value we read from the database. We are going to keep the current value, and we are not going to know that that row has been modified until we call AcceptChanges. Then the current value also becomes the original value, and then we have the unmodified value.

Also, on the second part of the question, when we say, "Well if we keep those changes, does the state continue even if the data sets have been persisted and rehydrated?" Basically, this depends on how we persist it. When we use the WriteXml method and the overload is called, there is one argument that we can take advantage of, which is the XmlWriteMode. This Enum basically has a value that we can choose from, which is DiffGram. So if we do a write XML with the DiffGram value in this XmlWrite mode argument, then we will be able to persist those states in the persisting of the data set. In other words, if we don't take advantage of that — that wouldn't be persisted along with the data set itself, just the current values.

Otto: I'm not sure; we may have kind of addressed these in the last couple of questions: On slide 35, the original value is kept in a data set disconnected from the data source until the AcceptChanges is called. That's a clarification, I guess.

Ayax: Just clarifying about slide 35, basically, even if the changes have been committed to the back end, we're still seeing that the row is modified until we call AcceptChanges.

Otto: Does state continue if the data set is remoted?

Ken: Yes. The state is held if it is remoted.

Otto: Several times during the presentation you had mentioned a lab that's going to be available. Where is that going to be?

Ken: That should be on the download site, along with the PowerPoint slides.

Otto: Okay. So that's right here on slide 44 then, the link.

Ken: It's a separate download.

Otto: It's a separate download within the actual PowerPoint deck.

Ken: No. You should be pulling down one .zip file, and it should contain the PowerPoint slides, the licensing agreement, the lab, and it will also contain a SQL Server script to create the database on your SQL Server computer.

One thing that I don't think I mentioned clearly in the lab was the fact that the SQL script will need to be run on the Northwind database in order to create that table, or the labs will need to be modified to use another table. The reason why I did that is I wanted to provide that script and get rid of the problems with primary keys and child tables, and things of that nature that came along with it. The point of that thing was to bring the concurrency options to the table and not get too intense with other information. I just wanted to make that clear for you.

Originally, there were some other labs that led up to this particular one, and that was one of the steps that was done in the other labs, telling you that you need to run the script on your SQL Server computer in order for these labs to work. Hopefully that will clear that up for you all.

Otto: We'll also directly link that download from our "Supplemental reading" section on the WebCast site.

With that, it does appear that we've answered all the questions that were submitted to the queue today. We want to certainly thank our listeners for coming out and giving us a listen here, and definitely hope that this information was useful to you. I also want to thank our presenters for coming out and giving us a great presentation as well.

If you have any suggestions for future topics, general comments about the sessions, or even the WebCast program as a whole, feel free to e-mail us at supweb@microsoft.com. I hope that you have the opportunity to tune in again soon. Thanks, and have a great day.