Computer Science 397 Database Programming

1
Computer Science 397Database Programming

• Chapter 5
• ADO.NET
• Overview

2
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.

3
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.

4
Typical HTTP Request
5
Scripting Languages

• Scripting languages - Allow us to add capability
  to whats 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).

6
Typical Server-Side Request
7
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.

8
.NET Architecture

• Multi-language, virtual machine driven

VB
J
C
C

Your Application
.NET FrameworkClass Library
Common Language Runtime (CLR)
Operating System
Hardware
9
CLR-based execution

• .NET applications are not stand-alone executable
  programs

APP.exe
OS Process
CLR
other FxCL components
Core FxCL
obj code
Underlying OS and HW
10
Implications?

• 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
• Design trade-off
• managed execution (more secure, memory
  protection, etc.)
• portability
• slower execution?

11
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

12
Visual Studio

• Option 2 is to use Visual Studio with complex
  and powerful development environment.

13
Typical 3-Tier Architecture
User Interface
Search Engine
Database
14
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

15
Two Major Sets of ADO.NET CLASSES

• The Managed Provider Classes allow you to work
  with the database while connected to it
• SQL Server Managed Provider Classes
• OLE DB Managed Provider Classes
• ODBC Managed Provider Classes
• The Generic Data Classes allow you to store
  local (client) copy of information to work with
  while disconnected from the database.

16
The Managed Provider Classes
17
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

18
SqlCommand Object

• Represents a command (SQL, T-SQL, Stored
  procedure) to execute against the database
• CommandType
• StoredProcedure
• TableDirect
• Text
• CommandText
• Name of the stored procedure if type is
  StoredProcedure
• Name of table(s) if type is TableDirect
• The SQL statement if type is Text
• Connection
• Parameters
• ExecuteReader method sends CommandText to the
  connection and creates an SqlDataReader (Note
  Does NOT open and close connection)

19
SqlParameter Object

• Represents a parameter for a command object
• SqlDbType
• ParameterName
• Value
• Nullable, etc.
• Represents collection of SqlParameters
• Add
• Insert

SqlParameterCollection Object
20
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

21
Sql Managed Provider Namespace

• Namespaces provide unique identification of
  collections of classes
• For the SQL managed provider classes System.Data
  .SqlClient

22
The Generic Data Classes
23
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.
• Tables
• Relations
• DataSetName
• Clear
• AcceptChanges

24
SqlDataAdapter Object

• Represents a set of data commands that are used
  to fill the DataSet and update a SQL Server
  database.
• SelectCommand
• UpdateCommand
• InsertCommand
• DeleteCommand
• Fill method to fill DataSet table with data from
  SelectCommand
• Update method used to update data in the database

25
DataTable Object

• Represents a table in dataset
• TableName
• Columns
• Rows
• Primary Key

DataTableCollection Object
26
DataRow DataRowCollectionDataColumn
DataColumnCollectionConstraintDataViewDataRela
tionUniqueConstraintForeignKeyConstraintNamespa
ce System.Data
27
SqlTransaction Object

• Represents a Transact-SQL transaction to be made
  in a SQL Server database. This class cannot be
  inherited. (Set of commands that work all or
  none).
• Connection
• Commit
• Rollback

28
ADO.NET Communications with SQL Server
Console ApplicationInteracts with ConsoleWindow
Windows ApplicationInteracts with Windows Form
with controls
Web ApplicationASP.NET page withHTML,
scripting, controls
ADO.NET datasettables and relationshipsreading
and writing
ADO.NET data readerquick and simple read only
ADO.NET data adapter reading,writing
ADO.NET commandspecifies data to send or receive
ADO.NET connectionSpecifies data source,
provider, security information
SQL Server
29
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

30
Example program from handout (cont.)
HollywoodMovieStar
DataAdapter
Connection
SelectCommand
WebForm
DataSet
DataGrid
myStars
31
Example program from handout (cont.)
HollywoodMovieStar
DataAdapter
Connection
SelectCommand
WebForm
DataSet
DataGrid
myStars
this.sqlDataAdapter1.Fill(dataSet11,myStars)
32
Example program from handout (cont.)
HollywoodMovieStar
DataAdapter
Connection
SelectCommand
WebForm
DataSet
DataGrid
myStars
this.DataBind()
33
Example program from handout (cont.)

• Note Were only doing sequential read from the
  database table
• Dont 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 Commands connection and CommandText

34
Example program from handout (cont.)
private void Page_Load(object sender,
System.EventArgs e) this.sqlConnection1.Open()
this.DataGrid1.DataSource this.sqlCommand1.Ex
ecuteReader() this.DataBind() this.sqlConnec
tion1.Close()
35
using System using System.Data using
System.Data.SqlClient class SelectIntoDataSet
public static void Main() string
connectionString "serverlocalhostdatabas
eNorthwinduidsapwdsa" SqlConnection
mySqlConnection new SqlConnection(connecti
onString) string selectString "SELECT
TOP 10 CustomerID, CompanyName, ContactName,
Address " "FROM Customers "
"ORDER BY CustomerID" SqlCommand
mySqlCommand mySqlConnection.CreateCommand()
mySqlCommand.CommandText selectString
SqlDataAdapter mySqlDataAdapter new
SqlDataAdapter() mySqlDataAdapter.SelectComma
nd mySqlCommand DataSet myDataSet new
DataSet() mySqlConnection.Open()
Console.WriteLine("Retrieving rows from the
Customers table") mySqlDataAdapter.Fill(myDat
aSet, "Customers") mySqlConnection.Close()
DataTable myDataTable myDataSet.Tables"Custo
mers" foreach (DataRow myDataRow in
myDataTable.Rows) Console.WriteLine("Custom
erID " myDataRow"CustomerID")
Console.WriteLine("CompanyName "
myDataRow"CompanyName")
Console.WriteLine("ContactName "
myDataRow"ContactName")
Console.WriteLine("Address "
myDataRow"Address")