Transcript Introducing Web Applications

CIS 407A
Web Application Development
Assistant Professor Kyvernitis
Module 3: ADO .NET (viewing data from DB)
1
Outline




Web Application Architecture: How do we create a
stateful database applications on top of stateless
HTTP?
How Visual Studio makes it easy for programmers –
reduces the complexity introduced by html and http.
Programmers can pretend they are writing a client
server application.
Review DB terminology, review SQL select
statements
ADO .NET Data Providers, ADO classes
2
Outline (Continued)








Read data from a database, display in label of webform.
Use Parameterized SQL select statement (prevent SQL
injection attack).
Use Like in SQL select statement (gives nice functionality).
Move data read code to a Class.
Use String Builder instead of string – more efficient for
appending.
Build a html table to display data (not just text with
newlines).
Format date and currency data for display.
Create reusable methods to prevent null value exceptions
3
Business Applications:




Read from a database
Summarize and present information extracted
from a database
Write (inserts, updates) to a database.
Access restricted based on secure logon and
user role.
4
What is challenging
about trying to put
business applications
on the web?
5
Web Architecture

HTTP is the protocol used by web browsers and web servers.
It is a “stateless” (“connectionless”) protocol in which:





the client (browser) asks for a page,
that request is directed to the right server (by domain name), and
that server gives the requested (HTML) page back to the client.
HTML is pretty limited (layout, pictures, links, javascript that
can give a message box & move things around on the page,
user input can be posted back to a web server).
The client PC/browser cannot communicate directly with any
database. Why???
6
Why Client PCs Don’t Talk to Databases

HTTP (the protocol that governs the communication between
client PC and web server) is connectionless- it gets a request
from anyone, responds to that request and that’s it !


However, methods have been devised that enable web servers to keep
session information about it’s users – remember login information per
user, what they’ve done, and what they are trying to do.
HTML (the language of web pages) has no commands for
accessing a database.


Those commands don’t exist because the purpose of HTML was to
display information and links, not run database applications.
No one has added this functionality to HTML because companies
don’t want their database server to be visible and accessible to all the
PCs (and hackers) on the internet
7
So, how did they put
business applications
on the internet??
8
Web Application System Architecture
Client PC:
Browser runs HTML code
created by ASP .NET
HTML
Values of posted
Form elements
Application Server:
Presentation Layer
(ASP .NET Web Forms):
Get user input, display results.
SQL
Database Server:
DBMS (database mgt
system) is only way to
access data, enforces
integrity rules (e.g., PK
FK constraints).
Business Objects
Business Objects
(ASP .NET Classes):
Validate input, calculate results.
Prepare/invoke SQL code.
Results
(e.g., data)
Reads,
Udpates
database
9
Web Application System Architecture



The rightmost server is the database server (e.g., oracle or sql
server database stored on it, dbms installed on it). This is the
only place where database queries (select statements) and
updates are run. The sql that is run may be saved in stored
procedures (in the database) or it may be dynamically created
by the web application (and presented to the dbms to be run).
The server in the lower left represents 1 or more application
servers that typically all access the same database server.
Your application logic (Business logic and presentation logic)
runs here. We always try to separate business logic (what it
is, how we can manipulate it) from presentation logic (how
we want it to look).
The database server and the application servers are located
“inside the firewall” of a company.
10
Web Application System Architecture


Between the company’s application servers
and the client’s PCs running browsers, there is
the internet.
The application server can only hand the
client PCs simple HTML possibly with some
javascript in it.
11
.NET converts our webforms into plain
HTML for us !

So, our middle tier code (business logic):



accesses the database “in between” the generation of html
pages (remember that a button click event causes the page
to be regenerated, just like a new page request causes a
page to be generated)
The database information can be incorporated into the
generated page, e.g., a list of products pulled from the
database, in response to user entered data.
In addition to this, .NET:

stores the state of the client (what they entered previously)
in server side session variables.
12
Web Applications

If something changes on the client’s page
(e.g., after a user clicked on button), this
change was either caused by:


javascript executing (e.g., an alert messagebox or
javascript is writing to the document and
changing the way the document looks).
or it is a new page (that looks a lot like the old
page).  This is what is happening with the
basic .NET code we are writing…
13
Web Controls



Examples: buttons, textboxes, radio buttons.
In design mode (to programmers) they look/act like
windows application forms – except you avoid using
events other than button click & perhaps dropdown
list selections.
Also called ASP .NET server controls because they
actually reside on the server (even tho they look like
the reside in the client’s browser). And they only
run on the server “in between” pages.
14
Database Terms (review)



Database (most of today’s databases are relational
databases): Microsoft SQL Server, Oracle, Sybase,
DB2, Informix, MySQL, Microsoft Access.
DBMS – software that provides the only interface to
read from or write to the database. This SW can
enforce integrity rules, database constraints.
SQL (Structured Query Language)- the language we
use (either thru SQL GUI interface or via
programmatic control) to communicate with the
DBMS – telling it what we want to read or write.
15
More Database Terms (review)





Table – data that represents a set of objects, such as a group
of employees, payments, departments.
Row = Record, e.g. a specific employee or payment or
department (stored in a table).
Column = Field, e.g., employee’s name. Columns define a
table’s layout.
Primary key – a field or fields that uniquely identify a row in
a table, e.g., SS number, Department Code.
Foreign key – a field or fields that show a relationship from
one record to another. For example, Employee.Dept_Code is
a foreign key in the Employee table. It shows the Department
where a particular Employee works.
16
Basic SQL Select Statements





Select * from Titles;
Select Title, ISBN from Titles;
Select * from Titles where ISBN = ‘12345’;
Select * from Titles where Title like ‘Cat%’ order by
Title ASC;
Select * from Titles, AuthorISBN, Author where
Title.ISBN = AuthorISBN.ISBN and
AuthorISBN.authorID = Authors.AuthorID and
Author.lastName = ‘Poe’; (this called “inner join”)
17
ADO .NET
ADO.NET stands for ActiveX Data Objects. It consists of two
primary parts:
 Data provider -- classes that provide access to a data source.
Each type of database (e.g., sql server, oracle) has its own set of
providers, but they all provide similar functionality including:
 Connection object: connection to the database.
 Command object: such as select, insert, update, delete.
 DataReader object: holds result sets from select statements.
 DataAdapter object: A bridge used to transfer data between
a data source and a DataSet object (see below).
 DataSet – classes that represent a simple in-memory relational
database (e.g., tables, relationships, constraints).
18
ADO .NET Data Providers
.NET
Your .NET
Application Code
SQL Server
.NET
provider
SQL Server
database
Oracle
.NET
provider
Oracle
database
Ole DB
.NET
provider
ODBC
.NET
provider
Ole DB
provider
ODBC
driver
Data
Source
19
ADO .NET Data Providers

A data provider is a set of ADO .NET classes that let you
access (read from, write to) a specific database. ADO .NET
version 1.1 comes with these providers:
 ODBC (Open DataBase Connectivity): accesses any data
source using db specific ODBC driver.
 OLE DB (Object Linking & Embedding-DataBase):
accesses any data source w/OLE DB driver, includes SQL
Server earlier than v 7.0. This access method is a little
more efficient than ODBC.
 SQL Server: accesses SQL Svr DBs v 7.0 or later. This is
the most efficient for SQL server databases.
 Oracle: accesses Oracle DB (v 8i or later). This is the most
efficient for oracle databases.
20
Selecting a Data Provider



1st choice is a data provider that is optimized
for your database. So, if SQL server 7.0 or
later, use SQL server. If Oracle 8i or later,
use Oracle.
Else, you’ll have to use OLE DB or ODBC.
For simplicity, we’ll use OLE DB with an
MSAccess database in this class.
21
ADO .NET
Ole Db Data Provider Classes




OleDbConnection – manages a connection to a
datasource (a particular database).
OleDbCommand – a specific SQL command to be
executed.
OleDbDataReader – provides fast, read-only,
forward-only access to query.
OleDbDataAdapter – connects to a datasource,
runs an SQL command, and populates a DataSet
(which can be modified then updated back to DB).
22
Add Using Statement to WebForm



Add this at the top of WebForm1.aspx.cs
using System.Data.OleDb;
The “using” statement is like the java import
statement. It enables you to have code like this:
OleDbDataReader reader;
Instead of having to use code like this:
System.Data.OleDb.OleDbDataReader reader;

Type in the code from the next slide into the button
click event – or you can copy this code from the
zipped project you just downloaded/extracted.
23
Code to Read From Database
(without using Classes)
private void btnGetData_Click(object sender, System.EventArgs e) {
this.lblDisplayData.Text = ""; // clear value if there is any
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a\\payroll.mdb";
OleDbConnection con = new OleDbConnection(strCon);
OleDbDataReader reader;
try {
string sql = "SELECT FirstName, LastName FROM emp";
OleDbCommand cmd = new OleDbCommand(sql,con);
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read()) {
this.lblDisplayData.Text += reader.GetValue(0).ToString() + " ";
this.lblDisplayData.Text += reader.GetValue(1).ToString() + "<br>";
}
reader.Close();
24
con.Close();
} // continued on next slide
Code to Read From Database
(without using Classes -- continued)
// .. Continued from previous slide
catch (Exception ex) {
this.lblDisplayData.Text=ex.Message;
con.Close();
}
}
25
Using ADO
String to
ID the DB
Connection
Connection.Open()
String with valid
SQL statement
inside
Command
Command.ExecuteReader()
DataReader
(holds data
selected from DB)
26
The GetValue input parameter is based on the order
of cols in the SQL select statement (not the order of
cols as designed in the DB table).
// …
string sql = "SELECT FirstName, LastName FROM emp";
//…
while (reader.Read()) {
this.lblDisplayData.Text += reader.GetValue(0).ToString() + " ";
this.lblDisplayData.Text += reader.GetValue(1).ToString() + “<br>";
}

GetValue(0) gets the first column as specified in the SQL statement.

GetValue(1) gets the next column as specified in the SQL statement.

GetValue(2) would get the next column after that, and so on.

The GetValue parameter has nothing to do with the order of the
columns when looking at the table in design mode in MSAccess.

Use GetValue instead of GetString or GetInt so we don’t abort on say a
27
null value in the database.
How to Deal with Runtime errors
caused by bad SQL




If there is a syntax error in your SQL statement, your
program will abort at runtime. Unfortunately, you will not
get a compiler error.
All database access should be put in a try block. Of course,
the try block should close the connection when the
connection is no longer needed. The catch block should also
close the connection – or else you’ll get a “file in use” error
the next time you try to run after you’ve encountered an
error.
At any rate, if you ever run into a message like “file already
in use” – you may have to close Visual Studio, reopen and
retry. If this doesn’t fix it, you can try rebooting.
Make sure that you ALWAYS close every connection you
open (for every code path, including error code paths).
28
Handling Errors

There are two common errors:



We can easily simulate both errors:



The database is not available.
There is a error in the SQL (maybe syntax or maybe a table or field
misspelled).
Misspell database name (acts like db not available).
Misspell a SQL keyword (SQL syntax error).
Make sure your program is resilient to both potential
problems. You want your code to close any open connection
to the database (even if there IS an error) or you’ll have
trouble running/testing your program next time. Do this by
closing the connection in the try code as well as in the catch
code. Otherwise, you’ll have to restart VS and/or reboot
everytime you test and this is a frustrating way to code.
29
Invasive SQL Injection Attacks



Suppose you have a webform where the user enters in a
customer name into a textbox and then presses a button to see
that customer’s record.
If the program incorporates the user’s data directly into the sql
select statement (without using a parameterized sql statement),
we open ourselves up to a SQL injection attack.
Here is an example of a sql statement that is open to SQL
injection attack:
string sql = “select * from Cust where name = ‘” + txtName.Text + “’”;

So, if the user enters “Smith” into the textbox, then the
expression above evaluates to this:
select * from Cust where name = ‘Smith’
30
Invasive SQL Injection Attacks …
string sql = “select * from Cust where name = ‘” + txtName.Text + “’”;

A malicious (technically savvy) user could enter this into the
textbox:
Smith’ or ‘1’=‘1

Then the expression evaluates to:
select * from Cust where name = ‘Smith’ or ‘1’=‘1’
 Then the user would see all of the (possibly sensitive) data in
the customer file.
31
Destructive Sql Injection Attacks


Or (assuming same web form) the user enters this
into the textbox:
Smith’; delete * from Cust - The expression then evaluates to this (much more
malicious than the previous example):
select * from Cust where name = ‘Smith’;
delete * from Cust-- ’
; ends the first sql statement and lets you start another
statement.
32
- - comments out the rest of the statement
Parameterized SQL Commands




Parameterized SQL commands prevent SQL injection
attacks.
Parameterized SQL commands automatically create
stored procedures within the database (assuming the db
provides stored procedures – MSAccess does not).
When the DBMS (Database Mgt System, e.g. oracle or
Sql) sees a new parameterized SQL command, it
compiles it, uses it, and saves it for the next time.
Stored also procedures run much more quickly than
plain text sql statements because they are pre-compiled
(by the DBMS).
33
Example of Parameterized SQL Command
(not open to Sql Injection Attack)
string sql = "SELECT FirstName, LastName FROM emp "
+ "where LastName = @lname";
OleDbCommand cmd = new OleDbCommand(sql,con);
cmd.Parameters.Add("@lname","Willis");
con.Open();
System.Data.OleDb.OleDbDataReader reader;
reader = cmd.ExecuteReader();
…
// In this example, you’ll see all the first and last name of all emp records
where the last name = ‘Willis’
34
Tip on Using Parameterized SQL

Always replace parameters in the same order
they occur in the SQL statement. For
example, call the Add method for @lname
THEN call the Add method for @fname.
string sql = "SELECT FirstName, LastName FROM emp "
+ "where LastName = @lname or FirstName = @fname";
OleDbCommand cmd = new OleDbCommand(sql,con);
cmd.Parameters.Add("@lname","Willis");
cmd.Parameters.Add("@lname",“Bruce");
35
Using LIKE in SQL Select Statement
string sql = "SELECT FirstName, LastName FROM emp "
+ "where LastName like @lname";
OleDbCommand cmd = new OleDbCommand(sql,con);
cmd.Parameters.Add("@lname","Will%");
con.Open();
System.Data.OleDb.OleDbDataReader reader;
reader = cmd.ExecuteReader();
…
% is a wild card character
This gets all the records where last name Starts With ‘Will’
36
Create Database Connection
Wrapper Class



You can design flexible data access software by “putting a
wrapper around” the data provider classes (e.g., Connection,
Command, Reader).
For example, when you first start designing a web
application, you use a simple Access database (cheaper,
easier to obtain) and plan to switch to a more robust database
later.
So, rather that directly using OleDbConnection, etc, you can
create your own Connection class that is just a “wrapper”
around OleDbConnection. When you upgrade to different
DB, just modify that Connection class (e.g., to be a SQL
server Connection class).
37
A Closer Look at our Connection Wrapper
Class DbConn – its data members
using System.Data.OleDb;
public class DbConn {
// the actual connection
private OleDbConnection conn;
// error message if there was a problem
private string errMsg;
38
DbConn class (Constructor)
// Constructor creates then opens a db connection.
public DbConn()
{
try {
string myConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data "+
"Source=C:\\a\\payroll.mdb";
conn = new OleDbConnection(myConnectionString);
conn.Open();
}
catch (Exception ex) {
this.errMsg = "Could not access database. Error: " +
ex.Message + "\n";
}
}
39
Create DbConn class (other methods)
public OleDbConnection getConn(){
return conn;
}
public void closeConn(){
conn.Close();
}
40
Create DbConn class (other methods)
public bool isConnErr(){
string state = conn.State.ToString();
if (state.Equals("Open"))
return false;
else {
this.errMsg=“DB connection is not open.”;
return true;
}
}
public string getErrMsg(){
return this.errMsg;
}
41
DB Connection Management



Waiting to get a db connection can be slow
(one computer requests of another, over a
network).
I timed it once (oracle) at about 2-3 seconds
per connection.
If one page request (either new page or button
click on same page) asks for 5 database
connections, this can add 10-15 seconds onto
the time that a user has to wait for the page.
42
DB Connection Management:
Just get one per page – pass it around.



One simple but effective way of managing database
connections is to get one in the page (e.g., button click event,
or page load event) and pass that single one around to
whoever needs it. Then, make sure to close it (every code
path).
Don’t get more than one database connection per page since
this is an unnecessary waste. If a particular page is heavily
hit, you’ll require twice or three times as many database
connections.
The general limit of database connections (that I’ve seen in
industry) is about 90 or so. It depends on various factors.
43
DB Connection Management:
Close every connection that’s opened



EVERY time a connection is opened it MUST be closed.
If there’s even one code path where a db connection is
opened but not closed, this creates a db connection leak. If
this code path is heavily used, then the database will run out
of connections, the application will fail -- until the database
server is rebooted. User work will be lost.
If this code path is not heavily used, then its possible that the
database server will not need to be rebooted (each connection
will “time out” if not used over a certain time period, as
determined by the database administrator).
44
DB Connection Management –
DB Connection Pooling


To combat this potential area of slowness, database
servers (e.g., oracle software) and application servers
(e.g., .NET software) employ connection pooling.
That is to say, they request connections ahead of
time and have a limited number of them already
available.
In order to take advantage of this connection
pooling, however, you must specify the connection
exactly the same way each time to try to access a
particular database. This can best be accomplished
by funneling all db connection requests through a
single class – your database wrapper class.
45
Closer look at EmpRead class’s
data members & constructor
private DbConn dbc; // db connection
private string errMsg; // error msg
private bool err; // is there an error?
public EmpRead(DbConn dbc) {
this.dbc=dbc;
}
46
Put all SQL access into Same Class



In our architecture, we create a SQL class that
holds all the access to a particular table.
That way, if changes are made to the
database, we know where we’ll have to
change the code (in the SQL classes).
In our Sample code, we put the SQL into a
class called StockRead.
47
Examine EmpRead.read method (1/3)
public string read(string lastNameLike) {
this.err=false;
this.errMsg="";
string tmp = "";
OleDbConnection con = dbc.getConn();
if (dbc.isConnErr()) {
this.err=true;
this.errMsg=dbc.getErrMsg();
return "*** Error Connecting to Database ***";
}
// continued on next slide
48
Examine EmpRead.read method (2/3)
// continued from … public string read(string lastNameLike) {
try {
string sql = "SELECT FirstName, LastName FROM emp "+
"where LastName LIKE @nameParm";
OleDbCommand cmd = new OleDbCommand(sql,con);
cmd.Parameters.Add("@nameParm",lastNameLike+"%");
System.Data.OleDb.OleDbDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read()) {
tmp += reader.GetValue(0).ToString() + " ";
tmp += reader.GetValue(1).ToString() + "<br>";
}
reader.Close();
}
49
Examine EmpRead.read method (3/3)
// continued from … public string read(string lastNameLike) {
catch (Exception e){
this.err=true;
this.errMsg=e.Message;
}
return tmp;
}
50
Use StringBuilder instead
of String (more efficient when appending)
using System.Text;
…
StringBuilder s = new StringBuilder();
while (reader.Read()) {
s.Append(reader.GetValue(0).ToString() + " ");
s.Append(reader.GetValue(1).ToString() + “<br>");
}
reader.Close();
con.Close();
return s.ToString();
// MAKE THESE CHANGES to the read method of the EmpRead Class
51
Build a String that contains a html
table full of data
StringBuilder s = new StringBuilder();
s.Append(“<table border = ‘1’>\n”);
while (reader.Read()) {
s.Append(“ <tr>\n”);
s.Append(“ <td>”+reader.GetValue(0).ToString() + “</td>\n ");
s.Append(“ <td>”+reader.GetValue(1).ToString() + "</td>\n");
s.Append(“ </tr>\n”);
}
s.Append(“</table>”);
reader.Close();
// Nicer layout for display on the form…
// Make changes in EmpRead read method
52
Formatting Currency and Date Values
// Convert to string applying a currency format
s.Append(reader.GetDecimal(2).ToString("C") + " ");
// Convert to string applying a date format
s.Append(reader.GetDateTime(3).ToString("d") + "\n");
How can you find this out again (in case you loose this info)?
Click on “ToString” Method anywhere in your code, press F1
(context sensative help). Click on IFormattable. Click on
IFormatProvider. Click on DateTimeFormatInfo or
NumberFormatInfo. Then scroll down to see a list of available
formats (like the C and d shown above).
53
Reading Null DB Values
will Create Exception
Previous sample code aborts if it encounters a null value from the
database. Create reusable utility methods to deal with this:
private string getDate(OleDbDataReader reader, int pos) {
try {
return reader.GetDateTime(pos).ToString("d");
}
catch (Exception e) { // exception thrown if reading null
return "--/--/----";
}
}
54