Figure 16.1 shows the interface of the application, and I suggest you open the application to see how it works. To specify the order's header, you simply select the customer who placed the order and the employee who made the sale from the appropriate ComboBox controls and enter the order's shipping address in the TextBox control at the bottom of the form. Then you must enter the products purchased; this is the most challenging part of the application because users should be able to enter new products quickly and safely.

Figure 16.1. The user interface of the NWOrders application

The NWOrders application assumes that you're using an instance of SQL Server running on the same machine and that you have installed the Northwind sample database. If SQL Server is running on a remote machine, you must edit the NorthwindConnectionString setting in the application's configuration file accordingly. To do so, open the app.config file, and edit the connectionString setting in the connectionStrings section:

<connectionStrings>
    <add name="NWOrders.My.MySettings.NorthwindConnectionString"
        connectionString="Data Source=(local);
        Initial Catalog=Northwind;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

This application will most likely be used as a point-of-sale (POS) system and must be intuitive, easy to use, and extremely fast. The user of the application is probably a cashier and shouldn't be expected to open the Products table, select the desired one, and then add it to the invoice. With the interface I'm suggesting, users enter the product ID in the TextBox above the ID column of the grid. As soon as the user presses the Enter key, the application retrieves the desired product and displays its description and price. The two TextBoxes are read-only, and the user can't change either. You may want to allow users to edit the price, but this isn't a recommended practice. A general discount that's applied to all items is probably the simplest way to go, but it's trivial to add another TextBox control for entering a different discount for each detail line (along with a new column in the ListView control with the order's details). As soon as the item is retrieved and displayed on the TextBox controls at the top, the focus is moved to the TextBox for the quantity, where the user can type the number of items. After pressing the Enter key for a second time, the new row is added to the grid with the order's details, and the focus is moved automatically to the ID field in anticipation of the next product's ID.

Users aren't expected to know the IDs of the products, obviously. In a real-world scenario, the application should accept the product's bar code instead of the ID. You can use a barcode reader with this application without any modifications. The barcode reader reads the bar's characters and sends them to the computer via the serial or USB port, but to the application they appear as if they were typed at the keyboard. The whole process is transparent, and you need not write a single additional line of code. Since the unique identifier in the Products table is the product ID, we'll use the ID as a bar code for this application.

Occasionally, the reader might fail to read a product ID. In that case, users can type a few characters from the product name, and the application will retrieve matching rows and display them so the user can select the desired one. Although searching with an ID will return a single product, searching with a product name may result in multiple matches, so some user intervention is needed to guarantee that the proper product is selected. In the discussion of the application, I will ignore this feature, which isn't trivial, and will focus on the other aspects of the application. If you're interested in the implementation of this feature, please open the NWOrders project with Visual Studio and examine the code; it is well documented. The code retrieves all the products whose name contains the characters specified in the ID box and displays them on a second ListView control, which is normally hidden but remains visible until the user selects the desired product. Figure 16.2 shows the application when a product is selected by name.

Figure 16.2. Selecting a product by name in the NWOrders application

While entering new products, the user may decide to delete a product that was added to the grid by mistake or modify the quantity. Although most of the time users work with the TextBox controls located at the top of the grid, in this case they must move the focus to the grid to select a row. This operation doesn't require the use of the mouse. By pressing the down arrow key while they're in the ID TextBox, users can move the focus to the first row of the grid and select it. Once they've landed on the grid with the order's details, users can move up and down with the arrow keys, and the currently selected row's fields are displayed in the TextBox controls on the top of the grid. To delete the selected row, they can simply press the Delete key. They can also use the mouse to locate the row to be removed on the grid, but it will take longer. Data-entry operators just don't like using their mouse. To return to the ID box and enter a new detail, they can press Escape.

Depending on the nature of the business, you may skip the quantity too and add a new row to the grid with a quantity of 1. If the same product is added multiple times, the application will generate multiple detail lines. You can edit the application's code to increase the quantity of the existing row, instead of adding a new row with the same product and a quantity of 1. If the application is going to be used at a bookstore, where people usually purchase a single copy of each title, the alternate approach will work better. You can use this application as your starting point for a POS system and add all the features required by your business needs.

The NWOrders application consists of two forms, the frmLINQ and frmSQL forms. The frmSQL form uses the basic ADO.NET objects to interact with the database, while frmLINQ uses the classes generated by the LINQ to SQL component of Visual Studio. You'll have to change the application's startup form to the desired one to test it.

We have discussed the application's interface, but before you start writing code, you must identify the requests you want to make to the database and implement them as methods in a separate class. The code behind the form will call these methods to retrieve data or submit data to the database and should not know anything about the database. This is not a requirement for small projects, but in a large project there will be different developers working on the data-access layer and different developers working on the user interface. By separating the data-access logic from the user interface, you can also implement multiple interfaces (a Windows application and a Web application, for example) that use the same data-access methods.

When you work on the application UI, you shouldn't need to care about the structure of the tables in the database. Your class should expose the business objects you need for the application and map them to the database tables. For example, the class you'll create shortly for the NWOrders application should expose an Order object that combines an order's header and details. Your UI code will populate an Order object and pass it as an argument to a method of the class that will submit it to the database. When the interface requests an order by its ID, the class should return an object of the Order type. This object will contain all the information about the order but with a structure that's totally different from the structure of the relevant tables in the database. In effect, the custom class will isolate the user interface from the data-access layer; should you have to implement the same application with a different database, you'll only have to revise the class that implements the data-access layer.

The class with the data-access components is referred to as the data-access layer, while the application's interface is the UI layer. In most applications, there's a third layer that implements the business logic. This layer is not related to the database, but it can't be part of the user interface either. You can implement it in either the UI or data-access layer, but logically it doesn't belong there and should be implemented as a separate class. If you ever decide to change some of the business rules, you will revise a specific class and not the entire application. Likewise, if you decide to move to a different database, you only need to revise the data-access layer and not the UI layer.

The business layer contains functionality that's specific to a corporation. For example, you may implement the discount calculations or a customer's credit line as a business layer. All methods that require this functionality, whether they reside in the UI or the data-access layer, should call the appropriate method of the class that implements the business logic.

The most basic rule in the design of data-driven applications is that the UI should not contain code that interacts with the database. Ideally, you must create classes that represent the business objects that represent the entities in your application. These business objects are classes that implement customers, employees, orders, and so on. Let's jump to a practical example to see how the application is structured.

The data-access layer of the frmSql form of the NWOrder application is implemented in the NWOrder class. The two simpler business objects of the NWOrder class are the NWEmployee and NWCustomer objects, which are implemented with two trivial classes by the same name. Both classes expose two properties, the ID and name of the corresponding entity and a custom implementation of the ToString method. I needed a custom implementation of the ToString method because the code in the UI application needs to add customers and employees to a ComboBox control and display their names.

In addition to their properties, the two classes provide the GetAllEmployees and GetAllCustomers methods, which return a strongly typed List of NWCustomer and NWEmployee objects, respectively. Listing 16.1 shows the code of the NWEmployee and NWCustomer classes (I've skipped the implementation of the two methods, which are fairly lengthy).

Public Class NWEmployee
    Public EmployeeID As Integer
    Public EmployeeName As String
    Public Overrides Function ToString() As String
        Return EmployeeName
    End Function

    Public Shared Function GetAllEmployees() As List(Of NWEmployee)
    End Function

End Class

Public Class NWCustomer
    Public CustomerID As String
    Public CustomerName As String
    Public Overrides Function ToString() As String
        Return CustomerName
    End Function

    Public Shared Function GetAllCustomers() As List(Of NWCustomer)
    End Function
End Class

Another class, the NWProduct class, represents the products that can appear in the order's details. Although the Products table contains a large number of columns, for the purposes of selling products we only need the product's ID, name, and price. The NWProduct class should also expose a method that allows us to retrieve a product by its ID, the GetProductByID method. All methods used in this application are shared: You can call them without having to create a new instance of the corresponding class.

Public Class NWProduct
    Public ProductID As Integer
    Public ProductName As String
    Public ProductPrice As Decimal

    Public Shared Function GetProductByID(
                    ByVal productID As Integer) As NWProduct
    End Function
End Class

Finally, the core class of the application is the NWOrder class, which represents an order and its details (see Listing 16.2). This class exposes the columns of the Orders table as properties and the columns of the Order Details table as a collection of NWOrderDetail objects.

Public Class NWOrder
    Public Class NWOrderDetail
        Public ProductID As Integer
        Public ProductPrice As Decimal
        Public ProductQuantity As Integer
        Public ProductDiscount As Decimal
    End Class

    Public OrderDate As Date
    Public EmployeeID As Integer
    Public CustomerID As String
    Public Details As New List(Of NWOrderDetail)

    Public Shared Function SaveOrder(ByVal newOrder As NWOrder) As Integer
    End Function
End Class

The implementation of the SaveOrder method is not trivial and is described in detail next.

For Each LI As ListViewItem In ListView1.Items
    Dim newDetail As New NWOrder.NWOrderDetail
    newDetail.ProductID = LI.Text
     newDetail.ProductPrice =
               System.Convert.ToDecimal(LI.SubItems(2).Text)
     newDetail.ProductQuantity =
               System.Convert.ToInt32(LI.SubItems(3).Text)
     newOrder.Details.Add(newDetail)
Next

Saving the New Order

Once the newOrder variable has been initialized to the fields of the order on the form, the application submits it to the database by calling the SaveOrder method of the data-access layer, whose code is shown in Listing 16.4.

Example 16.4. The SaveOrder method of the NWData class

Public Shared Function SaveOrder(ByVal newOrder As NWOrder) As Integer
    Dim CN As New SqlClient.SqlConnection(
             My.Settings.NorthwindConnectionString)
    Dim CMD As New SqlClient.SqlCommand
    CN.Open()
    CMD.Connection = CN
    Dim TR As SqlClient.SqlTransaction = CN.BeginTransaction
    CMD.Transaction = TR
    CMD.CommandText = "INSERT Orders (OrderDate, EmployeeID, " &
                "CustomerID) VALUES (@orderDate, @employeeID, " &
                "@customerID);SELECT Scope_Identity()"
    CMD.Parameters.AddWithValue("@orderDate", Today)
    CMD.Parameters.AddWithValue("@employeeID", newOrder.EmployeeID)
    CMD.Parameters.AddWithValue("@customerID", newOrder.CustomerID)
    Dim OrderID As Int32
    Try
        OrderID = System.Convert.ToInt32(CMD.ExecuteScalar)
    Catch ex As Exception
        TR.Rollback()
        CN.Close()
        Throw ex
    End Try

For Each det As NWOrderDetail In newOrder.Details
        CMD.CommandText = "INSERT [Order Details] "  &
                 "(OrderID, ProductID, UnitPrice, Quantity) " &
                 "VALUES(@OrderID, @productID, @price, @quantity)"
        CMD.Parameters.Clear()
        CMD.Parameters.AddWithValue("@orderID", OrderID)
        CMD.Parameters.AddWithValue("@productID", det.ProductID)
        CMD.Parameters.AddWithValue("@price", det.ProductPrice)
        CMD.Parameters.AddWithValue("@quantity", det.ProductQuantity)
        Try
            CMD.ExecuteNonQuery()
        Catch ex As Exception
            TR.Rollback()
            CN.Close()
            Throw ex
        End Try
    Next
    TR.Commit()
    CN.Close()
    Return OrderID
End Function

Listing 16.4 contains a large number of trivial statements, such as the statements that prepare the SQL command to be executed and initialize its parameters, which you can ignore; instead, focus on the statements that execute all SQL commands in transactional mode. The statements of interest are shown in bold in Listing 16.4.

To execute a number of statements in the context of a transaction, you must do the following:

1. Set up a Transaction object by calling the BeginTransaction method on the Connection object that will be used to submit the commands to the database.

2. Assign the Transaction object to the Transaction property of the Command object.

3. Submit multiple commands through the Command object's ExecuteNonQuery method.

4. Should an error condition prevent one of the commands from completing successfully, you can call the Rollback method of the Transaction object to abort the transaction.

5. If all goes well and none of the exception handlers is entered, the transaction will be committed when the Transaction object's Commit method is called.

Let's take a closer look at the code that commits the new order to the database. First it inserts the order's header to the Orders table and retrieves the ID of the newly created order. This ID will be used as a foreign key with the Order Details table to associate the details of the new order with its header. The INSERT statement is executed from within an exception handler, and in the case of an error, the transaction is aborted.

If the INSERT statement returns the ID of the new order, the code goes through the details of the newOrder variable and submits each one to the database through the same Command object, which is associated with the TR Transaction object. Again, the transaction can be aborted as soon as a detail line fails to update the Order Details table. If all details are inserted successfully, then the code calls the Commit method to finalize the insertions and update the database.

You can easily test the transactional features of the application by adding two detail lines with the same product ID. One of the constraints in the Order Details table is that no order should contain the same product more than once. To comply with this constraint, you can update the quantities of each detail line, rather than attempt to insert multiple lines that refer to the same product. Anyway, I have ignored this constraint in the implementation of the sample application. If you attempt to submit an order that contains the same product twice, the transaction will be aborted with the error message shown in Figure 16.3.

Figure 16.3. A violation of the database constraints will cause the transaction to be aborted.

If the order can't be committed to the database, you should display a friendlier error message and either allow the user to further edit the order or clear the fields in anticipation of the next order.

Now, we can switch our attention to the second implementation of the NWOrders applications, which uses LINQ to SQL to implement the data-access layer. The frmLINQ form is based on a LINQ to SQL component, which converts the relevant tables into custom business objects. In effect, the LINQ to SQL component will create the classes for the application objects, which we'll simply use in our application. Our new data layer doesn't contain any code to implement the Product, Order, and Order_Detail classes. The component will expose these three objects for us, and all we have to do is add the GetProductByID, GetProducts, and GetEmployees methods to the class. The code is quite short, because we don't have to request the desired rows and then iterate through them with a DataReader.

Start by adding a new item to the project, a LINQ to SQL Class component. When prompted, enter NW as the name of the new component, and Visual Studio will open it in the designer. Drop the Customers, Employees, Products, Orders, and Order Detail tables onto the design surface, and the designer will generate a class for each table. Once you drop the tables on the designer surface, the designer will create a class for each table and will expose the contents of each table as a collection of typed objects. Each row of the Customers table will be mapped to an object of the Customer type, and the rows of the Customers table will be stored in the Customers collection, which is a List of Customer objects.

To access the collections that represent the tables of the Northwind database, use the DataContext object, which also will be exposed by the same class. Create a variable of the NWDataContext type. (This is an autogenerated class named after the LINQ to SQL Class component with the suffix DataContext appended.)

Dim ctx As New NWDataContext

To access the Customers table, use the following expression, which returns a List of Customer objects, and each element in the List is a variable of the Customer type. Because the collection is typed, you can use LINQ to query its elements, as discussed in Chapter 14. The following query will select a customer by its ID:

Dim cust = (From c In ctx.Customers
            Where c.CustomerID = "ALFKI"
            Select c).FirstOrDefault

The LINQ query will not be executed at the client. Instead, the compiler will convert the LINQ query into a SQL query and will execute it against the database as needed. For more information on LINQ and how LINQ to SQL queries are transformed into SQL queries, see Chapter 14.

Listing 16.5 shows the implementation of the methods of the NWData class. Because the methods are implemented as LINQ queries, they require very little, and straightforward, code.

Public Class NWData
    Public Function GetProductByID(
                            ByVal productID As Integer) As Product
        Dim ctx As New NWDataContext
        Dim prod = (From p In ctx.Products
                      Where p.ProductID = productID
                      Select p).FirstOrDefault
        Return prod
    End Function

    Public Function GetEmployees() As List(Of Employee)
        Dim ctx As New NWDataContext
        Dim emps = (From emp In ctx.Employees
                      Select emp).ToList
        Return emps
    End Function

Public Function GetCustomers() As List(Of Customer)
        Dim ctx As New NWDataContext
        Dim customers = (From cust In ctx.Customers
                      Select cust).ToList
        Return customers
    End Function

End Class

The SaveOrder method of the NWData class uses the SubmitChanges method of the DataContext object generated by the designer, the NWDataContext object, to submit the new order to the database. The SubmitChanges method will insert the header in the Orders table and the details in the Order Details table. It will also handle the identity issue automatically.

The implementation of the SaveOrder method is trivial compared to the SaveOrder method of the NWOrder class we use with the frmSQL form, because now we don't have to perform the INSERT statements ourselves. To submit the changes made to a table back to the database, you must call the InsertOnSumbit/DeleteOnSubmit method of the appropriate table. These two methods do not submit any data to the database. They accept an entity that represents a new or deleted row as an argument and add it to the collection that represents the table. The rows are submitted to the database when you call the SubmitChanges method.

Even though all the rows will be inserted to the database with a single method, the SubmitChanges method, you must still execute this method in the context of a transaction. This time I'll use a different, and simpler, approach to the transactional update. The Framework provides the TransactionScope object, which initiates a transaction automatically. To use TransactionScope, you must first add a reference to the System.Transactions namespace to the project. Then, you must create a new instance of this class and embed the statements that must be executed in the transaction's scope in a Using statement. Listing 16.6 shows the implementation of the SaveOrder method of the NWData class.

Public Shared Function SaveOrder(ByVal newOrder As Order) As Integer
    Dim ctx As New NWDataContext
    Using TR As New System.Transactions.TransactionScope
        ctx.Orders.InsertOnSubmit(newOrder)
        Try
            ctx.SubmitChanges()
            TR.Complete()
        Catch ex As Exception
            Return (−1)
        End Try
    End Using
    Return newOrder.OrderID
End Function

One statement that may not be clear in the listing is the line that returns the value −1 when the transaction fails. Normally, the method returns the ID of the newly inserted row, and our application expects a positive value. The ID can also be accessed through the OrderID property of the newOrder object. This property has no value when the method is called, but the SubmitChanges method updates the fields of the newOrder object for us.

The newOrder.OrderID property is updated when the SubmitChanges method submits the order header to the Orders table. If this operation succeeds, the ID of the new order is assigned to the OrderID property. Should the transaction fail while inserting the row details, the value of the OrderID property won't change automatically. It will still have a valid value, even though no rows will be committed either to the Orders or Order Details table. That's why you need a return value from the SaveOrders method to indicate the success or failure of the operation.

The data-access layer of the form that uses the LINQ to SQL component to interact with the database is quite short and much easier to understand. I repeat it here for your convenience so you can compare it to the custom NWData class you developed earlier. As you can understand, the code is so much simpler because of the DataContext object, which abstracts the database tables and much of the functionality of the operations that are performed against these tables.

Public Class NWData
    Public Function GetProductByID(ByVal productID As Integer) As Product
        Dim ctx As New NWDataContext
        Dim prod = (From p In ctx.Products
                      Where p.ProductID = productID
                      Select p).FirstOrDefault
        Return prod
    End Function

    Public Function GetEmployees() As List(Of Employee)
        Dim ctx As New NWDataContext
        Dim emps = (From emp In ctx.Employees
                      Select emp).ToList
        Return emps
    End Function

    Public Function GetCustomers() As List(Of Customer)
        Dim ctx As New NWDataContext
        Dim customers = (From cust In ctx.Customers
                      Select cust).ToList
        Return customers
    End Function

    Public Shared Function SaveOrder(ByVal newOrder As Order) As Integer
        Dim ctx As New NWDataContext

        Using TR As New System.Transactions.TransactionScope
            ctx.Orders.InsertOnSubmit(newOrder)

Try
                ctx.SubmitChanges()
                TR.Complete()
            Catch ex As Exception
                Return (−1)
            End Try
        End Using
        Return newOrder.OrderID
    End Function
End Class

This concludes the first part of the chapter where you've learned how to use objects to represent the data stored in the database tables as custom objects. In the second half, you'll learn about DataSets, which are a standard mechanism for storing data at the client. DataSets are not new to Visual Studio; they have been around for many years, and they are used heavily in data-driven applications.

To use DataSets in your application, you must first create a DataAdapter object, which is the preferred technique for populating the DataSet. The DataAdapter is nothing more than a collection of Command objects that are needed to execute the various SQL statements against the database. As you recall from our previous discussion, we interact with the database by using four different command types: one to select the data and load them to the client computer with the help of a DataReader object (a Command object with the SELECT statement) and three more to submit to the database the new rows (a Command object with the INSERT statement), update existing rows (a Command object with the UPDATE statement), and delete existing rows (a Command object with the DELETE statement). A DataAdapter is a container for Connection and Command objects. If you declare a SqlDataAdapter object with a statement like the following:

Dim DA As New SqlDataAdapter

you'll see that it exposes the properties described in Table 16.1.

Each of these properties is an object and has its own Connection property, because each may not act on the same database (as unlikely as it may be). These properties also expose their own Parameters collection, which you must populate accordingly before executing a command.

The DataAdapter class performs the two basic tasks of a data-driven application: It retrieves data from the database to populate a DataSet and submits the changes to the database. To populate a DataSet, use the Fill method, which fills a specific DataTable object. There's one DataAdapter per DataTable object in the DataSet, and you must call the corresponding Fill method to populate each DataTable. To submit the changes to the database, use the Update method of the appropriate DataAdapter object. The Update method is overloaded, and you can use it to submit a single row to the database or all edited rows in a DataTable. The Update method uses the appropriate Command object to interact with the database.

Dim DS As New DataSet

Dim cmdSelectCustomers As String = "SELECT * FROM Customers " &
             "WHERE Customers.Country=@country"
Dim cmdDeleteCustomer As String = "DELETE Customers WHERE CustomerID=@CustomerID"
Dim cmdEditCustomer As String = "UPDATE Customers " &
             "SET CustomerID = @CustomerID, CompanyName = @CompanyName, " &
             "ContactName = @ContactName, ContactTitle = @ContactTitle " &
             "WHERE CustomerID = @CustID"
Dim cmdInsertCustomer As String = "INSERT Customers " &
             " (CustomerID, CompanyName, ContactName, ContactTitle) " &
             "VALUES(@CustomerID, @CompanyName, @ContactName, @ContactTitle) "

Dim DACustomers As New SqlDataAdapter()

Dim CN As New SqlConnection(ConnString)

DACustomers.SelectCommand = New SqlClient.SqlCommand(cmdSelectCustomers)
DACustomers.SelectCommand.Connection = CN
Dim param As New SqlParameter
param.ParameterName = "@Country"
param.SqlDbType = SqlDbType.VarChar
param.Size = 15
param.Direction = ParameterDirection.Input
param.IsNullable = False
param.Value = "Germany"
DACustomers.SelectCommand.Parameters.Add(param)

DA.SelectCommand.Parameters.Add(
     New System.Data.SqlClient.qlParameter(
     paramName, paramType, paramSize, paramDirection,
     paramNullable, paramPrecision, paramScale,
     columnName, rowVersion, paramValue)

CN.Open
DACustomers.Fill(DS, "Customers")
CN.Close

Each DataAdapter object that you set up in your code is associated with a single SELECT query, which may select data from one or multiple joined tables. The INSERT/UPDATE/DELETE queries of the DataAdapter can submit data to a single table. So far, you've seen how to manually set up each Command object in a DataAdapter object. There's a simpler method to specify the queries: You start with the SELECT statement, which selects data from a single table, and then let a CommandBuilder object infer the other three statements from the SELECT statement. Let's see this technique in action.

Declare a new SqlCommandBuilder object by passing the name of the adapter for which you want to generate the statements:

Dim CustomersCB As SqlCommandBuilder =
                New SqlCommandBuilder(DA)

This statement is all it takes to generate the InsertCommand, UpdateCommand, and DeleteCommand objects of the DACustomers SqlDataAdapter object. When the compiler runs into the previous statement, it will generate the appropriate Command objects and attach them to the DACustomers SqlDataAdapter. Here are the SQL statements generated by the CommandBuilder object for the Products table of the Northwind database:

UPDATE Command
UPDATE [Products] SET [ProductName] = @p1,
       [CategoryID] = @p2, [UnitPrice] = @p3,
       [UnitsInStock] = @p4, [UnitsOnOrder] = @p5
WHERE  (([ProductID] = @p6))

INSERT Command
INSERT INTO [Products]
         ([ProductName], [CategoryID],
          [UnitPrice], [UnitsInStock],
          [UnitsOnOrder])
          VALUES (@p1, @p2, @p3, @p4, @p5)

DELETE Command
DELETE FROM [Products] WHERE (([ProductID] = @p1))

These statements are based on the SELECT statement and are quite simple. You may notice that the UPDATE statement simply overrides the current values in the Products table. The CommandBuilder can generate a more elaborate statement that takes into consideration concurrency. It can generate a statement that compares the values read into the DataSet to the values stored in the database. If these values are different, which means that another user has edited the same row since the row was read into the DataSet, it doesn't perform the update. To specify the type of UPDATE statement you want to create with the CommandBuilder object, set its ConflictOption property, whose value is a member of the ConflictOption enumeration: CompareAllSearchValues (compares the values of all columns specified in the SELECT statement), CompareRowVersion (compares the original and current versions of the row), and OverwriteChanges (simply overwrites the fields of the current row in the database).

The OverwriteChanges option generates a simple statement that locates the row to be updated with its ID and overwrites the current field values unconditionally. If you set the ConflictOption property to CompareAllSearchValues, the CommandBuilder will generate the following UPDATE statement:

UPDATE [Products]
SET    [ProductName] = @p1, [CategoryID] = @p2,
       [UnitPrice] = @p3, [UnitsInStock] = @p4,

[UnitsOnOrder] = @p5
WHERE  (([ProductID] = @p6) AND ([ProductName] = @p7)
       AND ((@p8 = 1 AND [CategoryID] IS NULL) OR
       ([CategoryID] = @p9)) AND
       ((@p10 = 1 AND [UnitPrice] IS NULL) OR
       ([UnitPrice] = @p11)) AND
       ((@p12 = 1 AND [UnitsInStock] IS NULL) OR
       ([UnitsInStock] = @p13)) AND
       ((@p14 = 1 AND [UnitsOnOrder] IS NULL) OR
       ([UnitsOnOrder] = @p15)))

This is a lengthy statement indeed. The row to be updated is identified by its ID, but the operation doesn't take place if any of the other fields don't match the value read into the DataSet. This statement will fail to update the corresponding row in the Products table if it has already been edited by another user.

The last member of the ConflictOption enumeration, the CompareRowVersion option, works with tables that have a TimeStamp column, which is automatically set to the time of the update. If the row has a time stamp that's later than the value read when the DataSet was populated, it means that the row has been updated already by another user and the UPDATE statement will fail.

The SimpleDataSet sample project, which is discussed later in this chapter and demonstrates the basic DataSet operations, generates the UPDATE/INSERT/DELETE statements for the Categories and Products tables with the help of the CommandBuilder class and displays them on the form when the application starts. Open the project to examine the code, and change the setting of the ConflictOption property to see how it affects the autogenerated SQL statements.

Each row in the DataSet has a State property. This property indicates the row's state, and its value is a member of the DataRowState enumeration, whose members are described in Table 16.2.

You can use the GetChanges method to find the rows that must be added to the underlying table in the database, the rows to be updated, and the rows to be removed from the underlying table.

If you want to update all rows of a DataTable, call an overloaded form of the DataAdapter Update method, which accepts as an argument a DataTable and submits its rows to the database. The edited rows are submitted through the UpdateCommand object of the appropriate DataAdapter, the new rows are submitted through the InsertCommand object, and the deleted rows are submitted through the DeleteCommand object.

Instead of submitting the entire table, however, you can create a subset of a DataTable that contains only the rows that have been edited, inserted, or deleted. The GetChanges method of the DataTable object retrieves a subset of rows, depending on the argument you pass to it, and this argument is a member of the DataRowState enumeration:

Dim DT As New DataTable =
    Products1.Products.GetChanges(DataRowState.Deleted)

This statement retrieves the rows of the Customers table that were deleted and stores them in a new DataTable. The new DataTable has the same structure as the one from which the rows were copied, and you can access its rows and their columns as you would access any DataTable of a DataSet. You can even pass this DataTable as an argument to the appropriate DataAdapter's Update method. This form of the Update method allows you to submit selected changes to the database.

In addition to a state, rows have a version. What makes the DataSet such a powerful tool for disconnected applications is that it maintains not only data but also the changes in its data. The Rows property of the DataTable object is usually called with the index of the desired row, but it accepts a second argument, which determines the version of the row you want to read:

DS.Tables(0).Rows(i, version)

This argument is a member of the DataRowVersion enumeration, whose values are described in Table 16.3.

If you attempt to submit an edited row to the database and the operation fails, you can give the user the option to edit the row's current version or to restore the row's original values. To retrieve the original version of a row, use an expression like the following:

DS.Tables(0).Row(i, DataRowVersion.Original)

Although you can't manipulate the version of a row directly, you can use the AcceptChanges and RejectChanges methods to either accept the changes or reject them. These two methods are exposed by the DataSet, DataTable, and DataRow classes. The difference is the scope: Applying RejectChanges to the DataSet restores all changes made to the DataSet (not a very practical operation), whereas applying RejectChanges to a DataTable object restores the changes made to the specific table rows; applying the same method to the DataRow object restores the changes made to a single row.

The AcceptChanges method sets the original value of the affected row(s) to the proposed value. Deleted rows are physically removed. The RejectChanges method removes the proposed version of the affected row(s). You can call the RejectChanges method when the user wants to get rid of all changes in the DataSet. Notice that after you call the AcceptChanges method, you can no longer update the underlying tables in the database, because the DataSet no longer knows which rows were edited, inserted, or deleted. Call the AcceptChanges method only for DataSets you plan to persist on disk and not submit to the database.