Programming with ADO.NET: Address Book Case Study

Programming with ADO NET Address Book Case Study

Our next example implements a simple address book application that enables users to insert rows into, locate rows from and update the SQL Server database AddressBook.mdf (located in the chapter's examples directory on the CD that accompanies the book).

The AddressBook application (Fig. 20.44) provides a GUI through which users can execute SQL statements on the database. However, instead of displaying a database table in a DataGridView, this example presents data from a table one row at a time, using a set of TextBoxes that display the values of each of the row's columns. A BindingNavigator allows you to control which row of the table is currently in view at any given time. The BindingNavigator also allows you to add new rows, delete row, and save changes to the data in view. Note that lines 1734 in Fig. 20.44 are similar to the corresponding lines of code in the chapter's earlier examples. We discuss the application's additional functionality and the code in lines 3853 that supports it momentarily. We begin by showing you the steps to create this application.

Figure 20.44. AddressBook application that allows you to manipulate entries in an address book database.

Step 1: Adding the Database to the Project

As in the preceding examples, you must begin by adding the database to the project. After adding the AddressBook.mdf as a data source, the Data Sources window will list AddressBookDataSet, which contains a table named Addresses.

Step 2: Indicating that the IDE Should Create a Set of Labels and TextBoxes to Display Each Row of Data

In the earlier sections, you dragged a node from the Data Sources window to the Form to create a DataGridView bound to the data source member represented by that node. The IDE allows you to specify the type of control(s) that it creates when you drag and drop a data source member onto a Form. In Design view, click the Addresses node in the Data Sources window (Fig. 20.45). Note that this node becomes a drop-down list when you select it. Click the down arrow to view the items in the list. The icon to the left of DataGridView will initially be highlighted in blue, because the default control to be bound to a table is a DataGridView (as you saw in the earlier examples). Select the Details option in the drop-down list to indicate that the IDE should create a set of LabelTextBox pairs for each column namecolumn value pair when you drag and drop the Addresses table onto the Form. (You will see what this looks like in Fig. 20.46.) The drop-down list contains suggestions for controls to display the table's data, but you can also choose the Customize... option to select other controls that are capable of being bound to a table's data.

Figure 20.45. Selecting the control(s) to be created when dragging and dropping a data source member onto the Form.

Figure 20.46. Displaying a table on a Form using a series of Labels and TextBoxes.

(This item is displayed on page 1035 in the print version)

Step 3: Dragging the Addresses Data Source Node to the Form

Drag the Addresses node from the Data Sources window to the Form (Fig. 20.46). The IDE creates a series of Labels and TextBoxes because you selected Details in the preceding step. As in the earlier examples, the IDE also creates a BindingNavigator and the other components in the component tray. The IDE sets the text of each Label based on the corresponding column name in the table in the database, and uses regular expressions to insert spaces into multiword column names to make the Labels more readable.

Step 4: Making the AddressID TextBox ReadOnly

The AddressID column of the Addresses table is an auto-incremented identity column, so users should not be allowed to edit the values in this column. Select the TextBox for the AddressID and set its ReadOnly property to TRue using the Properties window. Note that you may need to click in an empty part of the Form to deselect the other Labels and TextBoxes before selecting the AddressID TextBox.

Step 5: Running the Application

Run the application and experiment with the controls in the BindingNavigator at the top of the window. Like the previous examples, this example fills a DataSet object (specifically an AddressBookDataSet object) with all the rows of a database table (i.e., Addresses). However, only a single row of the DataSet appears at any given time. The CD- or DVD-like buttons of the BindingNavigator allow you to change the currently displayed row (i.e., change the values in each of the TextBoxes). The buttons to add a row, delete a row and save changes also perform their designated tasks. Adding a row clears the TextBoxes and makes a new auto-incremented ID (i.e., 5) appear in the TextBox to the right of Address ID. After entering some data, click the Save button to record the new row in the database. After closing and restarting the application, there should still be five rows. Delete the new row by clicking the appropriate button, then save the changes.

Step 6: Adding a Query to the AddressesTableAdapter

While the BindingNavigator allows you to browse the address book, it would be more convenient to be able to find a specific entry by last name. To add this functionality to the application, you must add a new query to the AddressesTableAdapter using the TableAdapter Query Configuration Wizard. Click the Edit DataSet with Designer icon () in the Data Sources window. Select the box representing the AddressesTableAdapter. Right click the TableAdapter's name and select Add Query.... In the TableAdapter Query Configuration Wizard, keep the default option Use SQL Statements and click Next. On the next screen, keep the default option SELECT which returns rows and click Next. Rather than use the Query Builder to form your query (as we did in the preceding example), modify the query directly in the text box in the wizard. Append the clause "WHERE LastName = @lastName" to the end of the default query. Note that @lastName is a parameter that will be replaced by a value when the query is executed. Click Next, then enter FillByLastName and GetdataByLastName as the names for the two methods that the wizard will generate. The query contains a parameter, so each of these methods will take a parameter to set the value of @lastName in the query. You will see how to call the FillByLastName method and specify a value for @lastName shortly. Click Finish to complete the wizard and return to the Dataset Designer (Fig. 20.47). Note that the newly created Fill and Get methods appear under the AddressesTableAdapter and that parameter @lastName is listed to the right of the method names.

Figure 20.47. Dataset Designer for the AddressBookDataSet after adding a query to AddressesTableAdapter.

Step 7: Adding Controls to Allow Users to Specify a Last Name to Locate

Now that you have created a query to locate rows with a specific last name, add controls to allow users to enter a last name and execute this query. Go to Design view (Fig. 20.48) and add to the Form a Label named findLabel, a TextBox named findTextBox and a Button named findButton. Place these controls in a GroupBox named findGroupBox, then set its Text property to Find an entry by last name. Set the Text properties of the Label and Button as shown in Fig. 20.48.

Figure 20.48. Design view after adding controls to locate a last name in the address book.

(This item is displayed on page 1037 in the print version)

Step 8: Programming an Event Handler That Locates the User-Specified Last Name

Double click findButton to add a Click event handler for this Button. In the event handler, write the following lines of code (lines 4243 of Fig. 20.44):

addressesTableAdapter.FillByLastName( addressBookDataSet.Addresses, findTextBox.Text );

The FillByLastName method replaces the current data in addressBookDataSet.Addresses with data for only those rows with the last name entered in findTextBox. Note that when invoking FillByLastName, you must pass the DataTable to be filled, as well as an argument specifying the last name to find. This argument becomes the value of the @lastName parameter in the SELECT statement created in Step 6. Start the application to test the new functionality. Note that when you search for a specific entry (i.e., enter a last name and click Find), the BindingNavigator allows the user to browse only the rows containing the specified last name. This is because the data source bound to the Form's controls (i.e., addressBookDataSet.Addresses) has changed and now contains only a limited number of rows.

Step 9: Allowing the User to Return to Browsing All Rows in the Database

To allow users to return to browsing all the rows after searching for specific rows, add a Button named browseAllButton below the findGroupBox. Double click browseAllButton to add a Click event handler to the code. Set the Text property of browseAllButton to Browse All Entries in the Properties window. Add a line of code that calls addressesTableAdapter.Fill( addressBookDataSet.Addresses ) to refill the Addresses DataTable with all the rows from the table in the database (line 50 of Fig. 20.44). Also, add a line of code that clears the Text property of findTextBox (line 52). Start the application. Find a specific last name as in the previous step, then click the browseAllButton button to test the new functionality.

Data Binding in the AddressBook Application

Dragging and dropping the Addresses node from the Data Sources window onto AddressBookForm in this example caused the IDE to generate several components in the component tray. These serve the same purposes as those generated for the earlier examples that use the Books database. In this case, addressBookDataSet is an object of a strongly typed DataSet, AddressBookDataSet, whose structure mimics that of the AddressBook database. addressesBindingSource is a BindingSource object that refers to the Addresses table of the AddressBookDataSet. addressesTableAdapter encapsulates a SqlDataAdapter object configured with SqlCommand objects that execute SQL statements against the AddressBook database. Finally, addressesBindingNavigator is bound to the addressesBindingSource object, thus allowing you to indirectly manipulate the Addresses table of the AddressBookDataSet.

In each of the earlier examples using a DataGridView to display all the rows of a database table, the DataGridView's BindingSource property was set to the corresponding BindingSource object. In this example, you selected Details from the drop-down list for the Addresses table in the Data Sources window, so the values from a single row of the table appear on the Form in a set of TextBoxes. The IDE sets up the data binding in this example by binding each TextBox to a specific column of the Addresses DataTable in the AddressBookDataSet. To do this, the IDE sets the TextBox's DataBindings.Text property. You can view this property by clicking the plus sign next to (DataBindings) in the Properties window (Fig. 20.49). Clicking the drop-down list for this property allows you to choose a BindingSource object and a property (i.e., column) within the associated data source to bind to the TextBox.

Figure 20.49. Viewing the DataBindings.Text property of a TextBox in the Properties window.

Consider the TextBox that displays the FirstName valuenamed firstNameTextBox by the IDE. This control's DataBindings.Text property is set to the FirstName property of the AddressesBindingSource (which refers to AddressBookDataSet.Addresses). Thus, firstNameTextBox always displays the value of the FirstName column in the currently selected row of addressBookDataSet.Addresses. Each IDE-created TextBox on the Form is configured in a similar manner. Browsing the address book with the AddressesBindingNavigator changes the current position in addressBookDataSet.Addresses and thus changes the values displayed in each TextBox. Regardless of changes to the contents of addressBookDataSet.Addresses, the TextBoxes remain bound to the same properties of the DataTable and always display the appropriate data. Note that the TextBoxes do not display any values if the cached version of Addresses is empty (i.e., if there are no rows in the DataTable because the query that filled the DataTable returned no rows).