Web Access of Database

Download Report

Transcript Web Access of Database 

Computer Science 101
Web Access to Databases
Overview of Web Access to
Databases
Why web access to database?
• Provides platform independent, remote
access to the database
– Viewing data
– Gathering data
– Updating data
Client-Server Technologies
• Server: A program that provides services to
other programs. It stands ready for requests
and when it gets a request, it provides the
service.
• Client: A program requesting a service of a
server program. It makes a request, gets the
service, and makes use of it.
Web Browsers and Servers
• Web server - This is a program that runs on
the internet host computer (server). It takes
requests for web pages from clients and
delivers the pages back to the client.
• Web browser - This is a program that runs on
your local PC (client). It allows you to request
web pages from an internet host.
HTML and HTTP
• HyperText Markup Language - Allows “marking
up” a document with tags specifying appearance
and structure.
• HyperText Transfer Protocol - Protocol used for
browsers to communicate with web servers.
Basically, this consists of “requests” from the
browser and “responses” from the server.
Typical HTTP Request
HTTP Response
<HTML>
<B> This is a web page </B>
<IMAGE> Picture</IMAGE>
</HTML>
Browser
This is a
web page
Web Server
HTTP Request
http://website/some.html
Browser interprets
HTML and displays
page
Typical HTML Request
• Client Side (Browser)
• Server Side
– Issues request for
HTML page
– Reads request from
client
– Receives response
HTML page
– Finds page on server
– Interprets HTML and
creates web page
– Displays web page
– Work is done here
– Sends response page to
client
– Essentially a file server
Scripting Languages
• Scripting languages - Allow us to add capability to
what’s provided by HTML. Allow parts of the page to be
built “on the fly”. These scripts are “interpreted” as they
run rather than being compiled.
• Client-side scripts - Script engine on client machine
builds parts of page when page is loaded by browser (date
is simple example). Javascript is prime example.
• Server-side scripts - Script engine on server builds parts
of page before sending to client (database query results
for example). ASP, PHP, JSP, Purl are examples.
Server Side Programs
• Here we have compiled programs that run
on the server. Examples could be Java
Servlets, ASP.NET languages such as C#
Typical Server-Side Request
Runs script or program
<HTML>
on the server
<% server script or program %>
</HTML>
<HTML>
<H1> Stars </H1>
<B> John Wayne <BR>
Meg Ryan </B>
</HTML>
Sends response
to client
Gets Page
Web Server
HTTP Request
http://stars.aspx
Client
Stars
John Wayne
MegRyan
Browser interprets
HTML and displays
page
Typical Server-Side Request
• Client Side (Browser)
– Issues request for
HTML page
– Receives response
HTML page
– Interprets HTML and
creates web page
– Displays web page
• Server Side
– Reads request from
client
– Finds page on server
– Runs server-side
program needed
– Alters HTML file
– Sends response page to
client
Web Access of Database
• The database resides on the server.
• Web pages with scripts (or calls to
programs) allow the user to send database
requests to the server.
• The server accesses the database to honor
the requests.
• Results can be returned on an html page.
• Actions can take place on the database.
So, what are the pieces we need?
• Browser software on user machines
• A machine for the server
• Web server software on server
• Database management system on server
• Scripting/programming language for accessing
the database – something supported by the web
server
Web Applications:
3-Tier Architecture
User Query
User
Interface
SQL Query
Application
Logic - Program
Results -HTML
Query results
Browser
Web Server with
Local Machine
ASP.NET (C#)
Database
Access
Database
The Big Picture – User Interface
• User Interface contains web components for user input
or for displaying information returned from the
database.
• Two types of components that we will use for
displaying data in our examples will be GridViews and
DropDownLists.
• GridViews provide row and column displays. They can
be configured for really nice appearance, paging, etc.
• These components can have associated with them a
data source – essentially a database query.
We’ll keep it simple.
The Big Picture – Middle Tier
• The middle tier has program code to get users
input, connect to the database, present request
to the database, get results from the database,
send results on to the interface.
• This is where the server side programming
code would come in.
The Big Picture – Database
• Database receives requests in form of SQL
statements or in form of request for stored
query, performs the query and returns results
to middle tier.
• You are already expert at this part, correct?
SQL Injection
SQL Injection
Simple model – retrieving and
displaying data from the database
• For now we are going to consider examples
where we want the web page just to display
information resulting from a query to the
database.
• We’ll see that Visual Web Developer is
very helpful with setting up connections to
database, “binding” web components to
data sources, etc.
Example1 – Faculty Information
• As a first example, let’s have a web page
with a data grid displaying the faculty
information – LastName, FirstName, Phone
extension, and email address.
• So we would
– Start Visual Web Developer
– Open our website
– Create a new file (webform)
Example1 – Faculty Information (cont.)
• After placing any heading or whatever on the
page, we drag a GridView onto the page from
the Data Section of the ToolBox.
Example1 - Choosing the data source for the
GridView
New data source
Browse for database
Access Database
Example1 - Setting up the query
Example1 - Testing the query
Example1 - View the web page
Example1 - Modification
• Now let’s modify Example1 so that it displays
information only for the advisors – department
chairs who are not advisors will not appear.
• This only requires modifying the query associated
with the grid view.
• To get to the query editor
– Click on the grid view
– Then click on the triangle in the upper right corner of
the grid view.
– Click on Configure data source
Example1 – Modifying the query
• There is nothing in the Faculty table to
indicate whether or not a person is an advisor
of one of our class members.
• We need to use the Student table as well as
the Faculty table.
SELECT DISTINCT F.LastName, F.FirstName, Phone, Email
FROM Faculty F, Students S
WHERE FacultyID = AdvisorID
Example1 – Modifying the query
Example2 - Majors
• To begin, we’ll have a drop down list with
the various majors listed.
• The process is the same as before, put the
drop down list on the page and configure
the data source.
Data source for drop down list
Example2 – Viewing page with drop
down list
Example2 – GridView with students
• Now let’s modify Example2 by adding a grid view
that displays the names, class year, city, state and
advisor fields for the students having the major
selected from the drop down list.
• To have the selection in the drop down list take place,
we need to have a way to force the page to “post
back” to the server.
• We’ll use a button for now. It does nothing except
that clicking a button causes the form to be “posted”
to the server.
Example2 – Add GridView and Button
Example2 – Data Source for Grid View
Example2 – Testing the web page
Example2 – Adding advisors last name
• Now let’s modify the query so that we see
the advisor’s last name rather than the
advisor’s ID number.
Example2 – Testing the page
Example2 – Removing the button
• There is a property of drop down lists called
AutoPostBack.
• The default value for this is false.
• If this value is set to true, the page will post back
to the server whenever an item in the drop down
list is selected. In Example2, we can set this
property to true and remove the button.
• In many cases, we do not want that to happen
since we may want to make several selection
before having the action take place – make
selections and then click button.
Example3 – Stored query
• Often it is easier to develop and test queries
within the database itself as you did in lab.
• If we save these queries, they can be used in
configuring a data source just like tables.
Example3 – Data source