1 / 39

CHAPTER TEN

CHAPTER TEN. Incorporating Databases with ADO.NET. Objectives. Understand database files Connect to a database using ADO.NET 4.0 Use multiple database types Connect Form objects to the data source Bind database fields to the Windows Form object

shika
Download Presentation

CHAPTER TEN

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 TEN Incorporating Databases with ADO.NET

  2. Objectives • Understand database files • Connect to a database using ADO.NET 4.0 • Use multiple database types • Connect Form objects to the data source • Bind database fields to the Windows Form object • Access database information on a Windows Form object Chapter 10: Incorporating Databases with ADO.NET

  3. Objectives • Add a record • Delete a record • Select records from a list • Program beyond the Database Wizard • Create the OleDbDataAdapter object Chapter 10: Incorporating Databases with ADO.NET

  4. Chapter Project Chapter 10: Incorporating Databases with ADO.NET

  5. Preview the Chapter Project Chapter 10: Incorporating Databases with ADO.NET

  6. Database Files • A database is a collection of related information stored in a structured format • A database organizes data in tables • Each row is referred to as a record • Each column in a table is referred to as a field • A unique field is an identifier that represents the primary key for the table Chapter 10: Incorporating Databases with ADO.NET

  7. Establishing a Database Connection • With Visual Studio 2010 open,click the New Project button on the Standard toolbar and then click Windows in the Project types area on the left side of the New Project dialog box.Name the project ApprovedTravelRequests. Click the OK button. When the Windows Form object opens, name it frmApprovedTravel. Change the Text property to Intuition Approved Travel Requests. Resize the form to a size of 586, 394. Change the BackColor property to White on the Web tab.An image representing the Intuition Financial Services company logo named intuitionlogo.gif is available at scseries.com/vb2010/ch10/images Chapter 10: Incorporating Databases with ADO.NET

  8. Establishing a Database Connection • Place a PictureBox object on the left side of the window.Name the PictureBox object picIntuitionLogo. Change the Size property to 223,109. Make the location 24,28. Using the Image property, import the intuitionlogo.gif image for the PictureBox object. Change the SizeModeto StretchImage.On the right side of the form, place a Label object named lblTitle. Change the Text property to Intuition Approved Travel Requests on two lines. Make the Font property Times New Roman, size 20, and the ForeColor property CadetBlue on the Web tab. Change the Location property of the lblTitle Label object to 311,46. Close the Toolbox. Click Data on the menu bar Chapter 10: Incorporating Databases with ADO.NET

  9. Establishing a Database Connection • Click Add New Data Source on the Data menu • In the Choose a Data Source Type dialog box, click Database, and then click Next. In the Choose a Database Model dialog box, click Dataset, and then click Next • Click the New Connection button. In the Add Connection dialog box, click the Change... button to select the data source • In the Change Data Source dialog box, select Microsoft Access Database File because the Intuition Travel database is an Access database. Click the OK button • Click the Browse button to the right of Database file name. Select the USB device on the E: drive, and then select the file named Travel Chapter 10: Incorporating Databases with ADO.NET

  10. Establishing a Database Connection • Click the Open button. The Add Connection dialog box reopens. Click the OK button in the Add Connection dialog box • Click the Next button • Click the No button • Click the Next button. The Choose Your Database Objects dialog box opens. You need to select which database objects you want in the DataSet. Click the expand icon next to the Tables option. Click the approvedTravelRequestscheck box to select that table.A connection is made from the Visual Basic application to the ApprovedTravelRequeststable within the Travel.accdb database • Click the Finish button Chapter 10: Incorporating Databases with ADO.NET

  11. Establishing a Database Connection Chapter 10: Incorporating Databases with ADO.NET

  12. Establishing a Database Connection Chapter 10: Incorporating Databases with ADO.NET

  13. Connecting Form Objects to the Data Source • In the ApprovedTravelRequest project window, click Data on the menu bar • Click Show Data Sources on the Data menu • Click the expand icon in front of the ApprovedTravelRequests table to expand the listing of the field names within the table.Eachbindable field item in the Data Sources window can be placed on the Windows Form object Chapter 10: Incorporating Databases with ADO.NET

  14. Connecting Form Objects to the Data Source Chapter 10: Incorporating Databases with ADO.NET

  15. Connecting Form Objects to the Data Source Chapter 10: Incorporating Databases with ADO.NET

  16. Binding Database Fields to the Windows Form • Select the Employee ID field in the Data Sources window. Drag the Employee ID field to the Windows Form object at the location 25,158 • Drag the rest of the field objects from the Data Sources window to the Windows form. Select all the field labels and field TextBox objects and change the font to size 10. Use the formatting tools in the Format menu to equally distribute the bound objects. You can select the Label and the TextBox objects separately to move them independently of each other. Use the layout shown in Figure 10-24 on page 730 • Run the application by clicking the StartDebugging button on the Standard toolbar to fill the Windows Form object with the data from the ApprovedTravelRequests table. Use the Move next button on the navigation toolbar to move through the records. Click the Move last button to display the last record Chapter 10: Incorporating Databases with ADO.NET

  17. Binding Database Fields to the Windows Form Chapter 10: Incorporating Databases with ADO.NET

  18. Binding Database Fields to the Windows Form Chapter 10: Incorporating Databases with ADO.NET

  19. Adding a Record • Click the Start Debugging button on the Standard toolbar to run the Intuition Approved Travel Requests application • Click the Add new button to add a new record to the database table • Add a new record by typing the Employee ID, 101.Type the rest of the information as displayed in Figure 10-28 on page 734. After the record is complete, click the Save Data button on the BindingNavigator control to save the new record to the original database Chapter 10: Incorporating Databases with ADO.NET

  20. Adding a Record Chapter 10: Incorporating Databases with ADO.NET

  21. Adding a Record Chapter 10: Incorporating Databases with ADO.NET

  22. Deleting Records • Click the Start Debugging button on the Standard toolbar to execute the Intuition Approved Travel Request application • Use the navigation buttons to move to Kaylee Swanson’s record. Her travel request should be deleted because she is ill and unable to travel. Click the Delete button on the BindingNavigator control to delete her record from the database table. Then click the Save Data button to remove the record from the original database Chapter 10: Incorporating Databases with ADO.NET

  23. Deleting Records Chapter 10: Incorporating Databases with ADO.NET

  24. Deleting Records Chapter 10: Incorporating Databases with ADO.NET

  25. Selecting Records from a List • Select the Last Name Label object and TextBox object on the Windows form. Press the DELETE key to delete the Last Name objects from the Windows form. Select the Last Name table field in the Data Sources window and then click its list arrow • Click the ComboBox object from the Toolbox object listing for the Last Name field. Drag the Last Name field ComboBox object to the original location of the Last Name TextBox object on the Windows Form object. Change the font size to 10 and then align the ComboBox on the Windows Form object • To fill the ComboBox object with the last names of the employees, the ComboBox object must be bound to the Last Name field. To bind the items to the ComboBox object, select the Last Name object on the Windows form and click the Action tag on the Last Name ComboBox object Chapter 10: Incorporating Databases with ADO.NET

  26. Selecting Records from a List • Click the Use Data Bound Items check box on the ComboBox Tasks menu. The Data Binding Mode list is displayed. Click the Data Source list arrow under the Data Binding Mode and then select the ApprovedTravelRequestsBindingSource to connect the table to the ComboBox object. Next, click the Display Member list arrow and then select Last Name. Click the Value Member list arrow and then click Last Name in the list. Do not change the Selected value entry • Click the Start Debugging button on the Standard toolbar to run the application. After the Windows form opens, click the list arrow on the Last Name ComboBox object • Click Coveny to move directly to the record containing the information for Ramona Coveny’s approved travel request Chapter 10: Incorporating Databases with ADO.NET

  27. Selecting Records from a List Chapter 10: Incorporating Databases with ADO.NET

  28. Selecting Records from a List Chapter 10: Incorporating Databases with ADO.NET

  29. Programming Beyond the Database Wizard Chapter 10: Incorporating Databases with ADO.NET

  30. Programming Beyond the Database Wizard • Download the original Access database file Travel.accdb again to overwrite any data you added or deleted from the database.Open the Approved Travel Windows application.Adda Button object named btnTotalTravelCost to the Windows Form object and change the Text property to Total Travel Cost. Change the font size to 10 and the ForeColor property to CadetBlue. Set the Location property to 28,286, and set the Size property for the button to 121,23.Add a Label object named lblTotalTravelCost with the Text property of 30 “X’s”. Change the font size to 14 and Bold. Change the ForeColor property to CadetBlue. Change the Location property to 58,328. Set the Visible property for the lblTotalTravelCost Label object to False because the X’s should not be displayed when the program begins Chapter 10: Incorporating Databases with ADO.NET

  31. Programming Beyond the Database Wizard • Double-click the Total Travel Cost button to create the btnTotalTravelCost_Clickevent handler.To initialize the OleDbDataAdapter, enter the code in Figure 10-44 on page 746 inside the click event. The first variable strSql is assigned the SQL statement that queries all the fields in the ApprovedTravelRequests table. The second variable strPath is assigned the database driver for Access and the path to the Travel.accdb file. The third variable odaTravel is an instance of the OleDbDataAdapter • After the first three variables are initialized, initialize the rest of the variables needed for the Button object event handler. An instance named datCostis initialized to represent the DataTableobject.The variable intCount is used to count through a For loop. The last variable, decTotalCost, will contain the total amount of the approved travel costs Chapter 10: Incorporating Databases with ADO.NET

  32. Programming Beyond the Database Wizard • Continuing inside the btnTotalTravelCost_Click event handler, enter the code in Figure 10-46 on page 747 to fill the DataTable with the contents of the ApprovedTravelRequests table. In the next line of code, the Dispose method is used to closed the connection • Enter the code in Figure 10-47 on page 747 to create a For loop to increment through each record in the ApprovedTravelRequests table. Because the rows are numbered 0 to 15, the upper range is one less than the numbers of rows in the table, making 16 records. The value in each Travel Cost field is added to the value in the decTotalCost variable • Enter the code in Figure 10-48 on page 747 to display the total approved travel request cost Chapter 10: Incorporating Databases with ADO.NET

  33. Programming Beyond the Database Wizard Chapter 10: Incorporating Databases with ADO.NET

  34. Program Design Chapter 10: Incorporating Databases with ADO.NET

  35. Program Design Chapter 10: Incorporating Databases with ADO.NET

  36. Event Planning Document Chapter 10: Incorporating Databases with ADO.NET

  37. Summary • Understand database files • Connect to a database using ADO.NET 4.0 • Use multiple database types • Connect Form objects to the data source • Bind database fields to the Windows Form object • Access database information on a Windows Form object Chapter 10: Incorporating Databases with ADO.NET

  38. Summary • Add a record • Delete a record • Select records from a list • Program beyond the Database Wizard • Create the OleDbDataAdapter object Chapter 10: Incorporating Databases with ADO.NET

  39. CHAPTER TEN COMPLETE Incorporating Databases with ADO.NET

More Related