Transcript Chapter_10

Microsoft Visual Basic 2008
CHAPTER TEN
Incorporating Databases
with ADO.NET
10
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
2
10
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
3
10
Objectives
►Select records from a list
• Program beyond the Database Wizard
• Create the OleDbDataAdapter object
• SQL
[ structured query language
• LINQ
[ Language INtegrated Query ]
►LINQ
]
Appendix E
Chapter 10: Incorporating Databases with ADO.NET
4
10
Chapter Project
Chapter 10: Incorporating Databases with ADO.NET
5
10
Database Files
►A database is 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 key for the table
Chapter 10: Incorporating Databases with ADO.NET
6
10
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.accdb is
available from the book companion website
Chapter 10: Incorporating Databases with ADO.NET
7
10
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
8
10
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
9
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 DataSource
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
Chapter 10: Incorporating Databases with ADO.NET
10
10
Establishing a Database Connection
Chapter 10: Incorporating Databases with ADO.NET
11
10
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
12
10
Connecting Form Objects to the Data Source
Chapter 10: Incorporating Databases with ADO.NET
13
10
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
14
10
Binding Database Fields to the Windows Form
Chapter 10: Incorporating Databases with ADO.NET
15
10
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
16
10
Adding a Record
Chapter 10: Incorporating Databases with ADO.NET
17
10
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
18
10
Deleting Records
Chapter 10: Incorporating Databases with ADO.NET
19
10
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
20
10
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
21
10
Selecting Records from a List
Chapter 10: Incorporating Databases with ADO.NET
22
10
Programming Beyond the Database Wizard
Chapter 10: Incorporating Databases with ADO.NET
23
10
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
24
10
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
25
10
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
26
10
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
27
10
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
28
10
‘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
29
10
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
30
10
Program Design
Chapter 10: Incorporating Databases with ADO.NET
31
10
Program Design
Chapter 10: Incorporating Databases with ADO.NET
32
10
Event Planning Document
Chapter 10: Incorporating Databases with ADO.NET
33
10
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
34
10
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
35
10
Summary
►Select records from a list
►Program beyond the Database Wizard
►Create the OleDbDataAdapter object
Chapter 10: Incorporating Databases with ADO.NET
36
Microsoft Visual Basic 2008
CHAPTER 10 COMPLETE
Incorporating Databases
with ADO.NET