1 / 50

Chapter 12: Using ADO.NET 2.0

Chapter 12: Using ADO.NET 2.0. Programming with Microsoft Visual Basic 2005, Third Edition. Databases Lesson A Objectives. Define the terms used when talking about databases Connect an application to a database Bind table and field objects to controls

uma-marks
Download Presentation

Chapter 12: Using ADO.NET 2.0

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. Chapter 12: Using ADO.NET 2.0 Programming with Microsoft Visual Basic 2005, Third Edition

  2. DatabasesLesson A Objectives • Define the terms used when talking about databases • Connect an application to a database • Bind table and field objects to controls • Explain the purpose of the DataSet, BindingSource, TableAdapter, and BindingNavigator objects Programming with Microsoft Visual Basic 2005, Third Edition

  3. DatabasesLesson A Objectives (continued) • Access the records in a dataset • Move the record pointer Programming with Microsoft Visual Basic 2005, Third Edition

  4. Previewing the Trivia Game Application • Go to Run command on Windows Start menu • Browse to the VB2005\Chap12 folder • Open the Trivia Game.exe file • Trivia Game application user interface appears Programming with Microsoft Visual Basic 2005, Third Edition

  5. Previewing the Trivia Game Application (continued) Figure 12-1: Trivia Game application Programming with Microsoft Visual Basic 2005, Third Edition

  6. Database Terminology • Database: organized collection of related information • Relational database • Database that stores information in tables • Each column in a table represents a field • Each row in a table represents a record • Primary key: field uniquely identifying a record • A two-table database has parent and child tables • Foreign key: links child record to parent record Programming with Microsoft Visual Basic 2005, Third Edition

  7. Database Terminology (continued) Figure 12-2: Example of a one-table relational database Programming with Microsoft Visual Basic 2005, Third Edition

  8. Database Terminology (continued) Figure 12-3: Example of a two-table relational database Programming with Microsoft Visual Basic 2005, Third Edition

  9. ADO.NET 2.0 • ADO (ActiveX Data Objects).Net 2.0 technology • Connects an application to a database • Allows application to read from and write to database • Connection is closed after dataset is copied or saved • Dataset: copy of records application wants to access • Opening Employees.mdf in IDE window • Connect the database to an application • Right-click table’s name in Server Explorer window • Click Show Table Data Programming with Microsoft Visual Basic 2005, Third Edition

  10. ADO.NET (continued) Figure 12-4: Data contained in the tblEmploy table Programming with Microsoft Visual Basic 2005, Third Edition

  11. Connecting an Application to a Database • Create a database connection to access data • Data Source Configuration Wizard • Helps you connect an application to a database Programming with Microsoft Visual Basic 2005, Third Edition

  12. Connecting an Application to a Database (continued) Figure 12-5: Procedure for connecting an application to a database Programming with Microsoft Visual Basic 2005, Third Edition

  13. Connecting an Application to a Database (continued) Figure 12-12: EmployeesDataSet added to the Data Sources window Programming with Microsoft Visual Basic 2005, Third Edition

  14. Previewing the Data Contained in a Dataset • Right-click the Data Sources window • Click Preview Data to open Preview Data dialog box • Select the object to preview, then click Preview • After previewing the data, click the Close button Programming with Microsoft Visual Basic 2005, Third Edition

  15. Previewing the Data Contained in a Dataset (continued) Figure 12-14: Data displayed in the Preview Data dialog box Programming with Microsoft Visual Basic 2005, Third Edition

  16. Binding the Objects in a Dataset • Bind dataset objects before viewing contents • Binding: connecting a dataset object to a control • Bound controls: the connected controls • Types of controls used to bind dataset objects • Computer-created control • Existing control Programming with Microsoft Visual Basic 2005, Third Edition

  17. Binding the Objects in a Dataset (continued) Figure 12-15: Ways to bind the objects in a dataset Programming with Microsoft Visual Basic 2005, Third Edition

  18. Having the Computer Create a Bound Control • Impact of dragging dataset object to form • Computer creates control (indicated by icon) • Dataset object is automatically bound to control • Example • Drag tblEmployee table object to form • DataGridView control displays tabular data • Rows represent records, columns represent fields • Use list arrow to change control linked to object Programming with Microsoft Visual Basic 2005, Third Edition

  19. Having the Computer Create a Bound Control (continued) Figure 12-17: Result of clicking the tblEmploy table object’s list arrow Programming with Microsoft Visual Basic 2005, Third Edition

  20. Having the Computer Create a Bound Control (continued) Figure 12-20: Illustration of the relationships among the database, the objects in the component tray, and the controls on the form Programming with Microsoft Visual Basic 2005, Third Edition

  21. The Copy to Output Directory Property • Determines the way a file is handled in application • Copy always • Default setting of Copy to Output Directory property • Database file copied to project’s bin\Debug folder • Result: .mdf file appears in two different folders • Changes to file in bin\Debug folder are overwritten • Copy if newer • Set this property to preserve run-time changes • Copies over file in bin\Debug only if file is not current Programming with Microsoft Visual Basic 2005, Third Edition

  22. Binding to an Existing Control • Method 1 • Drag object from Data Sources window to control • Method 2 • Click the control • Set one or more properties in the Properties window • Properties to set depends on control being bound • DataGrid: set DataSource and DataMember • ListBox: set DataSource and DisplayMember Programming with Microsoft Visual Basic 2005, Third Edition

  23. Binding to an Existing Control (continued) Figure 12-23: Result of dragging the field objects to the existing label controls Programming with Microsoft Visual Basic 2005, Third Edition

  24. Accessing the Records in a Dataset • BindingSource object’s Position property • Stores an invisible record pointer • Positions are integer values  = 0 • Syntax: bindingSourceName.Position • Example • Me.TblEmployBindingSource.Position = 4 • Moves record pointer to fifth record in the dataset • BindingSource object’s Move methods • Also used to move the record pointer in a dataset Programming with Microsoft Visual Basic 2005, Third Edition

  25. Accessing the Records in a Dataset (continued) Figure 12-25: Syntax and examples of the BindingSource object’s Move methods Programming with Microsoft Visual Basic 2005, Third Edition

  26. Summary – Lesson A • A relational database stores information in tables • Tables comprise rows (records) and columns (fields) • Two-table database comprises parent and child tables • Two types of id fields: primary key and foreign key • Relational database is stored as a file on disk Programming with Microsoft Visual Basic 2005, Third Edition

  27. Summary – Lesson A (continued) • ADO.NET 2.0 connects application to database • Dataset: copies of records from database stored in main memory • To view dataset objects, they must be bound to a control • Use BindingSource object’s Position property or its Move methods to move the record pointer Programming with Microsoft Visual Basic 2005, Third Edition

  28. Creating Queries Lesson B Objectives • Write SQL SELECT statements • Create a query using the Query Configuration Wizard • Associate a ToolStrip control with a query Programming with Microsoft Visual Basic 2005, Third Edition

  29. The DataSet Designer • Browse to VB2005\Chap12 folder • Copy Morgan Industries Solution-DataGrid folder • Rename copy as follows: • Morgan Industries Solution-DataGrid-Query • Open new file in Visual Studio 2005 Programming with Microsoft Visual Basic 2005, Third Edition

  30. The DataSet Designer (continued) Figure 12-26: Data displayed in the TblEmployDataGridView control Programming with Microsoft Visual Basic 2005, Third Edition

  31. The DataSet Designer (continued) • DataSet Designer controls the display of data • Shows name of table and field objects for dataset • Also shows the name of TableAdapter object • TableAdapter object: queries underlying database • Query: specifies fields and records to retrieve • DataSet Designer puts .xsd file in Solution Explorer • .xsd extension indicates XML schema definition file • XML (extensible markup language) • .xsd file type defines tables and fields for a dataset Programming with Microsoft Visual Basic 2005, Third Edition

  32. The DataSet Designer (continued) Figure 12-27: Three ways to open the DataSet Designer Programming with Microsoft Visual Basic 2005, Third Edition

  33. The Dataset Designer (continued) Figure 12-28: DataSet Designer Programming with Microsoft Visual Basic 2005, Third Edition

  34. Structured Query Language • Structured Query Language (SQL) • Access/manipulate data stored in databases • Organized as a set of commands • Database tasks performed with SQL commands • Storing, retrieving, updating, deleting, and sorting • SELECT statement • Most commonly used command in SQL • Specifies the fields and records you want to view • Refine query with WHERE and/or ORDER BY clause Programming with Microsoft Visual Basic 2005, Third Edition

  35. Structured Query Language (continued) Figure 12-30: Syntax and examples of the SELECT statement (continued) Programming with Microsoft Visual Basic 2005, Third Edition

  36. Structured Query Language (continued) Figure 12-30: Syntax and examples of the SELECT statement Programming with Microsoft Visual Basic 2005, Third Edition

  37. Creating a New Query • Use TableAdapter Query Configuration Wizard Programming with Microsoft Visual Basic 2005, Third Edition

  38. Creating a New Query (continued) Figure 12-31: Procedure for creating a query using the TableAdapter Query Configuration Wizard Programming with Microsoft Visual Basic 2005, Third Edition

  39. Creating a New Query (continued) Figure 12-38: SELECT statement containing the WHERE and ORDER BY clauses Programming with Microsoft Visual Basic 2005, Third Edition

  40. Allowing the User to Run a Query • ToolStrip control • Allows a user to run a query at runtime • How to add query feature • Right-click the name of the TableAdapter object • Click Add Query to open Search Criteria Builder dialog • Select the Existing query name radio button • Click the down arrow in list box next to radio button • Click the name of the query in the list • Click OK button to close Search Criteria Builder dialog Programming with Microsoft Visual Basic 2005, Third Edition

  41. Allowing the User to Run a Query (continued) Figure 12-44: ToolStrip control and object added to the application Programming with Microsoft Visual Basic 2005, Third Edition

  42. Summary – Lesson B • DataSet Designer: used to control the display of fields and records from a database • TableAdapter object: connects dataset to underlying database using queries • Query: specifies fields and records to retrieve from a database • Structured Query Language (SQL): set of commands used to access and manipulate data in a database • Select statement: specifies fields/records to view Programming with Microsoft Visual Basic 2005, Third Edition

  43. The Trivia Game ApplicationLesson C Objectives • Connect a SQL Server database to an application • Bind field objects to controls in an interface • Position the record pointer in a dataset • Determine the number of records in a dataset Programming with Microsoft Visual Basic 2005, Third Edition

  44. Coding the Trivia Game Application • Review requirements for Trivia Game application • Display questions along with answers • Allow user to select from set of answers • Track number of incorrect answers • Display information at the end of the game • Location of partial solution • VB2005\Chap12\Trivia Game Solution folder • First set of tasks • Connect application to Trivia.mdf, define dataset Programming with Microsoft Visual Basic 2005, Third Edition

  45. Coding the Trivia Game Application (continued) Figure 12-47: Completed Add Connection dialog box Programming with Microsoft Visual Basic 2005, Third Edition

  46. Coding the Trivia Game Application (continued) Figure 12-49: TriviaDataSet added to the Data Sources window Programming with Microsoft Visual Basic 2005, Third Edition

  47. Coding the Trivia Game Application (continued) • Tasks following definition of the dataset • Preview data that can be displayed • Bind field objects to text boxes • Three event procedures to code • xExitButton’s Click event procedure • MainForm’s Load event procedure • xSubmitButton’s Click event procedure • Test the application after all code is added Programming with Microsoft Visual Basic 2005, Third Edition

  48. Coding the Trivia Game Application (continued) Figure 12-51: Question field object being dragged to the xQuestionTextBox Programming with Microsoft Visual Basic 2005, Third Edition

  49. Coding the Trivia Game Application (continued) Figure 12-55: Pseudocode for the xSubmitButton’s Click event procedure Programming with Microsoft Visual Basic 2005, Third Edition

  50. Summary – Lesson C • First step for accessing a database: connect the application to a database • Following connection to database, specify field objects that will form a dataset • Dataset objects can be bound to interface controls • Methods of BindingSource objects enable you to move through records Programming with Microsoft Visual Basic 2005, Third Edition

More Related