Transcript Chapter 11 - Databases in Visual Basic

Microsoft Visual Basic 2005:
Reloaded
Second Edition
Chapter 11
Using ADO.NET 2.0
Objectives
After studying this chapter, you should be able to:
• Define the terms used when talking about databases
• Connect a database to an application
• Bind table and field objects to controls
• Explain the purpose of the DataSet, BindingSource,
TableAdapter, and BindingNavigator objects
• Access the records in a dataset
Microsoft Visual Basic 2005: Reloaded, Second Edition
2
Objectives (continued)
• Write SQL SELECT statements
• Create a query using the Query Configuration
Wizard
• Associate a ToolStrip control with a query
Microsoft Visual Basic 2005: Reloaded, Second Edition
3
Database Terminology
• Database: an organized collection of related
information stored in a file on a disk
• Relational database: a database that stores
information in tables composed of columns and rows
• Field: a single item of information
• Record: a group of related fields
• Table: a group of related records
• Primary key: a field that uniquely identifies each
record
Microsoft Visual Basic 2005: Reloaded, Second Edition
4
Database Terminology (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
5
Database Terminology (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
6
Database Terminology (continued)
• Parent table: contains a primary key
• Child table: contains a foreign key from the parent
table to link the tables
• Foreign key: a field in a table that contains the
primary key of another table
• Relational database advantages:
– Less redundancy
– Fast retrieval
– Ability to selectively retrieve data
Microsoft Visual Basic 2005: Reloaded, Second Edition
7
ADO.NET 2.0
• ADO.NET 2.0: a technology to connect an
application to a database
• ADO: ActiveX Data Objects
• Dataset: a copy of fields and records that the
application wants to access
• Connection to the database is temporary:
– Only connected when retrieving data or making
changes to data
Microsoft Visual Basic 2005: Reloaded, Second Edition
8
ADO.NET 2.0 (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
9
Connecting a Database to an
Application
Microsoft Visual Basic 2005: Reloaded, Second Edition
10
Connecting a Database to an
Application (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
11
Connecting a Database to an
Application (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
12
Previewing the Data Contained in a
Dataset
Microsoft Visual Basic 2005: Reloaded, Second Edition
13
Previewing the Data Contained in a
Dataset (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
14
Binding the Objects in a Dataset
• Binding: connecting an object in a dataset to a
control
• Bound controls: controls that are connected to an
object in a dataset
• Can bind an object to:
– An existing control in the interface
– A control the computer creates for you
Microsoft Visual Basic 2005: Reloaded, Second Edition
15
Binding the Objects in a Dataset
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
16
Having the Computer Create
a Bound Control
• To allow the computer to create a bound control
– Drag the object from the dataset to the form
• DataGridView control: displays table data in a row
and columnar format
• Use the list arrow next to an object’s name to
change the type of control to be created
Microsoft Visual Basic 2005: Reloaded, Second Edition
17
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
18
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
19
Having the Computer Create
a Bound Control (continued)
• DataGridView control:
– AutoSizeColumnsMode property: select Fill to
automatically adjust column widths to exactly fill the
display area
• BindingNavigator control: allows you to move from
one record to the next in a dataset
• Four objects are placed in the component tray:
–
–
–
–
DataSet
BindingSource
TableAdapter
BindingNavigator
Microsoft Visual Basic 2005: Reloaded, Second Edition
20
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
21
Having the Computer Create
a Bound Control (continued)
• TableAdapter object: connects the database to
the DataSet object
• DataSet object: stores the information to be
accessed from the database
• BindingSource object: connects the DataSet
object to the bound controls on the form
Microsoft Visual Basic 2005: Reloaded, Second Edition
22
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
23
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
24
Having the Computer Create
a Bound Control (continued)
• Two event procedures are automatically created in
Code Editor window:
– bindingNavigatorSaveItem_Click
– MainForm_Load
• bindingNavigatorSaveItem_Click event:
– Saves any changes made to the dataset
– EndEdit method: applies pending changes
– Update method: commits the changes to the
database
Microsoft Visual Basic 2005: Reloaded, Second Edition
25
Having the Computer Create
a Bound Control (continued)
• MainForm_Load event:
– Fill method: TableAdapter object’s method to
retrieve data from the database and store it in the
dataset
• DataGridView control: allows data to be edited
directly within the control to update the database
Microsoft Visual Basic 2005: Reloaded, Second Edition
26
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
27
Having the Computer Create
a Bound Control (continued)
• BindingNavigator control:
–
–
–
–
Allows movement to first, last, next, or previous record
Allows direct selection of record by number
Allows you to add or delete a record
Allows you to save changes made to the dataset
Microsoft Visual Basic 2005: Reloaded, Second Edition
28
Having the Computer Create
a Bound Control (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
29
Binding to an Existing Control
• Two ways to bind to an existing control:
– Drag an object in the dataset to a control on the form
– Select the control and set properties
• Properties to bind the control are specific to the
control
• DataSet, BindingSource, and TableAdapter objects
are added to the component tray
• BindingNavigator control is NOT added automatically
Microsoft Visual Basic 2005: Reloaded, Second Edition
30
Binding to an Existing Control
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
31
Binding to an Existing Control
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
32
Binding to an Existing Control
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
33
Accessing the Records in a Dataset
• BindingSource properties:
– Position: stores position of current record; position
number is zero-relative
– Move: moves the record pointer’s position
Microsoft Visual Basic 2005: Reloaded, Second Edition
34
Accessing the Records in a Dataset
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
35
Accessing the Records in a Dataset
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
36
Accessing the Records in a Dataset
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
37
Accessing the Records in a Dataset
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
38
DataSet Designer
• DataSet Designer:
– Indicates the order in which to display the data
– Specifies fields and records to be viewed
Microsoft Visual Basic 2005: Reloaded, Second Edition
39
DataSet Designer (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
40
DataSet Designer (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
41
DataSet Designer (continued)
• Extensible Markup Language (XML): a text-based
language used to store and share data between
applications and across networks
• XML schema definition file:
– Defines the tables and fields that make up the dataset
– Has extension of .xsd
• Query:
– Specifies the fields and records to retrieve from the
database
– Specifies the field order
Microsoft Visual Basic 2005: Reloaded, Second Edition
42
DataSet Designer (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
43
DataSet Designer (continued)
• Structured Query Language (SQL): a set of
commands to access and manipulate database
data
• SELECT statement:
– Used to specify the fields and records to retrieve, as
well as the order of display
– WHERE clause: limit the records to be selected
– ORDER BY clause: control the order of display
Microsoft Visual Basic 2005: Reloaded, Second Edition
44
DataSet Designer (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
45
Creating a New Query
Microsoft Visual Basic 2005: Reloaded, Second Edition
46
Creating a New Query (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
47
Creating a New Query (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
48
Creating a New Query (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
49
Creating a New Query (continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
50
Using the Query Builder Dialog Box
Microsoft Visual Basic 2005: Reloaded, Second Edition
51
Allowing the User to Run a Query
Microsoft Visual Basic 2005: Reloaded, Second Edition
52
Allowing the User to Run a Query
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
53
Allowing the User to Run a Query
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
54
Allowing the User to Run a Query
(continued)
Microsoft Visual Basic 2005: Reloaded, Second Edition
55
Programming Tutorial
Microsoft Visual Basic 2005: Reloaded, Second Edition
56
Programming Example
Microsoft Visual Basic 2005: Reloaded, Second Edition
57
Summary
• Database: an organized collection of related
information stored in a file on a disk
• Relational database: a database that stores
information in tables composed of columns and
rows
• Primary key: a field in a database table that
uniquely identifies each record
• ADO.NET 2.0: a technology to access data in a
database
Microsoft Visual Basic 2005: Reloaded, Second Edition
58
Summary (continued)
• You must connect the application to a database to
create a dataset
• Display dataset information by binding controls to
dataset objects
• TableAdapter: connects a database to a DataSet
object
• BindingSource object: connects a DataSet object to
bound controls on a form
• BindingSource object’s Position property: stores
the location of the record pointer in a dataset
Microsoft Visual Basic 2005: Reloaded, Second Edition
59
Summary (continued)
• BindingSource object’s Move methods: used to
move the record pointer in a dataset
• SQL SELECT statement: specifies the fields and
records to include in a dataset
• Query Configuration Wizard: creates queries
• Query Builder dialog box: provides a convenient
way to create a SELECT statement
• Associate a query with a ToolStrip control on a form
Microsoft Visual Basic 2005: Reloaded, Second Edition
60