1 / 71

Microsoft Visual Basic 2010: Reloaded Fourth Edition

Microsoft Visual Basic 2010: Reloaded Fourth Edition. Chapter Twelve Access Databases and LINQ. Objectives. After studying this chapter, you should be able to: Define the terms used when talking about databases Connect an application to a Microsoft Access database

williambaum
Download Presentation

Microsoft Visual Basic 2010: Reloaded Fourth Edition

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. Microsoft Visual Basic 2010: ReloadedFourth Edition Chapter Twelve Access Databases and LINQ

  2. Objectives After studying this chapter, you should be able to: • Define the terms used when talking about databases • Connect an application to a Microsoft Access database • Bind table and field objects to controls • Explain the purpose of the DataSet, BindingSource, TableAdapter, TableAdapterManager, and BindingNavigator objects Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  3. Objectives (cont'd.) • Customize a DataGridView control • Handle errors using the Try…Catch statement • Position the record pointer in a dataset • Access the value stored in a field object • Query a dataset using LINQ • Customize a BindingNavigator control • Use the LINQ aggregate methods Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  4. Database Terminology • Computer database: electronic file containing an organized collection of related information • Relational database: database that stores information in tables composed of columns and rows • Field: single item of information • Record: group of related fields • Table: group of related records • Primary key: field that uniquely identifies each record Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  5. Database Terminology (cont'd.) Figure 12-1: Example of a one-table relational database Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  6. Database Terminology (cont'd.) • Parent table: contains a primary key • Child table: contains a foreign key from the parent table to link the tables • Foreign key: field in a table that contains the primary key of another table • Relational database advantages: • Less redundancy • Fast retrieval • Ability to selectively retrieve data Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  7. Database Terminology (cont'd.) Figure 12-2: Example of a two-table relational database Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  8. Connecting an Application to a Microsoft Access Database • Microsoft Access database: • Has a file extension of .accdb • Must connect an application to the database before the application can access the data • Use the Data Source Configuration Wizard to connect to a database • Dataset: copy of the fields and records stored in the computer’s internal memory, which the application can access Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  9. Connecting an Application to a Microsoft Access Database (cont'd.) Figure 12-3: Data contained in the tblEmploy table Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  10. Connecting an Application to a Microsoft Access Database (cont'd.) Figure 12-4: How to connect an application to an Access database Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  11. Connecting an Application to a Microsoft Access Database (cont'd.) Figure 12-5: Result of running the Data Source Configuration Wizard Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  12. Connecting an Application to a Microsoft Access Database (cont'd.) Figure 12-6: How to preview the contents of a dataset Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  13. Figure 12-7: EmployeesDataSet shown in the Preview Data dialog box Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  14. Binding the Objects in a Dataset • Binding: connecting an object in a dataset to a control on a form • Bound controls: controls that are connected to an object in a dataset • Can bind an object to: • An existing control in the interface • A control the computer creates for you Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  15. Binding the Objects in a Dataset (cont'd.) Figure 12-8: How to bind an object in a dataset Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  16. Having the Computer Create a Bound Control • To allow the computer to create a bound control: • Drag the object from the dataset to the form • DataGridView control: displays table data in a row and column format • Use the list arrow next to an object’s name to change the type of control to be created Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  17. Having the Computer Create a Bound Control (cont'd.) Figure 12-9: Icons in the Data Sources window Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  18. Having the Computer Create a Bound Control (cont'd.) Figure 12-10: Result of clicking the tblEmploy table object’s list arrow Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  19. Having the Computer Create a Bound Control (cont'd.) Figure 12-11: Result of clicking the Last_Name filed object’s list arrow Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  20. Having the Computer Create a Bound Control (cont'd.) • BindingNavigator control: • Allows movement to first, last, next, or previous record • Allows direct selection of record by number • Allows you to add or delete a record • Allows you to save changes made to the dataset • Five objects are placed in the component tray: • DataSet, BindingSource, TableAdapter, TableAdapterManager, BindingNavigator Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  21. Having the Computer Create a Bound Control (cont'd.) Figure 12-12: Result of dragging the table object to the form Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  22. Having the Computer Create a Bound Control (cont'd.) • TableAdapter object: connects the database to the DataSet object • DataSet object: stores the information to be accessed from the database • TableAdapterManager object: handles saving data to multiple tables in the DataSet • BindingSource object: connects the DataSet object to the bound controls on the form Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  23. Having the Computer Create a Bound Control (cont'd.) Figure 12-13: Illustration of the relationships among the database, the objects in the component tray, and the bound controls Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  24. The DataGridView Control • DataGridView control: displays data in a row and column format • Each row represents a record • Each column represents a field • Cell: the intersection of a row and column • DataGridView has a task list used to control its appearance and behavior Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  25. The DataGridView Control (cont'd.) Figure 12-14: Task list for a DataGridView control Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  26. The DataGridView Control (cont'd.) Figure 12-15: Purpose of each task in the DataGridView’s task list Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  27. The DataGridView Control (cont'd.) Figure 12-15: Edit Columns dialog box Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  28. The DataGridView Control (cont'd.) • Many properties of DataGridView are listed only in the Properties window • AutoSizeColumnsMode property: • Select Fill setting to automatically adjust column widths to exactly fill the display area • Select ColumnHeader setting to adjust column widths based on the header text Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  29. The DataGridView Control (cont'd.) Figure 12-17: DataGridView control after setting some of its properties Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  30. Visual Basic Code • DataGridView control allows data to be edited directly within the control to update the database • Two event procedures are automatically created in Code Editor window when a table or field object is dragged to the form • MainForm_Load • bindingNavigatorSaveItem_Click • MainForm_Load event: • Fill method: TableAdapter object’s method to retrieve data from the database and store it in the dataset Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  31. Visual Basic Code (cont'd.) • bindingNavigatorSaveItem_Click event: • Saves any changes made to the dataset • EndEdit method: applies pending changes to the dataset • UpdateAll method: commits the dataset changes to the database • Be sure to use error-handling code Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  32. Visual Basic Code (cont'd.) Figure 12-18: Code automatically entered in the Code Editor window Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  33. Handling Errors in the Code • Exception: an error that occurs while an application is running • VB “handles” errors if the program does not by showing an error message and terminating the application • Try…Catch statement: used to trap errors and attempt to handle them • Place code that could cause an exception within the Try block • Catch block is executed if an exception occurs Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  34. Figure 12-19: How to use the Try…Catch statement Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  35. Handling Errors in the Code (cont’d.) Figure 12-19: How to use the Try…Catch statement (cont’d.) Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  36. Handling Errors in the Code (cont'd.) Figure 12-20: Try…Catch statement entered in the Save Data button’s Click event procedure Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  37. Handling Errors in the Code (cont'd.) Figure 12-21: Sample run of the Morgan Industries application Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  38. Handling Errors in the Code (cont'd.) • BindingNavigator control provides buttons for first, last, previous, and next record and for adding records, deleting records, and saving changes • Can also use the control to access a record by its record number Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  39. The Copy to Output Directory Property • Local database file: database file contained in a project • Copy to Output Directory property:determines how VB saves changes to a local database file • Copy always setting: database file is copied from the project folder to bin\Debug folder each time the application is started • Changes are made only to the bin\Debug copy • Copy if newer setting: newer of the two database files (project folder and bin\Debug folder) is saved in bin\Debug folder Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  40. The Copy to Output Directory Property (cont’d.) Figure 12-22: How to use the Copy to Output Directory property Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  41. Binding to an Existing Control • Can bind an object in a dataset to an existing control in two ways: • Drag an object in the dataset to a control on the form • Select the control and set properties • Properties to bind the control are specific to the control • DataSet, BindingSource,TableAdapter, and TableAdapterManager objects are added to the component tray • BindingNavigator control is NOT added automatically Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  42. Binding to an Existing Control (cont'd.) Figure 12-23: Result of dragging field objects to existing label controls Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  43. Binding to an Existing Control (cont'd.) Figure 12-24: Sample run of a different version of the Morgan Industries application Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  44. Coding the Next Record and Previous Record Buttons • BindingSource object uses an invisible record pointer to track the current record in the dataset • Position property: stores position of current record; this position number is zero-relative • Move method: moves the record pointer’s position to first, last, previous, or next record in the dataset Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  45. Figure 12-23: How to use the BindingSource object’s Position property Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  46. Figure 12-26: How to use the BindingSource object’s Move methods Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  47. Coding the Next Record and Previous Record Buttons (cont'd.) Figure 12-27: Code entered in the Click event procedures for the Next Record and Previous Record buttons Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  48. Accessing the Value Stored in a Field Figure 12-28: How to access the value stored in a field object Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  49. Creating a Query • Can arrange records in a dataset in any order • Query: specifies the records to select in a dataset • Language Integrated Query (LINQ): language used to create a query • Where clause: optional, specifies a condition to limit which records to view • Order By clause: optional, specifies whether to arrange in descending or ascending order • Option Infer On: allows computer to infer the data type from the variables in the query Microsoft Visual Basic 2010: Reloaded, Fourth Edition

  50. Creating a Query (cont’d.) Figure 12-29: How to use LINQ to select and arrange records in a dataset Microsoft Visual Basic 2010: Reloaded, Fourth Edition

More Related