Transcript Outline

Chapter 19 – Databases, SQL, and
ADO.NET
Outline: 只上 19.1, 19.2, 19.3, 19.5, 19.6
19.1
19.2
19.3
19.4
19.5
Introduction
Relational Database Model
Relational Database Overview: The Books Database
Structured Query Language (SQL)
19.4.1 Basic SELECT Query
19.4.2 WHERE Clause
19.4.3 ORDER BY Clause
19.4.4 Merging Data from Multiple Tables: INNER JOIN
19.4.5 Joining Data from Tables Authors, AuthorISBN,
Titles and Publishers
19.4.6 INSERT Statement
19.4.7 UPDATE Statement
19.4.8 DELETE Statement
ADO.NET Object Model
 2002 Prentice Hall. All rights reserved.
1
2
Outline
19.6
19.7
19.8
Programming with ADO.NET: Extracting Information from a
DBMS
19.6.1 Connecting to and Querying an Access Data
Source
19.6.2 Querying the Books Database
Programming with ADO.NET: Modifying a DBMS
Reading and Writing XML Files
 2002 Prentice Hall. All rights reserved.
3
19.1 Introduction
• Database:
– Integrated collection of data
– Database management system (DBMS)
• Provides mechanisms for storing and organizing data in a way
that is consistent with database’s format
• Allows storage and access to database without knowledge of
internal representation
– Relational Databases most popular
• Use Structured Query Language to perform queries (search)
and manipulate data
• Programming languages need an interface to interact with
relational databases
 2002 Prentice Hall. All rights reserved.
4
19.2 Relational Database Model
• Logical representation of data:
– Relationships can be considered without concern for
physical structure of data
• Composed of tables
– Rows called records
– Columns called fields
– Primary key: field that contains unique data
• Each record can be identified by at least one distinct value
– New sets made from queries called result sets
 2002 Prentice Hall. All rights reserved.
5
19.2 Relational Database Model
record/row
number
name
department
salary
location
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Angeles
35761
Myers
611
1400
Orlando
47132
Neumann
413
9000
New Jersey
78321
Stephens
611
8500
Orlando
primary key
field/column
Fig. 19.1 Relational database structure of an Employee table.
 2002 Prentice Hall. All rights reserved.
6
19.3 Relational Database Overview: Books
Database
• Rule of Entity Integrity: every record must have a
unique value in its primary-key field
• Compound Primary key: when a record has a
unique key based on a combination of two fields
• Foreign key:
– Field for which every entry has a unique value in another
table and where the field in the other table is the primary key
for that table
– Rule of Referential Integrity: every foreign-key field value
must appear in another table’s primary-key field
– One to many relationship: A foreign key can appear many
times in its own table, but only once as primary key in
another table
 2002 Prentice Hall. All rights reserved.
19.3 Relational Database Overview: Books
Database
department
location
413
New Jersey
611
Orlando
642
Los Angeles
Fig. 19.2 Result set formed by selecting Department and Location data from the Employee table.
 2002 Prentice Hall. All rights reserved.
7
19.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
authorID
Author’s ID number in the database. In the Books database, this integer field is
firstName
defined as an auto-incremented field. For each new record inserted in
this table, the database automatically increments the authorID value to ensure
that each record has a unique authorID. This field represents the table’s
primary key.
Author’s first name (a string).
lastName
Author’s last name (a string).
Fig. 19.3
Authors ta b le from Books.
Fig. 19.3 Authors table from Books.
 2002 Prentice Hall. All rights reserved.
8
19.3 Relational Database Overview: Books
Database
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Kate
Steinbuhler
5
Sean
Santry
6
Ted
Lin
7
Praveen
Sadhu
8
David
McPhie
9
Cheryl
Yaeger
10
Marina
Zlatkina
11
Ben
Wiedermann
12
Jonathan
Liperi
Fig. 19.4
Da ta from the
Authors ta b le of Books.
Fig. 19.4 Data from the Authors table of Books.
 2002 Prentice Hall. All rights reserved.
9
19.3 Relational Database Overview: Books
Database
Field
Desc rip tion
publisherID
The publisher’s ID number in the database. This auto-incremented integer
is the table’s primary-key field.
publisherName
The name of the publisher (a string).
Fig. 19.5
Publishers ta b le from Books.
Fig. 19.5 Publishers table from Books.
publisherID
publisherName
1
Prentice Hall
2
Prentice Hall PTG
Fig. 19.6
Da ta from the
Publishers ta b le of Books.
Fig. 19.6 Data from the Publishers table of Books.
 2002 Prentice Hall. All rights reserved.
10
19.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
authorID
The author’s ID number, which allows the database to associate each
book with a specific author. The integer ID number in this field must
also appear in the Authors table.
isbn
The ISBN number for a book (a string).
Fig. 19.7
AuthorISBN ta b le from Books.
Fig. 19.7 AuthorISBN table from Books.
 2002 Prentice Hall. All rights reserved.
11
19.3 Relational Database Overview: Books
Database
authorID
isbn
authorID
isbn
1
0130895725
2
0139163050
1
0132261197
2
013028419x
1
0130895717
2
0130161438
1
0135289106
2
0130856118
1
0139163050
2
0130125075
1
013028419x
2
0138993947
1
0130161438
2
0130852473
1
0130856118
2
0130829277
1
0130125075
2
0134569555
1
0138993947
2
0130829293
1
0130852473
2
0130284173
1
0130829277
2
0130284181
1
0134569555
2
0130895601
1
0130829293
3
013028419x
1
0130284173
3
0130161438
1
0130284181
3
0130856118
1
0130895601
3
0134569555
2
0130895725
3
0130829293
2
0132261197
3
0130284173
2
0130895717
3
0130284181
2
0135289106
4
0130895601
Fig. 19.8
Portion of d a ta from ta b le
AuthorISBN in d a ta b a se Books.
Fig. 19.8 Portion of data from table AuthorISBN in database Books.
 2002 Prentice Hall. All rights reserved.
12
19.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
isbn
ISBN number of the book (a string).
title
Title of the book (a string).
editionNumber
Edition number of the book (an integer).
copyright
Copyright year of the book (a string).
publisherID
Publisher’s ID number (an integer). This value must correspond to an ID
number in the Publishers table.
imageFile
Name of the file containing the book’s cover image (a string).
price
Fig. 19.9
Suggested retail price of the book (a real number). [Note: The prices
shown in this book are for example purposes only.]
Titles ta b le from Books.
Fig. 19.9 Titles table from Books.
 2002 Prentice Hall. All rights reserved.
13
19.3 Relational Database Overview: Books
Database
isbn
title
01309236 Python How to
13
Program
01306222 C# How to
14
Program
01303415 Java How to
17
Program
01306493 The Complete
41
Java Training
Course
editio publis copy- imageFil pric
nh-erID right e
e
Number
1
1
2002
python.j $69.95
pg
1
1
2002
cshtp.jp $69.95
g
4
1
2002
jhtp4.jp $69.95
g
4
2
2002
javactc4 $109.95
.jpg
01308956 Advanced Java 2 1
01
Platform How to
Program
1
2002
advjhtp1
.jpg
$69.95
01303089 Internet and
2
78
World Wide Web
How to Program
1
2002
iw3htp2.
jpg
$69.95
01302936 Visual Basic
36
.NET How to
Program
2
1
2002
vbnet.jp
g
$69.95
01308956 The Complete
36
C++ Training
Course
3
2
2001
cppctc3.
jpg
$109.95
01308955 The Complete e12
Business & eCommerce
Programming
Training Course
1
2
2001
ebecctc.
jpg
$109.95
Fig. 19.10 Data from the Titles table of Books.
 2002 Prentice Hall. All rights reserved.
14
19.3 Relational Database Overview: Books
Database
01308956 The Complete
2
1X
Internet & World
Wide Web
Programming
Training Course
2
2001
iw3ctc2.
jpg
$109.95
01308955 The Complete
47
Perl Training
Course
1
2
2001
perl.jpg
$109.95
01308955 The Complete
63
XML
Programming
Training Course
1
2
2001
xmlctc.j
pg
$109.95
01308957 C How to
25
Program
3
1
2001
$69.95
01308957 C++ How to
17
Program
3
1
2001
01302841 e-Business and e- 1
9X
Commerce How
to Program
1
2001
chtp3.jp
g
cpphtp3.
jpg
ebechtp1
.jpg
01306222 Wireless Internet 1
65
and Mobile
Business How to
Program
1
2001
wireless
.jpg
$69.95
01302841 Perl How to
81
Program
1
1
2001
$69.95
01302841 XML How to
73
Program
1
1
2001
perlhtp1
.jpg
xmlhtp1.
jpg
$69.95
$69.95
$69.95
Fig. 19.10 Data from the Titles table of Books.
 2002 Prentice Hall. All rights reserved.
15
19.3 Relational Database Overview: Books
Database
01308561 The Complete
1
18
Internet and
World Wide Web
Programming
Training Course
2
2000
iw3ctc1.
jpg
$109.95
01301250 Java How to
3
75
Program (Java 2)
1
2000
jhtp3.jp
g
$69.95
01308524 The Complete
81
Java 2 Training
Course
3
2
2000
javactc3
.jpg
$109.95
01303236 e-Business and e- 1
40
Commerce for
Managers
1
2000
ebecm.jp
g
$69.95
01301614 Internet and
1
38
World Wide Web
How to Program
1
2000
iw3htp1.
jpg
$69.95
01301324 Getting Started
97
with Visual C++
6 with an
Introduction to
MFC
01308292 The Complete
93
Visual Basic 6
Training Course
1
1
1999
gsvc.jpg
$49.95
1
2
1999
vbctc1.j
pg
$109.95
01345695 Visual Basic 6
55
How to Program
1
1
1999
$69.95
01327197 Java Multimedia 1
46
Cyber Classroom
2
1998
vbhtp1.j
pg
javactc.
jpg
$109.95
Fig. 19.10 Data from the Titles table of Books.
 2002 Prentice Hall. All rights reserved.
16
19.3 Relational Database Overview: Books
Database
01363258 Java How to
90
Program
1
1
1998
jhtp1.jpg
$0.00
01391630 The Complete
50
C++ Training
Course
2
2
1998
cppctc2.jp
g
$109.95
01352891 C++ How to
06
Program
2
1
1998
cpphtp2.jp
g
$49.95
01379056 The Complete
96
Java Training
Course
2
2
1998
javactc2.j
pg
$109.95
01308292 The Complete
2
77
Java Training
Course (Java 1.1)
2
1998
javactc2.j
pg
$99.95
01389939 Java How to
47
Program (Java
1.1)
2
1
1998
jhtp2.jpg
$49.95
01311733 C++ How to
40
Program
1
1
1994
cpphtp1.jp
g
$69.95
01322611 C How to
97
Program
2
1
1994
chtp2.jpg
$49.95
01311804 C How to
36
Program
1
1
1992
chtp.jpg
$69.95
Fig. 19.10 Da ta from the
Titles ta b le of Books.
Fig. 19.10 Data from the Titles table of Books.
 2002 Prentice Hall. All rights reserved.
17
19.3 Relational Database Overview: Books
Database
Titles
AuthorISBN
Authors
1
authorID
firstName
lastName
¥
¥
authorID
isbn
title
editionNumber
1
1
Publishers
publisherID
¥
publisherName
Fig. 19.11 Table relationships in Books.
 2002 Prentice Hall. All rights reserved.
isbn
copyright
publisherID
imageFile
price
18
19
19.4 Structured Query Language (SQL)
• Used to request data (perform queries) and
manipulate data
 2002 Prentice Hall. All rights reserved.
20
19.4 Structured Query Language (SQL)
SQL keyw ord
De sc rip tio n
SELECT
Select (retrieve) fields from one or more tables.
FROM
Tables from which to get fields or delete records. Required in every
SELECT and DELETE.
WHERE
Criteria for selection that determine the rows to be retrieved.
INNER JOIN
Join records from multiple tables to produce a single set of records.
GROUP BY
Criteria for grouping records.
ORDER BY
Criteria for ordering records.
INSERT
Insert data into a specified table.
UPDATE
Update data in a specified table
DELETE
Delete data from a specified table.
Fig. 19.12 SQL q uery keyword s.
Fig. 19.12 SQL query keywords.
 2002 Prentice Hall. All rights reserved.
21
19.4.1Basic Select Query
• Extracts information from one or more tables in a
database
• Format:
– Basic: SELECT * FROM tableName
– Example: SELECT * FROM Authors
– * extracts all columns
– To get specific fields use a comma separated list instead of *
 2002 Prentice Hall. All rights reserved.
22
19.4.1 Basic Select Query
a uthorID
la stNa m e
1
Deitel
2
Deitel
3
Nieto
4
Steinbuhler
5
Santry
6
Lin
7
Sadhu
8
McPhie
9
Yaeger
10
Zlatkina
11
Wiedermann
12
Liperi
Fig. 19.13
authorID a nd lastName from the Authors ta b le.
Fig. 19.13 authorID and lastName from the Authors table.
 2002 Prentice Hall. All rights reserved.
23
19.4.2 Where Clause
• Used to specify certain criteria in a query
• Basic form:
– SELECT * FROM tableName WHERE criteria
• Example:
– SELECT * FROM Titles WHERE copyright > 1999
• Can use LIKE clause
– Used for pattern matching
• Uses wildcards
– *: zero or more characters take its place
– ?: exactly one character takes its place
 2002 Prentice Hall. All rights reserved.
24
19.4.2 WHERE Clause
Title
ed itio nNumb er
c op yrig ht
Internet and World Wide Web How to Program
2
2002
Java How to Program
4
2002
The Complete Java Training Course
4
2002
The Complete e-Business & e-Commerce Programming
Training Course
1
2001
The Complete Internet & World Wide Web Programming 2
Training Course
2001
The Complete Perl Training Course
1
2001
The Complete XML Programming Training Course
1
2001
C How to Program
3
2001
C++ How to Program
3
2001
The Complete C++ Training Course
3
2001
e-Business and e-Commerce How to Program
1
2001
Internet and World Wide Web How to Program
1
2000
The Complete Internet and World Wide Web
Programming Training Course
1
2000
Java How to Program (Java 2)
3
2000
The Complete Java 2 Training Course
3
2000
XML How to Program
1
2001
Perl How to Program
1
2001
Advanced Java 2 Platform How to Program
1
2002
e-Business and e-Commerce for Managers
1
2000
Wireless Internet and Mobile Business How to Program
1
2001
C# How To Program
1
2002
Python How to Program
1
2002
Visual Basic .NET How to Program
2
Fig. 19.14 Titles with c op yrig hts a fter 1999 from ta b le
2002
Titles.
Fig. 19.14 Titles with copyrights after 1999 from table Titles.
 2002 Prentice Hall. All rights reserved.
25
19.4.2 WHERE Clause
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 19.15 Authors w hose la st na m es sta rt w ith
D fro m the Authors ta b le.
Fig. 19.15 Authors whose last names start with D from the Authors table.
a uthorID
firstNa m e
la stNa m e
3
Tem
Nieto
6
Ted
Lin
11
Ben
Wiedermann
12
Jonathan
Liperi
Fig. 19.16 The a utho rs from the
their sec ond letter.
Authors ta b le w hose la st na m es c onta in i a s
Fig. 19.16 The authors from the Authors table whose last names contain i as their second letter.
 2002 Prentice Hall. All rights reserved.
26
19.4.3 ORDER BY Clause
• Used to arrange results of a query
– Can be ascending or descending order
• Uses ASC and DESC respectively
• Example:
– SELECT authorID FROM Authors ORDER BY
authorID ASC
• Can be used to sort by multiple
fields
 2002 Prentice Hall. All rights reserved.
27
19.4.3 ORDER BY Clause
a uthorID
firstNa m e
la stNa m e
2
Paul
Deitel
1
Harvey
Deitel
6
Ted
Lin
12
Jonathan
Liperi
8
David
McPhie
3
Tem
Nieto
7
Praveen
Sadhu
5
Sean
Santry
4
Kate
Steinbuhler
11
Ben
Wiedermann
9
Cheryl
Yaeger
10
Marina
Zlatkina
Fig. 19.17 Authors from ta b le
Authors in a sc end ing o rd er b y lastName.
Fig. 19.17 Authors from table Authors in ascending order by lastName.
 2002 Prentice Hall. All rights reserved.
28
19.4.3 ORDER BY Clause
a uthorID
firstNa m e
la stNa m e
10
Marina
Zlatkina
9
Cheryl
Yaeger
11
Ben
Wiedermann
4
Kate
Steinbuhler
5
Sean
Santry
7
Praveen
Sadhu
3
Tem
Nieto
8
David
McPhie
12
Jonathan
Liperi
6
Ted
Lin
2
Paul
Deitel
1
Harvey
Deitel
Fig. 19.18 Authors from ta b le
Authors in d esc end ing o rd er b y lastName.
Fig. 19.18 Authors from table Authors in descending order by lastName.
 2002 Prentice Hall. All rights reserved.
29
19.4.3 ORDER BY Clause
a uthorID
firstNa m e
la stNa m e
1
Harvey
Deitel
2
Paul
Deitel
6
Ted
Lin
12
Jonathan
Liperi
8
David
McPhie
3
Tem
Nieto
7
Praveen
Sadhu
5
Sean
Santry
4
Kate
Steinbuhler
11
Ben
Wiedermann
9
Cheryl
Yaeger
10
Marina
Zlatkina
Fig. 19.19 Authors from ta b le
b y firstName.
Authors in a sc end ing o rd er b y lastName a nd
Fig. 19.19 Authors from table Authors in ascending order by lastName and by firstName.
 2002 Prentice Hall. All rights reserved.
30
19.4.3 ORDER BY Clause
isb n
title
ed itio nNum b er
c op y-rig ht
p ric e
0130895601
Advanced Java 2 Platform
How to Program
1
2002
$69.95
0131180436
C How to Program
1
1992
$69.95
0130895725
C How to Program
3
2001
$69.95
0132261197
C How to Program
2
1994
$49.95
0130622214
C# How To Program
1
2002
$69.95
0135289106
C++ How to Program
2
1998
$49.95
0131173340
C++ How to Program
1
1994
$69.95
0130895717
C++ How to Program
3
2001
$69.95
013028419X
e-Business and e-Commerce
How to Program
1
2001
$69.95
0130308978
Internet and World Wide Web 2
How to Program
2002
$69.95
0130161438
Internet and World Wide Web 1
How to Program
2000
$69.95
0130341517
Java How to Program
4
2002
$69.95
0136325890
Java How to Program
1
1998
$0.00
0130284181
Perl How to Program
1
2001
$69.95
0130923613
Python How to Program
1
2002
$69.95
0130293636
Visual Basic .NET How to
Program
2
2002
$69.95
0134569555
Visual Basic 6 How to
Program
1
1999
$69.95
0130622265
Wireless Internet and Mobile
Business How to Program
1
2001
$69.95
0130284173
XML How to Program
1
2001
$69.95
Fig. 19.20 Books from ta b le Titles w hose titles e nd w ith
a sc end ing ord e r b y title.
How to Program in
Fig. 19.20 Books from table Titles whose titles end with How to Program in ascending order by title.
 2002 Prentice Hall. All rights reserved.
31
19.4.4 Merging Data from Multiple Tables:
INNER JOIN
• INNER JOIN
– Merges records from multiple tables into a single record
– Tests for matching values in a common field
– General Form: SELECT * FROM table1 INNER JOIN
table2 ON table1.fieldName=table2.fieldName
– Example: SELECT firstName, isbn FROM Authors INNER
JOIN AuthorISBN ON Authors.authorID=
AuthorISBN.authorID
• Fully-qualified names use the table name and dot
operator followed by the field name
 2002 Prentice Hall. All rights reserved.
32
19.4.4 Merging Data from Multiple Tables:
INNER JOIN
firstName
lastName
isbn
firstName
lastName
isbn
Harvey
Deitel
0130895601
Paul
Deitel
0134569555
Harvey
Deitel
0130284181
Paul
Deitel
0130829277
Harvey
Deitel
0130284173
Paul
Deitel
0130852473
Harvey
Deitel
0130829293
Paul
Deitel
0138993947
Harvey
Deitel
0134569555
Paul
Deitel
0130125075
Harvey
Deitel
0130829277
Paul
Deitel
0130856118
Harvey
Deitel
0130852473
Paul
Deitel
0130161438
Harvey
Deitel
0138993947
Paul
Deitel
013028419x
Harvey
Deitel
0130125075
Paul
Deitel
0139163050
Harvey
Deitel
0130856118
Paul
Deitel
0135289106
Harvey
Deitel
0130161438
Paul
Deitel
0130895717
Harvey
Deitel
013028419x
Paul
Deitel
0132261197
Harvey
Deitel
0139163050
Paul
Deitel
0130895725
Harvey
Deitel
0135289106
Tem
Nieto
0130284181
Harvey
Deitel
0130895717
Tem
Nieto
0130284173
Harvey
Deitel
0132261197
Tem
Nieto
0130829293
Harvey
Deitel
0130895725
Tem
Nieto
0134569555
Paul
Deitel
0130895601
Tem
Nieto
0130856118
Paul
Deitel
0130284181
Tem
Nieto
0130161438
Paul
Deitel
0130284173
Tem
Nieto
013028419x
Paul
Deitel
0130829293
Sean
Santry
0130895601
Fig. 19.21 Portion of the a uthors a nd the ISBN num b ers fo r the b ooks they ha ve
written in a sc end ing ord e r b y lastName a nd firstName.
Fig. 19.21 Portion of the authors and the ISBN numbers for the books they have written in ascending
order by lastName and firstName.
 2002 Prentice Hall. All rights reserved.
33
19.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
• Tables produced by INNER JOIN can be used as
arguments for another INNER JOIN
 2002 Prentice Hall. All rights reserved.
19.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
1
2
3
4
5
6
7
8
9
10
11
SELECT Titles.title, Titles.isbn, Authors.firstName,
Authors.lastName, Titles.copyright,
Publishers.publisherName
FROM
( Publishers INNER JOIN Titles
ON Publishers.publisherID = Titles.publisherID )
INNER JOIN
( Authors INNER JOIN AuthorISBN
ON Authors.authorID = AuthorISBN.authorID )
ON Titles.isbn = AuthorISBN.isbn
ORDER BY Titles.title
Join two created tables if
titlesISBN matches authorsISBN
Fig. 19.22 TitleAuthor query of Books database.
 2002 Prentice Hall. All rights reserved.
34
Join Publishers and
Titles tables if the
publisherID matches
Join Authors and
AuthorISBN if
authorID matches
Sort new table by title
19.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
Title
isb n
firstNa m e
la stNa m e
c op yrig ht
p ub lisherNa m e
Advanced Java 2 Platform
How to Program
0130895601
Paul
Deitel
2002
Prentice Hall
Advanced Java 2 Platform
How to Program
0130895601
Harvey
Deitel
2002
Prentice Hall
Advanced Java 2 Platform
How to Program
0130895601
Sean
Santry
2002
Prentice Hall
C How to Program
0131180436
Harvey
Deitel
1992
Prentice Hall
C How to Program
0131180436
Paul
Deitel
1992
Prentice Hall
C How to Program
0132261197
Harvey
Deitel
1994
Prentice Hall
C How to Program
0132261197
Paul
Deitel
1994
Prentice Hall
C How to Program
0130895725
Harvey
Deitel
2001
Prentice Hall
C How to Program
0130895725
Paul
Deitel
2001
Prentice Hall
C# How To Program
0130622214
Tem
Nieto
2002
Prentice Hall
C# How To Program
0130622214
Paul
Deitel
2002
Prentice Hall
C# How To Program
0130622214
Cheryl
Yaeger
2002
Prentice Hall
C# How To Program
0130622214
Marina
Zlatkina
2002
Prentice Hall
C# How To Program
0130622214
Harvey
Deitel
2002
Prentice Hall
C++ How to Program
0130895717
Paul
Deitel
2001
Prentice Hall
C++ How to Program
0130895717
Harvey
Deitel
2001
Prentice Hall
C++ How to Program
0131173340
Paul
Deitel
1994
Prentice Hall
C++ How to Program
0131173340
Harvey
Deitel
1994
Prentice Hall
C++ How to Program
0135289106
Harvey
Deitel
1998
Prentice Hall
C++ How to Program
0135289106
Paul
Deitel
1998
Prentice Hall
Fig. 19.23 Portion of the result set produced by the query in Fig. 19.22.
 2002 Prentice Hall. All rights reserved.
35
19.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
e-Business and e-Commerce
for Managers
0130323640
Harvey
Deitel
2000
Prentice Hall
e-Business and e-Commerce
for Managers
0130323640
Kate
Steinbuhl 2000
er
Prentice Hall
e-Business and e-Commerce
for Managers
0130323640
Paul
Deitel
2000
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X Harvey
Deitel
2001
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X Paul
Deitel
2001
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X Tem
Nieto
2001
Prentice Hall
Fig. 19.23 Portion of the result se t p rod uc ed b y the q uery in Fig . 19.22.
Fig. 19.23 Portion of the result set produced by the query in Fig. 19.22.
 2002 Prentice Hall. All rights reserved.
36
37
19.4.6 Insert Statement
• Inserts a new record into a table
• Form: INSERT INTO tableName(fieldName1)
VALUES (value1)
• Values must match field names in order and type
 2002 Prentice Hall. All rights reserved.
38
19.4.6 INSERT Statement
a uthorID
firstNa m e
la stNa m e
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Kate
Steinbuhler
5
Sean
Santry
6
Ted
Lin
7
Praveen
Sadhu
8
David
McPhie
9
Cheryl
Yaeger
10
Marina
Zlatkina
11
Ben
Wiedermann
12
Jonathan
Liperi
13
Sue
Smith
Fig. 19.24 Ta b le
Authors a fte r a n INSERT INTO op e ra tion to a d d a rec o rd .
Fig. 19.24 Table Authors after an INSERT INTO operation to add a record.
 2002 Prentice Hall. All rights reserved.
39
19.4.7 UPDATE Statement
• Modifies data in a table
• Form: UPDATE tableName SET fieldName1 =
value1 WHERE criteria
 2002 Prentice Hall. All rights reserved.
19.4.7 UPDATE Statement
a uthorID
firstNa m e
la stNa m e
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Kate
Steinbuhler
5
Sean
Santry
6
Ted
Lin
7
Praveen
Sadhu
8
David
McPhie
9
Cheryl
Yaeger
10
Marina
Zlatkina
11
Ben
Wiedermann
12
Jonathan
Liperi
13
Sue
Jones
Fig. 19.25 Ta b le
Authors a fte r a n UPDATE op era tion to c ha ng e a rec ord .
Fig. 19.25 Table Authors after an UPDATE operation to change a record.
 2002 Prentice Hall. All rights reserved.
40
41
19.4.8 DELETE Statement
• Removes data from a table
• Form: DELETE FROM tableName WHERE criteria
 2002 Prentice Hall. All rights reserved.
19.4.8 DELETE Statement
a uthorID
firstNa m e
la stNa m e
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Kate
Steinbuhler
5
Sean
Santry
6
Ted
Lin
7
Praveen
Sadhu
8
David
McPhie
9
Cheryl
Yaeger
10
Marina
Zlatkina
11
Ben
Wiedermann
12
Jonathan
Liperi
Fig. 19.26 Ta b le
Authors a fte r a DELETE op era tion to rem ove a re c ord .
Fig. 19.26 Table Authors after a DELETE operation to remove a record.
 2002 Prentice Hall. All rights reserved.
42
43
19.5 ADO .NET and Object Model
• Provides an API for accessing database systems
programmatically
• Namespaces:
– System.Data
• System.Data.DataSet: DataTables and their relationships represent a
cache of data. Store data from source in local memory
– System.Data.OleDb: classes to work with any datasource
• OleDbConnection: a connect to a datasource
• OleDbDataAdapter: connect to a datasource through an instance of
OleDbConnection and can populate DataSet with data from a
datasource
• OleDbCommand: represent a SQL command to be executed on a
datasource. Do not cache data in local memory
– System.Data.SqlClient: classes that are optimized to work with MS
SQL Server 2000
 2002 Prentice Hall. All rights reserved.
19.6 Programming with ADO .NET:
Extracting Information from a DBMS
•
Examples that demonstrate how to connect to a
database, query the database and display the results of
the query
1. 工具 -> 連接資料庫
– MicroSoft Jet 4.0 OLE DB Provider
– 選擇 Access 資料庫
2. 在 “伺服器總管” 拖曳 資料庫 至 表單 以 產生
OldDbConnection1
3. 在 “資料” 拖曳 OldDbDataAdapter 至表單, 在精靈的問項保
留預設值, 按 “查詢產生器”, 選擇 Authors 表格, 在 ‘*’ 左
邊打 
4. 在 “資料” 拖曳 DataSet, 選擇 “不具型別資料集”
5. 在程式碼第 12 行( initializeComponent )之後加上
OleDbDataAdapter1.Fill(DataSet1, "Authors")
 2002 Prentice Hall. All rights reserved.
44
45
19.6.1 Connecting to and Querying an
Access Data Source
• Retrieves data and stores it in a DataGrid
 2002 Prentice Hall. All rights reserved.
46
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Outline
' Fig. 19.27: DisplayTable.vb
' Displaying data from a database table.
Public Class FrmTableDisplay
Inherits System.Windows.Forms.Form
DisplayTable.vb
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
Constructor to populate dataSet1
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the
' InitializeComponent() call
' fill DataSet11 with data
OleDbDataAdapter1.Fill(DataSet1, "Authors")
Call method fill to retrieve data
from database associated with
dgdAuthors
oleDbConnection
' bind data in Users table in dataSet11 to
dgdAuthors.SetDataBinding(DataSet1, "Authors")
End Sub ' New
Bind DataGrid to
data
clean
upsource
the component
' Form overrides dispose to
Protected Overloads Overrides Sub Dispose( _
ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub ' Dispose
list.
 2002 Prentice Hall.
All rights reserved.
47
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Friend WithEvents dgdAuthors As System.Windows.Forms.DataGrid
Friend WithEvents OleDbSelectCommand1 As _
System.Data.OleDb.OleDbCommand
Outline
DisplayTable.vb
Friend WithEvents OleDbInsertCommand1 As _
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbUpdateCommand1 As _
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbDeleteCommand1 As _
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbConnection1 As _
System.Data.OleDb.OleDbConnection
Friend WithEvents OleDbDataAdapter1 As _
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents DataSet1 As System.Data.DataSet
' Required by the Windows Form Designer
Private components As System.ComponentModel.Container
' NOTE: The following procedure is required by the
' Windows Form Designer
' It can be modified using the Windows Form Designer.
' Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.dgdAuthors = New System.Windows.Forms.DataGrid()
Me.OleDbSelectCommand1 = _
New System.Data.OleDb.OleDbCommand()
 2002 Prentice Hall.
All rights reserved.
48
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
Me.OleDbInsertCommand1 = _
New System.Data.OleDb.OleDbCommand()
Me.OleDbUpdateCommand1 = _
New System.Data.OleDb.OleDbCommand()
Outline
DisplayTable.vb
Me.OleDbDeleteCommand1 = _
New System.Data.OleDb.OleDbCommand()
Me.OleDbConnection1 = _
New System.Data.OleDb.OleDbConnection()
Me.OleDbDataAdapter1 = _
New System.Data.OleDb.OleDbDataAdapter()
Me.DataSet1 = New System.Data.DataSet()
CType(Me.dgdAuthors, _
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataSet1, _
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
' dgdAuthors
'
Me.dgdAuthors.DataMember = ""
Me.dgdAuthors.Location = New System.Drawing.Point(8, 8)
Me.dgdAuthors.Name = "dgdAuthors"
Me.dgdAuthors.Size = New System.Drawing.Size(304, 256)
Me.dgdAuthors.TabIndex = 0
 2002 Prentice Hall.
All rights reserved.
49
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
'
' OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = _
"SELECT authorID, firstName, lastName FROM Authors"
Outline
DisplayTable.vb
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
'
' OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = _
"INSERT INTO Authors(authorID, firstName, lastName)" & _
"VALUES (?, ?, ?)"
Me.OleDbInsertCommand1.Connection = _
Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("authorID", _
System.Data.OleDb.OleDbType.Numeric, 0, _
System.Data.ParameterDirection.Input, False, _
CType(10, Byte), CType(0, Byte), "authorID", _
System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("firstName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "firstName", _
System.Data.DataRowVersion.Current, Nothing))
 2002 Prentice Hall.
All rights reserved.
50
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
Me.OleDbInsertCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("lastName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "lastName", _
System.Data.DataRowVersion.Current, Nothing))
'
' OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = _
"UPDATE Authors SET authorID = ?, firstName = ?, " & _
"lastName = ? WHERE (authorID = ?)" & _
" AND (firstName = ?) AND (lastName = ?)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("authorID", _
System.Data.OleDb.OleDbType.Numeric, 0, _
System.Data.ParameterDirection.Input, False, _
CType(10, Byte), CType(0, Byte), "authorID", _
System.Data.DataRowVersion.Current, Nothing))
Outline
DisplayTable.vb
Set CommandText for
oleDbUpdateCommand1
Set connection property for
oleDbUpdateCommand1
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("firstName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "firstName", _
System.Data.DataRowVersion.Current, Nothing))
 2002 Prentice Hall.
All rights reserved.
51
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("lastName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "lastName", _
System.Data.DataRowVersion.Current, Nothing))
Outline
DisplayTable.vb
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter _
("Original_authorID", _
System.Data.OleDb.OleDbType.Numeric, 0, _
System.Data.ParameterDirection.Input, False, _
CType(10, Byte), CType(0, Byte), "authorID", _
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter _
("Original_firstName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "firstName", _
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter _
("Original_lastName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "lastName", _
System.Data.DataRowVersion.Original, Nothing))
 2002 Prentice Hall.
All rights reserved.
52
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
'
' OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = _
"DELETE FROM Authors WHERE (authorID = ?) AND " & _
"(firstName = ?) AND (lastName = ?)"
Outline
DisplayTable.vb
Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("authorID", _
System.Data.OleDb.OleDbType.Numeric, 0, _
System.Data.ParameterDirection.Input, False, _
CType(10, Byte), CType(0, Byte), "authorID", _
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("firstName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "firstName", _
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add _
(New System.Data.OleDb.OleDbParameter("lastName", _
System.Data.OleDb.OleDbType.Char, 50, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), "lastName", _
System.Data.DataRowVersion.Original, Nothing))
 2002 Prentice Hall.
All rights reserved.
53
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
"User ID=Admin;Data Source=C:\Documen" & _
"ts and Settings\thiago\Desktop\vbhtp2e\examples\" & _
"Ch19\Fig19_27\Books.mdb;Mode=Sha" & _
"re Deny None;Extended Properties="""";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Regis" & _
"try Path="""";Jet OLEDB:Database Password="""";" & _
"Jet OLEDB:Engine Type=5;Jet OLEDB:Dat" & _
"abase Locking Mode=1;Jet OLEDB:Global Partial " & _
"Bulk Ops=2;Jet OLEDB:Global Bulk T" & _
"ransactions=1;Jet OLEDB:New Database " & _
"Password="""";Jet OLEDB:Create System Databas" & _
"e=False;Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB:Don't Copy Locale on Compact=" & _
"False;Jet OLEDB:Compact Without Replica " & _
"Repair=False;Jet OLEDB:SFP=False"
'
' OleDbDataAdapter1
'
Me.OleDbDataAdapter1.DeleteCommand = _
Me.OleDbDeleteCommand1
Me.OleDbDataAdapter1.InsertCommand = _
Me.OleDbInsertCommand1
Me.OleDbDataAdapter1.SelectCommand = _
Me.OleDbSelectCommand1
Outline
DisplayTable.vb
Initialize oleDbConnection,
and specify path to database
Specify how
oleDbDataAdapter
deletes data
Specify how
oleDbDataAdapter
inserts data
Specify how
oleDbDataAdapter
selects data  2002 Prentice Hall.
All rights reserved.
54
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
Me.OleDbDataAdapter1.TableMappings.AddRange _
(New System.Data.Common.DataTableMapping() _
{New System.Data.Common.DataTableMapping("Table", _
"Authors", New System.Data.Common.DataColumnMapping() _
{New System.Data.Common.DataColumnMapping("authorID", _
"authorID"), New System.Data.Common.DataColumnMapping _
("firstName", "firstName"), _
New System.Data.Common.DataColumnMapping("lastName", _
"lastName")})})
Me.OleDbDataAdapter1.UpdateCommand = _
Me.OleDbUpdateCommand1
'
' DataSet1
'
Me.DataSet1.DataSetName = "NewDataSet"
Me.DataSet1.Locale = _
New System.Globalization.CultureInfo("en-US")
Outline
DisplayTable.vb
Specify how
oleDbDataAdapter
updates data
'
' FrmTableDisplay
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(320, 273)
Me.Controls.AddRange(New System.Windows.Forms.Control() _
{Me.dgdAuthors})
Me.Name = "FrmTableDisplay"
Me.Text = "Table Display"
CType(Me.dgdAuthors, System.ComponentModel. _
ISupportInitialize).EndInit()
CType(Me.DataSet1, System.ComponentModel. _
ISupportInitialize).EndInit()
 2002 Prentice Hall.
All rights reserved.
55
295
296
297
298
299
300
301
302
Me.ResumeLayout(False)
End Sub ' InitializeComponent
Outline
DisplayTable.vb
#End Region
End Class ' FrmTableDisplay
 2002 Prentice Hall.
All rights reserved.
56
19.6.2 Querying the Books Database
• Use SELECT statements on database and display
results
 2002 Prentice Hall. All rights reserved.
57
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
' Fig. 19.28: DisplayQueryResults.vb
' Displays the contents of the authors
Public Class FrmDisplayQueryResult
Inherits System.Windows.Forms.Form
Form FrmDisplayQueryResult contains
Outline
database.
TextBox txtQuery, in which users input SELECT
statements
DisplayQueryResu
lts.vb
Friend WithEvents txtQuery As System.Windows.Forms.TextBox
Friend WithEvents cmdSubmit As System.Windows.Forms.Button
Friend WithEvents dgdResults As System.Windows.Forms.DataGrid
Friend WithEvents BooksConnection As _
System.Data.OleDb.OleDbConnection
Friend WithEvents BooksDataAdapter As _
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents BooksDataSet As System.Data.DataSet
'
After entering a query, the user clicks Button cmdSubmit,
Visual labeled
Studio Submit
.NET generated
Query,code
to view the results of the query
' perform SQL query on data
Private Sub cmdSubmit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdSubmit.Click
Try
' set the text of the SQL query to what the user typed
' in
BooksDataAdapter.SelectCommand.CommandText = _
txtQuery.Text
' clear the DataSet from the previous operation
BooksDataSet.Clear()
 2002 Prentice Hall.
All rights reserved.
58
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
' Fill the data set with the information that results
' from the SQL query
BooksDataAdapter.Fill(BooksDataSet, "Authors")
' Bind the DataGrid to the contents of the DatSet
dgdResults.SetDataBinding(BooksDataSet, "Authors")
Outline
DisplayQueryResu
lts.vb
' display database connection verbose message
Catch exception As System.Data.OleDb.OleDbException
MessageBox.Show("Invalid Query")
End Try
End Sub ' cmdSubmit_Click
End Class ' FrmDisplayQueryResults
Program Output
 2002 Prentice Hall.
All rights reserved.
59
19.7 Programming with ADO.NET: Modifying
a DBMS
• Example implements an address book
– User can insert, locate and update records
 2002 Prentice Hall. All rights reserved.
60
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
' Fig. 19.29: AddressBook.vb
' Using SQL statements to manipulate a database.
Imports System.Windows.Forms
Outline
AddressBook.vb
Public Class FrmAddressBook
Inherits Form
' top set of command buttons
Friend WithEvents cmdFind As Button
Friend WithEvents cmdAdd As Button
Friend WithEvents cmdUpdate As Button
Friend WithEvents cmdClear As Button
Friend WithEvents cmdHelp As Button
' textbox identifier labels
Friend WithEvents lblId As Label
Friend WithEvents lblFirst As Label
Friend WithEvents lblLast As Label
Friend WithEvents lblAddress As Label
Friend WithEvents lblCity As Label
Friend WithEvents lblState As Label
Friend WithEvents lblZip As Label
Friend WithEvents lblCountry As Label
Friend WithEvents lblEmail As Label
Friend WithEvents lblPhone As Label
Friend WithEvents lblFax As Label
' input textboxes
Friend WithEvents
Friend WithEvents
Friend WithEvents
Friend WithEvents
Friend WithEvents
Friend WithEvents
txtId As TextBox
txtFirst As TextBox
txtLast As TextBox
txtAddress As TextBox
txtCity As TextBox
txtState As TextBox
 2002 Prentice Hall.
All rights reserved.
61
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Friend
Friend
Friend
Friend
Friend
WithEvents
WithEvents
WithEvents
WithEvents
WithEvents
txtZip As TextBox
txtCountry As TextBox
txtEmail As TextBox
txtPhone As TextBox
txtFax As TextBox
Outline
AddressBook.vb
' query status display textbox
Friend WithEvents txtStatus As TextBox
' database connection
Friend WithEvents AddressBookConnection As _
System.Data.OleDb.OleDbConnection
' database adapter
Friend WithEvents AddressBookDataAdapter As _
System.Data.OleDb.OleDbDataAdapter
' query dataset
Friend WithEvents AddressBookDataSet As System.Data.DataSet
' constructor
Public Sub New()
MyBase.New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent call
' open connection
AddressBookConnection.Open()
End Sub ' New
' Visual Studio .NET generated code
 2002 Prentice Hall.
All rights reserved.
62
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
Outline
' finds record in database
Private Sub cmdFind_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdFind.Click
AddressBook.vb
Method Clear of class DataSet is invoked to empty the DataSet of
' ensure userany
input
priorlast
data name
If txtLast.Text <> "" Then
Event handler cmdFind_Click performs the SELECT query on the
clear
DataSet
from with
lastthe
operation
database for'the
record
associated
String entered in txtLast
Try
AddressBookDataSet.Clear()
' create SQL query to find contact
' with specified last name
AddressBookDataAdapter.SelectCommand.CommandText = _
"SELECT * FROM addresses WHERE " & _
The TextBoxes
are updated with
"lastname
= '" & txtLast.Text
& "' a"call to method Display
' fill AddressBookDataSet with the rows resulting
' from the query
AddressBookDataAdapter.Fill(AddressBookDataSet)
' display information
Display(AddressBookDataSet)
txtStatus.Text &= vbCrLf & "Query Successful " & _
vbCrLf
' prompt user for last name
Else
txtLast.Text = _
"Enter last name here then press Find"
End If
' display verbose information with database exception
Catch oleDbExceptionParameter As _
 2002 Prentice Hall.
All rights reserved.
63
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
Outline
System.Data.OleDb.OleDbException
Console.WriteLine(oleDbExceptionParameter.StackTrace)
txtStatus.Text &= oleDbExceptionParameter.ToString
' display message box when invalid operation
Catch invalidOperationExceptionParameter
As _
Method cmdAdd_Click performs INSERT
InvalidOperationException
AddressBook.vb
and UPDATE operations
MessageBox.Show( _
invalidOperationExceptionParameter.Message)
End Try
End Sub ' cmdFind_Click
' adds record to database
Private Sub cmdAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdAdd.Click
Try
' ensure first and last name input
If (txtLast.Text <> "" AndAlso txtFirst.Text <> "") Then
' create the SQL query to insert a row
AddressBookDataAdapter.InsertCommand.CommandText = _
"INSERT INTO addresses(firstname, " & _
"lastname, address, city, " & _
"stateorprovince, postalcode, country, " & _
"emailaddress, homephone, faxnumber) " & _
"VALUES('" & txtFirst.Text & "' , " & _
"'" & txtLast.Text & "' , " & _
"'" & txtAddress.Text & "' , " & _
"'" & txtCity.Text & "' , " & _
"'" & txtState.Text & "' , " & _
 2002 Prentice Hall.
All rights reserved.
64
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
"'"
"'"
"'"
"'"
"'"
&
&
&
&
&
txtZip.Text & "' , "
txtCountry.Text & "'
txtEmail.Text & "' ,
txtPhone.Text & "' ,
txtFax.Text & "')"
&
,
"
"
_
" & _
& _
& _
Outline
AddressBook.vb
' notify the user the query is being sent
txtStatus.Text &= vbCrLf & "Sending query: " & _
AddressBookDataAdapter.InsertCommand. _
CommandText & vbCrLf
' send query
AddressBookDataAdapter.InsertCommand. _
ExecuteNonQuery()
txtStatus.Text &= vbCrLf & "Query successful"
' prompt user to input first and last name
Else
txtStatus.Text &= vbCrLf & _
"Enter at least first and last name then " & _
"press Add" & vbCrLf
End If
' display verbose information when database exception
Catch oleDbExceptionParameter As _
System.Data.OleDb.OleDbException
Console.WriteLine(oleDbExceptionParameter.StackTrace)
txtStatus.Text &= oleDbExceptionParameter.ToString
End Try
End Sub ' cmdAdd_Click
' updates entry in database
 2002 Prentice Hall.
All rights reserved.
65
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
Private Sub cmdUpdate_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdUpdate.Click
Try
Outline
AddressBook.vb
' make sure user has already found
' record to update
If txtId.Text <> "" Then
' set SQL query to update all fields in
' table where id number matches id in
' idTextBox
AddressBookDataAdapter.UpdateCommand.CommandText = _
"UPDATE addresses SET firstname=" & _
"'" & txtFirst.Text & "' , " & _
"lastname = '" & txtLast.Text & "' , " & _
"address='" & txtAddress.Text & "' , " & _
"city='" & txtCity.Text & "' , " & _
"stateorprovince= " & _
"'" & txtState.Text & "', " & _
"postalcode='" & txtZip.Text & "', " & _
"country='" & txtCountry.Text & "' , " & _
"emailaddress='" & txtEmail.Text & "' , " & _
"homephone='" & txtPhone.Text & "' , " & _
"faxnumber='" & txtFax.Text & "' " & _
"WHERE id=" & txtId.Text & " ; "
' notify user that query is being sent
txtStatus.Text &= vbCrLf & "Sending query: " & _
AddressBookDataAdapter.UpdateCommand. _
CommandText & vbCrLf
' execute query
AddressBookDataAdapter.UpdateCommand. _
ExecuteNonQuery()
 2002 Prentice Hall.
All rights reserved.
66
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
txtStatus.Text &= vbCrLf & "Query Successful" & _
vbCrLf
' prompt user to input existing record
Else
txtStatus.Text &= vbCrLf & _
"You may only update an existing record. " & _
"Use Find to locate the record, then " & _
"modify the information and press Update." & _
vbCrLf
End If
Outline
AddressBook.vb
' display verbose information when database exception
Catch oleDbExceptionParameter As _
System.Data.OleDb.OleDbException
Console.WriteLine(oleDbExceptionParameter.StackTrace)
txtStatus.Text &= oleDbExceptionParameter.ToString
End Try
End Sub ' cmdUpdate_Click
' clears all information in textboxes
Private Sub cmdClear_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdClear.Click
txtId.Clear()
ClearTextBoxes()
End Sub ' cmdClear_Click
' displays information on application use
Private Sub cmdHelp_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdHelp.Click
 2002 Prentice Hall.
All rights reserved.
67
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
Outline
txtStatus.AppendText(vbCrLf
& _ interface with data from the
Method
Display updates the user
"Click Find to locate a record" & vbCrLf & _
newly
retrieved
booka record
"Click
Add address
to insert
new record." & vbCrLf & _
"Click Update to update the information in a " & _
"record " & vbCrLf & "Click Clear to empty the " & _
"textboxes")
End Sub ' cmdHelp_Click
AddressBook.vb
' displays data in dataset
Private Sub Display(ByVal dataset As DataSet)
Try
Checks whether the queryRetrieves
returned the
anyfield
rowswith index 0, 0 and stores the
value in variable recordNumber
' get first DataTable - there will be one
Dim dataTable As DataTable = dataset.Tables(0)
' ensure dataTable not empty
Retrieve<>the
fields of data from the
If dataTable.Rows.Count
0 remaining
Then
DataTable
the user interface
Dim recordNumber
As Integerto= populate
_
Convert.ToInt32(dataTable.Rows(0)(0))
txtId.Text = recordNumber.ToString
txtFirst.Text = _
Convert.ToString(dataTable.Rows(0)(1))
txtLast.Text = _
Convert.ToString(dataTable.Rows(0)(2))
txtAddress.Text = _
Convert.ToString(dataTable.Rows(0)(3))
txtCity.Text = _
Convert.ToString(dataTable.Rows(0)(4))
txtState.Text = _
 2002 Prentice Hall.
All rights reserved.
68
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
Convert.ToString(dataTable.Rows(0)(5))
txtZip.Text = _
Convert.ToString(dataTable.Rows(0)(6))
Outline
AddressBook.vb
txtCountry.Text = _
Convert.ToString(dataTable.Rows(0)(7))
txtEmail.Text = _
Convert.ToString(dataTable.Rows(0)(8))
txtPhone.Text = _
Convert.ToString(dataTable.Rows(0)(9))
txtFax.Text = _
Convert.ToString(dataTable.Rows(0)(10))
' display not-found message
Else
txtStatus.Text &= vbCrLf & "No record found“ & vbCrLf
End If
' display verbose information when database exception
Catch oleDbExceptionParameter As _
System.Data.OleDb.OleDbException
Clear button
clears the text from the
The
TextBoxes using method ClearTextBoxes
Console.WriteLine(oleDbExceptionParameter.StackTrace)
txtStatus.Text &= oleDbExceptionParameter.ToString
End Try
End Sub ' Display
' clears text boxes
Private Sub ClearTextBoxes()
txtFirst.Clear()
 2002 Prentice Hall.
All rights reserved.
69
316
317
318
319
320
321
322
323
324
325
326
327
txtLast.Clear()
txtAddress.Clear()
txtCity.Clear()
txtState.Clear()
txtZip.Clear()
txtCountry.Clear()
txtEmail.Clear()
txtPhone.Clear()
txtFax.Clear()
End Sub ' ClearTextBoxes
Outline
AddressBook.vb
End Class ' FrmAddressBook
 2002 Prentice Hall.
All rights reserved.
70
Outline
AddressBook.vb
 2002 Prentice Hall.
All rights reserved.
71
Outline
AddressBook.vb
 2002 Prentice Hall.
All rights reserved.
72
Outline
AddressBook.vb
 2002 Prentice Hall.
All rights reserved.
73
Outline
AddressBook.vb
 2002 Prentice Hall.
All rights reserved.
74
19.8 Reading and Writing XML Files
• ADO.NET can convert data from data source into
XML files
– Uses methods WriteXml, ReadXml andGetXml
 2002 Prentice Hall. All rights reserved.
75
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Outline
' Fig. 19.30 XMLWriter.vb
' Demonstrates generating XML from an ADO.NET DataSet
Public Class FrmXMLWriter
Inherits System.Windows.Forms.Form
XMLWriter.vb
' constructor
Public Sub New()
MyBase.New()
' This call is required by the Windows Form Designer.
Establishes
a connection to the Baseball database
InitializeComponent()
Method Fill of class OleDbDataAdapter is called to
populate
with data from the
' Add any initialization
after BaseballDataSet
the
' InitializeComponent()
call
Players
table in the Baseball database
' open database connection
BaseballConnection.Open()
Binds the
dgdPlayers to BaseballDataSet to
the information
to the user
with display
data from
OleDbDataAdapter
' fill DataSet
BaseballDataAdapter.Fill(BaseballDataSet, "Players")
' bind DataGrid to DataSet
dgdPlayers.SetDataBinding(BaseballDataSet, "Players")
End Sub
Friend WithEvents cmdWrite As System.Windows.Forms.Button
Friend WithEvents dgdPlayers As System.Windows.Forms.DataGrid
Friend WithEvents txtOutput As System.Windows.Forms.TextBox
Friend WithEvents BaseballConnection As _
System.Data.OleDb.OleDbConnection
Friend WithEvents BaseballDataAdapter As _
System.Data.OleDb.OleDbDataAdapter
 2002 Prentice Hall.
All rights reserved.
76
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Friend WithEvents BaseballDataSet As System.Data.DataSet
Outline
DataSet method WriteXml is invoked to generate an
XML representation of the data contained in the DataSet
XMLWriter.vb
representation
of DataSet
clicked
and then writes
the XMLwhen
to thebutton
specified
file
' Visual Studio .NET generated code
' write XML
Private Sub cmdWrite_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdWrite.Click
' write XML representation of DataSet to file
BaseballDataSet.WriteXml("Players.xml")
' display XML in TextBox
txtOutput.Text &= "Writing the following XML:" & _
vbCrLf & BaseballDataSet.GetXml() & vbCrLf
End Sub ' cmWrite_Click
End Class ' FrmXMLWriter
 2002 Prentice Hall.
All rights reserved.
77
19.8 Reading and Writing XML Documents
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Players>
<firstName>John</firstName>
<lastName>Doe</lastName>
<battingAverage>0.375</battingAverage>
<playerID>1</playerID>
</Players>
<Players>
<firstName>Jack</firstName>
<lastName>Smith</lastName>
<battingAverage>0.223</battingAverage>
<playerID>2</playerID>
</Players>
<Players>
<firstName>George</firstName>
<lastName>O'Malley</lastName>
<battingAverage>0.444</battingAverage>
<playerID>3</playerID>
</Players>
</NewDataSet>
Fig. 19.31 XML document generated from DataSet in DatabaseXMLWriter.
 2002 Prentice Hall. All rights reserved.