20 Persistent data storage relational databases and ADO
20 – Persistent data storage: relational databases and ADO Mark Dixon 1
Admin: Module Feedback • Student Perception Questionnaire (SPQ) • Comments on back page – most useful • Be as specific as possible LCriticisms – suggestions for change JPositive – what to keep Mark Dixon 2
Admin: SQL Book Gennick J (2010) SQL Pocket Guide (3 rd edition). O'Reilly. ISBN: 1 -4493 -9409 -4 Mark Dixon 3
Questions: Session variables • Write a line of VB code to put 74 into a session variable called score. Session("score") = 74 • Write VB code that adds 1 to a variable called g, when a session variable called i is over 25. If Session("i") > 25 Then g = g + 1 End If Mark Dixon 4
Question: Self-Contained • Are the following routines self contained? Dim g As Double Dim w As Double Sub Square(By. Ref res As Double, By. Val n 1 As Double) res = n 1 * n 1 End Sub Function u(num As Double) As Double Return num * (w + g) End Function Mark Dixon 5
Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in using relational databases for persistent data storage • Objectives, by end of this week’s sessions, you should be able to: – create a relational database table – create a web page (ASP. Net) that displays data from a single table in a database • using Active. X Data Objects (ADO) – use SQL in your programs to create more complex record-sets Mark Dixon 6
Persistent Data Storage • So far – programs (web-pages) 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, object oriented, …) Mark Dixon 7
Example: People (analysis) SPECIFICATION • User Requirements – need to have access to people's details • Software Requirements – Functional: – Display list of people from a database – Non-functional should be viewable anywhere in the world Mark Dixon 8
Example: People (Database) • Information organised into – tables (e. g. person) – fields (e. g. phone) – records (e. g. 1 Dixon Mark 01752 586225 …) Person ID 1 2 3 Mark Dixon Surname Dixon Smith Jones Field Record Forenames Mark John Sally Phone 01752 232556 01752 111111 01752 888888 Gender Male Female 9
Questions: Music (Database) • How many fields? • How many records? 3 9 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 10
DBMS • Database Management Systems (DBMS) provide facilities for: – creating and changing databases • add/remove records • add/remove fields • add/remove data – For example: • • • Mark Dixon d. Base Borland Paradox Microsoft Access My. SQL Microsoft SQL Server Oracle home/small business large scale 11
MS Access Mark Dixon 12
Active. X Data Objects • Active. X Data Objects (ADO) – common database interface • allow you to write code for any DBMS MS Access VB. Net code DB front end Mark Dixon ADO MS SQL Server … … 13
Using Record Sets Connection string – identify database Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT *Open FROMconnection Person; ", cn) cn. Open() r = cmd. Execute. Reader() Read next record h = "" Do While r. Read() Read field data h = h & r("Surname") Loop Close connection Mark Dixon cn. Close 14
Example: People (code) People. as px Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. Db" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub </script> Mark Dixon Must include database library 15
Example: People (r) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 16
Example: People (h) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 17
Example: People (Execute. Reader) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 18
Example: People (h) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 19
Example: People (Read) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 20
Example: People (Surname) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 21
Example: People (Loop) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 22
Example: People (Read) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 23
Example: People (Surname) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith Dixon Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 24
Example: People (Loop) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 25
Example: People (Read) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 26
Example: People (Surname) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith. Jones Dixon. Smith Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 27
Example: People (Loop) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith. Jones Dixon. Smith Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 28
Example: People (Read) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith. Jones Dixon. Smith Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 29
Example: People (Close) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith. Jones Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 30
Example: People (Display) People. as px r Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command h Dim r As Ole. Db. Data. Reader Dixon. Smith. Jones Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 31
Example: People (html tags) People. aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path(“People. accdb")html + "; "by hand put br tags between data Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r(“Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Need data on separate lines Mark Dixon 32
Example: People (error) People. aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path(“People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Try putting br tag here Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = VB does not Do While r. Read() h = h & r(“Surname") understand html Loop cn. Close par. Data. Inner. Html = h End Sub Need data on separate lines Mark Dixon 33
Example: People (html tags) People. aspx • Need double Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ quotes around "Data Source=" + Server. Map. Path(“People. accdb") + "; " tag Dim cn As New Ole. Db. Connection(cs) • (VB sees html as Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader literal string) Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = " " Do While r. Read() h = h & r(“Surname") Loop cn. Close par. Data. Inner. Html = h End Sub runs, but br in wrong place Mark Dixon 34
Example: People (html tags) People. aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ Move br tag inside loop. "Data Source=" + Server. Map. Path(“People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Which bit of code pulls data Dim cmd As Ole. Db. Command from database? Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = " " Do While r. Read() h = h & r(“Surname") Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 35
Example: People (error) People. aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path(“People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Try putting br tag here Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" looks for field Do While r. Read() h = h & r(“Surname ") called Loop Surname cn. Close par. Data. Inner. Html = h End Sub Need data on separate lines Mark Dixon 36
Example: People (html tags) People. aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ Move br tag inside loop. "Data Source=" + Server. Map. Path(“People. accdb")after + "; " field data Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r(“Surname") & " " Loop cn. Close par. Data. Inner. Html = h End Sub View Source Mark Dixon 37
Embedding html in VB • html must be string (inside double quotes) • follows normal pattern for expressions: data h = h + "<b>" + r("Surname") + "</b>" operator Mark Dixon operator 38
Embedding html in VB (errors) h = h + "<i>" r("Gender") + "</i>" missing operator h = h + r("Height") + </i>" missing double quote h = h + <ul> + r("Height") html tag must be inside double quotes h = h + "<ul>" + r("<b>Height") looks for field in database called <b>Height Mark Dixon 39
Questions: HTML in VB • Are these correct (assume variables and fields exist)? g = g + rs("Surname ") h = h + "<ol>" r("Width") a = "<p>" + a + "</p>" html = html + "<img src=‘face. gif’ />" h = <table> + h + "</table>" Mark Dixon 40
Example: People v 2 • Display Surname of Male people: Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim h As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() If r("Gender") = True Then h = h & r("Surname") & " " End If Loop cn. Close par. Data. Inner. Html = h End Sub Mark Dixon 41
Example: People v 3 • Display Surname of Male people: Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim sql As String = "SELECT * FROM Person WHERE Gende Dim h As String cmd = New Ole. Db. Command(sql, cn) cn. Open() r = cmd. Execute. Reader() h = "" Do While r. Read() h = h & r("Surname") & " " Loop cn. Close par. Data. Inner. Html = h End Sub SQL statement Mark Dixon 42
SQL: Queries • main purpose of databases: – get information back out: searching • Structured Query Language – dedicated to interacting with databases • 3 rd Generation Language (such as VB, C#) – code describes how to do task • 4 th Generation Language (such as SQL) – code describes what to do (not how to do it) Mark Dixon 43
SQL: SELECT statement • SELECT statement – used to get data – can be embedded in VB, via: – all fields … v = "SELECT * FROM [Person]". . . cmd = New Ole. Db. Command(v, cn) Mark Dixon 44
SQL: WHERE & ORDER BY • WHERE clause – used to restrict data SELECT * FROM [People] WHERE [age]>=18; • ORDER BY clause – used to change order of data SELECT * FROM [People] ORDER BY [Surname]; Mark Dixon 45
SQL: strings (text data) • Possible confusion: SELECT * FROM Person WHERE Surname = Smith this will look for field called Smith - gives error need single (SQL) quotes to signify literal text SELECT * FROM Person WHERE Surname = 'Smith' Mark Dixon 46
SQL & MS access queries • MS Access – Queries: select data from database – really SQL select statements – can use queries to test SQL code Mark Dixon 47
Questions: SQL • Create an SQL statement to extract Track Title of records by Aerosmith 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 SELECT [Track Title] FROM Track WHERE [Artist Name] = 'Aerosmith'; Mark Dixon 48
Questions: SQL • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name 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 SELECT * FROM Track WHERE [Artist Name] = 'Disturbed' ORDER BY [Track Title]; Mark Dixon 49
Example: People v 4 • User controls what is displayed: Mark Dixon 50
SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien Ant Farm Disturbed SELECT DISTINCT [Artist Name] FROM [Track]; Artist Name Black Sabbath Aerosmith Alien Ant Farm Disturbed Mark Dixon 51
Access Driver (for 32 bit Office) • http: //www. microsoft. com/engb/download/details. aspx? id=13255 Mark Dixon 52
Access Driver (for 64 bit Office) • http: //www. microsoft. com/engb/download/details. aspx? id=23734 Mark Dixon 53
Tutorial Exercise: People • • • Task 1: Create your own People database (or download from resources): – Open MS Access – Create a new database file – Create a new table – Create fields – Enter data Task 2: Get the People v 1 example (from the lecture) working. 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 it displays the data in an html table. Task 5: Modify your page so that records for males are displayed in blue, and records for females are displayed in red. Task 6: Modify your page as per version 4. You will need to: – Add a form to the page, and three submit buttons – In your code, detect when a button has been pressed (have a look at previous weeks) Task 7: Modify your page so that the user can type a letter, and only names starting with that letter are displayed. Task 8: Modify your page so that the user can type a series of numerical digits and only phone numbers containing those digits are displayed. Task 9: Modify your code so that the user can order the data by surname, or email address (You may want to use a Query String) Mark Dixon 54
- Slides: 54