Transcript Chapter 10

Chapter 10 – Database
Management
10.1 An Introduction to Databases
10.2 Relational Databases and SQL
Chapter 10 - VB.Net by Schneider
1
10.1 An Introduction to
Databases
• The Server Explorer
• Accessing a Database with a Data Table
Chapter 10 - VB.Net by Schneider
2
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.
• In a phone books, there are fields for name,
address, and phone number. Your entry in the
phone book is a record.
Chapter 10 - VB.Net by Schneider
3
Database Management
Software (DBMS)
• Used to create databases
• Databases can contain one or more
related tables
• Examples of DBMS include Access and
Oracle
Chapter 10 - VB.Net by Schneider
4
The Server Explorer
• Allows the programmer to view
information located on other computers
• Can also be used to view a database
Chapter 10 - VB.Net by Schneider
5
Using the Server Explorer
1. Place your mouse over the “Server Explorer” tab on the
left side of Visual Studio.
2. Right-click on “Data Connections”, and select “Add
Connection”.
3. In the Data Link Properties window that appears, click on
the “Provider” tab at the top. Select the item “Microsoft
Jet 4.0 OLE DB Provider” from the OLE DB Providers List
box, and then press the next button.
4. Click on the “...” button to the right of the first text box.
This will open up a file,browser that allows you to locate
any file then press Open.
5. Clear the contents of the “User name” text box.
Chapter 10 - VB.Net by Schneider
6
Server Explorer continued
6. 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.
7. An icon should appear in Server Explorer. Click on the +
sign to the left of the icon to expand this entry. Three
subentries will appear: Tables, Views, and Stored
Procedures.
8. Expand the Tables entry to reveal the subentries.
9. Expand an entry to reveal the fields of the table.
10. Double-click on a table to show the table in a grid.
Chapter 10 - VB.Net by Schneider
7
Figure 10.1 – Server Explorer
Chapter 10 - VB.Net by Schneider
8
Figure 10.2 – The Cities Table
Chapter 10 - VB.Net by Schneider
9
Accessing a Database with a
Data Table
• A DataTable object holds the contents of
a table as a rectangular array.
• A data table is similar to a twodimensional array; it has rows and
columns.
• The following declares a DataTable
variable
Dim dt As New DataTable()
Chapter 10 - VB.Net by Schneider
10
Connecting with a DataTable
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()
Chapter 10 - VB.Net by Schneider
11
Properties of the DataTable
• 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
Chapter 10 - VB.Net by Schneider
12
More Properties
• 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 string containing the entry in the
specified field of the ith record is
dt.Rows(i)(fieldName)
Chapter 10 - VB.Net by Schneider
13
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 filed can be
displayed in the list box by:
lstBox.DisplayMember = "country"
Chapter 10 - VB.Net by Schneider
14
10.2 Relational Databases
and SQL
•
•
•
•
Primary and Foreign Keys
SQL
Four SQL Requests
The DataGrid Control
Chapter 10 - VB.Net by Schneider
15
Primary Keys
• A primary key is used to uniquely
identify each record.
• Databases of student enrollments in a
college usually use a field of Social
Security numbers as the primary key.
• Why wouldn't names would not be a
good choice as a primary key?
Chapter 10 - VB.Net by Schneider
16
Primary Key Fields
• When a database is then created, a field can be
specified as a primary key.
• VB.NET will insist that every record have an entry in
the primary-key field and that the same entry does not
appear in two different records.
• If the user tries to enter a record with no data in the
primary key, the error message “Index or primary key
can’t contain a null record.” will be generated.
• If the user tries to enter a record with the same primary
key data as another record, the error message “The
changes you requested to the table were not
successful…"
Chapter 10 - VB.Net by Schneider
17
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 field.
• Notice that every entry in Cities.country
appears uniquely in Countries.country and
Countries.country is a primary key.
• We say that Cities.country is a foreign key of
Countries.country.
Chapter 10 - VB.Net by Schneider
18
Foreign Keys
• Foreign keys can be specified when a
table is first created VB.NET 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.
Chapter 10 - VB.Net by Schneider
19
Join
• A foreign key allows VB.NET to link (or join)
together two tables from a relational database
• When the two tables Cities and Countries from
MEGACITIES.MDB are joined based on the
foreign key Cities.country, the result is Table
10.4.
• The record for each city is expanded to show
its country’s population and its currency.
Chapter 10 - VB.Net by Schneider
20
SQL
• Structured Query Language developed
for use with relational databases
• Very powerful language
• Allows for the request of specified
information from a database
• Allows displaying information from
database in a specific format
Chapter 10 - VB.Net by Schneider
21
Four SQL Requests
• Show the records of a table in a specified
order
SELECT * FROM Table1 ORDER BY field1 ASC
• or
SELECT * FROM Table1 ORDER BY field1 DESC
Specifies
ASCending
Or
DESCending
Chapter 10 - VB.Net by Schneider
22
Show just the records that
meet certain criteria
* means
"all the fields"
Specified
Criteria
SELECT * FROM Table1 WHERE criteria
Name of the
Table where the
Records may be found
Chapter 10 - VB.Net by Schneider
23
Join the tables together
• connected by a foreign key, and present
the records as in Requests I and II.
SELECT * FROM Table1 INNER JOIN Table2 ON foreign
field = primary field WHERE criteria
Chapter 10 - VB.Net by Schneider
24
Make available just some of
the fields
• of either the basic tables or the joined
table.
SELECT field1, field2, . . ., fieldN FROM
Table1 WHERE criteria
Chapter 10 - VB.Net by Schneider
25
Criteria Clause
• A string containing a condition of the type used
with If blocks.
• Uses the standard operators <, >, and =
• Also can use the operator Like.
• Like uses the wildcard characters “_” and “%”
to compare a string to a pattern.
Chapter 10 - VB.Net by Schneider
26
Like Examples
• An underscore character stands for a single
character in the same position as the
underscore character.
• The pattern “B_d” is matched by “Bid”, “Bud”,
and “Bad”.
• A percent sign stands for any number of
characters in the same position as the asterisk.
• The pattern “C%r” is matched by “Computer”,
“Chair”, and “Car”.
Chapter 10 - VB.Net by Schneider
27
SELECT clause
• SELECT fields FROM clause
• fields is either * (to indicate all fields) or a
sequence of the fields to be available
(separated by commas)
• clause is either a single table or a join of
two tables
Chapter 10 - VB.Net by Schneider
28
Join clause
• A join of two tables is indicated by a clause of
the form
table1 INNER JOIN table2 ON foreign key of
table1=primary key of table2
• Appending WHERE criteria
to the end of the sentence restricts the records
to those satisfying criteria.
• Appending ORDER BY field(s) ASC (or
DESC) presents the records ordered by the
specified field or fields.
Chapter 10 - VB.Net by Schneider
29
General SQL statements
• SELECT www FROM xxx WHERE yyy
ORDER BY zzz
• where SELECT www FROM xxx is
always present
• May be accompanied by one or both of
WHERE yyy and ORDER BY zzz.
• The xxx portion might contain an INNER
JOIN phrase.
Chapter 10 - VB.Net by Schneider
30
More on SQL statements
• The single quote, rather than the normal
double quote, is used to surround
strings.
• Fields may be specified with the table
they come from by
tableName.FieldName
Chapter 10 - VB.Net by Schneider
31
Virtual Tables
• SQL statements create a new “virtual” table from
existing tables.
• Think of the following statement as creating a
"virtual table"
SELECT city, pop2015 FROM Cities WHERE
pop2015>=20
city
pop2015
Tokyo
28.7
• Results in:
Sao Paulo
Bombay
Shanghai
Chapter 10 - VB.Net by Schneider
20.8
27.4
23.4
32
Views
• “Virtual” tables don’t exist physically.
• For all practical purposes, VB.NET acts
as if they did.
• You may also see a “virtual” table called
a view.
Chapter 10 - VB.Net by Schneider
33
The DataGrid Control
• The DataGrid, displays the values for an
entire view in a table format identical to the
table displayed by Server Explorer.
• The prefix for the name of a DataGrid control is
dg.
• After a data table has been filled, the
statement
dgDisplay.DataSource = dt
• displays the contents of the data table dt in the
data grid.
Chapter 10 - VB.Net by Schneider
34
Example 1
Private Sub Form1_Load(...) Handles MyBase.Load
UpdateDataGrid("Select * From Cities")
End Sub
Private Sub btnOrderbyPop_Click(...) Handles btnOrderbyPop.Click
UpdateDataGrid("Select * From Cities Order By pop1995 ASC")
End Sub
Private Sub btnShowCurrency_Click(...)
Handles btnShowCurrency.Click
UpdateDataGrid("SELECT city, Cities.country, " & _
"Cities.pop1995, currency " & _
"FROM Cities INNER JOIN Countries " & _
"ON Cities.country=Countries.country " & _
"ORDER BY city ASC")
End Sub
Chapter 10 - VB.Net by Schneider
35
Example 1 continued
Sub UpdateDataGrid(ByVal sqlStr As String)
Dim dt As New DataTable()
Dim connStr As String ="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = MEGACITIES.MDB"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
dgDisplay.DataSource = dt
End Sub
Chapter 10 - VB.Net by Schneider
36
Example 2
Private Sub btnFindCities_Click(...)
Handles btnFindCities.Click
UpdateDataGrid("SELECT city FROM Cities WHERE" & _
"country = '" & txtCountry.Text & _
"' ORDER BY city ASC")
End Sub
Sub UpdateDataGrid(ByVal sqlStr As String)
'Declare and populate the data table.
Dim dt As New DataTable()
Dim connStr As String = "Provider = " & _
"Microsoft.Jet.OLEDB.4.0;Data Source = " & _
"MEGACITIES.MDB"
Chapter 10 - VB.Net by Schneider
37
Example 2 continued
Dim dataAdapter As New _
OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
'Display the names of the cities in the specified
'country.
If dt.Rows.Count = 0 Then
MsgBox("No cities from that country " & _
"in the database")
Else
dgDisplay.DataSource = dt
End If
End Sub
Chapter 10 - VB.Net by Schneider
38
Example 2 Output
Chapter 10 - VB.Net by Schneider
39
Changing the Contents of a
Database
• Data grids can also be used to add, modify, and
delete records from a database.
• After a DataAdapter has been created, the
statement
Dim commandBuilder As New
OleDbCommandBuilder(dataAdapter)
will automatically generate the commands used
for the Insert, Update, and Delete operations.
Chapter 10 - VB.Net by Schneider
40
Using the DataAdapter to
Change a Database
• If changes is an Integer variable, then
the statement
changes = dataAdapter.Update(dt)
will store all of the insertions, updates,
and deletions made in the data table to
the database and assign the number of
records changed to the variable
changes.
Chapter 10 - VB.Net by Schneider
41
Comments
1. There is a one-to-many relationship from
the Countries table to the Cities table if each
record of the Countries table is related to one
or more records of the Cities table, but each
record of the Cities table is related to only one
record of the Countries table.
2. SQL statements are case insensitive.
3. When the Like operator is used, the “pattern”
must appear on the right of the operator.
SELECT * FROM Cities WHERE city Like 'S%'
Chapter 10 - VB.Net by Schneider
42
Comments continued
4. An expression such as “[letter1-letter2]” is a
placeholder for any letter from letter1 to
letter2. Example: the pattern “[A-F]ad” is
matched by Bad and Dad, but not Sad.
5. When Like is used in SQL statements, it is
case insensitive. That is, (‘bad’ Like ‘[A-F]ad’)
is True. When Like is used in an If block, the
asterisk is used instead of the percent sign to
denote any number of characters, and the
question mark stands for any one character.
Chapter 10 - VB.Net by Schneider
43
Comments continued
6. The requirement that no record may
have a null primary key and that entries
for primary keys be unique is called the
Rule of Entity Integrity.
Chapter 10 - VB.Net by Schneider
44