Section 20.8. Programming with ADO.NET: Address Book Case Study

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 Label TextBox 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.

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 Label s and TextBox es 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 Label s 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 Label s and TextBox es 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 TextBox es). The buttons to add a row, delete a row and save changes also perform their designated tasks . Adding a row clears the TextBox es 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.

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.

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 );