1 / 49

Databaser

Databaser. Introduksjon til Databaser. Resten av foilene fra kap 10.1 som vi går gjennom på neste forelesning. Chapter 10 – Database Management. 10.1 An Introduction to Databases. 10.1 An Introduction to Databases. Database Explorer Accessing a Database with a Data Table.

inga
Download Presentation

Databaser

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. Databaser

  2. Introduksjon til Databaser • Resten av foilene fra kap 10.1 som vi går gjennom på neste forelesning

  3. Chapter 10 – Database Management 10.1 An Introduction to Databases

  4. 10.1 An Introduction to Databases • Database Explorer • Accessing a Database with a Data Table

  5. Sample Table – Cities Table

  6. Sample Table – Countries Table

  7. Database Terminology • A table is a rectangular array of data. • Each column of the table, called a field, contains the same type of information. • Each row, called a record, contains all the information about one entry in the database.

  8. Database Management Software (DBMS) • Used to create databases • Databases can contain one or more related tables • Examples of DBMS include Access and Oracle

  9. Se databasen fra Visual BasicDatabase Explorer • The Standard and Professional editions of Visual Basic contain Server Explorerthat also allows the programmer to view information located on other computers. • We will focus on Database Explorer. However, with slight modifications, our discussion applies to Server Explorer.

  10. Using the Database Explorerside 513 • Click on Database Explorer from the View Menu. The Explorer will appear on the left side of the screen. • Right-click on “Data Connections”, and select “Add Connection”. • Set the Data Source to “Microsoft Access Database File.” • Click on the “Browse …” button and select the file MEGACITIES.MDB from the folder Programs\Ch10\MajorDatabases, and press Open. • Clear the contents of the “User name” text box.

  11. Database Explorer continued • Press the Test Connection button. The message box stating “Test Connection Succeeded” will appear. Press the OK button on that message box, and then press the OK button on the Data Link Properties box. • An icon should appear in Database Explorer. Click on the + sign to the left of the icon to expand this entry. four subentries will appear: Tables, Views, and Stored Procedures, and Functions. • Expand the Tables entry to reveal the subentries, the tables Cities and Countries. • Expand an entry to reveal the fields of the table. (See next slide: Figure 10.1) • Double-click on a table to show the table in a grid. (See slide two pages ahead: Figure 10.2)

  12. Figure 10.1 – Database Explorer

  13. Figure 10.2 – The Cities Table

  14. Vi lager et VB program som henter data fra en database • Bruker et DataTable object i VB • DataTable object er en tabell • Vi har sett på tabeller med en kolonne og mange rekker F.eks.: Dim biler() As Bil

  15. ... henter data fra en database • Tabeller ha: • mange kolonner og mange rekker (todimensjonale) • en egen tabell med todimensjonale tabeller! (tredimensjonale) • osv. i prinsippet i det uendelige! • I INF150 ser programmerer skal vi beherske endimensjonale og kjenne til todimensjonale • Tabeller med flere enn to dimensjoner benyttes sjelden

  16. Todimensjonale tabeller(7.5 Two-dimensional Arrays p.377) • Hittil har vi sett på endimensjonale tabeller hvor hvert felt kan inneholde: • Enkle verdier • Strukturer • Alle felt må være av samme type • Nå ser vi på todimensjonale tabeller • Også her må alle felt må være av samme type • F.eks. Bilannonse • Merke, Modell, Arsmodell, AnnonseSattInn, AntVisninger

  17. Eksempel på todimmensjonal tabellBilannonse Oppgave: Indiker hva som er et “Field” og en “Record”

  18. Todimensjonal tabell i Visual Basic ’Deklarerer tabell med 10 rekker og 5 kolonner: Dim bilannonse(9, 4) As String ‘ Legger inn verdier i kolonnene i rekke 0: bilannonse(0,0) = “BMW” bilannonse(0,1) = “325” bilannonse(0,2) = “1999” bilannonse(0,3) = “17.11.2006” bilannonse(0,4) = “9” ‘ Legger inn verdier i kolonnene i rekke 1: bilannonse(1,0) = “VW” ‘.... MsgBox(bilannonse.Length)

  19. Alle feltene var av type String • Det er vanlig at databaser lagrer alle data som String • Vi kan gi tilleggsopplysninger til databasen om hvilken type teksten representerer slik at de som leser databasen kan omforme til rett type

  20. Vi legger tabellen inn i Microsoft Access og leser den inn i et VB program • Start Access ... • Opprett BILANNONSER.MDB • Lag annonser-tabell • Legg inn de tre annonsene • Start Visual Basic • Åpne for tilgang til DataObject ...

  21. “Data Table Object” • Et “DataTable object” er et ferdig Visual Basic objekt som inneholder en kopi av innholdet i en databasetabell. • En DataTable ligner en vanlig todimensjonal tabell i VB med kolonner og rekker.

  22. Åpne for tilgang til DataObject • VB har mange ferdige objekter og kontroller. • Koden til alle er svært omfattende, så det er bare de vanligste som er tatt med som standard. • Vi må derfor gi beskjed dersom vi vil benytte det som ikke er standard, så som DataObject • Gi beskjed om å ta med koden til DataObject (se de neste slides)

  23. I VB-editoren: Ta med koden til DataObject, denne finnes i System.Data.dll and System.Xml.dll (se side 521:) a) Click on Project in the Menu bar. b) Click on Add Reference in the drop-down menu. To invoke the “Add Reference” dialog box. c) Make sure the .NET tab is selected. d) Click on System.Data. Hold down the Ctrl key and click on System.Xml. e) Press the OK button.

  24. I koden vår I kodevinduet, helt på toppen: Imports System.Data Public Class Form1 ...End Class

  25. Connecting with a DataTableside 521 Dim dt As New DataTable() Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New _ OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose()

  26. Linje 1 forrige side Dim dt As New DataTable() • Deklarerer dt som en variable av type DataTable

  27. Linje 2 Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" • Angir • Databasedriveren • Filespec til databasefilen • forutsetter at filen ligger i bin/debug katalogen

  28. Linje 3 Dim sqlStr As String = "SELECT * FROM Cities" • Sier hvilken tabell data skal hentes fra • SQL: Standard Query Language

  29. Linje 4 Dim dataAdapter As New _ OleDb.OleDbDataAdapter(sqlStr, connStr) • DB er på disken mens DataTable er i minnet • Adapter inneholder kode som omformer vår forespørsel gitt i sqlstr til kode tilpasset databasen gitt i connStr

  30. Linje 5 og 6 dataAdapter.Fill(dt) dataAdapter.Dispose() • Bruker DataAdapter til å hente data fra DB til tabellen i dt • Dispose() kalles når vi ikke har bruk for tilkoblingen mer.

  31. Kode for å hente fra DB Imports System.Data Public Class Form1 Dim dt As New DataTable Private Sub btnTest_Click(...) Handles btnTest.Click Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=BILANNONSER.MDB" Dim sqlStr As String = "SELECT * FROM Annonser" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() ’... se neste side

  32. Kode for å hente fra DB lstVis.Items.Add(”Ant. rekker: ” & dt.Rows.Count) lstVis.Items.Add(”Ant. kolonner: ” & dt.Columns.Count) lstVis.Items.Add(”Rekke 0 kolonne 0: ” & dt.Rows(0)(0)) lstVis.Items.Add(”Rekke 0 kolonne modell: ”dt.Rows(0)("modell")) End Sub

  33. Utskrift til listbox formateres: Dim fmtStr As String = "{0,-15}{1,-15}{2,-5}{3,11}{4,5}” For i As Integer = 0 To dt.Rows.Count - 1 lstVis.Items.Add(String.Format(fmtStr, dt.Rows(i)(0), dt.Rows(i)(1),_ dt.Rows(i)(2), dt.Rows(i)(3), dt.Rows(i)(4))) Next

  34. Kode for å hente fra DataObject til vår egen todimenjonale tabell 'Tabell med 3 rekker og 5 kolonner: Dim bilannonse(2, 4) As String ’Kopierer fra DataTable, dt: bilannonse(0, 0) = dt.Rows(0)(0) ’ ...

  35. Properties of the DataTableside 522 • After the six lines of code are executed, the number of records in the table is given by dt.Rows.Count • The number of columns in the table is given by dt.Columns.Count • The records are numbered 0 through dt.Rows.Count – 1 • The fields are numbered 0 through dt.Columns.Count – 1

  36. ... • The name of the jth field is given by dt.Columns(j) • The entry in the jth field of the ith record is dt.Rows(i)(j) • The entry in the specified field of the ith record is dt.Rows(i)(fieldName)

  37. Example 1: Form Display one record at a time from the Cities table.

  38. Example 1: Partial Code Dim dt As New DataTable() Dim rowIndex As Integer = 0 Private Sub frmCities_Load(...) Handles _ MyBase.Load (Last five statements of boilerplate) UpdateTextBoxes() End Sub Sub UpdateTextBoxes() 'Display contents of row specified by rowIndex variable txtCity.Text = CStr(dt.Rows(rowIndex)("city")) txtCountry.Text = CStr(dt.Rows(rowIndex)("country")) txtPop2005.Text = CStr(dt.Rows(rowIndex)("pop2005")) txtPop2015.Text = CStr(dt.Rows(rowIndex)("pop2015")) End Sub

  39. Example 1: Partial Code cont. Private Sub btnNext_Click(...) Handles btnNext.Click 'Show the next record if current one is not the last If (rowIndex < dt.Rows.Count - 1) Then rowIndex += 1 'Increase rowIndex by 1 UpdateTextBoxes() End If End Sub Private Sub btnPrevious_Click(...) Handles _ btnPrevious.Click 'Show previous record if current one is not the first If (rowIndex > 0) Then rowIndex = rowIndex - 1 UpdateTextBoxes() End If End Sub

  40. Example 1: Partial Code cont. Private Sub btnFind_Click(...) Handles btnFind.Click Dim cityName As String Dim cityFound As Boolean = False cityName=InputBox("Enter name of city to search for.") For i As Integer = 0 To (dt.Rows.Count - 1) IfCStr(dt.Rows(i)("city")) = cityName Then cityFound = True rowIndex = i UpdateTextBoxes() End If Next If (Not cityFound) Then MsgBox("Cannot find requested city",0,"Not in Table") End If End Sub

  41. Example 1: Output

  42. Example 2: Form Display Cities table along with percentage growth.

  43. Example 2: Code Private Sub btnShow_Click(...) Handles btnShow.Click Dim fmtStr As String="{0,-15}{1,-10}{2,7:N1}{3,7:N1}{4,7:P0}" Dim percentIncrease As Double (Six statements of boilerplate) lstDisplay.Items.Add(String.Format(fmtStr, "CITY", _ "COUNTRY","2005", "2015", "INCR.")) For i As Integer = 0 To dt.Rows.Count - 1 percentIncrease = (CDbl(dt.Rows(i)("pop2015")) - _ CDbl(dt.Rows(i)("pop2005"))) / CDbl(dt.Rows(i)("pop2005")) lstDisplay.Items.Add(String.Format(fmtStr, dt.Rows(i)(0), _ dt.Rows(i)(1),dt.Rows(i)(2),dt.Rows(i)(3),percentIncrease)) Next End Sub

  44. Example 2: Output

  45. Bound Controls • A data table that is bound to a list box can transfer information automatically into the list box. • The following statement binds a list box to a data table: lstBox.DataSource = dt • The contents of a specified field can be displayed in the list box by: lstBox.DisplayMember = "country"

  46. I vårt eksempel lstVis.DataSource = dt lstVis.DisplayMember = "merke"

  47. Example 3: Form Display the list of countries. When the user clicks on a country, its monetary unit should be displayed.

  48. Example 3: Code Dim dt As New DataTable() Private Sub frmCountries_Load(...) HandlesMyBase.Load (Last five statements of boilerplate) lstCountries.DataSource = dt 'Bind list box lstCountries.DisplayMember = "country" End Sub Private Sub lstCountries_SelectedIndexChanged(...) _ Handles lstCountries.SelectedIndexChanged txtMonetaryUnit.Text = _ CStr(dt.Rows(lstCountries.SelectedIndex)("monetaryUnit") End Sub

  49. Example 3: Output

More Related