Chapter 19 Databases SQL and ADO NET Outline
Chapter 19 – Databases, SQL, and ADO. NET Outline 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, Author. ISBN, 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.
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. 6
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 Fig. 19. 3 Authors table from Books. 2002 Prentice Hall. All rights reserved. 8
19. 3 Relational Database Overview: Books Database Fig. 19. 4 Data from the Authors table of Books. 2002 Prentice Hall. All rights reserved. 9
19. 3 Relational Database Overview: Books Database Fig. 19. 5 Publishers table from 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 Fig. 19. 7 Author. ISBN table from Books. 2002 Prentice Hall. All rights reserved. 11
19. 3 Relational Database Overview: Books Database Fig. 19. 8 Portion of data from table Author. ISBN in database Books. 2002 Prentice Hall. All rights reserved. 12
19. 3 Relational Database Overview: Books Database Fig. 19. 9 Titles table from Books. 2002 Prentice Hall. All rights reserved. 13
19. 3 Relational Database Overview: Books Database Fig. 19. 10 Data from the Titles table of Books. 2002 Prentice Hall. All rights reserved. 14
19. 3 Relational Database Overview: Books Database Fig. 19. 10 Data from the Titles table of Books. 2002 Prentice Hall. All rights reserved. 15
19. 3 Relational Database Overview: Books Database Fig. 19. 10 Data from the Titles table of Books. 2002 Prentice Hall. All rights reserved. 16
19. 3 Relational Database Overview: Books Database Fig. 19. 10 Data from the Titles table of Books. 2002 Prentice Hall. All rights reserved. 17
19. 3 Relational Database Overview: Books Database Authors 1 author. ID first. Name last. Name ¥ Author. ISBN ¥ author. ID isbn publisher. ID 1 ¥ publisher. Name Fig. 19. 11 Table relationships in Books. 2002 Prentice Hall. All rights reserved. isbn title edition. Number 1 Publishers Titles copyright publisher. ID image. File 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) Fig. 19. 12 SQL query keywords. 2002 Prentice Hall. All rights reserved.
21 19. 4. 1 Basic Select Query • Extracts information from one or more tables in a database • Format: – – Basic: SELECT * FROM table. Name 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 Fig. 19. 13 author. ID and last. Name 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 table. Name 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 Fig. 19. 14 Titles with copyrights after 1999 from table Titles. 2002 Prentice Hall. All rights reserved.
25 19. 4. 2 WHERE Clause Fig. 19. 15 Authors whose last names start with D from the Authors table. 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 author. ID FROM Authors ORDER BY author. ID ASC • Can be used to sort by multiple fields 2002 Prentice Hall. All rights reserved.
27 19. 4. 3 ORDER BY Clause Fig. 19. 17 Authors from table Authors in ascending order by last. Name. 2002 Prentice Hall. All rights reserved.
28 19. 4. 3 ORDER BY Clause Fig. 19. 18 Authors from table Authors in descending order by last. Name. 2002 Prentice Hall. All rights reserved.
29 19. 4. 3 ORDER BY Clause Fig. 19 Authors from table Authors in ascending order by last. Name and by first. Name. 2002 Prentice Hall. All rights reserved.
30 19. 4. 3 ORDER BY Clause 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.
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 table 1 INNER JOIN table 2 ON table 1. field. Name=table 2. field. Name – Example: SELECT first. Name, isbn FROM Authors INNER JOIN Author. ISBN ON Authors. author. ID= Author. ISBN. author. ID • Fully-qualified names use the table name and dot operator followed by the field name 2002 Prentice Hall. All rights reserved. 31
19. 4. 4 Merging Data from Multiple Tables: INNER JOIN Fig. 19. 21 Portion of the authors and the ISBN numbers for the books they have written in ascending order by last. Name and first. Name. 2002 Prentice Hall. All rights reserved. 32
19. 4. 5 Joining Data from Tables Authors, Author. ISBN, Titles and Publishers • Tables produced by INNER JOIN can be used as arguments for another INNER JOIN 2002 Prentice Hall. All rights reserved. 33
19. 4. 5 Joining Data from Tables Authors, Author. ISBN, Titles and Publishers 34 Join Publishers and Titles tables if the publisher. ID matches Join Authors and Author. ISBN if author. ID matches Join two created tables if titles. ISBN matches authors. ISBN Fig. 19. 22 Title. Author query of Books database. 2002 Prentice Hall. All rights reserved. Sort new table by title
19. 4. 5 Joining Data from Tables Authors, Author. ISBN, Titles and Publishers 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, Author. ISBN, Titles and Publishers 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 table. Name(field. Name 1) VALUES (value 1) • Values must match field names in order and type 2002 Prentice Hall. All rights reserved.
38 19. 4. 6 INSERT Statement 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 table. Name SET field. Name 1 = value 1 WHERE criteria 2002 Prentice Hall. All rights reserved.
19. 4. 7 UPDATE Statement 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 table. Name WHERE criteria 2002 Prentice Hall. All rights reserved.
19. 4. 8 DELETE Statement 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 • Data. Sets act as caches: – Store data from source in local memory 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 2002 Prentice Hall. All rights reserved. 44
19. 6. 1 Connecting to and Querying an Access Data Source • Retrieves data and stores it in a Data. Grid 2002 Prentice Hall. All rights reserved. 45
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: Display. Table. vb ' Displaying data from a database table. 46 Public Class Frm. Table. Display Inherits System. Windows. Form #Region " Windows Form Designer generated code " Public Sub New() My. Base. New() Constructor to populate data. Set 1 ' This call is required by the Windows Form Designer. Initialize. Component () ' Add any initialization after the ' Initialize. Component() call ' fill Data. Set 11 with data Ole. Db. Data. Adapter 1. Fill(Data. Set 1, "Authors") Call method fill to retrieve data Display. Table. vb from database associated with dgd. Authors ole. Db. Connection ' bind data in Users table in data. Set 11 to dgd. Authors. Set. Data. Binding(Data. Set 1, "Authors") End Sub ' New Bind Data. Grid to data clean up source the component ' Form overrides dispose to Protected Overloads Overrides Sub Dispose( _ By. Val disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components. Dispose() End If My. Base. Dispose(disposing) End Sub ' Dispose list. 2002 Prentice Hall. All rights reserved.
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 With. Events dgd. Authors As System. Windows. Forms. Data. Grid Friend With. Events Ole. Db. Select. Command 1 As _ System. Data. Ole. Db. Command Outline 47 Friend With. Events Ole. Db. Insert. Command 1 As _ System. Data. Ole. Db. Command Friend With. Events Ole. Db. Update. Command 1 As _ System. Data. Ole. Db. Command Friend With. Events Ole. Db. Delete. Command 1 As _ System. Data. Ole. Db. Command Friend With. Events Ole. Db. Connection 1 As _ System. Data. Ole. Db. Connection Friend With. Events Ole. Db. Data. Adapter 1 As _ System. Data. Ole. Db. Data. Adapter Display. Table. vb Friend With. Events Data. Set 1 As System. Data. Set ' Required by the Windows Form Designer Private components As System. Component. Model. 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. Debugger. Step. Through()> _ Private Sub Initialize. Component() Me. dgd. Authors = New System. Windows. Forms. Data. Grid() Me. Ole. Db. Select. Command 1 = _ New System. Data. Ole. Db. Command() 2002 Prentice Hall. All rights reserved.
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. Ole. Db. Insert. Command 1 = _ New System. Data. Ole. Db. Command() Outline 48 Me. Ole. Db. Update. Command 1 = _ New System. Data. Ole. Db. Command() Me. Ole. Db. Delete. Command 1 = _ New System. Data. Ole. Db. Command() Me. Ole. Db. Connection 1 = _ New System. Data. Ole. Db. Connection() Me. Ole. Db. Data. Adapter 1 = _ New System. Data. Ole. Db. Data. Adapter() Me. Data. Set 1 = New System. Data. Set() CType(Me. dgd. Authors, _ System. Component. Model. ISupport. Initialize). Begin. Init() Display. Table. vb CType(Me. Data. Set 1, _ System. Component. Model. ISupport. Initialize). Begin. Init() Me. Suspend. Layout() ' ' dgd. Authors ' Me. dgd. Authors. Data. Member = "" Me. dgd. Authors. Location = New System. Drawing. Point(8, 8) Me. dgd. Authors. Name = "dgd. Authors" Me. dgd. Authors. Size = New System. Drawing. Size(304, 256) Me. dgd. Authors. Tab. Index = 0 2002 Prentice Hall. All rights reserved.
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 ' ' Ole. Db. Select. Command 1 ' Me. Ole. Db. Select. Command 1. Command. Text = _ "SELECT author. ID, first. Name, last. Name FROM Authors" Outline 49 Me. Ole. Db. Select. Command 1. Connection = Me. Ole. Db. Connection 1 ' ' Ole. Db. Insert. Command 1 ' Me. Ole. Db. Insert. Command 1. Command. Text = _ "INSERT INTO Authors(author. ID, first. Name, last. Name)" & _ "VALUES (? , ? )" Me. Ole. Db. Insert. Command 1. Connection = _ Me. Ole. Db. Connection 1 Me. Ole. Db. Insert. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("author. ID", _ System. Data. Ole. Db. Type. Numeric, 0, _ System. Data. Parameter. Direction. Input, False, _ CType(10, Byte), CType(0, Byte), "author. ID", _ System. Data. Row. Version. Current, Nothing)) Display. Table. vb Me. Ole. Db. Insert. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("first. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "first. Name", _ System. Data. Row. Version. Current, Nothing)) 2002 Prentice Hall. All rights reserved.
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. Ole. Db. Insert. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("last. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "last. Name", _ System. Data. Row. Version. Current, Nothing)) ' ' Ole. Db. Update. Command 1 ' Me. Ole. Db. Update. Command 1. Command. Text = _ "UPDATE Authors SET author. ID = ? , first. Name = ? , " & _ "last. Name = ? WHERE (author. ID = ? )" & _ " AND (first. Name = ? ) AND (last. Name = ? )" Me. Ole. Db. Update. Command 1. Connection = Me. Ole. Db. Connection 1 Me. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("author. ID", _ System. Data. Ole. Db. Type. Numeric, 0, _ System. Data. Parameter. Direction. Input, False, _ CType(10, Byte), CType(0, Byte), "author. ID", _ System. Data. Row. Version. Current, Nothing)) Outline 50 Set Command. Text for ole. Db. Update. Command 1 Set connection property for ole. Db. Update. Command 1 Display. Table. vb Me. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("first. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "first. Name", _ System. Data. Row. Version. Current, Nothing)) 2002 Prentice Hall. All rights reserved.
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. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("last. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "last. Name", _ System. Data. Row. Version. Current, Nothing)) Outline 51 Me. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter _ ("Original_author. ID", _ System. Data. Ole. Db. Type. Numeric, 0, _ System. Data. Parameter. Direction. Input, False, _ CType(10, Byte), CType(0, Byte), "author. ID", _ System. Data. Row. Version. Original, Nothing)) Me. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter _ ("Original_first. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "first. Name", _ System. Data. Row. Version. Original, Nothing)) Display. Table. vb Me. Ole. Db. Update. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter _ ("Original_last. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "last. Name", _ System. Data. Row. Version. Original, Nothing)) 2002 Prentice Hall. All rights reserved.
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 ' ' Ole. Db. Delete. Command 1 ' Me. Ole. Db. Delete. Command 1. Command. Text = _ "DELETE FROM Authors WHERE ( author. ID = ? ) AND " & _ "(first. Name = ? ) AND (last. Name = ? )" Outline 52 Me. Ole. Db. Delete. Command 1. Connection = Me. Ole. Db. Connection 1 Me. Ole. Db. Delete. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("author. ID", _ System. Data. Ole. Db. Type. Numeric, 0, _ System. Data. Parameter. Direction. Input, False, _ CType(10, Byte), CType(0, Byte), "author. ID", _ System. Data. Row. Version. Original, Nothing)) Me. Ole. Db. Delete. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("first. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "first. Name", _ System. Data. Row. Version. Original, Nothing)) Display. Table. vb Me. Ole. Db. Delete. Command 1. Parameters. Add _ (New System. Data. Ole. Db. Parameter("last. Name", _ System. Data. Ole. Db. Type. Char, 50, _ System. Data. Parameter. Direction. Input, False, _ CType(0, Byte), "last. Name", _ System. Data. Row. Version. Original, Nothing)) 2002 Prentice Hall. All rights reserved.
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 ' 'Ole. Db. Connection 1 ' Me. Ole. Db. Connection 1. Connection. String = _ "Provider=Microsoft. Jet. OLEDB. 4. 0; Password=""""; " & _ "User ID=Admin; Data Source=C: Documen" & _ "ts and SettingsthiagoDesktopvbhtp 2 eexamples" & _ "Ch 19Fig 19_27Books. 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" ' ' Ole. Db. Data. Adapter 1 ' Me. Ole. Db. Data. Adapter 1. Delete. Command = _ Me. Ole. Db. Delete. Command 1 Me. Ole. Db. Data. Adapter 1. Insert. Command = _ Me. Ole. Db. Insert. Command 1 Me. Ole. Db. Data. Adapter 1. Select. Command = _ Me. Ole. Db. Select. Command 1 Outline 53 Initialize ole. Db. Connection, and specify path to database Display. Table. vb Specify how ole. Db. Data. Adapter deletes data Specify how ole. Db. Data. Adapter inserts data Specify how ole. Db. Data. Adapter selects data 2002 Prentice Hall. All rights reserved.
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 Outline Me. Ole. Db. Data. Adapter 1. Table. Mappings. Add. Range _ (New System. Data. Common. Data. Table. Mapping() _ {New System. Data. Common. Data. Table. Mapping("Table", _ "Authors", New System. Data. Common. Data. Column. Mapping() _ {New System. Data. Common. Data. Column. Mapping("author. ID", _ "author. ID"), New System. Data. Common. Data. Column. Mapping _ ("first. Name", "first. Name"), _ New System. Data. Common. Data. Column. Mapping("last. Name", _ "last. Name")})}) Me. Ole. Db. Data. Adapter 1. Update. Command = _ Me. Ole. Db. Update. Command 1 ' ' Data. Set 1 ' Me. Data. Set 1. Data. Set. Name = "New. Data. Set" Me. Data. Set 1. Locale = _ New System. Globalization. Culture. Info("en-US") Specify how ole. Db. Data. Adapter updates data Display. Table. vb ' ' Frm. Table. Display ' Me. Auto. Scale. Base. Size = New System. Drawing. Size(5, 13) Me. Client. Size = New System. Drawing. Size(320, 273) Me. Controls. Add. Range(New System. Windows. Forms. Control() _ {Me. dgd. Authors}) Me. Name = "Frm. Table. Display" Me. Text = "Table Display" CType(Me. dgd. Authors, System. Component. Model. _ ISupport. Initialize ). End. Init() CType(Me. Data. Set 1, System. Component. Model. _ ISupport. Initialize ). End. Init() 54 2002 Prentice Hall. All rights reserved.
295 296 297 298 299 300 301 302 Me. Resume. Layout(False) Outline 55 End Sub ' Initialize. Component #End Region End Class ' Frm. Table. Display. Table. vb 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.
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: Display. Query. Results. vb ' Displays the contents of the authors Public Class Frm. Display. Query. Result Inherits System. Windows. Form Frm. Display. Query. Result contains Outline database. Text. Box txt. Query, in which users input SELECT statements 57 Friend With. Events txt. Query As System. Windows. Forms. Text. Box Friend With. Events cmd. Submit As System. Windows. Forms. Button Friend With. Events dgd. Results As System. Windows. Forms. Data. Grid Friend With. Events Books. Connection As _ System. Data. Ole. Db. Connection Friend With. Events Books. Data. Adapter As _ System. Data. Ole. Db. Data. Adapter Friend With. Events Books. Data. Set As System. Data. Set ' After entering a query, the user clicks Button cmd. Submit, Query, code to view the results of the query Visual labeled Studio Submit. NET generated ' perform SQL query on data Private Sub cmd. Submit_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Submit. Click Display. Query. Resu lts. vb Try ' set the text of the SQL query to what the user typed ' in Books. Data. Adapter. Select. Command. Text = _ txt. Query. Text ' clear the Data. Set from the previous operation Books. Data. Set. Clear() 2002 Prentice Hall. All rights reserved.
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 Books. Data. Adapter. Fill(Books. Data. Set, "Authors") Outline 58 ' Bind the Data. Grid to the contents of the Dat. Set dgd. Results. Set. Data. Binding(Books. Data. Set, "Authors") ' display database connection verbose message Catch exception As System. Data. Ole. Db. Exception Message. Box. Show("Invalid Query") End Try End Sub ' cmd. Submit_Click Display. Query. Resu lts. vb End Class ' Frm. Display. Query. Results Program Output 2002 Prentice Hall. All rights reserved.
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. 59
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: Address. Book. vb ' Using SQL statements to manipulate a database. Outline Imports System. Windows. Forms Public Class Frm. Address. Book Inherits Form ' top set of command buttons Friend With. Events cmd. Find As Button Friend With. Events cmd. Add As Button Friend With. Events cmd. Update As Button Friend With. Events cmd. Clear As Button Friend With. Events cmd. Help As Button ' textbox identifier labels Friend With. Events lbl. Id As Label Friend With. Events lbl. First As Label Friend With. Events lbl. Last As Label Friend With. Events lbl. Address As Label Friend With. Events lbl. City As Label Friend With. Events lbl. State As Label Friend With. Events lbl. Zip As Label Friend With. Events lbl. Country As Label Friend With. Events lbl. Email As Label Friend With. Events lbl. Phone As Label Friend With. Events lbl. Fax As Label ' input textboxes Friend With. Events txt. Id As Text. Box txt. First As Text. Box txt. Last As Text. Box txt. Address As Text. Box txt. City As Text. Box txt. State As Text. Box Address. Book. vb 2002 Prentice Hall. All rights reserved. 60
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 With. Events txt. Zip As Text. Box txt. Country As Text. Box txt. Email As Text. Box txt. Phone As Text. Box txt. Fax As Text. Box Outline ' query status display textbox Friend With. Events txt. Status As Text. Box ' database connection Friend With. Events Address. Book. Connection As _ System. Data. Ole. Db. Connection ' database adapter Friend With. Events Address. Book. Data. Adapter As _ System. Data. Ole. Db. Data. Adapter ' query dataset Friend With. Events Address. Book. Data. Set As System. Data. Set Address. Book. vb ' constructor Public Sub New() My. Base. New() ' This call is required by the Windows Form Designer. Initialize. Component () ' Add any initialization after the Initialize. Component call ' open connection Address. Book. Connection. Open() End Sub ' New ' Visual Studio. NET generated code 2002 Prentice Hall. All rights reserved. 61
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 cmd. Find_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Find. Click Try Method Clear of class Data. Set is invoked to empty the Data. Set of ' ensure userany input priorlast dataname If txt. Last. Text <> "" Then Event handler cmd. Find_Click performs the SELECT query on the clear Data. Set fromwith lastthe operation database for'the record associated String entered in txt. Last Address. Book. Data. Set. Clear() ' create SQL query to find contact ' with specified last name Address. Book. Data. Adapter. Select. Command. Text = _ "SELECT * FROM addresses WHERE " & _ The Text. Boxes are updated with "lastname = '" & txt. Last. Text & "' a"call to method Display ' fill Address. Book. Data. Set with the rows resulting ' from the query Address. Book. Data. Adapter. Fill(Address. Book. Data. Set) Address. Book. vb ' display information Display(Address. Book. Data. Set) txt. Status. Text &= vb. Cr. Lf & "Query Successful " & _ vb. Cr. Lf ' prompt user for last name Else txt. Last. Text = _ "Enter last name here then press Find" End If ' display verbose information with database exception Catch ole. Db. Exception. Parameter As _ 2002 Prentice Hall. All rights reserved. 62
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. Ole. Db. Exception Console. Write. Line(ole. Db. Exception. Parameter. Stack. Trace) txt. Status. Text &= ole. Db. Exception. Parameter. To. String ' display message box when invalid operation Catch invalid. Operation. Exception. Parameter As _ Method cmd. Add_Click performs INSERT Invalid. Operation. Exception and UPDATE operations Message. Box. Show( _ invalid. Operation. Exception. Parameter. Message) End Try End Sub ' cmd. Find_Click ' adds record to database Private Sub cmd. Add_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Add. Click Address. Book. vb Try ' ensure first and last name input If (txt. Last. Text <> "" And. Also txt. First. Text <> "") Then ' create the SQL query to insert a row Address. Book. Data. Adapter. Insert. Command. Text = _ "INSERT INTO addresses(firstname, " & _ "lastname, address, city, " & _ "stateorprovince, postalcode, country, " & _ "emailaddress, homephone, faxnumber) " & _ "VALUES('" & txt. First. Text & "' , " & _ "'" & txt. Last. Text & "' , " & _ "'" & txt. Address. Text & "' , " & _ "'" & txt. City. Text & "' , " & _ "'" & txt. State. Text & "' , " & _ 2002 Prentice Hall. All rights reserved. 63
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 "'" "'" "'" & & & txt. Zip. Text & "' , " txt. Country. Text & "' txt. Email. Text & "' , txt. Phone. Text & "' , txt. Fax. Text & "')" & , " " _ " & _ & _ Outline ' notify the user the query is being sent txt. Status. Text &= vb. Cr. Lf & "Sending query: " & _ Address. Book. Data. Adapter. Insert. Command. _ Command. Text & vb. Cr. Lf ' send query Address. Book. Data. Adapter. Insert. Command. _ Execute. Non. Query () txt. Status. Text &= vb. Cr. Lf & "Query successful" ' prompt user to input first and last name Else txt. Status. Text &= vb. Cr. Lf & _ "Enter at least first and last name then " & _ "press Add" & vb. Cr. Lf End If Address. Book. vb ' display verbose information when database exception Catch ole. Db. Exception. Parameter As _ System. Data. Ole. Db. Exception Console. Write. Line(ole. Db. Exception. Parameter. Stack. Trace) txt. Status. Text &= ole. Db. Exception. Parameter. To. String End Try End Sub ' cmd. Add_Click ' updates entry in database 2002 Prentice Hall. All rights reserved. 64
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 cmd. Update_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Update. Click Outline Try ' make sure user has already found ' record to update If txt. Id. Text <> "" Then ' set SQL query to update all fields in ' table where id number matches id in ' id. Text. Box Address. Book. Data. Adapter. Update. Command. Text = _ "UPDATE addresses SET firstname=" & _ "'" & txt. First. Text & "' , " & _ "lastname = '" & txt. Last. Text & "' , " & _ "address='" & txt. Address. Text & "' , " & _ "city='" & txt. City. Text & "' , " & _ "stateorprovince= " & _ "'" & txt. State. Text & "', " & _ "postalcode='" & txt. Zip. Text & "', " & _ "country='" & txt. Country. Text & "' , " & _ "emailaddress='" & txt. Email. Text & "' , " & _ "homephone='" & txt. Phone. Text & "' , " & _ "faxnumber='" & txt. Fax. Text & "' " & _ "WHERE id=" & txt. Id. Text & " ; " Address. Book. vb ' notify user that query is being sent txt. Status. Text &= vb. Cr. Lf & "Sending query: " & _ Address. Book. Data. Adapter. Update. Command. _ Command. Text & vb. Cr. Lf ' execute query Address. Book. Data. Adapter. Update. Command. _ Execute. Non. Query() 2002 Prentice Hall. All rights reserved. 65
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 txt. Status. Text &= vb. Cr. Lf & "Query Successful" & _ vb. Cr. Lf Outline ' prompt user to input existing record Else txt. Status. Text &= vb. Cr. Lf & _ "You may only update an existing record. " & _ "Use Find to locate the record, then " & _ "modify the information and press Update. " & _ vb. Cr. Lf End If ' display verbose information when database exception Catch ole. Db. Exception. Parameter As _ System. Data. Ole. Db. Exception Console. Write. Line(ole. Db. Exception. Parameter. Stack. Trace) txt. Status. Text &= ole. Db. Exception. Parameter. To. String End Try Address. Book. vb End Sub ' cmd. Update_Click ' clears all information in textboxes Private Sub cmd. Clear_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Clear. Click txt. Id. Clear() Clear. Text. Boxes() End Sub ' cmd. Clear_Click ' displays information on application use Private Sub cmd. Help_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Help. Click 2002 Prentice Hall. All rights reserved. 66
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 txt. Status. Append. Text(vb. Cr. Lf & _interface with data from the Method Display updates the user "Click Find to locate a record" & vb. Cr. Lf & _ newly retrieved booka record "Click Add address to insert new record. " & vb. Cr. Lf & _ Outline "Click Update to update the information in a " & _ "record " & vb. Cr. Lf & "Click Clear to empty the " & _ "textboxes") End Sub ' cmd. Help_Click ' displays data in dataset Private Sub Display(By. Val dataset As Data. Set) Checks whether the query. Retrieves returned the anyfield rowswith index 0, 0 and stores the Try value in variable record. Number ' get first Data. Table - there will be one Dim data. Table As Data. Table = dataset. Tables(0) ' ensure data. Table not empty Retrieve<>the fields of data from the If data. Table. Rows. Count 0 remaining Then Data. Table the user interface Address. Book. vb Dim record. Number As Integerto= populate _ Convert. To. Int 32( data. Table. Rows(0)(0)) txt. Id. Text = record. Number. To. String txt. First. Text = _ Convert. To. String(data. Table. Rows(0)(1)) txt. Last. Text = _ Convert. To. String(data. Table. Rows(0)(2)) txt. Address. Text = _ Convert. To. String(data. Table. Rows(0)(3)) txt. City. Text = _ Convert. To. String(data. Table. Rows(0)(4)) txt. State. Text = _ 2002 Prentice Hall. All rights reserved. 67
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. To. String(data. Table. Rows(0)(5)) Outline 68 txt. Zip. Text = _ Convert. To. String(data. Table. Rows(0)(6)) txt. Country. Text = _ Convert. To. String(data. Table. Rows(0)(7)) txt. Email. Text = _ Convert. To. String(data. Table. Rows(0)(8)) txt. Phone. Text = _ Convert. To. String(data. Table. Rows(0)(9)) txt. Fax. Text = _ Convert. To. String(data. Table. Rows(0)(10)) ' display not-found message Else txt. Status. Text &= vb. Cr. Lf & "No record found“ & vb. Cr. Lf End If Address. Book. vb ' display verbose information when database exception Catch ole. Db. Exception. Parameter As _ Clear button clears the text from the System. Data. Ole. Db. Exception The Text. Boxes using method Clear. Text. Boxes Console. Write. Line(ole. Db. Exception. Parameter. Stack. Trace) txt. Status. Text &= ole. Db. Exception. Parameter. To. String End Try End Sub ' Display ' clears text boxes Private Sub Clear. Text. Boxes() txt. First. Clear() 2002 Prentice Hall. All rights reserved.
316 317 318 319 320 321 322 323 324 325 326 327 txt. Last. Clear() txt. Address. Clear() txt. City. Clear() txt. State. Clear() txt. Zip. Clear() txt. Country. Clear() txt. Email. Clear() txt. Phone. Clear() txt. Fax. Clear() End Sub ' Clear. Text. Boxes Outline End Class ' Frm. Address. Book. vb 2002 Prentice Hall. All rights reserved. 69
Outline Address. Book. vb 2002 Prentice Hall. All rights reserved. 70
Outline Address. Book. vb 2002 Prentice Hall. All rights reserved. 71
Outline Address. Book. vb 2002 Prentice Hall. All rights reserved. 72
Outline Address. Book. vb 2002 Prentice Hall. All rights reserved. 73
74 19. 8 Reading and Writing XML Files • ADO. NET can convert data from data source into XML files – Uses methods Write. Xml, Read. Xml and. Get. Xml 2002 Prentice Hall. All rights reserved.
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. 30 XMLWriter. vb ' Demonstrates generating XML from an ADO. NET Data. Set Outline Public Class Frm. XMLWriter Inherits System. Windows. Form ' constructor Public Sub New() My. Base. New() ' This call is required by the Windows Form Designer. Establishes a connection to the Initialize. Component () Baseball database Method Fill of class Ole. Db. Data. Adapter is called to populate with data from the ' Add any initialization after Baseball. Data. Set the ' Initialize. Component() call Players table in the Baseball database ' open database connection Baseball. Connection. Open() Binds the dgd. Players to Baseball. Data. Set. XMLWriter. vb to the information to the user with display data from Ole. Db. Data. Adapter ' fill Data. Set Baseball. Data. Adapter. Fill(Baseball. Data. Set, "Players") ' bind Data. Grid to Data. Set dgd. Players. Set. Data. Binding(Baseball. Data. Set, "Players") End Sub Friend With. Events cmd. Write As System. Windows. Forms. Button Friend With. Events dgd. Players As System. Windows. Forms. Data. Grid Friend With. Events txt. Output As System. Windows. Forms. Text. Box Friend With. Events Baseball. Connection As _ System. Data. Ole. Db. Connection Friend With. Events Baseball. Data. Adapter As _ System. Data. Ole. Db. Data. Adapter 2002 Prentice Hall. All rights reserved. 75
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 Outline Friend With. Events Baseball. Data. Set As System. Data. Set method Write. Xml is invoked to generate an XML representation of the data contained in the Data. Set representation of Data. Set clicked and then writes the XMLwhen to thebutton specified file ' Visual Studio. NET generated code ' write XML Private Sub cmd. Write_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles cmd. Write. Click ' write XML representation of Data. Set to file Baseball. Data. Set. Write. Xml("Players. xml") ' display XML in Text. Box txt. Output. Text &= "Writing the following XML: " & _ vb. Cr. Lf & Baseball. Data. Set. Get. Xml() & vb. Cr. Lf End Sub ' cm. Write_Click End Class ' Frm. XMLWriter. vb 2002 Prentice Hall. All rights reserved. 76
77 19. 8 Reading and Writing XML Documents Fig. 19. 31 XML document generated from Data. Set in Database. XMLWriter. 2002 Prentice Hall. All rights reserved.
- Slides: 77