1 / 52

Address Book Application Introducing Database Programming

24. Address Book Application Introducing Database Programming. Outline. 24.1 Test-Driving the Address Book Application 24.2 Planning the Address Book Application 24.3 Creating Database Connections 24.4 Programming the Address Book Application. In this tutorial you will learn:

nolan-avila
Download Presentation

Address Book Application Introducing Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 24 • Address Book Application • Introducing DatabaseProgramming

  2. Outline • 24.1 Test-Driving the AddressBook Application • 24.2 Planning the AddressBook Application • 24.3 Creating Database Connections • 24.4 Programming the AddressBook Application

  3. In this tutorial you will learn: Connect to databases. Create LINQ to SQL classes. View the contents of a SQL Server Express database. Add database controls to Windows Forms. Use the Data Sources window. Use LINQ to query a database. Read information from and update informationin databases. Objectives

  4. Introduction • Sequential-access files are inappropriate forlarge instant-access applications. • Instant access is made possible by databases. • Individual database records can be accessed directly without sequentially searching. • Databases have been used in business applicationsfor decades. • LINQ greatly simplifies manipulating databases, which previously required the use of Structured Query Language (SQL).

  5. 24.1 Test-Driving the AddressBook Application • You have been asked to create an address book application that stores the first name, last name, e-mail address and phone number of multiple people in a database table. Each entry should be stored as a different row in the table. The user should be able to navigate through the data, add rows, delete rows and save changes to the data. Specific entries should be retrievable by searching the data by last name.

  6. Test-Driving the AddressBook Application • Run the completed application (Fig. 24.1). • The BindingNavigator at the top of the Form is the strip of Buttons below the window’s title bar. BindingNavigator ReadOnlyAddress ID:TextBox Labels and TextBoxes display contact information Find an entry by last nameGroupBox Browse All Entries Button Figure 24.1|Address Book application.

  7. Test-Driving the AddressBook Application (Cont.) • Click the Move next, Move last, Moveprevious and Move firstButtons (Fig. 24.2) to navigate through entries. Move first Button Move previous Button Move next Button Move last Button Figure 24.2|Navigating the entries in the Address Book application.

  8. Test-Driving the AddressBook Application (Cont.) • Click the Add newButton (Fig. 24.3). • The Address ID:TextBox is automatically filled in with the value 0. • Fill in the data fields, then click the Save DataButton Save Data Button Add new Button 0 is placed inAddress ID:TextBox Figure 24.3|Adding an entry in the AddressBook application.

  9. Test-Driving the AddressBook Application (Cont.) • Navigate to the entry for Lisa Black (the first entry). • Change the text in the Email:TextBox and save the changes to this entry. (Fig. 24.4). Email: field updated Figure 24.4|Editing an entry in the Address Book application.

  10. Test-Driving the AddressBook Application (Cont.) • Enter Brown in the TextBox of the Find anentry by last nameGroupBox, then click the FindButton (Fig. 24.5). • Note that the BindingNavigator now shows 1of2, because there are only two entries with the last name Brown. Figure 24.5|Browsing entries by last name.

  11. Test-Driving the AddressBook Application (Cont.) • Navigate to an entry and click the DeleteButton(Fig. 24.6). • The BindingNavigator now displays 4of6 instead of 4of7. Click the DeleteButton Figure 24.6|Deleting an entry in the Address Book application.

  12. 24.2 Planning the AddressBook Application When the Form loads Display the first entry in the AddressBook database When the user clicks the BindingNavigator’s auto-generated Add new Button Add a new entry When the user clicks the BindingNavigator’s auto-generated Save Data Button Update the database with any new, deleted or updated entries

  13. 24.2 Planning the AddressBook Application (Cont.) When the user clicks the BindingNavigator’s auto-generated Delete Button Delete the current entry displayed in the Form When the user clicks the Browse All Entries Button Display the first entry in the database and allow the user to browse all entries with the BindingNavigator Clear the search text box When the user clicks the Find Button If no entries have a last name that matches the input string, then display empty TextBoxes Otherwise, display the first entry with the specified last name and allow the user to browse through all matching entries with the BindingNavigator

  14. Action/Control/Event (ACE) Table forthe AddressBook Application • Use an ACE table to convert pseudocode into Visual Basic (Fig. 24.7). Figure 24.7| ACE table for the Address Book application. (Part 1 of 3.)

  15. Action/Control/Event (ACE) Table forthe AddressBook Application (Cont.) Figure 24.7| ACE table for the Address Book application. (Part 2 of 3.)

  16. Action/Control/Event (ACE) Table forthe AddressBook Application (Cont.) Figure 24.7| ACE table for the Address Book application. (Part 3 of 3.)

  17. Adding a Database Connection to theAddress Book Application • Create a new Windows Forms Applicationnamed AddressBook. • Change the name of the source file to AddressBook.vb and change the Form’sNameto AddressBookForm. • Change the Form’s Font to 9pt SegoeUI. Then set the Form’s Text property to AddressBook.

  18. Adding a Database Connection to theAddress Book Application (Cont.) • Select Tools > Connect to Database... • Select Microsoft SQL Server Database File. • Click Continue to open the Add Connection dialog. • Click Browse..., and open the AddressBook.mdf database file. Data source type Location of database file Figure 24.8|Adding a database with the Add Connection dialog.

  19. Adding a Database Connection to theAddress Book Application (Cont.) • Select View > DatabaseExplorer. • A database table stores related information in rows and columns. • To view the contents of the Addresses table: • Expand the AddressBook.mdf node in the DatabaseExplorer, then expand the Tables node. • Right click Addresses, and select Show Table Data (Fig. 24.9).

  20. Adding a Database Connection to theAddress Book Application (Cont.) Click to display the database’s tables Right click the Addresses node Select to view the table’s contents Figure 24.9|Viewing the Addresses table.

  21. Adding a Database Connection to theAddress Book Application (Cont.) • Figure 24.10 displays the contents of the Addresses table used in the Address Book application. • A record is a table row, and a field is a table column. Fields (columns) Records (rows) Figure 24.10|Addresses table data.

  22. Adding a Database Connection to theAddress Book Application (Cont.) • A table should contain a primarykey. • This is a field containing unique values to distinguish records from one another. • In this table, the AddressID field is the primary key. • Right click the Addresses tab in the IDE and select Close.

  23. Modeling the Database with LINQ to SQL Classes • LINQ to SQLclasses create an in-memory model of your application’s database. • These classes use ADO.NET technologies to retrieve information from and send information to the database. • LINQ to SQL classes manage all the ADO.NET code behind the scenes.

  24. Modeling the Database with LINQto SQL Classes (Cont.) • Right click the AddressBook project in the Solution Explorer and select Add > New Item.... • In the Add New Item dialog, select LINQ to SQLClasses and add AddressBookDataClasses.dbml (Fig. 24.11). LINQ to SQLClassestemplate Figure 24.11|Adding LINQ to SQL classes.

  25. Modeling the Database with LINQto SQL Classes (Cont.) • In the Object Relational Designer expand the AddressBook.mdf node in the Database Explorer. • Expand the Tables node. • Drag the Addresses table onto the left pane (Fig 24.12). • Click Yes in the dialog prompt. Address class created from the Addresses table Properties representingcolumns in the Addresses Figure 24.12|Adding a table from the Database Explorerto the Object Relational Designer.

  26. Adding a Data Source to theAddress Book Application • A data-boundcontrol displays information contained in a data source. • The BindingNavigator and TextBoxes in the completed application are data-bound controls. • Open the Data Sources window (Fig. 24.13) by selecting Data > Show Data Sources. • Click the link Add New Data Source....

  27. Adding a Data Source to theAddress Book Application (Cont.) Add New Data SourceButton Add New Data Sourcelink Figure 24.13|Data Sources window.

  28. Adding a Data Source to theAddress Book Application (Cont.) • This opens the Data Source Configuration Wizard. • Select Object in the wizard (Fig. 24.14) and click Next >. Select Object as the data source type Figure 24.14|Data Source Configuration Wizard dialog.

  29. Adding a Data Source to theAddress Book Application (Cont.) • Expand the AddressBook node and select the Address class as in Figure 24.15 and click Finish. • If the Address class is not displayed, select File > Save All. Select the Address class as the object data source Figure 24.15|Select Address to add a data source that can bindAddress information to data-bound controls.

  30. Adding a Data Source to theAddress Book Application (Cont.) • An Address node now appears in the Data Sources window (Fig. 24.16). Data source name Addresses table’s fields represented in the data source Figure 24.16|Updated Data Sources window.

  31. Displaying the Address Fields on the Form • Open AddressBookForm in Design view, then click the Address node in the Data Sources window (Fig. 24.17). • Click the down arrow to view the items in the list. • Select the Details option in the drop-down list to indicate that the IDE should create a set of Label–TextBox pairs for each field. Address data source Default control for displaying data Select Details to display data in a set of Label–TextBox pairs Figure 24.17|Selecting a display format for the data.

  32. Displaying the Address Fields on the Form (Cont.) • Drag the Address node from the Data Sources window to the Form. • The IDE creates a series of Labels and TextBoxes (Fig. 24.18). • The IDE also creates a BindingNavigator and a BindingSource.

  33. Displaying the Address Fields on the Form (Cont.) Auto-generatedBindingNavigator Auto-generated Labelsand TextBoxes to display contact information Auto-generated databinding objects Figure 24.18|Displaying a table on a Form using a series of Labels and TextBoxes.

  34. Displaying the Address Fields on the Form (Cont.) • The AddressID is used to uniquely identify each record, so users should not be allowed to edit its value. • Set the Address ID:TextBox’s ReadOnly property to True. • Also set its TabStop property to False.

  35. Displaying the Address Fields on the Form (Cont.) • Reposition the GUI controls to place them in a more natural order, then modify the application’s tab order (Fig. 24.19). Labels and TextBoxes repositioned Figure 24.19|GUI controls repositioned.

  36. Coding the Form’s Load Event Handler • Line 3 (Fig. 24.20) refers to AddressBookDataClassesDataContext— defined as part of the LINQ to SQL classes. • This object has properties representing each table you added to the AddressBookDataClasses LINQ to SQL classes. Figure 24.20|Creating a DataContext object.

  37. Coding the Form’s Load Event Handler (Cont.) • The FillAll method (Fig. 24.21) defines a LINQ query that retrieves each entry in the Addresses table. Figure 24.21|Retrieving data from the database using LINQ.

  38. Coding the Form’s Load Event Handler (Cont.) • The From clause specifies the data source. • The AddressBookDataClassesDataContext object handles all the details of querying the database. • The LINQ clause OrderBy sorts the query results according to the property specified after the OrderBy keywords.

  39. Coding the Form’s Load Event Handler (Cont.) • Double click the Form to generate its event handler (Fig. 24.22). Figure 24.22|Retrieving data from the database when the Form loads.

  40. Coding the Form’s Load Event Handler (Cont.) • Run the application (Fig. 24.23). • As you browse the entries, notice that they are sorted alphabetically by last name. • The Save DataButton in the BindingNavigator is disabled. Save Data button disabled Figure 24.23|Browsing entries in the Address Book application.

  41. Enabling the BindingNavigator’sSaveDataButton • Set the Save DataButton’s Enabled property to True. • Double click the Save DataButton to create its Click event handler (Fig. 24.24). Validate the Form’s input controls Save changes to the BindingSource Update the database on disk Figure 24.24|Click event handler for the Save DataButton.

  42. Enabling the BindingNavigator’sSaveDataButton (Cont.) • The Validate method validates any of the controls on the Form that implement Validating or Validated events. • The EndEdit method ensures that the data source is updated with any changes made by the user. • The SubmitChanges method writes the changes to the SQL Server database on disk. • The BindingSource’s MoveFirst method to move to the first entry.

  43. Searching the LastName Field in the AddressBook.mdf Database • Add to the Form a Label named searchLabel, a TextBox named searchTextBox and a Button named findButton(Fig. 24.25). • Place these controls in a GroupBox named findByLastNameGroupBox. searchTextBox Find an entry by last nameGroupBox findButton searchLabel Figure 24.25|Add controls to search data by last name.

  44. Searching the LastName Field in the AddressBook.mdf Database (Cont.) • Double click the FindButton to create its Click event handler (Fig. 24.26). • A LINQ query retrieves entries where the last name matches the name specified in searchTextBox. • Because all entries returned by this query have the same last name, the results are ordered the by first name.

  45. Searching the LastName Field in the AddressBook.mdf Database (Cont.) Figure 24.26|Retrieving entries with the specified last name.

  46. Adding the BrowseAllEntriesButtonand Its Click Event Handler • Add a Button named browseAllButton and set its Text property to BrowseAllEntries (Fig. 24.27). BrowseAllEntriesButton Figure 24.27| Adding the Browse All Entries Button to the Form.

  47. Adding the BrowseAllEntriesButtonand Its Click Event Handler (Cont.) • Double click the BrowseAllEntriesButton to create its Click event handler (Fig. 24.28). Refill the AddressBindingSource with all address entries Jump to the first entry Clear the searchTextBox Figure 24.28|Click event handler for browseAllButton.

  48. Testing Your Completed AddressBook Application • Enter a new contact, fill in the fields, then click the Save DataButton. • Next, search for the last name of the contact that you entered, using searchTextBox and findButton. • Now click the Browse All EntriesButton. • Test the delete function by deleting an entry and then clicking the Save DataButton.

  49. Outline • Figure 24.29 presents the source codefor the application. (1 of 4 ) DataContext class used to interact with the database Fill the BindingSource with data Query the Addresses table Order results by LastName

  50. Outline (2 of 4 ) Validate the Form’s controls Indicate edits are complete Update the database Move to the first item in the results from the database

More Related