Database Applications
Customer Management
Networks
CUI client
File Edit
Name: John Doe Address: 101 Dalmation Street Salary: $43,000 Age: 34 PF1 - Help PF2 - Add PF3 Enter details and press PF3 to update Update
Database Server
Customer Management Window Help John Doe 101 Dalmation St. $48,000 34 Update Cancel
LAN / WAN
GUI client
Name: Address: Salary: Age: Loan History
Disks & Files
Application Server
Browser client
Interne / Intrane t
Web Server
Sonata Software Limited
[Link] and [Link] - 1
DataReader: Direct Access to Database
Select Stored procedure
Client Application
Read
DataReader
Command
Windows Form
Connection
Write
Command
Insert Update Delete Stored procedure
Web Form
Database
Sonata Software Limited
[Link] and [Link] - 2
Dim dr As OleDbDataReader dr = [Link]()
Initial position of cursor
Cursor of a "Record Set"
Column index 0 Num 100 1 Name Java 2 Price 200.00 Column name
Current cursor position [Link]() moves the cursor to the next row and return True if the next row exists.
200 300 400
HTML 250.00 C XML 300.00 240.00
If current cursor position is here, then the execution of [Link]() returns False.
Record set
Sonata Software Limited
[Link] and [Link] - 3
Accessing Column Values
By Zero-based Column
Type Safe or Ordinal Reference
By Column Name String Reference dr("Num") dr("Name") dr("Price")
Returned Value 200 HTML 250.00
dr.getInt32(0) or dr(0) [Link](1) or dr(1) [Link](2) or dr(2)
Accessing the value of a column from the current record in the record set, e.g., dr, you can use either dr(i) where i is an ordinal reference (0, 1, 2, ...) or dr("ColumnName"). It will return an object and when it is used for output, it is implicitly converted to string data type. If you want to use Type Safe Reference, you can use [Link](i) where i is an ordinal reference (0, 1, 2, ...) and XXX is the appropriate data type of the column such as [Link](2). Type Safe Reference is more convenient when you want to format the retrieved data or to use it in a calculation. You cannot use column names with Type Safe Reference.
[Link]("Price") is illegal!
Sonata Software Limited
[Link] and [Link] - 4
Categories Table in NorthWind Access Database
Sonata Software Limited
[Link] and [Link] - 5
[Link]
Sonata Software Limited
[Link] and [Link] - 6
Using DataReader
Dynamically generated web page 2 SQL statement
Format result
1 5
set rows in HTML format
[Link] Code
DataReader
Record set
100
NorthWind
Category table
100
Retrieve result set,
one row at a time.
4 3 [Link] and [Link] - 7
Sonata Software Limited
[Link]
<%@ Page Language="VB" %> <%@ Import Namespace="[Link]" %> <%@ Import Namespace="[Link]" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link] <script runat="server"> Protected Sub Page_Load (ByVal sender As Object, ByVal e As [Link]) Dim conn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader conn = New _ OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]")
Sonata Software Limited
[Link] and [Link] - 8
[Link] - continued
If Not IsPostBack Then Try [Link]() cmd = New OleDbCommand("select * from categories", conn)
[Link] = [Link]
' require Namespace="[Link]"
dr = [Link]() While [Link]() [Link] &= dr.GetInt32(0) & "--" _ & [Link](1) & "--" _ & [Link](2) & "<br>" End While Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally [Link]() [Link]() End Try End If End Sub </script> [Link] and [Link] Sonata Software Limited
[Link] - continued
<html xmlns="[Link] > <head runat="server"> <title>[Link] Example - 1</title> </head> <body> <H1>List of categories</H1> <form id="form1" runat="server"> <div> <asp:Label id="Label1" runat="server"></asp:Label> </div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 10
Commonly Used DataReader Properties and Methods
Sonata Software Limited
[Link] and [Link] - 11
Using Code-Behind
Sonata Software Limited
[Link] and [Link] - 12
[Link]
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="[Link]" Inherits="CategoryList2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link] <html xmlns="[Link] > <head runat="server"> <title>Untitled Page</title> </head> <body> <H1>List of categories</H1> <form id="form1" runat="server"> <div> <asp:Label id="Label1" runat="server"></asp:Label> </div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 13
[Link]
Imports [Link] Imports [Link] Partial Class CategoryList2 Inherits [Link]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As [Link]) Handles [Link]
Dim conn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader conn = New _ OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]")) If Not IsPostBack Then Try [Link]() cmd = New OleDbCommand("select * from categories", conn) [Link] = [Link] ' require Namespace="[Link]" dr = [Link]() While [Link]() [Link] &= dr.GetInt32(0) & "--" _ & [Link](1) & "--" _ & [Link](2) & "<br>" End While Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally [Link]() End Try End If
End Sub End Class [Link] and [Link] - 14
Sonata Software Limited
[Link]
Sonata Software Limited
[Link] and [Link] - 15
Using DataReader
<form action="[Link]" method="post"> (from a static HTML page)
<a href="[Link]?cid=7&name=Produce"> Product</a>
Invocation of [Link] programs
1 Dynamically generated web page 5
Format result
2 SQL statement
set rows in HTML format
[Link] Code
DataReader
Record set
100
NorthWind
Category table
100
Retrieve result set,
one row at a time.
4 3 [Link] and [Link] - 16
Sonata Software Limited
Generated HTML Source Code
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Category Listing</TITLE></HEAD><BODY> <H1>Category List</H1>
<table border=4> <tr> <th>ID</th> <th>ID</th> </tr> <tr> <td>1</td> <td>Beverages</td> </tr> <tr> <td>2</td> <td>Condiments</td> </tr> <tr> <td>3</td> <td>Confections</td> </tr> . </table>
Sonata Software Limited
</BODY> </HTML>
[Link] and [Link] - 17
[Link]
<%@ Page Language="VB" %> <%@ Import Namespace="[Link]" %> <%@ Import Namespace="[Link]" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link]
<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As [Link])
Dim table As [Link] table = New [Link]("")
If Not IsPostBack Then
Dim conn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader
conn = New OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]"))
Try [Link]() cmd = New OleDbCommand("select * from categories", conn) [Link] = [Link] dr = [Link]()
[Link]("<table><table border='1'><tr>") [Link]("<th>ID</th><th>Name</th></tr>") While [Link]() [Link]("<tr><td> " & dr.GetInt32(0)) [Link]("</td><td> " & [Link](1) & "</td></tr>") End While
Sonata Software Limited
[Link] and [Link] - 18
[Link] - Continued
Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally
[Link]() [Link]("</table>") [Link] = [Link]()
End Try End If End Sub </script> <html xmlns="[Link] > <head runat="server"> <title>Category List</title> </head> <body> <H1>Category List</H1> <form id="form1" runat="server"> <div>
<asp:Label ID="Label1" runat="server"></asp:Label>
</div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 19
The Data Model of the NorthWind Database
Sonata Software Limited
[Link] and [Link] - 20
Drill Down Data Navigation
[Link] [Link] and [Link] - 21 Sonata Software Limited
[Link]
[Link]
<%@ Page Language="VB" %> <%@ Import Namespace="[Link]" %> <%@ Import Namespace="[Link]" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link]
<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As [Link]) Dim nwindConn As OleDbConnection Dim catCMD As OleDbCommand Dim myReader As OleDbDataReader nwindConn = _ New OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]")) Try catCMD = [Link]() [Link] = "SELECT CategoryID, CategoryName FROM Categories" [Link]() myReader = [Link]() [Link] &= "<table border=4><tr><th>ID</th><th>Name</th></tr>" Do While [Link]() [Link] &= "<tr><td>" & myReader("CategoryID") & _ "</td><td> <a href='[Link]?cid=" & _ myReader("CategoryID") & "&cname=" & _ [Link](myReader("CategoryName")) & "'>" & _ myReader("CategoryName") & "</a> </td></tr>" Loop [Link] and [Link] - 22 Sonata Software Limited
[Link] - Continued
Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally [Link]() nwindConn = Nothing [Link] &= "</table>" End Try End Sub </script> <html xmlns="[Link] > <head runat="server"> <title>Category List</title> </head> <body> <H1>Category List</H1> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server"></asp:Label> </div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 23
[Link]
<%@ Page Language="VB" %> <%@ Import Namespace="[Link]" %> <%@ Import Namespace="[Link]" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link]
<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As [Link]) Dim conn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader
[Link] = Products from & [Link]("cname")
conn = _ New OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]"))
Try cmd = [Link]()
[Link] = "SELECT ProductID, ProductName, UnitPrice FROM Products" & _
" Where CategoryID=" & [Link]("cid") [Link]() dr = [Link]() [Link] &= "<table border=4><tr><th>ID</th><th>Name</th><th>Price</th></tr>" Do While [Link]() [Link] &= "<tr><td>" & dr("ProductID") & _ "</td><td> <a href='[Link]?pid=" & _
dr("ProductID") & "'>" & _
dr("ProductName") & "</a> </td><td align='right'>" & _ Format(dr("UnitPrice"), "$#,##0.00") & "</td></tr>"
Loop
[Link] and [Link] - 24
Sonata Software Limited
[Link] - Continued
Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally [Link]() conn = Nothing [Link] &= "</table>" End Try End Sub </script> <html xmlns="[Link] > <head runat="server"> <title>Category List</title> </head> <body> <H1>Products from
<asp:Label ID="LabelTitle" runat="server" Text="Label"></asp:Label>
</H1> <form id="form1" runat="server"> <div>
<asp:Label ID="Label1" runat="server"></asp:Label>
</div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 25
Generating HTML Dynamically
<tr><td> 1 </td><td> Beverages </td></tr>
Separate static text from dynamic ones
"<tr><td>" &
& "</td><td>" &
Beverages
& "</td></tr>"
Replace dynamic data with actual DataReader methods
"<tr><td>" & dr(0) & "</td><td>" &
dr(1)
& "</td></tr>"
Sonata Software Limited
[Link] and [Link] - 26
<tr><td> 1 </td><td> <a href='[Link]?cid=1&cname= Beverages'>Beverages</a></td></tr> "<tr><td>" & 1 & "</td><td><a href='[Link]?cid=" & 1 & "&cname=" & Beverages & "'>" & Beverages & "</a></td></tr>" "<tr><td>" & dr(0) & "</td><td><a href='[Link]?cid=" & dr(0) & "&cname=" & dr(1) & "'>" & dr(1) & "</a></td></tr>"
[Link] &= "<tr><td>" & myReader("CategoryID") & _ "</td><td> <a href='[Link]?cid=" & _ myReader("CategoryID") & "&cname=" & _ [Link](myReader("CategoryName")) & "'>" & _ myReader("CategoryName") & "</a> </td></tr>"
Sonata Software Limited
[Link] and [Link] - 27
Design View
Sonata Software Limited
[Link] and [Link] - 28
[Link]
<%@ Page Language="VB" %> <%@ Import Namespace="[Link]" %> <%@ Import Namespace="[Link]" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[Link] <script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As [Link]) Dim nwindConn As OleDbConnection = _ New OleDbConnection("PROVIDER=[Link].4.0;" & _ "Data Source=" & [Link]("[Link]")) Dim prodCMD As OleDbCommand = [Link]()
Sonata Software Limited
[Link] and [Link] - 29
SQL Statement - Join
SELECT ProductID, ProductName, UnitPrice, [Link], CategoryName, Discontinued FROM Products, Categories Where ProductID=11 AND [Link] = [Link]
Sonata Software Limited
[Link] and [Link] - 30
[Link] = _
"SELECT ProductID, ProductName, UnitPrice, [Link] as x, " & _ " CategoryName, Discontinued FROM Products, Categories" & _
" Where ProductID=" & [Link]("pid") & _ " AND [Link] = [Link] "
Dim dr As OleDbDataReader Try
[Link]() dr = [Link]() If [Link]() Then [Link] = [Link]("pid")
[Link] = dr("ProductName") [Link] = dr("CategoryID") [Link] = dr("CategoryName") [Link] = dr("UnitPrice")
continued
[Link] = dr("Discontinued") [Link] = _ "[Link]?cid=" _ & dr("CategoryID") & cname= & dr(CategoryName)
Else [Link] = "No product found!" End If Catch ex As Exception [Link] = "Database error!" & "<br>" & [Link] Finally [Link]() End Try End Sub [Link] and [Link] - 31 Sonata Software Limited </script>
Continued
<html xmlns="[Link] > <head runat="server"> <title>Product Detail</title> </head> <body> <form id="form1" runat="server"> <div> <P><STRONG><FONT size="4">Product Detailed Information<BR> </FONT></STRONG>
<asp:label id="LabelErrorMsg" runat="server"></asp:label><BR> Product ID: <asp:label id="LabelProductID" runat="server"></asp:label><BR> Product Name: <asp:label id="LabelProductName" runat="server"></asp:label><BR> Category ID: <asp:label id="LabelCategoryID" runat="server"></asp:label><BR> Category Name: <asp:label id="LabelCategoryName" runat="server"></asp:label><BR> Unit Price: <asp:label id="LabelUnitPrice" runat="server"></asp:label><BR> <asp:checkbox id="CheckBoxDiscontinued" runat="server" Text="Discontinued"></asp:checkbox></P><P>
<asp:hyperlink id="HyperLinkProductList" runat="server"> Back to product list of the same Category</asp:hyperlink><BR>
<a href="[Link]">Show all product category. </A>
</div> </form> </body> </html>
Sonata Software Limited
[Link] and [Link] - 32
Sonata Software Limited
[Link] and [Link] - 33
Domain Name: [Link] Technical Contact Username: aspne3mz Technical Contact Password: xxxxxxx URL used to access your site: [Link]
Sonata Software Limited
[Link] and [Link] - 34
Highlight Source (Local) Web Site files Click the right pointing arrow to upload
URL used to access your page at: [Link]
Sonata Software Limited
[Link] and [Link] - 35