| Let's look at the Insert and Delete statements the DataAdapter created. The Insert statement is the same whether or not we choose optimistic concurrency. Since we are adding a new row, we don't have to worry about anyone else changing the row since it will not have existed yet. The only possible issue here is that someone else tries to add a row with a nonunique unique key. In this case, the server will abort the operation and the DataAdapter will simply throw an error. There is a concurrency issue with the Delete statement beyond another user deleting the row before you do. What if another user made changes to the row that makes it undesirable to delete due to business rules or some other reason? For example, what if a table had a column which, if true, indicates that the row should not be deleted. If another user set the value to true after you read the row, you would be able to delete the row because to you, the value would still be false. For this reason we need optimistic concurrency on deletions. Here is the Delete statement with concurrency checked. DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL) The where clause used here is the same as the where clause for the Update statement. If we turn off concurrency, we get this statement: DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) If there are no restrictions on deleting rows from the table, this would probably do fine. The other issue is referential integrity. If there are rules or foreign keys on this table that prohibit the row's deletion, they will be handled by the database system. The DataAdapter will throw an error if referential integrity rules are violated. Just for completeness, let's look at the Insert statement: INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax) As we said, we don't have to be concerned over concurrency with the Insert statement, so it's just a normal SQL statement. Using Stored Procedures At the beginning of this discussion, around Figure 6.3, we saw that there were two other options for the query type. We have looked at SQL statements. Now, let's look at stored procedures. If we start at Figure 6.3 and select New Stored Procedures, we get another page after clicking Next twice. The first click is still the Select statement. Even though we want to use stored procedures, the wizard still needs a basis for building the stored procedures so we must still give it a Select statement. After entering the Select statement, we get the page in Figure 6.15. Figure 6.15. The Create Stored Procedures page.  The four TextBoxes allow you to name your stored procedures. You should use meaningful names that conform to some naming convention. As per Microsoft standards, avoid using the sp_ prefix as this is reserved for system-stored procedures. A good practice is to use some form of your company initials or an acronym for the application you are building as a prefix. That way you can be reasonably assured your stored procedures won't interfere with another vendor's applications. Let's use adob0601 as our prefix. There are a couple of options. You can have the wizard create the stored procedures for you, or you can create them manually later on. Either way, the wizard uses a script that it generates to create the stored procedures. If you click the Preview SQL Script button, another window opens showing the generated script as in Figure 6.16. You can save the script using the Save As button or cut and paste it into a text editor to run later. You can then customize the script if needed. Figure 6.16. Preview SQL Script window.  If you scroll down through the script, you will see that the procedures simply wrap the same SQL statements we generated directly. The advantage to using stored procedures is that you can reuse the same procedures with DataAdapters elsewhere in your project. You can also include the scripts in your project and put them under source control so you can use them to recreate the procedures in another database. Using Existing Stored Procedures The third option is to use existing stored procedures. When you select this option, you have the opportunity to supply your own stored procedure names. The wizard will attempt to populate the Parameter collection from the stored procedure, as in Figure 6.17. Figure 6.17. Existing Stored Procedures page.  The Select and Insert commands have been completed. The Update command is ready to select from the drop down in Figure 6.18. Figure 6.18. Selecting the procedure name from the drop down.  Once you have selected your procedures, the rest of the pages are the same as for the other options. If the procedures you choose do not support optimistic concurrency, you may get a warning to that effect. If this is of no concern to you, you may ignore the warning. If you desire optimistic concurrency, you must make sure your stored procedures pass the original values as well as the new values. We have now seen how to use the wizard to configure the DataAdapter. In the next section, we will configure one entirely in code for you pure programmers who shun wizards and other automated tools. If you are in the latter group , look at the new tools in Visual Studio .NET. They are more sophisticated than their predecessors and can at least give you a place to start, which you can customize later. In this employment environment, anything that saves some time is worth looking at. But for you purists | 
