Transcript Introducing Database Access

Introducing
Database Access
from Web Applications
by Sally Kyvernitis
1
Outline





What are business applications?
What makes web software different from clientserver software?
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
What are Business Applications?
Software that:
 Writes to and reads from databases.
 Generates and presents information extracted
from a database.
 Usually restricts access based on user logon
and authorization levels.
4
What makes Web Software different
from Client-Server Software?



Uses HTML - a markup language that is
primarily intended to describe layout,
pictures, text, & links (not functionality or
behavior).
HTML has no commands for accessing a
database.
Initial web sites only displayed information
and pictures. They did not provide much
functionality other than that.
5
What’s makes Web Software
different from Client-Server Software?

Web browsers and web servers use HTTP, a “stateless” (or
“connectionless”) protocol in which:
 the client (browser) asks a web server for a page (the URL
in the browser’s address bar is the request),
the server gives the requested page (containing HTML markup)
back to the client and then forgets about the whole
exchange.
Web applications are installed on a web server. Client PCs
only need to have a browser installed in order to able to run
any/all web applications.
Most web applications can be run from any computer that
has internet access.



6
What’s makes Web Software
different from Client-Server Software?



Client Server software can remember all user input
(on the client PC) without difficulty, i.e., Client
Server software is inherently “stateful”.
Client Server software is installed on the client PC
(like MSWord or other applications that are
installed on a PC).
In a Client Server application, the client PC usually
must be inside (the “firewall”) of the company in
order to be able to access company databases.
7
How can Business Applications
be delivered through Web Software?



1:
Even though HTTP is connectionless, web server software
has been enhanced to keep session information about it’s
users, e.g., the user’s login information, what users have done
this session.
Even though HTML1 has no commands for accessing a
database, web server software can read user’s input, access a
database, and then (dynamically) create HTML that responds
to the user’s input with data from the database.
The next slide shows which code runs where in a typical web
application.
HTML is the web page markup language that is interpreted by browsers.
8
Typical 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
Typical Web Application
System Architecture


On the previous slide, the rightmost (purple) box represents the
database server (e.g., a computer where oracle or sql server software is
installed and where the database is stored). Only this computer accesses
the database (e.g., reads and writes).
The (blue) box in the lower left represents an application
server (and web server). This computer generally has web
server software (e.g., IIS) and application server software (e.g.,
dot NET) installed. This computer accepts requests from
browsers, accesses the database server (if necessary), then
creates dynamic HTML in response to the browser’s request.

The database and application servers are located “inside the firewall” of a
company where security is tight and computers can “trust” each other.

The client PC (yellow box, top left) only requests pages over
the internet, receives HTML, and possibly posts values (e.g.,
user input) back to the web server.
10
Web App Software Architecture

Your web application
code can get messy if
HTML layout commands
are heavily interspersed
with database values.

This is why you want to keep your layout code (how
things look) separated from your business logic (the
data being displayed).

Dot Net provides many features to help programmers separate layout code
from business logic.
11
When does a web server send
a page to a browser?

A web application server sends a page to a
browser


when the user requests a page (e.g., by typing a
URL into the browser) or
when the user performs some action like clicking
on a button. The “new page” may look almost
exactly like the old page, but perhaps it has an
extra message somewhere on that page.
12
Web Controls versus
Windows Controls



Examples of web controls are buttons, textboxes, and radio
buttons.
In design mode, web programmers “put code under” web form
buttons, very much like windows programmers put code under
buttons in a client server application. However, web
programmers avoid putting code anywhere other than button
click events (for performance reasons, to avoid a slow round trip
with the web server).
Also called ASP .NET server controls because they actually
reside on the server (even though they look like the reside in the
client’s browser). And they only run on the server “in between”
pages.
13
What is 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).
 In these tutorials, we just use the Connection, Command, and
DataReader objects.
14
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
15
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.
16
Selecting a Data Provider



1st choice is a data provider that is optimized for
your database. So, if your database is SQL server
7.0 or later, use SQL server data provider classes. If
it’s Oracle 8i or later, use Oracle provider classes.
Otherwise, you must OLE DB or ODBC.
For simplicity, we’ll use OLE DB with an
MSAccess database in this class (OLE DB is a little
faster than ODBC).
17
Classes in the
Ole Db Data Provider




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).
18
Using ADO
A string (identifying the database you
wish to use) is input to the Connection
constructor.
String to
ID the DB
Connection
Connection.Open()
A string (a SQL database command)
and an open connection are inputs to
the Command constructor.
String with valid
SQL statement
inside
Command
Command.ExecuteReader()
The Command.ExecuteReader method
returns a DataReader object that holds
the result set of the SQL select
statement (that was passed to the
Command object).
DataReader
(holds data
selected from DB)
19
The “using” Statement

To avoid writing really long class names like this:
System.Data.OleDb.OleDbDataReader reader;
add a “using” statethis to the top of the class where you are
coding:
using System.Data.OleDb;

The “using” statement (like the java import statement)
enables you to write shorter class names like this:
OleDbDataReader reader;
20
Sample Code – Database Read
(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 {
Must add this to the top of the webform
string sql = "SELECT FirstName, LastName FROM emp";
or else this code won’t work:
OleDbCommand cmd = new OleDbCommand(sql, con);
con.Open();
using System.Data.OleDb;
reader = cmd.ExecuteReader();
while (reader.Read()) {
this.lblDisplayData.Text += reader.GetValue(0).ToString() + " ";
this.lblDisplayData.Text += reader.GetValue(1).ToString() + "<br>";
}
reader.Close();
con.Close();
}
catch (Exception ex) {
this.lblDisplayData.Text=ex.Message;
con.Close();
}
}
21