Web Application Development

Download Report

Transcript Web Application Development

Web Application Development
Dr. Nasir Darwish
[email protected]
Information & Computer Science Department
King Fahd University of Petroleum & Minerals
Dhahran, Saudi Arabia
Using ASP for Web/Database Integration
Significance of the Web
Components of the Web
Dynamic HTML
Using ASP for Web/Database Integration
ADO Objects (Connection, Recordset, Command)
Examples using Access Database
Sample Application using SQL Server Database
The World-Wide Web
• A collection of documents (Web pages) scattered on Web
servers throughout the world.
• The page content is specified in HTML (Hypertext
Markup Language)
• The pages are retrieved using HTTP (Hypertext Transfer
Significance of the Web
Advantages of the Web as a publishing medium :
Enhanced document formatting
Hyper Linking
Include multimedia
Interactivity - solicit input through forms
Dynamically changing content through scripting
Reachability through search
Encapsulate other protocols such as FTP and e-mail.
Components of the Web
• Web Client (e.g. Microsoft Internet Explorer vs. Netscape
Web Server (e.g. Microsoft Internet Information Server)
HTML (Hyper Text Markup Language) - Latest version 4.0
HTTP (Hyper Text Transfer Protocol) - Latest version 1.1
TCP/IP Network
Architecture of the Web
HTTP Server
HTTP Client
Network Hardware
• To get input from user - i.e. feedback, purchase order
• A form can have any of : text boxes, list boxes, command
• Input data is passed to a program specified in the Action
• The data is passed in the format :
name1=value1&name2=value2&name3=value3& ...
• The Method parameter (POST or GET) governs how the
data is retrieved by the CGI program (standard input or
environment variable)
Form Processing
• HTML :
<Form method=POST
action="http://darwish/cgiform.exe" >
<!-- use a CGI program to handle the form data -->
Name:<Input type=text name="name" SIZE=30>
<Input type=submit value="Submit"></Form>
• The form’s data is passed to a program which
– stores in a database or mail to someone
– composes and writes to standard output an html
page containing confirmation
Sample Form (HTML)
<INPUT TYPE=Password NAME="pwd"> Password Box
<INPUT TYPE=CheckBox NAME="CB"> Check Box
<INPUT TYPE=SUBMIT value=submit> Submit Button
<INPUT TYPE=RESET value=clear> Reset Button
Text Area (Multi-line Text Box)
<TEXTAREA name="comment" cols=40 rows=3></TEXTAREA>
combo list
<select name="title">
<option value="Mr.">Mr.
<option value="Ms.">Ms.
Sample Form
Dynamic HTML Pages Defined
• An HTML page that responds locally (without access to
server) in some fashion to user actions such as highlighting,
expanding/collapsing lists
• An HTML page whose content is generated at the time it is
Example: List all cars priced below 50,000.
Approaches to Building Dynamic HTML Pages
• First type uses client-side scripting (via VBScript or
JavaScript) and style sheets, or client-side Java Applets.
• Second type uses server-side scripting such as ASP or
CGI programming in C or PERL.
Web/Database Integration
• Most Web Servers provide ODBC based interface to
• Available for the Web-Page designer via SSI (Server’s
Side Include) or template files such as ASP
Active Server Pages (ASP)
• A general approach for server side scripting to automate
the generation of html files
• The ASP code is mixed with html in a template file with
the file extension (.asp)
• Supports both VBScript or JavaScript as the scripting
• Include programming objects (ActiveX) to support form
processing, state maintenance and database access (ADO :
Active Data Objects).
Sample ASP file (hello.asp)
<% For i = 3 To 7 %>
<FONT SIZE=<% = i %>>
Hello World!<BR>
<% Next %>
Generic Model for Web-Database Access
Web Browser
Web Server
http protocol
Interface Layer
DB Engine
Server Station
User Station
Microsoft's Solution
IIS using ASP
Data Provider
Data Source
DB Engine
Microsoft's Solution
• ASP uses ADO (ActiveX Data Objects) as the
programming interface
• ADO uses either ODBC or OLE DB
• OLE DB is meant to replace ODBC.
– Native OLE DB driver is faster than ODBC
– OLE DB drivers for many data sources
(Text file, ADSI, Exchange)
– There is a generic OLE DB driver for ODBC
ADO Top Level Objects
• Connection Object
– to establish a connection with a data source
– execute queries
• Recordset Object
– represents a set of records returned by a select query
• Command Object
– to execute SQL Server stored procedures
ADO is part of Microsoft Data Access Components (MDAC)
downloadable from http://www.microsoft.com/data
<% Set Con=Server.CreateObject("ADODB.Connection")
Response.Write "ADO Version = " & Con.Version %>
Using the Connection Object
1) Create a connection object
Set Con= Server.CreateObject("ADODB.Connection")
2) Use the Open method and specify as a parameter either
a) an ODBC Data Source Name (DSN), or ODBC connection string
Con.Open mydsn
Con.Open "DSN=mydsn;UID=sa;PWD=secret;DATABASE=Pubs"
b) an OLE DB connection string
Con.Open "Provider=SQLOLEDB; Data Source=myServer;
OLE DB Providers
OLEDB for Access Database:
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\mydb.mdb"
OLEDB for MS SQL Server:
Con.Open "Provider=SQLOLEDB; Data Source=myServer;
Con.Open "Provider=MSDASQL; DSN=myDSN"
Using Connection Execute Method
• To execute Insert/Delete/Update query
St1 = "Insert tUser (username,password) values ('Ali', 'secret')"
St2 = "Delete * from tUser"
St3 = "Update tUser SET password='' "
Con.Execute st1
Con.Execute st2
Con.Execute st3
• Use a recordset (rs) of a Select query
Set rs = Con.Execute ("select * from tUser")
Using Transactions through a Connection Object
Con.Execute "Insert tUserX Select * from tUser"
Con.Execute "Delete * from tUser"
Transaction Support in ASP
• Use BeginTrans/CommitTrans of a Connection object
• Use MTS (Microsoft Transaction Server) and designate
an ASP page as transactional
• Use BeginTrans/CommitTrans within a stored procedure
in an SQL Server database and use the ADO Command
object to execute the stored procedure
Displaying Query Results
Use a table with two rows
– 1st row is a header row showing field (column) names
– 2nd row is used to display the data of a single record.
This row is embedded in a while loop
<% do while not rs.eof %>
<% rs.movenext
loop %>
Using the Recordset Object
The Recordset object provide sophisticated control over how
the result of a query is accessed and manipulated.
Cursor control (CursorType): adOpenForwardOnly,
adOpenStatic, adOpenDynamic, adOpenKeyset
Locking control (LockType): adLockReadOnly,
adLockPessimistic, adLockOptimistic,
Recordset Cursor Types
• adOpenForwardOnly
(Default). Used for fastest performance. Records are fetched
serially from first to last (cannot move back)
• adOpenStatic
supports back/forward movement but cannot detect changes by
other users
• adOpenKeyset
detect update changes but not insert/delete
• adOpenDynamic
(Richest). detects all changes
Recordset Lock Types
• adLockReadOnly
(Default). Allows read by multiple users. Data cannot be edited
• adLockPessimistic
Other users cannot access the record as soon as editing starts
• adLockOptimistic
Other users can access the record but not at the moment changes
are to be committed
• adLockBatchOptimistic
used in conjunction with UpdateBatch method
Opening A Recordset
1- Create a recordset object and set the required options
2- Open the recordset using a query and a predefined connection
<!-- #Include virtual="/adovbs.inc" -->
<% Set Con= Server.CreateObject("ADODB.Connection")
Con.Open mydsn
Set RS= Server.CreateObject("ADODB.Recordset")
RS.CursorType = adOpenStatic
RS.Open "Select * from Authors", Con
Response.Write "The recordset contains " & RS.RecordCount & " Records"
Accessing Fields Collection in a Recordset
• The fields are indexed from 0 to RS.Fields.Count-1
• Fields is the default collection of a recordset and thus the
syntax RS(I) and RS.Fields(I) are equivalent where I can
be an index value or a field name
• To access the value of the first field, named phone, use
RS(0) or RS.Fields(0) or RS("phone") or
RS.Fields("Phone"), or RS(0).Value, ..
• To access the name of the field, use RS(0).Name
Scrolling through a Recordset
• Move
moves forward or backward a number of records relative
to the current record or a bookmark
• MoveFirst (MoveLast)
moves to the first (last) record
• MoveNext (MovePrevious)
moves to the next (previous) record
Use BOF (EOF) to detect the beginning (end) of a recordset
Adding/Editing Records in a Recordset
To add a record open an adLockOptimistic Recordset and
use AddNew/Update methods.
RS("Name") = "Ali"
RS("Phone") = "8601234"
Building Parameterized Queries
• Parameters are typically used in the where clause in a query.
For example,
"Select * from tCar where color = ' " & colorval & " ' “
• A parameter value can be passed through form data or query string. For
colorval = Request.QueryString("color"), or
colorval = Request.Form("color")
If there is no ambiguity, colorval = Request("color")
Using Stored Procedures in MS SQL Server
A Stored Procedure is a compiled collection of SQL
statements stored as a single named object within a SQL
Server database. A stored procedure can contain normal
SQL statements (select, insert, update, delete) and
Transact-SQL programming statements
migrate complex ASP scripts to a database server
Creating A Stored Procedure
A stored procedure is created b executing a SQL Create
Procedure statement. Such a statement can be created and
executed through
– ASP Script
– Using MS Enterprise Manager
– Using MS Query Analyzer
Creating and Executing A Stored Procedure through ASP
Con.Execute "Create Procedure getAuthors as Select * from
Set RS = Con.Execute("getAuthors")
Using ADO Command Object
Con.Execute "Create Procedure getAuthors as Select * from Authors"
Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.CommandType = adCmdStroedProc
Cmd.CommandText = "getAuthors"
Set RS = Cmd.Execute()
Example 1: Car Dealer
Car Dealer Example
Uses an Access database (dbform.mdb). This contains
the following table (tCar)
Displaying Table Records (allcars.asp)
<% do while not rs.eof %>
<a href=carinfo.asp?recid=<% =rs("RecID") %> >
<%=rs("Maker") & " " & rs("ModelType") & " " &
rs("ModelYear")%> </a>
<td> <%=rs("price")%>
<td> <%=rs("color")%>
<% rs.MoveNext
Loop %>
Displaying more info. about a car (carinfo.asp)
<% sql="select * from tCar where RecID=" & Request("RecID")
Set rs = Conn.Execute(sql) %>
<tr><td class=hcell> Price <td class=ncell> <%=rs("price")%>
<tr><td class=hcell> Color <td class=ncell> <%=rs("color")%>
<td><img width=240 height=160
src="images/<%=rs("imgfname")%> " >
Example 2: Using Select criteria and Insert
One ASP file (dbform.asp) producing two screens
<% If Request.Form("hname") = "" Then ' show form %>
<form …>
<input TYPE=HIDDEN NAME="hname" VALUE="hvalue" >
… </form>
<% else ' process form’s data and produce html reply %>
<p>Thank you <%= Request.Form("title") %>&nbsp;
<%=Request.Form("name") %>
<% end if %>
Sample Application
Discussion Forum
The Discussion Forum Application (User Interface)
Primary ASP Files
Additional Files
• post.asp : form for posting a new message
• reply.asp : form for posting a reply to a message
• ForumFuncs.asp : common functions used by other asp
files; referenced through include directive
• InstallForum.sql : sql script for creating the database
objects (i.e. message table and stored procedures) under
MS SQL Server
Recreating the Discussion Forum Site
• Copy all application files to a directory that is web shared
• Use the Enterprise Manger of MS SQL Server to create a database
• Use the Query Tool to execute the script from InstallForum.sql
against the database created in the previous step
• Use the Enterprise Manger of MS SQL Server to create a User
Account (use SQL own security rather than Trusted Security) and
give him proper access right
• In the forumfuncs.asp file, edit dbCon string say using the following
dbCon = "Provider=SQLOLEDB; Data Source=localhost;
The Messages Table
CREATE TABLE dbo.messages (
m_id int IDENTITY (1, 1) NOT NULL ,
m_forumName varchar (30) NOT NULL ,
m_subject varchar (30) NOT NULL ,
m_username varchar (30) NOT NULL ,
m_email varchar (70) NOT NULL ,
m_entrydate datetime NOT NULL DEFAULT getdate(),
m_message text NULL ,
m_ordernum int NULL ,
m_reply bit NOT NULL )
Notes about fields in the message table
• The m_id is an IDENTITY (Like AutoNumber in Access)
automatically generated for each inserted record
• The m_reply distinguishes between a message or a reply
( 1 for a reply, 0 otherwise)
• The m_ordernum is used for ordering messages and
replies. A message and all of its replies have the same
value (ordering messages by m_ordernum will show the
message followed immediately by its replies).
The PostMessage Procedure
create procedure postMessage (@forumName varchar(30),
@subject varchar(30), @username varchar(30), @email varchar(70),
@newMessageID int OUTPUT) as
declare @maxOrderNum int
insert messages (m_forumName, m_subject, m_username, m_email,
m_reply) values (@forumName, @subject, @username, @email, 0)
select @newMessageID = @@IDENTITY
select @maxOrderNum = max( m_ordernum ) + 1
from messages where m_forumName = @forumName
update messages set m_ordernum = @maxOrderNum
where m_id = @newMessageID
The PostReply Procedure
create procedure postReply (@forumName varchar(30),
@subject varchar(30), @username varchar(30), @email varchar(70),
@newMessageID int OUTPUT, @reply integer) as
insert messages (m_forumName, m_subject, m_username, m_email,
m_reply) values (@forumName, @subject, @username, @email, 1)
select @newMessageID = @@IDENTITY
select @reply = m_ordernum from messages where m_id = @reply
update messages set m_ordernum = @reply
where m_id [email protected]
The getForums procedure
Used in forumlist.asp to show a list of forums
select m_forumname, count( m_id ) theCount
from messages
group by m_forumname
order by m_forumname
Synchronizing Frames
When the user click on a line in the messages list both frames
messagelist and message are updated.
<a href="messages.asp?f=ADO&m=21" target="messages">Setting
Cursor Types</a>
<!-- the frameset messages.asp -->
frmID = Request( "f" )
msgID = Request( "m" )
<frameset rows="*,*" >
<frame src="messagelist.asp?f=frmID&m=<%=msgID%>" >
<frame src="message.asp?m=<%=msgID%>" >