1 / 52

20 – Persistent data storage: relational databases and ADO

20 – Persistent data storage: relational databases and ADO. Questions: Session variables. Write a line of VB code to put 74 into a session variable called score. Write VB code that adds 1 to a variable called g, when a session variable called i is over 25. Session("score") = 74.

herbst
Download Presentation

20 – Persistent data storage: relational databases and ADO

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 20 – Persistent data storage: relational databases and ADO

  2. Questions: Session variables • Write a line of VB code to put 74 into a session variable called score. • Write VB code that adds 1 to a variable called g, when a session variable called i is over 25. Session("score") = 74 If Session("i") > 25 Then g = g + 1 End If

  3. Question: Self-Contained • Are the following routines self contained? Dim g As Double Dim w As Double Sub Square(ByRef res As Double, ByVal n1 As Double) res = n1 * n1 End Sub Function u(num As Double) As Double Return num * (w + g) End Function  

  4. Admin: SQL Book Gennick J (2006) SQL Pocket Guide (2nd edition). O'Reilly.ISBN: 0-596-52688-1

  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 ActiveX Data Objects (ADO) • use SQL in your programs to create more complex record-sets

  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, or object oriented)

  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-functionalshould be viewable anywhere in the world

  8. Example: People (Database) • Information organised into • tables (e.g. person) • fields (e.g. phone) • records (e.g. 1 Dixon Mark 01752 586225 …) Field Record Person

  9. Questions: Music (Database) 3 • How many fields? • How many records? 9 Track

  10. DBMS • Database Management Systems (DBMS) provide facilities for: • creating and changing databases • add/remove records • add/remove fields • add/remove data • For example: • Microsoft Access • dBase • Borland Paradox • MySQL • Microsoft SQL Server • Oracle home/small business large scale

  11. MS Access

  12. ActiveX Data Objects • ActiveX Data Objects (ADO) • common database interface • allow you to write code for any DBMS MS Access VB.Netcode MS SQL Server ADO … DB front end …

  13. Using Record Sets Connection string – identify database Open connection Read next record Read field data Close connection Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close

  14. Example: People (code) People.aspx <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub </script> Must include database library

  15. Example: People (r) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub

  16. Example: People (s) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s

  17. Example: People (ExecuteReader) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s

  18. Example: People (s) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s

  19. Example: People (Read) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s

  20. Example: People (Surname) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />

  21. Example: People (Loop) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />

  22. Example: People (Read) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />

  23. Example: People (Surname) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br /> Dixon<br />Smith<br />

  24. Example: People (Loop) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br /> Dixon<br />Smith<br />

  25. Example: People (Read) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br /> Dixon<br />Smith<br />

  26. Example: People (Surname) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />Smith<br />Jones <br /> Dixon<br /> Dixon<br />Smith<br />

  27. Example: People (Loop) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />Smith<br />Jones <br /> Dixon<br /> Dixon<br />Smith<br />

  28. Example: People (Read) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />Smith<br />Jones <br /> Dixon<br /> Dixon<br />Smith<br />

  29. Example: People (Close) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />Smith<br />Jones <br /> Dixon<br /> Dixon<br />Smith<br />

  30. Example: People (Display) People.aspx r Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub s Dixon<br />Smith<br />Jones <br /> Dixon<br /> Dixon<br />Smith<br />

  31. Example: Countries Countries.aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath(“Countries.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Country;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r(“Name") Loop cn.Close parData.InnerHtml = s End Sub html by hand put br tags between data <br /> Need data on separate lines

  32. Example: Countries (error) Countries.aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath(“Countries.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Country;", cn) cn.Open() r = cmd.ExecuteReader() s = <br /> Do While r.Read() s = s & r(“Name") Loop cn.Close parData.InnerHtml = s End Sub Try putting br tag here VB does not understand html Need data on separate lines

  33. Example: Countries Countries.aspx • Need double quotes around tag • (VB sees html as literal string) Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath(“Countries.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Country;", cn) cn.Open() r = cmd.ExecuteReader() s = "<br />" Do While r.Read() s = s & r(“Name") Loop cn.Close parData.InnerHtml = s End Sub runs, but br in wrong place

  34. Example: Countries Countries.aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath(“Countries.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Country;", cn) cn.Open() r = cmd.ExecuteReader() s = "<br />" Do While r.Read() s = s & r(“Name") Loop cn.Close parData.InnerHtml = s End Sub Move br tag inside loop. Which bit of code pulls data from database?

  35. Example: Countries Countries.aspx Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath(“Countries.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Country;", cn) cn.Open() r = cmd.ExecuteReader() s = "<br />" Do While r.Read() s = s & r(“Name") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub Move br tag inside loop. after field data View Source

  36. Embedding html in VB • html must be string (inside double quotes) • follows normal pattern for expressions: data data data data s = s+"<b>"+r("Name")+"</b>" operator operator operator

  37. Embedding html in VB (errors) s = s + "<i>" r("Gender") + "</i>" missing operator s = s + rs("Height") + </i>" missing double quote s = s + <ul> + r("Height") html tag must be inside double quotes s = s + "<ul>" + r("<b>Height") looks for field in database called <b>Height

  38. Questions: HTML in VB • Are these correct (assume variables and fields exist)? g = g + rs("Surname<br />") h = h + "<ol>" r("Width") a = "<p>" + a + "</p>" html = html + "<img src=‘face.gif’ />" h = <table> + h + "</table>"     

  39. Example: People v2 • Display Surname of Male people: Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim s As String cmd = New OleDbCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() If r("Gender") = True Then s = s & r("Surname") & "<br />" End If Loop cn.Close parData.InnerHtml = s End Sub

  40. Example: People v3 • Display Surname of Male people: Sub Page_Load() Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _ "Data Source=" + Server.MapPath("People.accdb") + ";" Dim cn As New OleDbConnection(cs) Dim cmd As OleDbCommand Dim r As OleDbDataReader Dim sql As String = "SELECT * FROM Person WHERE Gender = True;" Dim s As String cmd = New OleDbCommand(sql, cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub SQL statement

  41. SQL: Queries • main purpose of databases: • get information back out: searching • Structured Query Language • dedicated to interacting with databases • 3rd Generation Language (such as VB, C#) • code describes how to do task • 4th Generation Language (such as SQL) • code describes what to do (not how to do it)

  42. SQL: SELECT statement • SELECT statement • used to get data • can be embedded in VB, via: • …v = "SELECT * FROM [Person]"...cmd = New OleDbCommand(v, cn) all fields

  43. 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];

  44. 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 textSELECT * FROM Person WHERE Surname = 'Smith'

  45. SQL & MS access queries • MS Access • Queries: select data from database • really SQL select statements • can use queries to test SQL code

  46. Questions: SQL Track • Create an SQL statement to extract Track Title of records by Aerosmith SELECT [Track Title] FROM Track WHERE [Artist Name] = 'Aerosmith';

  47. Questions: SQL Track • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name SELECT * FROM Track WHERE [Artist Name] = 'Disturbed' ORDER BY [Track Title];

  48. Example: People v4 • User controls what is displayed:

  49. SQL: DISTINCT records SELECT [Artist Name]FROM [Track]; SELECT DISTINCT [Artist Name]FROM [Track];

  50. Access Driver (for 32bit Office) • http://www.microsoft.com/en-gb/download/details.aspx?id=13255

More Related