Incorporating Databases

1
CHAPTER 10

• Incorporating Databases
• with ADO.NET 2.0

2
Objectives

• Understand database files
• Connect to a database using ADO.NET 2.0
• Use multiple database types
• Connect Form objects to the data source

3
Objectives

• Bind database fields to the Windows Form object
• Access database information on a Windows Form
  object
• Add a record
• Delete a record

4
Objectives

• Select records from a list
• Program beyond the Database Wizard
• Create the OleDbDataAdapter object

5
Chapter Project
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

7
Establishing a Database Connection

• With Visual Studio 2005 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 570, 384. 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/vb2005/ch10/images

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 25,33. Using the Image
  property, import the intuitionlogo.gif image for
  the PictureBox object. Change the SizeMode to
  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
  308,56. Close the Toolbox. Click the Data on the
  menu bar

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
• 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

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

11
Establishing a Database Connection
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

13
Connecting Form Objects to the Data Source
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

15
Binding Database Fields to the Windows Form
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

17
Adding a Record
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
  Swansons 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

19
Deleting Records
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

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 Covenys
  approved travel request

22
Selecting Records from a List
23
Programming Beyond the Database Wizard
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
  Xs. 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 Xs should not be
  displayed when the program begins

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

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
  closed 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

27
Program Design
28
Program Design
29
Event Planning Document
30
Summary

• Understand database files
• Connect to a database using ADO.NET 2.0
• Use multiple database types
• Connect Form objects to the data source

31
Summary

• Bind database fields to the Windows Form object
• Access database information on a Windows Form
  object
• Add a record
• Delete a record

32
Summary

• Select records from a list
• Program beyond the Database Wizard
• Create the OleDbDataAdapter object

33
CHAPTER 10 COMPLETE

• Incorporating Databases
• with ADO.NET 2.0