17 Persistent data storage relational databases and ADO
17 – Persistent data storage: relational databases and ADO Mark Dixon Page 1
Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in persistent data storage and relational databases • Objectives, by end of this week’s sessions, you should be able to: – create a relational database table – create a web page (ASP) that displays data from a single table in a database • using Active. X Data Objects (ADO) Mark Dixon Page 2
Persistent Data Storage • So far – all programs lose data when closed • Not realistic – typically data stored to persistent storage device (e. g. hard disk, key drive, floppy disk, CD-RW) • Use either – flat files – database (relational, or object oriented) Mark Dixon Page 3
Example: People (Specification) • User requirement: – Display list of people from database online • How: – Combine our knowledge of: • ASP (active server pages) • ADO (active. X data objects) Mark Dixon Page 4
Example: People (Database) • Information organised into – tables (e. g. person) – fields (e. g. phone) – records (e. g. 1 Dixon Mark 01752 232556 …) Person ID 1 2 3 4 5 Surname Dixon Smith Jones Bloggs Johnson Mark Dixon Field Record Forenames Mark John Sally Fred Genny Phone 01752 232556 01752 111111 01752 888888 01752 123123 01752 987987 email mark. dixon@plymouth. ac. uk john. smith@john. smith. ac. uk sally. jones@sally. jones. com fred. bloggs@aaaaaa. com genny@bbbb. cccc. com Page 5
Example: Music (Database) • How many fields? • How many records? Track Mark Dixon Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Page 6
Database Management Systems • DBMS provides facilities for: – creating and changing databases • add/remove records • add/remove fields • add/remove data – For example: • • • Mark Dixon Microsoft Access d. Base Borland Paradox My. SQL Microsoft SQL Server Oracle home/small business large scale Page 7
MS Access Mark Dixon Music database Page 8
Active. X Data Objects (what & why) • Active. X Data Objects (ADO) – common database interface • allow you to write code for any DBMS MS Access VB or VB Script code DB front end Mark Dixon ADO MS SQL Server … … Page 9
ADO Record Set Object • Used to interact with tables • Properties – BOF: true if at start of record set (before first record) – EOF: true if at end of record set (after last record) – Fields: used to get and set data values • Methods – – – Mark Dixon Open: used to open record set Move. First: moves focus to first record Move. Previous: moves focus to previous record Move. Next: moves focus to next record Move. Last: moves focus to last record Close: closes record set Page 10
Using Record Sets Connection string – identify database <% Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Music. mdb; Persist Security Info=False" Dim rs rs = Create. Object("ADODB. Recordset") Open rs. Open("Person", cs) Do Until rs. EOF() … rs. Move. Next() Loop rs. Close() rs = Nothing %> Mark Dixon record set with table Move to next record Close record set Page 11
Connection Strings: UDL files • Generate connection strings – Right click on desktop – Select New, Text Document – Rename to *. UDL (Yes to warning message) – Double click – Select provider – Click Next – Select or enter DB name – Click Test Connection button – Click OK – Open with Notepad, cut & paste text Mark Dixon Page 12
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Loop rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 13
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Loop rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 14
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Loop rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 15
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 16
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop Smith rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 17
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop Smith rs. Close() Jones rs = Nothing %> </body> </html> Mark Dixon Page 18
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop Smith rs. Close() Jones rs = Nothing Bloggs %> </body> </html> Mark Dixon Page 19
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop Smith rs. Close() Jones rs = Nothing Bloggs Anderson %> </body> </html> Mark Dixon Page 20
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Dixon Loop Smith rs. Close() Jones rs = Nothing Bloggs Anderson %> </body> </html> Mark Dixon Page 21
People. as px <html> Example: People <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> rs <% Const cs = "Provider=…; Data Source=D: People. mdb; " Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Loop rs. Close() rs = Nothing %> </body> </html> Mark Dixon Page 22
Example: People v 2 <script runat="server"> Const cs = "Provider=…; Data Source=D: People. mdb; " Sub Display. People() Dim rs rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) Do Until rs. EOF() Response. Write(rs. Fields("Surname"). Value) Response. Write(" ") rs. Move. Next() Loop rs. Close() rs = Nothing End Sub </script> • Use procedure to separate: – code detail – html <html> <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font></b></center> <% Display. People() %> </body> </html> Mark Dixon Page 23
Tutorial Exercise: People • Task 1: Create your own People database: – – – Open MS Access Create a new database file Create a new table Create fields Enter data • Task 2: Create the People v 2 asp page (as per the lecture) to display data from the database. • Task 3: Modify your page so that it displays phone number as well as the person's name. • Task 4: Modify your page so that the user can type a letter, and only names starting with that letter are displayed. • Task 5: Modify your page so that the user can type a series of numerical digits and only phone numbers containing those digits are displayed. • Task 6: Modify your page so that it displays the data in an html table. Hint: use Response. Write to insert the appropriate tags. Mark Dixon Page 24
Tutorial Exercise: Music • Task 1: Create your own Music Database. • Task 2: Create an asp page to display data from this database. • Task 3: Modify your page so that the user can type the name of an artist, and only tracks by that artist are displayed • Task 4: Make your page case in-sensitive (i. e. UPPER or lower case makes no difference) Mark Dixon Page 25
- Slides: 25