Transcript chapter14

Chapter 14: Advanced Topics:
DBMS, SQL, and ASP.NET
Visual Basic .NET Programming:
From Problem Analysis to Program Design
Objectives
• Explore the relational database model
• Construct database queries using SQL
• Write VB .NET programs that interact with a
relational database
• Explore ASP.NET
Visual Basic .NET Programming: From Problem Analysis to Program Design
2
Exploring the Relational
Database Model
• Real-world applications require data persistence
– VB .NET provides several mechanisms
• Sequential files
• Relational database
Visual Basic .NET Programming: From Problem Analysis to Program Design
3
Understanding Tables, Rows,
Columns, and Primary and
Foreign Keys
• Tables
– Organize data
– Relational database consists of many different
tables
• Related to each other in some way
– Consist of
• Rows
• Columns
Visual Basic .NET Programming: From Problem Analysis to Program Design
4
Understanding Tables, Rows,
Columns, and Primary and
Foreign Keys (continued)
• Rows
– Records
– Instances
• Columns
– Attributes
– Fields
Visual Basic .NET Programming: From Problem Analysis to Program Design
5
Understanding Tables, Rows,
Columns, and Primary and
Foreign Keys (continued)
• Primary key
– Field that uniquely identifies each record
– Good database design calls for primary key whose
• Value is not likely to change over time
• Value is not null for any instance
– Can add unique identifying attribute to table
Visual Basic .NET Programming: From Problem Analysis to Program Design
6
Understanding Tables, Rows,
Columns, and Primary and
Foreign Keys (continued)
• Foreign key
– Links information in one table to information in
another table
– Must be primary key in other table
Visual Basic .NET Programming: From Problem Analysis to Program Design
7
Visual Basic .NET Programming: From Problem Analysis to Program Design
8
Mapping PD Attributes to
Database Tables
• Many different choices for organizing attributes
into relational tables
– Attributes can be mapped to more than one table
– Can add fields that were not originally contained in
PD class
– Seldom single choice
• Weigh benefits and drawbacks of several possible
designs
• Choose design that best fits needs of application
Visual Basic .NET Programming: From Problem Analysis to Program Design
9
Visual Basic .NET Programming: From Problem Analysis to Program Design
10
Visual Basic .NET Programming: From Problem Analysis to Program Design
11
Visual Basic .NET Programming: From Problem Analysis to Program Design
12
Constructing Queries Using
SQL
• Query
– Request for information.
– Usually specifies one or more conditions that
extracted information must meet
– Can be written in Structured Query Language
• SQL
Visual Basic .NET Programming: From Problem Analysis to Program Design
13
Constructing Queries Using
SQL (continued)
• SQL
– Enables you to perform other common database
operation
• Insert
• Update
• Delete
Visual Basic .NET Programming: From Problem Analysis to Program Design
14
Extracting Records from a
Database
• SELECT statement.
– Format:
SELECT attribute1, attribute2, ... , attributeN
FROM table
WHERE condition
– Each attribute specifies name of column
– Table specifies name of table
– Condition specifies criteria for selecting records
from table
Visual Basic .NET Programming: From Problem Analysis to Program Design
15
Extracting Records from a
Database (continued)
• SELECT statement
– Example:
SELECT FirstName, LastName FROM Customer
WHERE State = ‘GA’
– Can use keywords AND and OR
• To specify compound conditions
– Extract attributes from multiple tables
Visual Basic .NET Programming: From Problem Analysis to Program Design
16
Inserting Records into a
Database
• INSERT statement
– Formats:
INSERT INTO table VALUES (value1, value2, ... ,
valueN)
INSERT INTO table (attribute1, attribute2, ... ,
attributeN) VALUES (value1, value2, ... , valueN)
– First format
• Must know order of fields in underlying table
Visual Basic .NET Programming: From Problem Analysis to Program Design
17
Inserting Records into a
Database (continued)
• INSERT statement
– Example:
INSERT INTO Customer VALUES ('51954', 'John',
'Doe', 'Miami', 'FL', '345-6789')
Visual Basic .NET Programming: From Problem Analysis to Program Design
18
Updating Records in a
Database
• UPDATE statement
– Format:
UPDATE table
SET attribute1=value1, attribute2=value2, ...,
attributeN=valueN
WHERE condition
– SET clause
• Assign values to each attribute to be modified
Visual Basic .NET Programming: From Problem Analysis to Program Design
19
Updating Records in a
Database (continued)
• UPDATE statement (continued)
– WHERE clause
• Specifies to which record UPDATE command
applies
– Example:
UPDATE Customer SET State = 'Georgia' WHERE
State = 'GA'
Visual Basic .NET Programming: From Problem Analysis to Program Design
20
Deleting Records from a
Database
• DELETE statement
– Format:
DELETE FROM table WHERE condition
– WHERE clause
• Specifies conditions that determine record to delete
– Example:
DELETE FROM Customer WHERE State = 'Georgia'
Visual Basic .NET Programming: From Problem Analysis to Program Design
21
Writing VB .NET Programs
that Interact with a Relational
Database
• VB .NET Framework provides support for several
database management systems including:
– Oracle
– Microsoft Access
– Microsoft SQLServer
Visual Basic .NET Programming: From Problem Analysis to Program Design
22
Writing VB .NET Programs
that Interact with a Relational
Database (continued)
• Data providers
– Used to
• Connect to database
• Execute commands,
• Retrieve results
• Namespaces:
– System.Data
– System.Data.OleDb
Visual Basic .NET Programming: From Problem Analysis to Program Design
23
Visual Basic .NET Programming: From Problem Analysis to Program Design
24
Visual Basic .NET Programming: From Problem Analysis to Program Design
25
Visual Basic .NET Programming: From Problem Analysis to Program Design
26
Understanding the Examples in
this Section
• Build on Programming Example presented at end
of Chapter 13
• Integrated system
– Adding students and professors to database
– Producing lists of students and professors.
– Extends functionality to allow updating/deleting
Visual Basic .NET Programming: From Problem Analysis to Program Design
27
Visual Basic .NET Programming: From Problem Analysis to Program Design
28
Understanding the Examples in
this Section (continued)
• Three-tier design
– PD and GUI classes are unaware of how DA class
implements data persistence
– DA class handles all interaction with database and
carries out tasks of:
•
•
•
•
Inserting
Updating
Deleting
Extracting information
Visual Basic .NET Programming: From Problem Analysis to Program Design
29
Understanding the Examples in
this Section (continued)
• Three-tier design
– GUI classes that capture and display information
simply invoke DA methods
Visual Basic .NET Programming: From Problem Analysis to Program Design
30
Making the StudentDA Class
Work with an Access Database
• Import namespaces that provide functionality to
interact with Microsoft Access database
• Initialize method
– Creates and opens connection to database
– Attempt to open connection is enclosed within TryCatch block
Visual Basic .NET Programming: From Problem Analysis to Program Design
31
Making the StudentDA Class
Work with an Access Database
(continued)
• GetAll method
– Issues SELECT command to retrieve all records
from database
• General approach for executing SELECT
command:
– Create data set instance
– Create data adapter instance
Visual Basic .NET Programming: From Problem Analysis to Program Design
32
Making the StudentDA Class
Work with an Access Database
(continued)
• General approach for executing SELECT
command (continued):
– Define String variable containing SELECT
statement
– Use SelectCommand property of data adapter to
create command instance and execute command
against database
– Invoke Fill method of data adapter class to
populate data set
Visual Basic .NET Programming: From Problem Analysis to Program Design
33
Making the StudentDA Class
Work with an Access Database
(continued)
• Find method
– Nearly identical to GetAll method
– SELECT statement issued within Find method
returns at most one record
• AddNew method
– Insert new record into database
Visual Basic .NET Programming: From Problem Analysis to Program Design
34
Making the StudentDA Class
Work with an Access Database
(continued)
• To create and execute INSERT command in
VB.NET:
– Create data adapter instance
– Create instance of OleDbCommand class
– Assign instance to data adapter’s InsertCommand
– Invoke ExecuteNonQuery method of
OleDbCommand class
Visual Basic .NET Programming: From Problem Analysis to Program Design
35
Making the StudentDA Class
Work with an Access Database
(continued)
• Update method
– Similar to AddNew method.
– Defines SQL statement to update database
• Delete method
– Defines SQL DELETE statement
– Deletes record from database
• Terminate method
– Closes database connection
Visual Basic .NET Programming: From Problem Analysis to Program Design
36
Making the StudentDA Class
Work with an Access Database
(continued)
• Exception classes:
– NotFoundException
– DuplicateException
Visual Basic .NET Programming: From Problem Analysis to Program Design
37
Recognizing the Benefits of the
Three-Tier Design
• Very little change required to PD and GUI classes
– To make them work with new StudentDA class
Visual Basic .NET Programming: From Problem Analysis to Program Design
38
Programming Example:
Updating and Deleting Records
in a Relational Database
• Demonstrates how to update and delete records
from relational database in three-tier design
– Using methods of StudentDA class
• Benefit of three-tier design
– Add new GUIs without having to make changes in
PD and DA tiers
Visual Basic .NET Programming: From Problem Analysis to Program Design
39
Visual Basic .NET Programming: From Problem Analysis to Program Design
40
Introducing ASP.NET
• ASP.NET
– Used to write interactive web pages
Visual Basic .NET Programming: From Problem Analysis to Program Design
41
Reviewing Web Basics
• Hypertext Markup Language (HTML)
– Employed by web pages
– Enables browsers to display Web pages on variety
of computing platforms
– Descriptive language
• Uniform Resource Locator (URL)
• ASP.NET
– Can contain HTML and related files containing
code to provide interactivity
Visual Basic .NET Programming: From Problem Analysis to Program Design
42
Reviewing Web Basics
(continued)
• Web pages
– Actual files containing HTML code
– Have extension of .htm or .html
– Contains tags that define format of Web page
– Can be created using any text editor
• Such as Notepad
Visual Basic .NET Programming: From Problem Analysis to Program Design
43
Visual Basic .NET Programming: From Problem Analysis to Program Design
44
Creating ASP.NET Projects
• NET Framework provides tools to develop both
– Windows applications
– Web applications
• ASP.NET separates procedural code from Web
form (HTML) code:
– Web form file has suffix of .aspx,
– Code file has suffix .vb
• Called code-behind file
Visual Basic .NET Programming: From Problem Analysis to Program Design
45
Creating ASP.NET Projects
(continued)
• In new projects dialog box in Visual Studio .NET
– Choose ASP.NET Web Application template
• Use Toolbox to add form components
– Display Web Forms or HTML sections
Visual Basic .NET Programming: From Problem Analysis to Program Design
46
Visual Basic .NET Programming: From Problem Analysis to Program Design
47
Visual Basic .NET Programming: From Problem Analysis to Program Design
48
Creating ASP.NET Projects
(continued)
• Validation controls
– Facilitate adding data validation
– Appear in Toolbox
– Can be placed on Web page just as any other
controls
– Properties can be accessed in Properties window
Visual Basic .NET Programming: From Problem Analysis to Program Design
49
Visual Basic .NET Programming: From Problem Analysis to Program Design
50
Programming Example:
Electricity Billing
• Deploys Electricity Billing Programming Example
at end of Chapter 10
– As ASP.NET Web-based application
• GUI
– Inputs and validates
• Customer Number
• Previous, and Current Meter Readings
Visual Basic .NET Programming: From Problem Analysis to Program Design
51
Programming Example:
Electricity Billing (continued)
• Data validation:
– Customer Number is required
– Previous Reading is required
• Must be in range 0–99999
– Current Reading is required
• Must be in range 0–99999
• Must be greater than or equal to Previous Reading
Visual Basic .NET Programming: From Problem Analysis to Program Design
52
Summary
• Applications require data to be persistent
– Use relational databases
• Relational database
– Enables you to organize data into tables
• Primary key
– Field that uniquely identifies each record in table
• Query
– Request for information
Visual Basic .NET Programming: From Problem Analysis to Program Design
53
Summary (continued)
• Structured Query Language (SQL)
– Allows you to specify queries
• Three-tier design
– PD and GUI classes are unaware of how data DA
class implements data persistence
• Web development using ASP.NET is similar to
developing GUI applications for Windows
• ASP.NET separates procedural code from Web
form (HTML) code
Visual Basic .NET Programming: From Problem Analysis to Program Design
54