Web server

wrackbaaMobile - Wireless

Dec 10, 2013 (3 years and 4 months ago)

42 views

Computer Science 317

Database Management

Introduction to Web Access to
Databases

The Plan


I. Introduction


General overview


One simple approach


II. .NET Overview


III. ADO.NET Overview


III. Examples with C#


Part I: Introduction

Why web access to database?


Provides platform independent, remote
access to the database


Data viewing


Data gathering

Web Browsers and Servers


Web browser

-

This is a program that runs
on your local PC (client). It allows you to
request web pages from an internet host.



Web server

-

This is a program that runs on
the internet host computer (server). It takes
requests for web pages from clients and
delivers the pages back to the client.

HTML and HTTP


HyperText Markup Language

-

Allows “marking
up” a document with tags specifying appearance
and structure.



HyperText Transfer Protocol

-

Protocol used for
browsers to communicate with web servers.
Basically, this consists of “requests” from the
browser and “responses” from the server.

Typical HTTP Request

Browser

Web Server

HTTP Request

http://website/some.html

This is a

web page

Browser interprets

HTML and displays

page

<HTML>

<B> This is a web page </B>

<IMAGE> Picture</IMAGE>

</HTML>

HTTP Response

Typical HTML Request


Client Side (Browser)


Issues request for
HTML page


Receives response
HTML page


Interprets HTML and
creates web page


Displays web page


Work is done here



Server Side


Reads request from
client


Finds page on server


Sends response page to
client


Essentially a file server

Scripting Languages


Scripting languages

-

Allow us to add capability
to what’s provided by HTML. Allow parts of the
page to be built “on the fly”


Client
-
side scripts

-

Script engine on client
machine builds parts of page when page is loaded
by browser (date is simple example)


Server
-
side scripts

-

Script engine on server builds
parts of page before sending to client (database
query results for example).

Typical Server
-
Side Request

Client

Web Server

HTTP Request

http://web site

<HTML>

<H1> Stars </H1>

<B> John Wayne <BR>

Meg Ryan </B>

</HTML>

Interprets script code


Performs action


Fills in part of page

Sends response

to client

Stars

John Wayne

MegRyan

Browser interprets

HTML and displays

page

<HTML>

<% server script %>

</HTML>

Gets Page

Typical Server
-
Side Request


Client Side (Browser)


Issues request for
HTML page


Receives response
HTML page


Interprets HTML and
creates web page


Displays web page



Server Side


Reads request from client


Finds page on server


Interprets script code


Does work dictated by this
code (or has it done)


Alters HTML file


Sends response page to
client

Web Access of Database


The database resides on the server.


Web pages with scripts (or calls to scripts)
allows the user to send database requests to
the server.


The server accesses the database to honor
the requests.


Results can be returned on an html page.


Actions can take place on the database.

So, what are the pieces we need?


Browser software on user machines


A machine for the server


Web server software on server


Database management system on server


Scripting/programming language for
accessing the database


Drivers/interfaces for scripting language to
access the database

So, what are the pieces we use?


Browser
: Netscape or Internet Explorer


Server machine
: The Windows 2000 server that
happens to house the SQL server.


Web server
: Microsoft IIS


Database management system
: SQL Server


Programming
:

C# (VB.NET?) creating ASP.NET


Database interface
: ADO.NET


Part II: .NET Overview

Architecture


Multi
-
language, virtual machine driven…

Hardware

Operating System

Common Language Runtime (CLR)

Your Application

.NET Framework

Class Library

VB

J#

C#

C++



CLR
-
based execution


.NET applications are not stand
-
alone executable
programs

APP.exe

other FxCL
components








CLR

JIT Compiler

obj code


OS Process

Underlying OS and HW


Core
FxCL


Implications?

1.
Clients need CLR & FxCL to run .NET apps


available via
Redistributable .NET Framework


two versions: v1.0 (2002) and v1.1 (2003)


20MB download


runs on 98 and above, NT (sp6a) and above


included in Windows 2003, otherwise install via
Windows update

2.
Design trade
-
off…

+
managed execution (more secure, memory
protection, etc.)

+
portability


slower execution?


Cross
-
platform


Compiled .NET apps run on any supported
platform:



Your application


?

Win64

Win32

(
XP,2K,98)

WinCE

Command
-
line development


Option #1 is to use command
-
line tools…


.NET Framework SDK


free (100 MB)


complete set of command
-
line tools and docs


development supported on Windows NT, 2000, XP
Pro


http://msdn.microsoft.com/net



other platforms?


FreeBSD / Mac OS X via
Rotor

(i.e. SSCLI)


Linux via
Mono

project


Unix via
dotGNU

project

Part II: ADO.NET Overview

Introduction to ADO.NET


This is the layer that allows us to communicate with
the database.


Everything is object oriented which is good


Recall


Objects are defined by classes


Objects are instantiated by constructors that may have
parameters


Objects have


Properties or members that give them state


Methods that give them behavior


I will give some of the important classes (objects), and
some of the properties and methods.


Much more is available through the Visual Studio
documentation.


ADO.NET Communications with SQL Server

Console Application

Interacts with Console

Window

Windows Application

Interacts with Windows

Form with controls

Web Application

ASP.NET page with

HTML, scripting, controls

ADO.NET data reader

quick and simple read only

ADO.NET dataset

tables and relationships

reading and writing

ADO.NET command

specifies

data to send or receive

ADO.NET connection

Specifies data source, provider, security information

SQL Server

ADO.NET data

adapter


reading,

writing

SqlConnection Object


Represents a connection to an SQL database


One member is a ConnectionString that
contains information needed to connect to the
database


Open method to open the connection using the
connection string


There is a constructor that has connection string
as parameter


Close method

SqlCommand Object


Represents a command (SQL, T
-
SQL, Stored
procedure) to execute against the database


CommandType


StoredProcedure


Text


CommandText


Name of the stored procedure if type is StoredProcedure


The SQL statement if type is Text


Connection


ExecuteReader method


sends CommandText to
the connection and creates an SqlDataReader

(Note: Does NOT open and close connection)

SqlDataReader Object


Provides a means of reading a forward
-
only
stream of rows from a SQL Server database.


Has properties like HasRows, FieldCount


Methods for getting column values of current row,
depending on type.


Read method to move to next row.


Very efficient way and preferred if only reading

DataSet Object


Datasets store data in a disconnected cache.
The structure of a dataset is similar to that of a
relational database; it exposes a hierarchical
object model of tables, rows, and columns. In
addition, it contains constraints and
relationships defined for the dataset.

SqlDataAdapter Object


Represents a set of data commands that are
used to fill the DataSet and update a SQL
Server database.


SelectCommand


UpdateCommand


InsertCommend


DeleteCommand


Fill method to fill DataSet table with data from
SelectCommand


Update method used to update data in the database

Example program from handout


When you drug the table onto the webform:


An SqlConnection and SqlDataAdapter were created


The connection was set to the database of the table


The SelectCommand of the adapter was set to select all
rows from the table


Then you generated a DataSet object


You created a DataGrid object on the form


Set its DataSource to be the DataSet


Set its DataMember to be a tablename

Example program from handout (cont.)

Hollywood

MovieStar

Connection

DataSet



myStars

WebForm





DataGrid

DataAdapter



SelectCommand

Example program from handout (cont.)

Hollywood

MovieStar

Connection

DataSet



myStars

WebForm





DataGrid

DataAdapter



SelectCommand

this.sqlDataAdapter1.Fill(dataSet11,”myStars”);

Example program from handout (cont.)

Hollywood

MovieStar

Connection

DataSet



myStars

WebForm





DataGrid

DataAdapter



SelectCommand

this.DataBind();

Example program from handout (cont.)


Note: We’re only doing sequential read from the
database table


Don’t need DataSet and DataAdapter for this


Just need Connection and Command (and DataReader)


Drag connection and command objects to the form.


Set the connection string correctly for connection
object.


Set Command’s connection and CommandText

Example program from handout (cont.)

private void Page_Load(object sender, System.EventArgs e)

{


this.sqlConnection1.Open();



this.DataGrid1.DataSource = this.sqlCommand1.ExecuteReader();



this.DataBind();



this.sqlConnection1.Close();

}