In this section, you'll create a typed DataSet with the three basic tables of the Northwind database: Products, Categories, and Suppliers. Create a new Windows Forms Application project, the DataSetOperations project. (This is the same name as the sample project you can download from www.sybex.com/go/masteringvb2010.) Then open the Data menu, and choose the Add Data Source command. You will see the Data Source Configuration Wizard, which will take you through the steps of building a DataSet at design time. In the first page of the wizard, you'll be asked to select the data source type; it can be a database, a service (such as a web service), or an object, as in Figure 18.1. Select the Database icon, and click the Next button.

The Service option on the page shown in Figure 18.1 creates a DataSet that retrieves its data from a service (usually a web service). The Object option allows you to create a DataSet from a collection of custom objects.

On the next page of the wizard, shown in Figure 18.2, you must specify a connection string for the database you want to use. Click the New Connection button to create a new connection only if there's no connection for the database you want to use. If you've experimented already with the visual tools of Visual Basic, you may already have an existing connection, in which case you simply select it from the drop-down list.

Figure 18.1. Choosing the data source type in the Data Source Configuration Wizard

Figure 18.2. Choosing your data connection in the Data Source Configuration Wizard

To create a new connection, you must specify your credentials: whether you'll connect with a username and password or use Windows authentication. Once connected to the server, you can select the desired database from a ComboBox control. If you click the New Connection button to create a new connection to the Northwind database, you will see the Add Connection dialog box, as shown in Figure 18.3. This dialog box is not new to you; it's the same dialog box you use to create a new data connection in Server Explorer window. If you haven't created a connection to the Northwind database yet, do it now. Otherwise, select the existing connection.

Figure 18.3. Using the Add Connection dialog box to specify a new connection to the Northwind database

It's recommended that you use Windows authentication to connect to the database. If this isn't possible because the database server is not in the local network, you must specify a username and password in the boxes shown in Figure 18.3. In this case, the wizard will ask whether you want to store sensitive information (the account's password) to the connection string. You can choose to either include the password in the connection string (not a very safe approach) or supply it from within your code. You can always set the Password property of a Connection object in your code. To secure the password, you can prompt the user for a password when the application starts and save it to an application variable. This way, the password isn't persisted anywhere; it only exists in computer memory as long as the application is running. Alternatively, you can store an encrypted version of the password and decrypt and use it from within your code. The best approach for a local network is to use Windows authentication.

Click the OK button to close the Add Connection dialog box, and then click Next; you will see a dialog box with the default connection name: NorthwindConnectionString. This is the name of a new application setting that will store the connection information. You can edit this, as well as the other application settings, on the Settings tab of the project's Properties pages. (To see the project's Properties pages, choose Project Properties from the Project menu.)

Click Next again, and you will see the Choose Your Database Objects page, shown in Figure 18.4, where you can select the tables and columns you want to load to the DataSet. Notice that you can't use a SELECT statement to select the data you want from a table: You must select the entire table. Of course, you can write a stored procedure to limit your selection and then select it in the dialog box. If you select multiple tables and they're related, the relationship between them will also be imported for you (no need to create a DataRelation object for each relationship between the DataSet tables). For this example, select the Categories, Suppliers, and Products tables of the Northwind database. Select all columns of the Products and Categories tables, except for the Picture column of the Categories table. From the Suppliers table, select the SupplierID and CompanyName columns.

At the bottom of the dialog box shown in Figure 18.4, you can specify the name of the DataSet that will be generated for you. I'll use the default name, NorthwindDataSet. Click Finish to close the wizard and create the DataSet, which will be added automatically to the Solution Explorer window.

In the Data Sources window, shown in Figure 18.5, you will see a tree that represents the tables in the DataSet. The DataSet contains three DataTables, and each DataTable is made of the columns you selected in the wizard. This DataSet is typed, because it knows the structure of the data you're going to store in it. The interesting part of this tree is that it contains the Products table three times: in the first level along with the Categories table, as its own table, and once again under the Categories and Suppliers tables. (You must expand these tables to see it, as in Figure 18.5.) Whereas the Products table on the first level represents the entire table, the nested ones represent the products linked to their categories and their suppliers, respectively. You will see later in the chapter how to use the multiple Products DataTables.

The typed DataSet is actually a class that is generated on the fly. It's no longer a generic DataSet we will populate at runtime with any table we want through a DataAdapter; it's a specific object that can be populated only with the tables we specified in its design. If you want to see the code of the class generated by the wizard, click the Show All Files button in Solution Explorer window, and double-click the NorthwindDataSet.Designer.vb item under the Northwind DataSet. You shouldn't edit the code, because if you decide to edit the DataSet (you'll see how you can edit it with visual tools), the wizard will create a new class, and your changes will be lost. If you want to add some custom members to the Northwind class, create a new Partial class with the custom members, and name it NorthwindDataSet.vb.

Figure 18.4. Selecting the tables and columns you want to include in your typed DataSet

Figure 18.5. The Northwind DataSet created by the Data Source Configuration Wizard

Let's exercise the members of the typed DataSet a little and see how the typed DataSet differs from the equivalent untyped DataSet. The operations we'll perform are similar to the ones we performed in the preceding chapter with an untyped DataSet; you should focus on the different syntax. The code shown in this section belongs to the DataSetOperations sample project. This project contains three forms, and you will have to change the project's Startup object to view each one.

Figure 18.6 shows Form1 of the project, which demonstrates the basic operations on a typed DataSet, including how to populate it, edit some of its tables, and submit the changes to the database. They're basically the same operations you'd perform with an untyped DataSet, but you will see that it's much simpler to work with typed DataSets.

To populate the three DataTables of the DataSet, you need three DataAdapter objects. Instead of the generic DataAdapter, the class generated by the wizard has created a TableAdapter class for each DataTable: the CategoriesTableAdapter, SuppliersTableAdapter, and ProductsTableAdapter classes. Declare three objects of the corresponding type at the form level:

Dim CategoriesTA As New _
  NorthwindDataSetTableAdapters.CategoriesTableAdapter
Dim SuppliersTA As New _
  NorthwindDataSetTableAdapters.SuppliersTableAdapter
Dim ProductsTA As New _
  NorthwindDataSetTableAdapters.ProductsTableAdapter

Figure 18.6. Form1 of the DataSetOperations project demonstrates the basic operations on a typed DataSet.

The classes create the three objects that will retrieve the data from the database and submit the edited rows to the database from the SELECT statements you specified with point-and-click operations. These objects derive from the TableAdapter class, which in turn is based on the DataAdapter class. If you examine the code of the Northwind class, you will find the code that creates the SQL statements for querying and updating the three tables and how these statements are used to create a DataAdapter object. The code is similar to the code we used in the preceding chapter to create DataAdapters from within our code.

You must also create a DataSet object to store the data. This time, however, you can use a specific type that describes the structure of the data you plan to store at the client, not a generic DataSet. Insert the following declaration at the form level:

Dim DS As New NorthwindDataSet

Now place the Populate Tables button on the form, and insert the code shown in Listing 18.1 in its Click event handler.

Private Sub bttnPopulate_Click(...) Handles bttnPopulate.Click
    Dim categories As Integer = CategoriesTA.Fill(DS.Categories)
    Dim suppliers As Integer = SuppliersTA.Fill(DS.Suppliers)
    Dim products As Integer = ProductsTA.Fill(DS.Products)

As you can see, the Fill method doesn't accept any DataTable as an argument; instead, the type of its argument is determined by the TableAdapter object to which it's applied. The Fill method of the ProductsTA TableAdapter accepts as an argument an object of the ProductsDataTable type. The event handler of the sample project includes a few more statements that print the count of rows in each of the three tables.

To go through the rows of the Products table, write a simple loop like the following:

Dim prod As NorthwindDataSet.ProductsRow
For Each prod In DS.Products.Rows
    TextBox1.AppendText(prod.ProductName & vbTab &
         prod.UnitPrice.ToString("#,###.00") & vbCrLf)
Next

As you can see, the names of the fields are properties of the ProductsRow class. Some products may have no price (a Null value in the database). If you attempt to access the UnitPrice property of the ProductsRow class, a NullReferenceException exception will be thrown. To prevent it, you can make sure that the field is not Null from within your code, with an IIf function like the following:

TextBox1.AppendText(
        IIf(prod.IsUnitPriceNull,
        "Not for sale!",
        prod.UnitPrice.ToString("#,###.00")))

To read data from linked tables in a hierarchical way, you don't have to specify the relationship between the tables as you did with untyped DataSets, because the typed DataTables expose the appropriate methods.

Now place another button on your form, the Read Products By Supplier button, and in its Click handler insert the code shown in Listing 18.2 to iterate through suppliers and related products. Notice that the SuppliersRow class exposes the GetProductsRows method, which retrieves the Products rows that are associated with the current supplier. The GetProductsRows method is equivalent to the GetChildRows of an untyped DataSet, only with the latter you have to supply a relationship name as an argument. Moreover, the GetProductsRows method returns an array of ProductsRow objects, not generic DataRow objects.

Private Sub bttnSuppliersProducts_Click(...) Handles
                       bttnSuppliersProducts.Click
    TextBox1.Clear()
    Dim supp As NorthwindDataSet.SuppliersRow
    For Each supp In DS.Suppliers.Rows
        TextBox1.AppendText(supp.CompanyName & vbCrLf)
        Dim prod As NorthwindDataSet.ProductsRow
        For Each prod In supp.GetProductsRows
            TextBox1.AppendText(vbTab &
                  prod.ProductName & vbTab &

prod.UnitPrice.ToString("#,###.00") & vbCrLf)
        Next
    Next
End Sub

The ProductsRow object exposes the SuppliersRow and CategoriesRow methods, which return the current product's parent rows in the Suppliers and Categories DataTables.

The most useful method of the typed DataTable is the FindByID method, which locates a row by its ID in the DataTable. To locate a product by its ID, call the FindByProductID method, passing a product ID as an argument. The method returns a ProductsRow object that represents the matching product. The method's return value is not a copy of the found row but instead is a reference to the actual row in the DataTable, and you can edit it. The code behind the Update Products button, which is shown in Listing 18.3, selects a product at random by its ID and prompts the user for a new price. Then it sets the UnitPrice field to the user-supplied value.

Private Sub bttnUpdate_Click(...) Handles bttnUpdate.Click
    Dim selProduct As NorthwindDataSet.ProductsRow
    Dim RND As New System.Random
    selProduct = DS.Products.FindByProductID(RND.Next(1, 77))
    Dim newPrice As Decimal
    newPrice = Convert.ToDecimal(InputBox(
                  "Enter product's new price",
                   selProduct.ProductName,
                   selProduct.UnitPrice.ToString))
    selProduct.UnitPrice = newPrice
End Sub

As you can see, manipulating the rows of typed DataTables is much simpler than the equivalent operations with untyped DataSets, because the fields are exposed as properties of the appropriate class (the ProductsRow class for rows of the Products DataTable, the CategoriesRow class for rows of the Categories DataTable, and so on).

Let's look at the code for updating the database. The first step is to retrieve the edited rows with the GetChanges method, which returns a typed DataTable object, depending on the DataTable to which it was applied. To retrieve the modified rows of the Products DataTable, use the following statements:

Dim DT As NorthwindDataSet.ProductsDataTable
DT = DS.Products.GetChanges

You can pass an argument of the DataRowState type to the GetChanges method to retrieve the inserted, modified, or deleted rows. Because this is a typed DataSet, you can write a For Each loop to iterate through its rows (they're all of the ProductsRow type) and find the edits. One feature you'd expect to find in a typed DataTable is a method for retrieving the original versions of a row by name. Unfortunately, the class generated by the wizard doesn't include such a method; you must use the Item property, passing as an argument the name of the row. A row's original field versions are given by the following expression:

prod.Item("UnitPrice", DataRowVersion.Original)

To submit the edited rows to the database, you can call the appropriate TableAdapter Update method. The code behind the Submit Edits button does exactly that, and it's shown in Listing 18.4.

Private Sub bttnSubmit_Click(...) Handles bttnSubmit.Click
    If DS.HasChanges Then
        Dim DT As NorthwindDataSet.ProductsDataTable =
            DS.Products.GetChanges
        If DT IsNot Nothing Then
            Try
                ProductsTA.Update(DT)
            Catch ex As Exception
                MsgBox(ex.Message)
                Exit Sub
            End Try
            MsgBox(DT.Rows.Count.ToString &
                   " rows updated successfully.")
        End If
    End If
End Sub

Typed DataSets are quite convenient when it comes to coding. The real advantage of typed DataSets is that they can simplify enormously the generation of data-bound forms, which is the main topic of this chapter.

To understand the functionality of the BindingSource class, look up its members. Enter its name and the following period in the code window, and you will see a list of members. The Position property reads (or sets) the current item's index in the underlying DataTable. The DataGridView control doesn't maintain the order of the rows in the underlying table; besides, you can sort the DataGridView control rows in any way you like, but the DataTable rows won't be sorted. Use the Position property to find out the index of the selected row in the DataTable. The MoveFirst, MovePrevious, MoveNext, and MoveLast methods are simple navigational tools provided by the BindingSource class. You can place four buttons on the form and insert a call to these methods to move to the first, previous, next, and last rows, respectively. The four navigational buttons at the lower-left corner of the form shown in Figure 18.7 call these methods to select another row on the grid.

The two most interesting members of the BindingSource class are the Find method and the Filter property. The Filter property is set to an expression similar to the WHERE clause of a SQL statement to filter the data on the grid. Place a new button on the form, set its caption to Filter, set its name to bttnFilter, and insert the following statements in its Click event handler to filter the rows of the grid with their product names:

Private Sub bttnFilter_Click(...) Handles bttnFilter.Click
    Dim filter As String
    filter = InputBox("Enter product name, or part of it")
    ProductsBindingSource.Filter =
         "ProductName LIKE '%" & filter.Trim & "%'"
End Sub

Run the application, and click the Filter button to limit the rows displayed on the grid by their product names. If you're searching for products that contain the string sauce in their name, the Filter property limits the selection as if you had requested products with the following WHERE clause (the percent sign is a SQL wildcard that matches any string):

WHERE ProductName LIKE '%sauce%'

To restore the original selection, set the filter expression to a blank string. You can design an auxiliary form on which users can enter multiple criteria and filter products by their price or stock, their supplier, and so on. With a bit of programming effort, you can apply multiple criteria, such as products of a specific category that are on order, out-of-stock items from a specific supplier, and so on.

The Find method searches a value in a specific column. Both the column name and search argument are specified as arguments to the method, and the return value is the row's position in the DataTable. To select the row, set the BindingSource object's Position property to the value returned by the Find method. The code behind the Find button in the sample project is the following:

Dim search As String
search = InputBox("Enter exact product name")
Dim idx As Integer =
      ProductsBindingSource.Find("ProductName", search)
ProductsBindingSource.Position = idx

The Find method is not the most convenient search tool, because you have to specify the exact value of the field you're looking for. To retrieve the current row in the DataTable mapped to the BindingSource, use the Current property; to determine the number of rows in the same DataTable, read the value of the Count property. The Current property returns an object, which you must cast to the DataRowView type and call its Row property:

CType(ProductsBindingSource.Current, DataRowView).Row

This expression returns a DataRow object, which you can cast to a ProductsRow type. You will see examples of using the Current property of the BindingSource class to access the underlying row in the DataTable later in this chapter.

Adjusting the DataSet

To adjust the properties of the DataSet, right-click the DataSet in the Data Sources window, and choose Edit DataSet With Designer from the context menu. The DataSet Designer window, shown in Figure 18.9, will appear.

Right-click the header of the Discontinued column in the Products table, and choose Properties to see the properties of the DataColumn. One of them is the DefaultValue property, which is set by default to Null. Change it to 0 or False to impose a default value for this column. In the DataSet Designer, you can examine the data types of the columns of each table, drop or create new relations between tables, and set other interesting properties, such as the Caption property of a column, which will be used to name the column of the bound DataGridView control, or the NullValue property, which determines how the DataSet will handle Null values. The default value of the NullValue property is Throw Exception. Every time the application requests the value of a Null field, a runtime exception is thrown. You can set it to Empty (in which case an empty string is returned) or Nothing (in which case a Nothing value is returned). You can also set the autoincrement values of Identity columns here. If you select the ProductID column in the Products table, you'll see that the wizard has set the column's AutoIncrementSeed and AutoIncrementStep to −1, for the reasons explained already.

Figure 18.9. Editing the DataSet with visual tools

While you're in the DataSet Designer, right-click the Product DataTable, and choose Configure. This starts the TableAdapter Configuration Wizard. The first page of the wizard, shown in Figure 18.10, shows the SQL statement that the Data Source Configuration Wizard generated while you were selecting the columns you wanted to include in the DataSet. You can edit this statement by adding more columns, a WHERE clause to limit the number of rows to be selected, and an ORDER BY clause. To edit the SELECT statement, you modify it right on this page, or you can click the Query Builder button to view the Query Builder dialog box that lets you specify complicated queries with visual tools.

Figure 18.10. Editing the SELECT statement that populates the Products DataTable with the TableAdapter Configuration Wizard

If you click the Advanced Options button, you will see the Advanced Options dialog box, shown in Figure 18.11. Here you must specify which statements should be generated by the wizard. If you're developing a browser application, deselect the first check box: Generate Insert, Update, And Delete Statements. If you clear this option, the other two options will be disabled.

Figure 18.11. The Advanced Options dialog box lets you know how the TableAdapter will submit the updates to the underlying tables.

The Use Optimistic Concurrency option affects the UPDATE and DELETE statements generated by the wizard. If this check box is selected, the two statements will not update or delete a row if it has been edited by another user since it was read. The wizard will generate two long statements that take into consideration the values read from the database into the DataSet at the client (the row's original values), and if any of the row's columns in the database are different from the original version of the same row in the DataSet, it won't update or delete the row. By using optimistic concurrency, you're assuming that it's rather unlikely that two users will update the same row at the same time. If the row being updated has already been modified by another user, the update operation fails. If you clear this option, the UPDATE/DELETE statements take into consideration the row's primary key and are executed, even if the row has been modified since it was read. In effect, the last user to update a row overwrites the changes made by other users.

The last option in the Advanced Options dialog box specifies whether the TableAdapter reads back the inserted/updated rows. You should leave this check box selected so that the identity values assigned by the database to new rows will be read back and update the DataSet.

Real World Scenario: Implementing Optimistic Concurrency

Curious about the statements that take into consideration the original values of the row being updated? Here's the DELETE statement for the Products row that uses optimistic concurrency:

DELETE FROM [dbo].[Products]
WHERE (([ProductID] = @Original_ProductID) AND
      ([ProductName] = @Original_ProductName) AND

((@IsNull_SupplierID = 1 AND
              [SupplierID] IS NULL) OR
             ([SupplierID] = @Original_SupplierID)) AND
      ((@IsNull_CategoryID = 1 AND
              [CategoryID] IS NULL) OR
             ([CategoryID] = @Original_CategoryID)) AND
      ((@IsNull_QuantityPerUnit = 1 AND
              [QuantityPerUnit] IS NULL) OR
             ([QuantityPerUnit] = @Original_QuantityPerUnit)) AND
      ((@IsNull_UnitPrice = 1 AND
              [UnitPrice] IS NULL) OR
             ([UnitPrice] = @Original_UnitPrice)) AND
      ((@IsNull_UnitsInStock = 1 AND
              [UnitsInStock] IS NULL) OR
             ([UnitsInStock] = @Original_UnitsInStock)) AND
      ((@IsNull_UnitsOnOrder = 1 AND
             [UnitsOnOrder] IS NULL) OR
            ([UnitsOnOrder] = @Original_UnitsOnOrder)) AND
      ((@IsNull_ReorderLevel = 1 AND
             [ReorderLevel] IS NULL) OR
            ([ReorderLevel] = @Original_ReorderLevel)) AND
      ([Discontinued] = @Original_Discontinued))

The same statement with the optimistic concurrency off is quite short:

DELETE FROM [dbo].[Products]
WHERE ([ProductID] = @Original_ProductID)

Examine the long statement to understand how optimistic concurrency is handled. The first term in the WHERE clause locates a single row based on the product ID, which is unique. This is the row that should be deleted (or updated by the UPDATE statement). However, the statement will not select the row if any of its fields have been edited since you read it from the database. If another user has changed the price of a specific product, the following term will evaluate to false, and the WHERE clause will return no row to be deleted.

When you return to the configuration wizard, click Next, and you will see the dialog box shown in Figure 18.12, where you can specify the methods that the wizard will generate for you. The Fill method populates a DataTable, and the GetData method returns a DataTable object with the same data; the last option in the dialog box specifies whether the DataSet will expose methods for inserting/updating/deleting rows directly against the database.

Click Next again, and the wizard will regenerate the NorthwindDataSet class, taking into consideration the options you specified in the steps of the wizard.

In the following section, we'll let the editor build simple data-driven applications for us. You're going to see how to bind other controls to typed DataSets and how to customize the DataGridView control.

Figure 18.12. Selecting the methods to be generated by the TableAdapter Configuration Wizard

The navigational tools on the BindingNavigator are quite primitive. Let's enhance the toolbar at the top of the form by adding two buttons, the Filter and Find buttons of the preceding section. Stop the application, and open Form3 in design mode.

To add a new element to the ToolBar control, expand the combo box that's displayed at design time after the existing elements. From the drop-down list, select the Button item to add a Button control to the toolbar. Select the newly added button, and set its DisplayStyle property to Text. (Normally this property is set to Image, because the items on a toolbar are identified by an icon; you should find a few good icons and use them in your applications.) Set its Text property to Filter and its name to bttnFilter. Follow similar steps for the Find button as well. Then copy the code of the two buttons in Form2, and paste it in the Click event handlers of the two ToolStrip buttons. You just added a filtering and search feature to your application.

The Find feature isn't very practical with product names, because users have to specify the full and exact product name. This feature should be used with fields such as IDs, book ISBNs, and email addresses. To find a product by name, most users would use the Filter button to limit the selection on the grid and then locate the desired product.

You can also use two TextBox controls in place of the two Button controls on the toolbar. If you'd rather allow users to enter their search and filter arguments on the toolbar, you must intercept the Enter keystroke from within the respective control's KeyUp event and call the same code to filter or search the rows of the grid.

Now, add yet another button to the toolbar, and set its caption to Refresh Data. This button will reload the data from the database by calling the Fill method of the TableAdapter. Before loading the data, however, you must make sure that the DataSet doesn't contain any changes by examining the HasChanges property. If it's True, you must prompt the user accordingly. Notice that if a row failed to update the database, the DataSet will contain changes, even though the edits were submitted to the database. Some of the changes can be undone, but not all of them. A deleted row, for example, is no longer visible on the control, and users can't restore it. Listing 18.5 shows the code behind the Refresh Data button.

Private Sub bttnRefreshData_Click(...) Handles bttnRefreshData.Click
    If NorthwindDataSet.HasChanges Then
        Dim reply As MsgBoxResult =
             MsgBox("The DataSet contains changes." &
             vbCrLf & "Reload data anyway?",
             MsgBoxStyle.YesNo Or MsgBoxStyle.Exclamation)
        If reply = MsgBoxResult.No Then Exit Sub
    End If
    Me.ProductsTableAdapter.Fill(
                Me.NorthwindDataSet.Products)

End Sub

We developed a fairly functional application for browsing and editing one of the basic tables of the Northwind database, the Products table. The interface of the application is a bit rough around the edges (that's the least you can say about an interface that displays category and supplier IDs instead of category and supplier names), but we'll come back and adjust the interface of the application in a moment. First, I'd like to discuss another way of using the DataGridView control, namely, how to bind related tables to two or more DataGridView controls. This arrangement is the most common one, because we rarely work with a single table.

In this section, you'll build an interface to display categories and products on two DataGridView controls, as shown in Figure 18.15. The top DataGridView control is bound to the Categories DataTable and displays all the category rows. The lower DataGridView control displays the products of the category selected in the top control. In effect, you'll create two DataGridView controls linked together.

Figure 18.15. Viewing related data on two DataGridView controls with the LinkedDataTables application

Follow these steps:

Now you can run the application and see how it behaves. Every time you select a category, the selected category's products appear in the lower grid. If you change the CategoryID field of a product, that particular product disappears from the grid, as expected, since it belongs to another category after the CategoryID changes. You must select the category with the CategoryID you assigned to a product to see the product in the Products grid. Obviously, this is not the best way to edit the category of a product. You will see how to handle lookup tables in the "Displaying Lookup Columns in a DataGridView Control" section later in the chapter.

Experiment with the new interface. Start editing the two tables on the form. Add new categories, and then add products that belong to these categories. If you attempt to delete a category, the DataGridView will happily remove the row from its table. But didn't the DataSet pick the relationships from the database's definition? Now that both tables are at the client as DataTables with a relationship between them, shouldn't the DataSet reject this operation?

Let's take a look at the properties of the relationship between the two tables. Right-click the DS DataSet in the Data Sources window, and from the context menu, select Edit DataSet In Designer to see the DataSet Designer window. Right-click the line that connects the Products and Categories tables (this line represents the relationship between the two tables), and select Edit Relation to open the Relation dialog box, shown in Figure 18.16.

Figure 18.16. Setting relation properties

The FK_Products_Categories relation is marked as Relation Only. In the database, this is a relation and a foreign key constraint. The relation simply relates the two tables if their CategoryID fields match. Most importantly, the constraint won't let you insert a product that points to a nonexisting category or delete a category that has related rows in the Products table. Select the radio button Both Relation And Foreign Key Constraint, and then close the dialog box.

Try to run the application. If you receive the following error:

"ConstraintException was unhandled: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints."

right after running the application, make sure that the data is loaded in the right order in the form Load event. The Categories DataTable should be filled first, and the Products DataTable should be filled second:

Private Sub Form1_Load(...) Handles MyBase.Load
    Me.CategoriesTableAdapter.Fill(Me.DS.Categories)
    Me.ProductsTableAdapter.Fill(Me.DS.Products)
End Sub

Foreign key constraints are subject to three rules: the Update, Delete, and Accept/Reject rules, as shown in Figure 18.16. These rules determine what should happen when a parent row is removed from its table (the Delete rule), when a parent ID is modified (the Update rule), and when users accept or reject changes in the DataSet (the last rule). A rule can be set to None (no action is taken, which means that a runtime exception will be thrown), Cascade (the child rows are updated or deleted), SetNull (the foreign keys of the related rows are set to Null), and SetDefault (the foreign keys of the related rows are set to their default value).

I usually don't change the rules of a relationship in the DataSet, unless I've used rules in the database. Leave them set to None, and run the application again. You should avoid setting the Delete and Update rules to Cascade, because this can lead to irrecoverable errors. If you delete a category, for example, it will take with it the related products, and each deleted product will take with it the related rows in the Order Details table. A simple error can ruin the database. There are other situations, which aren't as common, where the Cascade rule can be used safely. When you delete a book in the Pubs database, for example, you want the book's entries in the TitleAuthors table to be removed as well. No rows in the Authors table will be removed, because they're primary, and not foreign, keys in the relation between the TitleAuthors and Authors tables.

Let's return to the interface for editing products and categories. Attempt again to remove a category. This time you'll get a lengthy error message that ends with the following suggestion:

To replace this default dialog please handle the DataError event.

Let's do exactly that to avoid displaying a totally meaningless message to our users. Open the DataError event handler for both controls, and insert the following statement:

MsgBox(e.Exception.Message)

Run the application again, and delete a category. This time you'll see the following error message, and the program won't remove the category from the DataGridView, because it can't remove it from its DataSet:

Cannot delete this row because constraints are enforced
  on relation FL_Products_Categories, and deleting this
  row will strand child rows.

You can still delete products and set their prices to negative values. These two operations are invalid in the context of the database but quite valid in the client DataSet.

Dim categoriesBS As New BindingSource
categoriesBS.DataSource = DS
categoriesBS.DataMember = "Categories"

Dim productsBS As New BindingSource
productsBS.DataSource = categoriesBS

productsBS.DataMember = "FK_Categories_Products"

DataGridView1.DataSource = categoriesBS
DataGridView2.DataSource = productsBS

The DataGridView control is bound to a single table as soon as you drop the table on the form, but its default appearance leaves a lot to be desired. To begin with, we must set the widths of the columns and hide certain columns. The product IDs, for example, don't need to be displayed. The numeric fields should be formatted properly and aligned to the right, and the foreign key fields should be replaced by the corresponding descriptions in the primary table. We also should display category and supplier names, instead of IDs.

The DataGridView control exposes a large number of properties, and you can experiment with them in the Properties window. They have obvious names, and you can see the effects of each property on the control as you edit it. Beyond the properties that apply to the entire control, you can also customize the individual columns through the Edit Columns dialog box.

To tweak the appearance of the columns of the DataGridView control, select the control with the mouse, and open the Tasks menu by clicking the little arrow in the upper-right corner of the control. This menu contains four check boxes that allow you to specify whether the user is allowed to add/edit/delete rows and reorder columns. To adjust the appearance of the grid's columns, click the Edit Columns hyperlink in the menu, and you will see the Edit Columns dialog box, shown in Figure 18.17, where you can set the properties for each column.

Figure 18.17. Use the Edit Columns dialog box to customize the appearance of the DataGridView control.

In the Edit Columns dialog box, you can set each column's header and width, as well as the minimum width of the column. (Users won't be allowed to make the column narrower than its minimum width.) You can also set the AutoSize property to True to let the control decide about column widths, but this may result in a very wide control. You can lock certain columns during editing by setting their ReadOnly property, or you can make other columns invisible with the Visible property.

To make the appearance of two grids more uniform on the Products per Category form in your LinkedDataTables projects, make both grids of the same width. This will leave the Categories grid with some empty, grayed-out space, since it has a smaller number of columns than the Products grid. This space can be easily covered by Category's Description property. Open the Edit Columns dialog box, and set the AutoSizeMode of the Description column to Fill.

The most interesting setting in this dialog box is the ColumnType property, which is the type of the column. By default, all columns are of the DataGridViewTextBoxColumn type, unless the corresponding field is a Boolean or Image type. A DataGridView column can be one of the following types:

Notice that as you change the style of a column, the Bound Column Properties pane of the Edit Columns dialog box is populated with the properties that apply to the specific column type. For a combo box column, for example, you can set the DropDownWidth and MaxDropDownItems properties. You can even populate the combo box with a set of values through the Items property, just as you would with a regular combo box on the form.

There aren't any properties in the Edit Columns dialog box to adjust the appearance of the selected column. To change the appearance of a column, select the DefaultCellStyle property, and click the button with the ellipses next to it to see the CellStyle Builder dialog box, which is shown in Figure 18.19.

In the CellStyle Builder dialog box, you can set the column's font, set its foreground and background colors, specify whether the text should wrap in its cell, and determine the alignment of the cell's contents. Turning on the wrap mode with the WrapMode property doesn't cause the rows to be resized automatically to the height of the tallest cell. To have rows resized automatically, you must set the control's AutoSizeRowsMode property to All. The other possible settings for this property are None, AllHeaders, AllCellsExceptHeaders, DisplayedHeaders, DisplayedCellsExceptHeaders, and DisplayedCells. Finally, you must set the Format property for all numeric and date fields and size the columns according to the data that will be displayed on the control.

Figure 18.19. Use this dialog box to adjust the appearance of a column in a DataGridView control.

This feast of customization techniques is possible because the DataGridView control is bound to a typed DataSet. If you want to use the techniques of the previous chapter to bind a DataGridView control to an untyped DataSet, you can still use the Edit Columns dialog box to add and customize the control's columns, but the process isn't nearly as convenient. You must also remember to set the control's AutoGenerateColumns property to False and each column's DataPropertyName property to the name of the database column that will be mapped to the corresponding grid column. If the AutoGenerateColumns property is left to its default value, which is True, the control will generate a new column for each data column in its data source.

Displaying Lookup Columns in a DataGridView Control

In this section, you're going to change the way a product's category and supplier are displayed. Instead of IDs, you must display category and supplier names. Moreover, you will display each product's category and supplier in a ComboBox control so that users can quickly select another value when editing the product. Let's return to the LinkedDataTables project and set up the bottom GridView control.

Select the DataGridView control with the products, and from the Task menu choose Edit Columns. In the Edit Columns dialog box, select the CategoryID column, and make it invisible by setting its Visible property to False. Then click the Add button to add a new column. In the Add Column dialog box, which is shown in Figure 18.20, click the Unbound Column radio button, and set the column's Name property to colCategory and its HeaderText property to Category. Click Add to add the column to the DataGridView control and then Close to close the dialog box.

Back in the Edit Columns dialog box, move the new column to the desired position by using the arrow buttons. You must now set up the new column so that it displays the name of the category that corresponds to the CategoryID column of the selected product. Locate the DataSource property, and click the arrow to expand the data sources. Select the CategoriesBindingSource entry. Then set the DisplayName property to CategoryName and the ValueMember to CategoryID. Click OK to close the dialog box. If you run the application now, you'll see that the CategoryID column has been replaced by the Category column, which displays a ComboBox with the list of all categories. However, the product's category isn't automatically selected; you have to drop down the items in the combo box to see the categories, and you still don't know the selected product's category. To link the appropriate category name to the selected product's CategoryID value, you must set yet another property, the DataPropertyName property. If you expand the list of available columns for this property, you'll see the columns of the Products table. Select the CategoryID column so that the combo box will display the category name that corresponds to the category ID of the selected product row. Now you have a much better interface for editing the products. You no longer need to enter IDs; you see the name of the selected product's category, and you can select a product's category from a drop-down list by using the mouse.

Figure 18.20. Adding a new column to the DataGridView control

Of course, you must do the same with the Suppliers table. Right-click the NorthwindDataSet object in the Data Sources window, and choose Edit DataSet With Designer from the context menu to open the DataSet in design mode.

To be able to display the meaningful column (typically the Name column) of the lookup table entry, you need to make the data in the lookup table available to the application. If the table with the lookup values isn't part of your DataSet, you can easily add a new DataTable to the DataSet. Right-click somewhere on the DataSet Designer's surface, and choose Add

In the case of lookup tables, you would choose the ID column so that the relationship can be maintained and the Name column whose value will be displayed in the grid. In your LinkedDataTables project, you added the Suppliers table when you created the DS DataSet.

Let's make the lookup table data available for an update operation on the Products grid. The following steps are similar to the steps performed when making categories displayed using the ComboBox inside the DataGridView in the previous section.

Hide the SupplierID column of the Suppliers table, and replace it on a grid with a ComboBox column that contains the names of the suppliers. Select the Edit Column command from the Products grid's Tasks menu, and set the properties for a newly added Suppliers column as listed in Table 18.2.

Notice that the ValueMember property is the SupplierID column of the Suppliers table, but the PropertyName property's value is the SupplierID column of the Products DataTable, because the control is bound to the Products table. The Designer will replace the name of the Suppliers DataTable with the SupplierBindingSource object. Run the application now, edit a few products, and then save the edits. You can also edit the categories (names and descriptions only). As a reminder, even the DataSet that was generated by the wizard doesn't enforce check constraints, and you can set the price of a product to a negative value. When you attempt to submit the changes to the database, a runtime exception will be thrown.

The code behind the Submit Edits button isn't new to you. The code sets the ContinueUpdateOnError property of the underlying DataAdapter objects to True and then calls the UpdateAll method of the TableAdapterManager to submit the changes made to all tables.

The rows that will fail to update the underlying tables in the database will be marked as errors. The DataGridView control marks the rows in error with an icon of an exclamation mark in the column's header, as shown earlier in Figure 18.14. If you hover over this icon, you'll see a description of the error in a ToolTip box.

Editing large tables on a grid isn't the most flexible method. In this section, you'll build an alternate interface for editing the Products table, as shown in Figure 18.21. This is the form of the Products sample project, which uses a data-bound ListBox control to display product names and text boxes for the individual fields. The toolbar at the top allows you to add new rows, delete existing ones, and submit the changes to the database. All the controls on the form are data bound, and the application contains very little code.

Figure 18.21. An alternate interface for editing the Products table

Here's how to get started:

Run the application, and see how it behaves. Every time you select a product in the list, the product's details appear in the data-bound text boxes, and its category and supplier are displayed in the two combo box controls. Use the list to navigate through the products, the Add button to add a new product, and the Delete button to delete a product. You can edit the product's fields on the form, and the edits will be written to the DataSet as soon as you move to another row.

You have created a functional application for selecting products and viewing their details. If all you need is a browsing application for the products, you can set the ReadOnly property of all TextBox controls on the form to True.

If you attempt to enter a new product and leave its Discontinued column set to Null, a runtime exception will be raised. This problem is easy to fix by specifying a default value for the Discontinued column. To do so, open the DataSet in the Designer, locate the Discontinued column in the Products table, and select Properties from the context menu. The DefaultValue property's value is DBNull. Set it to False so that unspecified fields will be automatically set to False.

You'll also get an error message if you attempt to submit the edits and the DataSet contains a product with a negative price. Such trivial errors can be caught and handled from within your code — no need to send the rows to the database and get an error message. Write a few statements to detect trivial errors, such as negative prices (or negative stocks, for that matter). First, decide how to handle these errors. Do you want to pop up a message box every time you detect an error condition? This will drive users crazy. Do you want to reject changes until the user enters a valid value? It's a better approach, but remember that data-entry operators don't look at the monitor. They expect that the Tab (or Enter) key will take them to the next field. The best approach is to do what the DataGridView control does: display an error icon next to the control in error.

Add an instance of the ErrorProvider control on the form. This control displays the exclamation mark icon next to a control. To display the error icon, you must call the control's SetError method, passing as arguments the control in error and the message for the error. (The message will be displayed in a ToolTip box when users hover over the icon.)

To detect errors from within your code, you need to insert some code in the CurrentItemChanged event handler of the ProductsBindingSource. Insert the statements shown in Listing 18.6 in this event handler.

Private Sub ProductsBindingSource_CurrentItemChanged(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles
            ProductsBindingSource.CurrentItemChanged
    If Not IsNothing(queryResult) AndAlso queryResult.Count < 1 Then Exit Sub
    ErrorProvider1.Clear()
    Dim product As NorthwindDataSet.ProductsRow
    product = CType(CType(
              ProductsBindingSource.Current,
              DataRowView).Row, NorthwindDataSet.ProductsRow)

If Not product.IsUnitPriceNull AndAlso
         Convert.ToDecimal(product.UnitPrice) < 0 Then
        ErrorProvider1.SetError(UnitPriceTextBox,
                  "PRICE CAN'T BE NEGATIVE!")
    End If
    If ProductNameTextBox.Text.Trim.Length = 0 Then
        If CType(ProductsBindingSource.Current,
           DataRowView).Row.RowState <>
           DataRowState.Detached Then
            ErrorProvider1.SetError(
                 ProductNameTextBox,
                 "PRODUCT NAME CAN'T BE BLANK!")
        End If
    End If
End Sub

This code segment requires some explanation. The CurrentItemChanged event is fired every time the user selects another row or column on the control. The code in this event handler retrieves the current row with the Current property of the ProductBindingSource object. This property returns an object, which is a DataRowView object. This is why I cast it to the DataRowView type, then retrieve its Row property, and finally cast it to the ProductsRow type. The product variable represents the currently selected row in the DataSet. This is a typed variable, and I can access the columns of the current row as properties. If the UnitPrice column has a negative value, the code sets an ErrorProvider control to display the error next to the corresponding text box.

Viewing the Deleted Rows

One unique aspect of this interface is that it provides a button to display the deleted rows. These rows exist in the DataSet, but they're not shown on the interface. The inserted and modified rows are on the ListBox control, and users can review them. You may even provide a button to display the old and new versions of the edited rows. But users have no way of reviewing the deleted rows. You can resolve this by showing the deleted rows in the auxiliary form:

1. Add a new button to the ProductsBindingNavigator ToolStrip, and set its name to bttnShowDeleted.

2. Set the text of the button to Show Deleted.

3. Add a new form (Form2) to the project.

4. Add a CheckListBox control to Form2.

5. Add a button called button1 to Form2, set the button's Text property to Restore Selected Rows, and close Form2 in the button's default event handler.

The Show Deleted Rows button in the ToolStrip opens an auxiliary form (Form2), like the one shown in Figure 18.22, which displays the deleted rows in a CheckedListBox control. Users are allowed to select some of the deleted rows and restore them.

Figure 18.22. Reviewing the deleted rows in the DataSet

Listing 18.7 shows the code that retrieves the deleted rows and displays them on the auxiliary form.

Example 18.7. Retrieving and displaying the deleted rows

Private Sub bttnShowDeleted_Click(...) Handles bttnShowDeleted.Click
    Form2.CheckedListBox1.Items.Clear()
    For Each row As DataRow In NorthwindDataSet.Products.Rows
        If row.RowState = DataRowState.Deleted Then
            Form2.CheckedListBox1.Items.Add(
            row.Item("ProductID",
            DataRowVersion.Original) & " " &
            row.Item("ProductName",
            DataRowVersion.Original))
        End If
    Next
    Form2.ShowDialog()
    Dim SelectedIDs As New ArrayList
    For Each itm As String In Form2.CheckedListBox1.CheckedItems
        SelectedIDs.Add(Convert.ToInt32(
           itm.Substring(0, itm.IndexOf(" ") + 1)))
    Next
    Dim cust As NorthwindDataSet.ProductsRow
    For Each cust In NorthwindDataSet.Products
        If cust.RowState = DataRowState.Deleted
            AndAlso SelectedIDs.Contains(cust.Item(
            "ProductID", DataRowVersion.Original)) Then
            cust.RejectChanges()
        End If
    Next
End Sub

The code goes through the Rows collection of the Products DataTable and examines the RowState property of each row. If its value is RowState.Deleted, it adds the row's ProductID and ProductName fields to the CheckedListBox control of the auxiliary form. Then it displays the form modally, and when the user closes it, the code retrieves the IDs of the selected rows into the SelectedIDs ArrayList. The last step is to restore the selected rows. The code goes through all rows again, examines their RowState properties, and if a row is deleted and its ID is in the SelectedIDs ArrayList, it calls the RejectChanges method to restore the row. The restored rows are automatically displayed in the ListBox control because this control is bound to the DataSet.

Real World Scenario: Handcrafting an Application's Interface

A practical feature you can add to the interface of a disconnected application is the ability to review the modifications. You can display the original and proposed versions of the inserted/modified rows, as well as the original versions of the deleted rows on an auxiliary form, as we have done in the preceding example.

If you don't mind writing a bit of code, you can display the original values of the edited rows in the same controls on your form. Because the controls are data bound, you can't display any different values on them; if you do, they'll be stored in the DataSet as well. But you can place nonbound controls, such as Label controls, in the place of the data-bound control. The Labels normally will be invisible, but when the user presses a function key, they can become visible, hiding the regular editable controls. When the user requests the original versions of the fields, you must populate the Labels from within your code and display them by toggling their Visible properties. You must also hide the regular controls on the form.

Another approach would be to suspend data binding momentarily by calling the SuspendBinding method of the BindingSource class. After data binding has been suspended, you can populate the text boxes on the form at will, because the values you display on them won't propagate to the DataSet. To display the bound values again, call the ResumeBinding method.

Another interesting feature you can add to a data-driven application is to display the state of the current row in the form's status bar. You can also give users a chance to undo the changes by clicking a button on the status bar. All you have to do in this button's Click event handler is to call the RejectChanges method on the current row. Use the Current property of the ProductsBindingSource to retrieve the current row and cast it to the ProductsRow type, and then call the RejectChanges method.

You can select one of the data-editing applications presented in this chapter, perhaps the Products application, and add as many professional features as you can to it. Start by adding a status bar to the form, and display on it the state of the current row. For modified rows, display a button on the toolbar that allows users to view and/or undo the changes to the current row. Program the form's KeyUp event handler, as explained in Chapter 4, "GUI Design and Event-Driven Programming," so that the Enter key behaves like the Tab key. Users should be able to move to the next field by pressing Enter. Finally, you can display an error message for the current row on a label in the form's status bar. Or display a message such as Row has errors as a hyperlink and show the actual error message when users click the hyperlink. Test the application thoroughly, and insert error handlers for all types of errors that can be caught at the client. Finally, make the edited rows a different color in the two DataGridView controls on the form of the LinkedDataTables sample project. To do so, you must insert some code in the control's RowValidated event, which is fired after the validation of the row's data. You'll need to access the same row in the DataSet and examine its RowState property by retrieving the Current property of the ProductsBindingSource object, as shown in Listing 18.8.

Example 18.8. Coloring the edited and inserted rows on the DataGridView control

Private Sub ProductsDataGridView_RowValidated(...)
            Handles ProductsDataGridView.RowValidated
    Dim row As DS.ProductsRow
        row = CType(CType(ProductsBindingSource.Current,
              DataRowView).Row, DS.ProductsRow)
    If row.RowState = DataRowState.Modified Then
        ProductsDataGridView.Rows(e.RowIndex).
             DefaultCellStyle.ForeColor = Color.Green
    Else
        ProductsDataGridView.Rows(e.RowIndex).
             DefaultCellStyle.ForeColor = Color.Black
    End If
    If row.RowState = DataRowState.Added Then
        ProductsDataGridView.Rows(e.RowIndex).
             DefaultCellStyle.ForeColor = Color.Blue
    Else
        ProductsDataGridView.Rows(e.RowIndex).
             DefaultCellStyle.ForeColor = Color.Black
    End If
End Sub

The code in the listing sets the foreground color of modified rows to green and the foreground color of inserted rows to blue. From within the same event's code, you can set the text of a Label on the form's status bar to the row's error description. If you run the application now, you'll see that it paints modified and inserted cells differently, but only while you're working with products of the same category. If you select another category and then return to the one whose products you were editing, they're no longer colored differently, because the RowValidated event is no longer fired. To draw rows differently, you must duplicate the same code in the control's RowPostPaint event as well.

If you carefully test the revised application, you'll realize that the DataGridView control doesn't keep track of modified rows very intelligently. If you append a character to the existing product name and then delete it (without switching to another cell between the two operations), the DataGridView control considers the row modified, even though the original and proposed versions are identical. As a result, the application will render the row in green. Moreover, the UpdateAll method will submit the row to the database.

For a truly disconnected application, you should give users a chance to store the data locally at the client. The LinkedDataTables application's main form contains two more buttons: the Save Data Locally and Load Local Data buttons. The first one saves the DataSet to a local file via the WriteXml method of the DataSet, and the second button loads the DataSet via the ReadXml method. The application uses the tmpData.#@# filename in the application's folder to store the data. It also uses an overloaded form of the two methods to accept an additional argument that stores not only the data but the changes as well. Here's the code behind the two buttons:

Private Sub Button1_Click(...) Handles Button1.Click
    DS.WriteXml("tmpData.#@#", XmlWriteMode.DiffGram)
End Sub

Private Sub Button2_Click(...) Handles Button2.Click
    ProductsBindingSource.SuspendBinding()
    DS.ReadXml("tmpData.#@#", XmlReadMode.DiffGram)
    ProductsBindingSource.ResumeBinding()
End Sub

Let's explore now how data binding can be used in combination with Language Integrated Query (LINQ) query syntax.

You are surely convinced by now that LINQ is a fundamental part of .NET query infrastructure. You have explored LINQ in detail in Chapter 14, "An Introduction to LINQ," where I also mentioned the LINQ to DataSet technology.

The great thing about LINQ is that once you learn how to write queries, you can apply the same syntax to any data source that supports LINQ. DataSets and typed DataSets also support LINQ, and you can apply the same syntax you saw used to query objects in LINQ to Objects and in LINQ to SQL to typed DataSets.

So far in this chapter you saw how data binding can make life a lot easier for a programmer developing data-centric applications. And you have already witnessed the power of LINQ. The two can be used in combination as a powerful tool for data manipulation.

mybindingSource.DataSource = (From product In NorthwindDataSet.Products
                               Select product).AsDataView()

Using LINQ to Filter Products Data

In cases where data operators have to manage a large number of entries, listing all of these on the form with navigation capability can prove not to be very practical. Users will be forced to spend a lot of time while navigating between records. In such cases, providing some filtering capability can prove to be very beneficial.

Let's add some filtering features to our form in the Products project. I will use following criteria for the filtering functionality:

• Category selected by user

• Units in stock less than the number entered by the user

• Units on order greater than the number entered by the user

This way, users will be able to filter products by category and by some important operational data. They will be able to see when the numbers of units in stock is low or what product units for an order are piling up. Figure 18.23 shows the new version of the Products interface.

Figure 18.23. Reviewing the deleted rows in the DataSet

You can use these criteria in any kind of combination. To know whether criteria should be applied or not, it is best to count on some special value. If the Units In Stock Less and Units On Order Greater text boxes are empty, then they should not be used in a query. For the category, the special value of the combo box is the text All. To add this value to Combobox, it has to be populated by your application and not through the data binding. The following code populates the Categories Combobox and is placed inside the Form_Load event handler:

cboCategoryFilter.DisplayMember = "CategoryName"
cboCategoryFilter.ValueMember = "CategoryID"
cboCategoryFilter.Items.Insert(0, "All")

For Each category In NorthwindDataSet.Categories
        cboCategoryFilter.Items.Insert(category.CategoryID, category)
   Next

Now you are ready to write the LINQ query that uses these criteria. Because these criteria can be used in any combination, writing the query might prove to be a bit trickier than you initially thought.

The solution is to use the special value of criteria inside the query itself. For example, the expression cboCategoryFilter.Text = "All" is true if the user does not want to apply the categories criteria. As you can see, I have named the categories ComboBox cboCategoryFilter. If you combine this statement with product.CategoryID = categoryId using the OrElse operator, then you can use the cboCategoryFilter.Text = "All" expression to turn off the categories criteria when the user selects All in the Categories combo. The complete statement to be put in the Where part of the LINQ query for Categories combo is as follows: cboCategoryFilter.Text = "All" OrElse product.CategoryID = categoryId.

The same pattern can be applied to rest of conditions. For other criteria, the special value is an empty string. You can take a look at the complete code for the Filter button in Listing 18.9.

Example 18.9. Coloring the edited and inserted rows on the DataGridView control

Private Sub bttnFilter_Click(...) Handles bttnFilter.Click
    Dim categoryId As Integer
    If Not cboCategoryFilter.Text = "All" Then
        categoryId = CType(cboCategoryFilter.SelectedItem,
                NorthwindDataSet.CategoriesRow).CategoryID
    End If

    queryResult = From product In NorthwindDataSet.Products
                  Where ((cboCategoryFilter.Text = "All" OrElse
                        product.CategoryID = categoryId) And

(String.IsNullOrEmpty(txtUnitsOnOrderFilter.Text) OrElse
                        product.UnitsOnOrder >
CInt(txtUnitsOnOrderFilter.Text)) And

(String.IsNullOrEmpty(txtUnitsInStockFilter.Text) OrElse
                        product.UnitsInStock <
CInt(txtUnitsInStockFilter.Text)))
                        Select product

    ProductsBindingSource.DataSource = queryResult.AsDataView()

End Sub