1 / 37

CHAPTER TEN

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

katen
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 3.5 • Use multiple database types • Connect Form objects to the data source Chapter 10: Incorporating Databases with ADO.NET

  3. Objectives • Bind database fields to the Windows Form object • Access database information on a Windows Form object • Add a record • Delete a record Chapter 10: Incorporating Databases with ADO.NET

  4. Objectives • Select records from a list • Program beyond the Database Wizard • Create the OleDbDataAdapter object • SQL [ structured query language ] • LINQ [ LanguageINtegratedQuery ] • LINQ Appendix E Chapter 10: Incorporating Databases with ADO.NET

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

  6. Database Files • A databaseis a collection of related information stored in a structured format. • Here when we refer to a database we mean a Relational database. • 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 keyfor the table Chapter 10: Incorporating Databases with ADO.NET

  7. Establishing a Database Connection • Create a new Windows project. • Name the project ApprovedTravelRequests. • Name = frmApprovedTravel • Text = Intuition Approved Travel Requests • Form Size = 570, 384 • BackColor property = White on the Web tab • An image representing the Intuition Financial Services company logo named intuitionlogo.gif and the Access database Travel.accdbis available from the book companion website 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. • Size property = 223,109. • Location =25,33. • Image property = intuitionlogo.gif • SizeMode = StretchImage. • Label object • Name = lblTitle. • Text property = Intuition Approved Travel Requests on two lines. • Font property = Times New Roman, size 20 • ForeColor property =CadetBlue on the Web tab. • Location property = 308,56. • 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 • 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 • Navigate to the database file named Travel Chapter 10: Incorporating Databases with ADO.NET

  10. Establishing a Database Connection • Click the Open button ( for Travel file ). The Add Connection dialog box reopens. Click the OK button in the Add Connection dialog box • Click the Next button. The Choose Your Database Objects dialog box opens to allow you to specify the DataSourceYou need to select which database objects you want in the DataSet. Click the plus sign next to the Tables option. Click the ApprovedTravelRequests check box to select that table. A connection is made from the Visual Basic application to the ApprovedTravelRequests table within the Travel.mdb 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. 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 plus sign in front of the ApprovedTravelRequests table to expand the listing of the field names within the table. Each bindable field item in the Data Sources window can be placed on the Windows Form object Chapter 10: Incorporating Databases with ADO.NET

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

  14. 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 22,166 • 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. Change the Windows Form size to 578,390 • 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

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

  16. 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 752. 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

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

  18. 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

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

  20. 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

  21. 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

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

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

  24. Programming Beyond the Database Wizard • Download the original Access database file Travel.mdb again to overwrite any data you added or deleted from the database. • Open the Approved Travel Windows application. • Add a 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 22,273, 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 55,312. • 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

  25. Programming Beyond the Database Wizard • Double-click the Total Travel Cost button to create the btnTotalTravelCost_Click event handler. To initialize the OleDbDataAdapter, enter the code in Figure 10-44 on page 764 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.mdb 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 datCost is initialized to represent the DataTable object. 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

  26. Programming Beyond the Database Wizard • Continuing inside the btnTotalTravelCost_Click event handler, enter the code in Figure 10-46 on page 765 to fill the DataTable with the contents of the ApprovedTravelRequests table. In the next line of code, the Dispose method is used to close the connection • Enter the code in Figure 10-47 on page 765 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 765 to display the total approved travel request cost Chapter 10: Incorporating Databases with ADO.NET

  27. Calculate Travel Cost Event Procedure Private Sub btnTotalTravelCost_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotalTravelCost.Click ' strSql is a SQL statement that selects all the fields from the ' ApprovedTravelRequests table Dim strSql As String = "SELECT * FROM ApprovedTravelRequests" 'strPath provides the database type and path of the Travel database Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" _ & "Data Source=H:\Travel.accdb“ ‘connection string Dim odaTravel As New OleDb.OleDbDataAdapter(strSql, strPath) Dim datCost As New DataTable Dim intCount As Integer Dim decTotalCost As Decimal = 0D Chapter 10: Incorporating Databases with ADO.NET

  28. Calculate Travel Cost Event Procedure ' Fill the data table datCost with table data odaTravel.Fill(datCost) ‘datCost is the data table ‘Disconnect from the database odaTravel.Dispose() For intCount = 0 To datCost.Rows.Count - 1 ‘accumulate cost for each travel request decTotalCost += Convert.ToDecimal(datCost.Rows(intCount)("Travel Cost")) Next intCount Me.lblTotalTravelCost.Visible = True Me.lblTotalTravelCost.Text = "The Total Approved Travel Cost is " _ & decTotalCost.ToString("C") End Sub Chapter 10: Incorporating Databases with ADO.NET

  29. ‘Determine number of rows (records) in a Data Table Dim intNumberOfRows as Integer intNumberOfRows = datCost.Rows.Count ‘Determine number of columns (fields) in a Data Table Dim intNumberOfColumnss as Integer intNumberOfColumns = datCost.Columns.Count ‘Get the value of a column (field) in a row (record) ‘table record # column name Dim decTravelCost = Convert.ToDecimal(datCost.Rows(intCount)("Travel Cost")) Chapter 10: Incorporating Databases with ADO.NET

  30. SQL Statements • SELECT • Columns (field names) • FROM • Table • WHERE • condition (boolean expression) • ORDER BY [ ASC | DESC ] • Sort ascending or descending Chapter 10: Incorporating Databases with ADO.NET

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

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

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

  34. Summary Understand database files Connect to a database using ADO.NET 2.0 Use multiple database types Connect Form objects to the data source Chapter 10: Incorporating Databases with ADO.NET

  35. Summary • Bind database fields to the Windows Form object • Access database information on a Windows Form object • Add a record • Delete a record Chapter 10: Incorporating Databases with ADO.NET

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

  37. CHAPTER 10 COMPLETE Incorporating Databases with ADO.NET

More Related