Databases, SQL and ADO - cs.tufts.edu

59 Pages·2001·1.17 MB·English

19
Databases, SQL and
ADO.NET
Objectives
• Tounderstandtherelationaldatabasemodel.
• Tounderstandbasicdatabasequeriesusing
StructuredQueryLanguage(SQL).
• Tousetheclassesandinterfacesofnamespace
System.Datatomanipulatedatabases.
• TounderstandanduseADO.NET’sdisconnected
model.
• Tousetheclassesandinterfacesofnamespace
System.Data.OleDb.
Itisacapitalmistaketotheorizebeforeonehasdata.
ArthurConanDoyle
Nowgo,writeitbeforetheminatable,andnoteitinabook,
thatitmaybeforthetimetocomeforeverandever.
TheHolyBible:TheOldTestament
Let'slookattherecord.
AlfredEmanuelSmith
Getyourfactsfirst,andthenyoucandistortthemasmuchas
youplease.
MarkTwain
Iliketwokindsofmen:domesticandforeign.
MaeWest
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
1324 Databases,SQLandADO.NET Chapter19
Outline
19.1 Introduction
19.2 RelationalDatabaseModel
19.3 RelationalDatabaseOverview:TheBooksDatabase
19.4 StructuredQueryLanguage(SQL)
19.4.1 BasicSELECTQuery
19.4.2 WHEREClause
19.4.3 ORDERBYClause
19.4.4 MergingDatafromMultipleTables:INNERJOIN
19.4.5 JoiningDatafromTablesAuthors,AuthorISBN,Titles
andPublishers
19.4.6 INSERTStatement
19.4.7 UPDATEStatement
19.4.8 DELETEStatement
19.5 ADO.NETObjectModel
19.6 ProgrammingwithADO.NET:ExtractingInformationfromaDBMS
19.6.1 ConnectingtoandQueryinganAccessDataSource
19.6.2 QueryingtheBooksDatabase
19.7 ProgrammingwithADO.NET:ModifyingaDBMS
19.8 ReadingandWritingXMLFiles
Summary•Terminology•Self-ReviewExercises•AnswerstoSelf-ReviewExercises•Exercises•
Bibliography
19.1 Introduction
Adatabaseisacollectionofdata.Therearemanydifferentstrategiesfororganizingdata
to facilitate easy access and manipulation of the data. A database management system
(DBMS)providesmechanismsforstoringandorganizingdatainamannerconsistentwith
thedatabase’sformat.Databasemanagementsystemsallowfortheaccessandstorageof
datawithoutworryingabouttheinternalrepresentationofdatabases.
Today’s most popular database systems are relational databases. A language called
StructuredQueryLanguage(SQL—pronouncedasitsindividuallettersoras“sequel”)is
usedalmostuniversallywithrelationaldatabasesystemstoperformqueries(i.e.,torequest
informationthatsatisfiesgivencriteria)andtomanipulatedata.[Note:Thewritinginthis
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
Chapter19 Databases,SQLandADO.NET 1325
chapterassumesthatSQLispronouncedasitsindividualletters.Forthisreason,weoften
precedeSQLwiththearticle“an”asin“anSQLdatabase”or“anSQLstatement.”]
Some popular enterprise-level relational database systems include Microsoft SQL
Server, Oracle™, Sybase™, DB2™, Informix™ and MySQL™. In this chapter, we
present examples using Microsoft Access—a relational database system that comes with
MicrosoftOffice.
Aprogramminglanguageconnectsto,andinteractswith,relationaldatabasesviaan
interface—software that facilitates communications between a database management
systemandaprogram.C#programmerscommunicatewithdatabasesandmanipulatetheir
data using the next generation of Microsoft ActiveX Data Objects™ (ADO), ADO.NET.
This development framework is a disconnected model and uses XML for data transmis-
sionstoachieveinteroperabilitywithotherplatforms.
19.2 Relational Database Model
Therelationaldatabasemodelisalogicalrepresentationofdatathatallowstherelation-
shipsbetweenthedatatobeconsideredwithoutconcernforthephysicalstructureofthe
data.Arelationaldatabaseiscomposedoftables.Figure19.1illustratesasampletablethat
mightbeusedinapersonnelsystem.ThetablenameisEmployeeanditsprimarypurpose
istoillustratethespecificattributesofanemployee.Aparticularrowofthetableiscalled
arecord(orrow).Thistableconsistsofsixrecords.Thenumberfield(orcolumn)ofeach
recordinthetableistheprimarykeyforreferencingdatainthetable.Aprimarykeyisa
field (or fields) in a table that contain(s) unique data that cannot be duplicated in other
recordsofthattable.Thisguaranteeseachrecordcanbeidentifiedbyauniquevalue.Ex-
amplesofprimary-keyfieldsareasocialsecuritynumber,anemployeeIDandapartnum-
ber in an inventory system. The records of Fig.19.1 are ordered by primary key. In this
case,therecordsareinincreasingorder(decreasingordercouldbeused).
number name department salary location
23603 Jones 413 1100 NewJersey
24568 Kerwin 413 2000 NewJersey
Row/Record 34589 Larson 642 1800 LosAngeles
35761 Myers 611 1400 Orlando
47132 Neumann 413 9000 NewJersey
78321 Stephens 611 8500 Orlando
Primarykey Column/Field
Fig.19.1 RelationaldatabasestructureofanEmployeetable.
Eachcolumnofthetablerepresentsadifferentfield(orcolumnorattribute).Records
normally are unique (by primary key) within a table, but particular field values may be
duplicatedbetweenrecords.Forexample,threedifferentrecordsintheEmployeetable’s
Departmentfieldcontainthenumber413.
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
1326 Databases,SQLandADO.NET Chapter19
Different users of a database often are interested in different data and different rela-
tionships among those data. Some users require only subsets of the table columns. To
obtaintablesubsets,weuseSQLstatementstospecifythedatatoselectfromatable.SQL
provides a complete set of commands (including SELECT) that enable programmers to
define complex queries to select data from a table. The results of a query commonly are
called result sets (or record sets). For example, we might select data from the table in
Fig.19.1tocreateanewresultsetthatshowsthelocationofeachdepartment.Thisresult
setappearsinFig.19.2.SQLqueriesarediscussedinSection19.4.
department location
413 NewJersey
611 Orlando
642 LosAngeles
Fig.19.2 ResultsetformedbyselectingDepartmentandLocationdata
fromtheEmployeetable.
19.3 Relational Database Overview: The Books Database
ThissectiongivesanoverviewofSQLinthecontextofasampleBooksdatabasewecre-
atedforthischapter.BeforewediscussSQL,weoverviewthetablesoftheBooksdata-
base. We use this to introduce various database concepts, including the use of SQL to
obtainusefulinformationfromthedatabaseandtomanipulatethedatabase.Weprovidea
scripttocreatethedatabase.Youcanfindthescriptintheexamplesdirectoryforthischap-
terontheCDthataccompaniesthisbook.Section19.6explainshowtousethisscript.
The database consists of four tables: Authors, Publishers, AuthorISBN and
Titles.TheAuthorstable(describedinFig.19.3)consistsofthreefields(orcolumns)
thatmaintaineachauthor’suniqueIDnumber,firstnameandlastname.Figure19.4con-
tainsthedatafromtheAuthorstableoftheBooksdatabase.
Field Description
authorID Author’sIDnumberinthedatabase.IntheBooksdatabase,thisintegerfieldis
definedasanautoincrementedfield.Foreachnewrecordinsertedinthistable,
thedatabaseautomaticallyincrementstheauthorIDvaluetoensurethateach
recordhasauniqueauthorID.Thisfieldrepresentsthetable’sprimarykey.
firstName Author’sfirstname(astring).
lastName Author’slastname(astring).
Fig.19.3 AuthorstablefromBooks.
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
Chapter19 Databases,SQLandADO.NET 1327
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 DatafromtheAuthorstableofBooks.
The Publishers table (described in Fig.19.5) consists of two fields representing
each publisher’s unique ID and name. Figure19.6 contains the data from the Pub-
lisherstableoftheBooksdatabase.
Field Description
publisherID Thepublisher’sIDnumberinthedatabase.Thisautoincrementedinteger
isthetable’sprimary-keyfield.
publisherName Thenameofthepublisher(astring).
Fig.19.5 PublisherstablefromBooks.
publisherID publisherName
1 PrenticeHall
2 PrenticeHallPTG
Fig.19.6 DatafromthePublisherstableofBooks.
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
1328 Databases,SQLandADO.NET Chapter19
TheAuthorISBNtable(describedinFig.19.7)consistsoftwofieldsthatmaintain
each ISBN number and its corresponding author’s ID number. This table helps associate
thenamesoftheauthorswiththetitlesoftheirbooks.Figure19.8containsthedatafrom
theAuthorISBNtableoftheBooksdatabase.ISBNisanabbreviationfor“International
Standard Book Number”—a numbering scheme with which publishers worldwide give
everybookauniqueidentificationnumber.[Note:Tosavespace,wehavesplitthecontents
ofthisfigureintotwocolumns,eachcontainingtheauthorIDandisbnfields.]
Field Description
authorID Theauthor’sIDnumber,whichallowsthedatabasetoassociateeach
bookwithaspecificauthor.TheintegerIDnumberinthisfieldmust
alsoappearintheAuthorstable.
isbn TheISBNnumberforabook(astring).
Fig.19.7 AuthorISBNtablefromBooks.
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
Fig.19.8 PortionofdatafromtableAuthorISBNindatabaseBooks.
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
Chapter19 Databases,SQLandADO.NET 1329
authorID isbn authorID isbn
2 0132261197 3 0130284173
2 0130895717 3 0130284181
2 0135289106 4 0130895601
Fig.19.8 PortionofdatafromtableAuthorISBNindatabaseBooks.
TheTitlestable(describedinFig.19.9)consistsofsixfieldsthatmaintaingeneral
information about each book in the database, including the ISBN number, title, edition
number,copyrightyear,publisher’sIDnumber,nameofafilecontaininganimageofthe
bookcover,andfinally,theprice.Figure19.10containsthedatafromtheTitlestable.
Field Description
isbn ISBNnumberofthebook(astring).
title Titleofthebook(astring).
editionNumber Editionnumberofthebook(aninteger).
copyright Copyrightyearofthebook(astring).
publisherID Publisher’sIDnumber(aninteger).ThisvaluemustcorrespondtoanID
numberinthePublisherstable.
imageFile Nameofthefilecontainingthebook’scoverimage(astring).
price Suggestedretailpriceofthebook(arealnumber).[Note:Theprices
showninthisbookareforexamplepurposesonly.]
Fig.19.9 TitlestablefromBooks.
copy
-
edition publish righ pric
isbn title -Number -erID t imageFile e
013092361 PythonHowto 1 1 2002 python.jpg $69.95
3 Program
013062221 C#HowtoPro- 1 1 2002 cshtp.jpg $69.95
4 gram
Fig.19.10 DatafromtheTitlestableofBooks(part1of4).
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
1330 Databases,SQLandADO.NET Chapter19
copy
-
edition publish righ pric
isbn title -Number -erID t imageFile e
013034151 JavaHowtoPro- 4 1 2002 jhtp4.jpg $69.95
7 gram
013064934 TheCompleteJava 4 2 2002 javactc4.jp $109.95
1 TrainingCourse g
013089560 AdvancedJava2 1 1 2002 advjhtp1.jp $69.95
1 PlatformHowto g
Program
013030897 InternetandWorld 2 1 2002 iw3htp2.jpg $69.95
8 WideWebHowto
Program
013029363 VisualBasic.NET 2 1 2002 vbnet.jpg $69.95
6 HowtoProgram
013089563 TheCompleteC++ 3 2 2001 cppctc3.jpg $109.95
6 TrainingCourse
013089551 TheCompletee- 1 2 2001 ebecctc.jpg $109.95
2 Business&e-Com-
merceProgram-
mingTraining
Course
013089561 TheComplete 2 2 2001 iw3ctc2.jpg $109.95
X Internet&World
WideWebPro-
grammingTrain-
ingCourse
013089554 TheCompletePerl 1 2 2001 perl.jpg $109.95
7 TrainingCourse
013089556 TheComplete 1 2 2001 xmlctc.jpg $109.95
3 XMLProgram-
mingTraining
Course
013089572 CHowtoProgram 3 1 2001 chtp3.jpg $69.95
5
013089571 C++HowtoPro- 3 1 2001 cpphtp3.jpg $69.95
7 gram
013028419 e-Businessande- 1 1 2001 ebechtp1.jp $69.95
X CommerceHowto g
Program
013062226 WirelessInternet 1 1 2001 wire- $69.95
5 andMobileBusi- less.jpg
nessHowtoPro-
gram
013028418 PerlHowtoPro- 1 1 2001 perlhtp1.jp $69.95
1 gram g
Fig.19.10 DatafromtheTitlestableofBooks(part2of4).
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
Chapter19 Databases,SQLandADO.NET 1331
copy
-
edition publish righ pric
isbn title -Number -erID t imageFile e
013028417 XMLHowtoPro- 1 1 2001 xmlhtp1.jpg $69.95
3 gram
013085611 TheComplete 1 2 2000 iw3ctc1.jpg $109.95
8 InternetandWorld
WideWebPro-
grammingTrain-
ingCourse
013012507 JavaHowtoPro- 3 1 2000 jhtp3.jpg $69.95
5 gram(Java2)
013085248 TheCompleteJava 3 2 2000 javactc3.jp $109.95
1 2TrainingCourse g
013032364 e-Businessande- 1 1 2000 ebecm.jpg $69.95
0 Commercefor
Managers
013016143 InternetandWorld 1 1 2000 iw3htp1.jpg $69.95
8 WideWebHowto
Program
013013249 GettingStarted 1 1 1999 gsvc.jpg $49.95
7 withVisualC++6
withanIntroduc-
tiontoMFC
013082929 TheComplete 1 2 1999 vbctc1.jpg $109.95
3 VisualBasic6
TrainingCourse
013456955 VisualBasic6 1 1 1999 vbhtp1.jpg $69.95
5 HowtoProgram
013271974 JavaMultimedia 1 2 1998 javactc.jpg $109.95
6 CyberClassroom
013632589 JavaHowtoPro- 1 1 1998 jhtp1.jpg $0.00
0 gram
013916305 TheCompleteC++ 2 2 1998 cppctc2.jpg $109.95
0 TrainingCourse
013528910 C++HowtoPro- 2 1 1998 cpphtp2.jpg $49.95
6 gram
013790569 TheCompleteJava 2 2 1998 javactc2.jp $109.95
6 TrainingCourse g
013082927 TheCompleteJava 2 2 1998 javactc2.jp $99.95
7 TrainingCourse g
(Java1.1)
013899394 JavaHowtoPro- 2 1 1998 jhtp2.jpg $49.95
7 gram(Java1.1)
Fig.19.10 DatafromtheTitlestableofBooks(part3of4).
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.
1332 Databases,SQLandADO.NET Chapter19
copy
-
edition publish righ pric
isbn title -Number -erID t imageFile e
013117334 C++HowtoPro- 1 1 1994 cpphtp1.jpg $69.95
0 gram
013226119 CHowtoProgram 2 1 1994 chtp2.jpg $49.95
7
013118043 CHowtoProgram 1 1 1992 chtp.jpg $69.95
6
Fig.19.10 DatafromtheTitlestableofBooks(part4of4).
Figure19.11illustratestherelationshipsamongthetablesintheBooksdatabase.The
firstlineineachtableisthetable’sname.Thefieldnameinitaliccontainsthattable’spri-
marykey.Atable’sprimarykeyuniquelyidentifieseachrecordinthetable.Everyrecord
musthaveavalueintheprimary-keyfield,andthevaluemustbeunique.Thisisknownas
theRuleofEntityIntegrity.NotethattheAuthorISBNhastwofieldsinitalic.Thisindicates
thatthesetwofieldsformacompoundprimarykey—eachrecordinthetablemusthavea
uniqueauthorIDandisbncombination.Forexample,theremayexistseveralrecords
withanauthorIDof2andseveralrecordswithanisbnof0130895601,butonlyone
recordcanhaveanauthorIDof2andanisbnof0130895601.
Authors AuthorISBN Titles
1 ∞ 1
authorID authorID isbn
∞
firstName isbn title
lastName editionNumber
copyright
∞
Publishers publisherID
1
publisherID imageFile
publisherName price
Fig.19.11 TablerelationshipsinBooks.
Common Programming Error 19.1
Notprovidingavalueforaprimary-keyfieldineveryrecordbreakstheRuleofEntityInteg-
rityandcausestheDBMStoreportanerror.
19.1
Common Programming Error 19.2
Providingduplicatevaluesfortheprimary-keyfieldinmultiplerecordscausestheDBMSto
reportanerror.
19.2
Copyright©2002Prentice-Hall,Inc.Allrightsreserved.