Web Application Development

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
Outline
•
•
•
•
•
•
•
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
Protocol)
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
Navigator)
•
•
•
•
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
HTTP
TCP
IP
HTTP
TCP
IP
Network Hardware
Forms
• To get input from user - i.e. feedback, purchase order
• A form can have any of : text boxes, list boxes, command
buttons
• Input data is passed to a program specified in the Action
parameter
• 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" >

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)
<FORM METHOD=POST ACTION="cgiform.exe">
<INPUT TYPE=TEXT SIZE=30 MAXLENGTH=10 NAME="name"> Text Box
<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.
</select>
</FORM>
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
OR
• An HTML page whose content is generated at the time it is
accessed.
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
databases
• 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
language
• Include programming objects (ActiveX) to support form
processing, state maintenance and database access (ADO :
Active Data Objects).
Sample ASP file (hello.asp)
<HTML>
<BODY>
<% For i = 3 To 7 %>
<FONT SIZE=<% = i %>>
Hello World!<BR>
<% Next %>
</BODY>
</HTML>
Generic Model for Web-Database Access
Web Browser
Web Server
http protocol
Interface Layer
DB Engine
Database
Server Station
User Station
Microsoft's Solution
IIS using ASP
Data Provider
Data Source
OLE DB
ODBC
DB Engine
Database
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;
UID=sa;PWD=secret;DATABASE=Pubs"
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;
UID=sa;PWD=secret;DATABASE=Pubs"
OLEDB for ODBC:
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.BeginTrans
Con.Execute "Insert tUserX Select * from tUser"
Con.Execute "Delete * from tUser"
Con.CommitTrans
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
<table>
<tr><td>Fld1<td>Fld2<td>Fld3
<% do while not rs.eof %>
<tr><td><%=rs("Fld1")%><td><%=rs("Fld2")%><td><%=rs("Fld3")%>
<% rs.movenext
loop %>
</table>
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,
adLockBatchOptimistic
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
Example:

<% 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.AddNew
RS("Name") = "Ali"
RS("Phone") = "8601234"
RS.Update
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
example,
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
Advantage:
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
Example:
Con.Execute "Create Procedure getAuthors as Select * from
Authors"
Set RS = Con.Execute("getAuthors")
Using ADO Command Object
Example:
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 %>
<tr><td>
<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") %> 
<%=Request.Form("name") %>
…
<% end if %>
Sample Application
Discussion Forum
The Discussion Forum Application (User Interface)
Primary ASP Files
forum.asp
(frameset)
topbar.asp
messagelist.asp
content.asp
(frameset)
message.asp
messages.asp
(frameset)
forumlist.asp
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;
UID=test;PWD=test;Database=forum"
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 =@newMessageID
The getForums procedure
Used in forumlist.asp to show a list of forums
CREATE PROCEDURE getForums as
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>

frmID = Request( "f" )
msgID = Request( "m" )
<frameset rows="*,*" >
<frame src="messagelist.asp?f=frmID&m=<%=msgID%>" >
<frame src="message.asp?m=<%=msgID%>" >
</frameset>

Web Application Development

Transcript Web Application Development

Directory