1 / 68

Chapter 10 – Database Management

Chapter 10 – Database Management. 10.1 An Introduction to Databases 10.2 Editing and Designing Databases. 10.1 An Introduction to Databases. Accessing a Database Table Binding to Additional Tables Querying a Table with LINQ Primary and Foreign Keys The Join of Two Tables.

coen
Download Presentation

Chapter 10 – Database Management

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. Chapter 10 – Database Management 10.1 An Introduction to Databases 10.2 Editing and Designing Databases

  2. 10.1 An Introduction to Databases • Accessing a Database Table • Binding to Additional Tables • Querying a Table with LINQ • Primary and Foreign Keys • The Join of Two Tables

  3. Sample Table – Cities Table

  4. Sample Table – Countries Table

  5. 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 table.

  6. Database Management Software (DBMS) • Used to create databases • Databases contain one or more related tables • Examples of DBMS are Access, Oracle, and SQL Server. • The databases used in this chapter are found in the folder Programs\Ch10\Databases. They were created with Access and have the extension accdb.

  7. Megacities.accdb • Contains the two tables Cities and Countries shown earlier. • This database will be used extensively in the examples for this chapter. • Several steps are required to bind to a table of the database. (See the next sixteen slides.)

  8. Binding to the Cities Table Add a BindingSource control to the form. (The control is in the Data and All Windows Forms group of the Toolbox. It appears in the form’s component tray with the name BindingSource1.)

  9. DataSource Property of BindingSource1 click here

  10. Choose Data Source Type select click on Next button

  11. Choose Database Model select click on Next button

  12. Choose Data Connection click on New Connection button

  13. Add Connection Dialog Box click on Change button

  14. Change Data Source Box select click on OK button

  15. Add Connection Dialog Box click on Browse button

  16. Select Database File double-click on Megacities.accdb

  17. Add Connection Dialog Box click on OK button

  18. Choose Data Connection click on Next button

  19. click on Yes button

  20. Save to File check this box click on Next button

  21. Choose Database Objects check on Tables box click on Finish button

  22. Changes in Properties Window and Form

  23. After Clicking on DataMember Down-Arrow click on Cities

  24. VB Generated Items new code new icon

  25. Binding Complete • We are now bound to the Cities table via the MegacitiesDataSet and the CitiesTableAdapter. • The next four slides show how to bind an additional table.

  26. Connect an Additional Table Add another BindingSource control to the form.

  27. Set DataSource Property click on MegacitiesDataSet

  28. Set DataMember Property click on Countries

  29. VB Generated Items new icon additional code shows in Load event procedure Me.CountriesTableAdapter.Fill(Me.MegacitiesDataSet.Countries)

  30. Example 1: Form txtTotalPop

  31. Example 1: Code Private Sub btnDisplay_Click(...) Handles _ btnDisplay.Click Dim query1 = From city In MegacitiesDataSet.Cities Where city.country = "India" Order By city.pop2010 Descending Select city.name lstOutput.DataSource = query1.ToList lstOutput.SelectedItem = Nothing

  32. Example 1: Code (continued) Dim query2 = From city In _ MegacitiesDataSet.Cities Where city.country = "India" Select city.pop2010 txtTotalPop.Text = CStr(query2.Sum) End Sub

  33. Example 1: Output

  34. Example 2: Form txtName dgvOutput

  35. Example 2: Code Dim query = From country In _ MegacitiesDataSet.Countries Where country.name = txtName.Text Select country.name, country.pop2010, country.monetaryUnit If query.Count = 1 Then dgvOutput.DataSource = query.ToList dgvOutput.CurrentCell = Nothing Else MessageBox.Show("Country not found") End If

  36. Example 2: Output

  37. Example 3: Form dgvOutput

  38. Example 3: Code Dim query = From city In _ MegacitiesDataSet.Cities Let popIncrease = city.pop2015 - city.pop2010 Let formattedIncr = FormatNumber(popIncrease, 1) Where popIncrease > 1 Order By popIncrease Descending Select city.name, formattedIncr

  39. Example 3: Code (continued) dgvOutput.DataSource = query.ToList dgvOutput.CurrentCell = Nothing dgvOutput.Columns("name").HeaderText = "City" dgvOutput.Columns("formattedIncr").HeaderText = "Population Increase"

  40. Example 3: Output

  41. Primary Keys • A primary key is used to uniquely identify each record. • Databases of student enrollments in a college usually use a field of student ID numbers as the primary key. • Why wouldn't names be a good choice as a primary key?

  42. Primary Key Fields • Specified when database is created. • Every record must have an entry in the primary-key field. • Two records cannot have the same entry in the primary-key field. • This pair of requirements is called the Rule of Entity Integrity.

  43. Two or More Tables • When a database contains two or more tables, the tables are usually related. • For instance, the two tables Cities and Countries are related by their country and name fields. • Notice that every entry in Cities.country appears uniquely in Countries.name and Countries.name is a primary key. • We say that Cities.country is a foreign key of Countries.name.

  44. Foreign Keys • Foreign keys can be specified when a table is first created. Visual Basic will insist on the Rule of Referential Integrity. • This Rule says that each value in the foreign key must also appear in the primary key of the other table.

  45. Join • A foreign key allows Visual Basic to link (or join) two tables from a relational database • When the two tables Cities and Countries from Megacities.accdb are joined based on the foreign key Cities.country, the result is the table in the next slide. • The record for each city is expanded to show its country’s population and its monetary unit.

  46. A Join of Two Tables

  47. Beginning of Query to Join the Two Tables from Megacities Dim query = From city In _ MegacitiesDataSet.Cities Join country In MegacitiesDataSet.Countries On city.country Equals country.name

  48. Example 6: Form

  49. Example 6: Code for Load Event Me.CountriesTableAdapter.Fill(Me.MegacitiesDataSet.Countries) Me.CitiesTableAdapter.Fill(Me.MegacitiesDataSet.Cities) Dim query = From country In _ MegacitiesDataSet.Countries Order By country.monetaryUnit Ascending Select country.monetaryUnit Distinct lstCurrencies.DataSource = query.ToList

  50. Example 6: Later Code Dim query = From city In _ MegacitiesDataSet.Cities Join country In MegacitiesDataSet.Countries On city.country Equals country.name Where country.monetaryUnit = lstCurrencies.Text Order By city.name Ascending Select city.name For Each city As String In query lstCities.Items.Add(city) Next

More Related