20.8. 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, which is included in the directory with this chapter's examples. The AddressBook application (Fig. 20.44) provides a GUI through which users can execute SQL statements on the database. However, rather than displaying a database table in a DataGridView, this example presents data from a table one row at a time, using several 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 rows, and save changes to the data. Note that lines 622 in Fig. 20.44 are similar to the corresponding lines of code in Figs. 20.34 and 20.43. We discuss the application's additional functionality and the code in lines 2641 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. 1 ' Fig. 20.44: FrmAddressBook.vb 2 ' Allows users to manipulate an address book. 3 Public Class FrmAddressBook 4 ' Click event handler for the Save Button in the 5 ' BindingNavigator saves the changes made to the data 6 Private Sub AddressesBindingNavigatorSaveItem_Click( _ 7 ByVal sender As System.Object, ByVal e As System.EventArgs) _ 8 Handles AddressesBindingNavigatorSaveItem.Click 9 10 Me .Validate() 11 Me.AddressesBindingSource.EndEdit() 12 Me.AddressesTableAdapter.Update(Me .AddressBookDataSet.Addresses) 13 End Sub ' AddressesBindingNavigatorSaveItem_Click 14 15 ' loads data into the AddressBookDataSet.Addresses table 16 Private Sub FrmAddressBook_Load(ByVal sender As System.Object, _ 17 ByVal e As System.EventArgs) Handles MyBase .Load 18 ' TODO: This line of code loads data into the 19 ' 'AddressBookDataSet.Addresses' table. You can move, or remove 20 ' it, as needed. 21 Me.AddressesTableAdapter.Fill(Me .AddressBookDataSet.Addresses) 22 End Sub ' FrmAddressBook_Load 23 24 ' loads data for the rows with the specified last name 25 ' into the AddressBookDataSet.Addresses table 26 Private Sub btnFind_Click(ByVal sender As System.Object, _ 27 ByVal e As System.EventArgs) Handles btnFind.Click 28 ' fill the DataSet's DataTable with only rows 29 ' containing the user-specified last name 30 AddressesTableAdapter.FillByLastName( _ 31 AddressBookDataSet.Addresses, txtFind.Text) 32 End Sub ' btnFind_Click 33 34 ' reloads AddressBookDataSet.Addresses with all rows 35 Private Sub btnBrowseAll_Click(ByVal sender As System.Object, _ 36 ByVal e As System.EventArgs) Handles btnBrowseAll.Click 37 ' fill the DataSet's DataTable with all rows in the database 38 AddressesTableAdapter.Fill(AddressBookDataSet.Addresses) 39 40 txtFind.Text = "" ' clear Find TextBox 41 End Sub ' btnBrowseAll_Click 42 End Class ' FrmAddressBook
(a) 
(b) 
(c)  |
Step 1. | Creating the Project
Create a new Windows Application named AddressBook. Rename the Form FrmAddressBook and name its source file FrmAddressBook.vb, then set the Form's Text property to Address Book.
| | | Step 2. | Adding the Database to the Project
As in Section 20.6.1, 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 3. | 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. 
| | | Step 4. | 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 database table, and uses regular expressions to insert spaces into multiword column names to make the Labels more readable.
| Step 5. | 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 6. | 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 appear in the TextBox to the right of Address ID. After entering several address-book entries, click the Save button to record the new rows in the database. When you close and restart the application, your should be able to use the BindingNavigator controls to browse your entries. Recall from Section 20.6 that to allow changes to the database during changes, you must select the database in the Solution Explorer, then change its Copy to Output Directory property to Copy if newer in the Properties window.
| | | Step 7. | 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 executes. 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 new 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 8. | 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 lblFind, a TextBox named txtFind and a Button named btnFind. Place these controls in a GroupBox named grpFind, 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. 
| Step 9. | Programming an Event Handler That Locates the User-Specified Last Name
Double click btnFind to add a Click event handler for this Button. In the event handler, write the following lines of code (lines 3031 of Fig20.44):
AddressesTableAdapter.FillByLastName( _ AddressBookDataSet.Addresses, txtFind.Text) The FillByLastName method replaces the current data in AddressBookDataSet.Addresses with data for only those rows with the last name entered in txtFind. 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 7. Start the application to test the new functionality. 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. The database in this example is initially empty, so you'll need to add several records before testing the find capability.
| | | Step 10. | 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 btnBrowseAll below the grpFind. Set the Text property of btnBrowseAll to Browse All Entries. Double click btnBrowseAll in Design view to add a Click event handler to the code. 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 38 of Fig. 20.44). Also, add a line of code that clears the Text property of txtFind (line 40). Start the application. Find a specific last name as in the previous step, then click the btnBrowseAll button to test the new functionality.
|
Data Binding in the AddressBook Application Dragging and dropping the Addresses node from the Data Sources window onto the Form FrmAddressBook 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, which allows 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. In this example, the IDE binds 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. The TextBoxes do not display any values if the cached version of Addresses is empty (i.e., if the DataTable is empty because the query that filled the DataTable returned no rows). |
