ASPADO

1
ASP-ADO

• CS-422
• Dick Steflik

2
ActiveX Data Objects (ADO)

• Does for Microsoft data sources what ODBC did for
  databases
• similar in concept to the combination JDBC and
  JNDI (and then some)
• The process of using ADO involves the four step
  process
• Connect to the database
• Define the data you want
• Manipulate the data
• Display the data

3
ADO Software Architecture
VC
VB
Script
Java
ADO
OLE DB
Directory Services
RDBMS
E-mail
4
ADO, ODBC and OLE DB

• ADO is the strategic way for using ODBC data
  sources on the Windows platform
• ODBC was the MS way of connecting to and using
  databases on the Windows platform.
• OLE DB extends ODBC by using the concept of
  service providers to add the ability to connect
  to other non-database data sources (MS Active
  Directory (LDAP), e-mail servers (Exchange))
• ODBC Data Sources provider MSDASQL
• MS Index Server provider MSIDXS
• MS Active Directory Server - provider
  ADSD300Object
• MS Jet databases - provider Microsoft.Jet.OLEDB.
  3.51
• MS SQL Server - provider SQLOLEDB
• Oracle Databases - provider MSDAORA

5
Providers and Drivers
ADO
OLE DB Providers
JET
SQL
Oracle
ODBC
ODBC Drivers
JET
SQL
Oracle
Access
SQL
Oracle
Access
SQL
Oracle
6
All About ADO

• To learn more about goto
• http//www.w3schools.com/ado/default.asp

7
Creating a CreateObject

• Connection objects are created using the
  CreateObject() function.
• The only parameter is a string indicating the
  object to create
• object_library_name.object

Dim cn create a variable to hold a reference
to the object Set cn CreateObject(ADODB.Connect
ion)
8
ConnectionString

• Once the ConnectionObject is created we need to
  give it a ConnectionString to logon to the
  datasource
• ConnectionString specifies the
• provider
• datasource name
• userid (optional)
• password (optional)

Dim cnn Dim str str ProviderMSDASQL Data
Sourcemydatasource User IdPassword cnn.Conne
ctionString str
9
Open() and Close()

• Once the connection has been established the
  Connection must be opened before using it
• Likewise it should be closed when done.

Dim cnn Dim str Set cnn CreateObject(ADODB.Conn
ection) str ProviderMSDASQL.1,Data
Sourcemydatasource str str User
TDmyuseridPasswordmypass cnn.ConnectionString
str cnn.Open .. cnn.Close
10
Recordset

• Record set objects are the recipients of the
  result of a SQL query
• create the Connection object the create a
  recordset object and associate them

Dim cnn Dim rs set cnn CreateObject(ADODB.Conne
ction) set rs CreateObject(ADODB.Recordset)
assocoate the record set with the
connection rs.ActiveConnection cnn
11
To use the Recordset, Open it
from previous code... Dim SQL SQL
SELECT..FROM.. rs.Open SQL you can
abbreviate this by doing the association at the
same time as the Open rs.Open SQL, cnn
this will eliminate the need for the
rs.ActiveConnection cnn statement
12
Getting the data out

• The record set is a collection of rows, each row
  containing the data in the order that it was
  asked for in the SQL statement, each column in
  the row can be accessed by its column name
• for SELECT CourseName from CourseTable

Dim string While Not rs.EOF string string
rs(CourseName) ltbrgt rs.MoveNext Wend
13
Getting data in

• To get data into an ADO data source use the ADO
  command object.
• Use this for the SQL
• insert
• update
• and delete statements

Dim SQL Dim Cmd Set cmd CreateObject(ADODB.Comm
and) create a command object SQL INSERT
into .. cmd.CommandText SQL set the
CommandTexy property of the CommandObject cmd.Acti
veConnection cnn cmd.Execute
14
Display records from a database in an HTML Page
lthtmlgt ltbodygt lt set connServer.CreateObject("AD
ODB.Connection") conn.Provider"Microsoft.Jet.OLED
B.4.0" conn.Open(Server.Mappath("northwind.mdb"))
set rs Server.CreateObject("ADODB.recordset") rs
.Open "Select from Customers", conn do until
rs.EOF for each x in rs.Fields
Response.Write(x.name) Response.Write("
") Response.Write(x.value "ltbr /gt")
next Response.Write("ltbr /gt")
rs.MoveNext loop rs.close conn.close gt lt/bodygt
lt/htmlgt
15
Put the data in a table with colors and titles
lthtmlgt ltbodygt lt set connServer.CreateObject("ADO
DB.Connection") conn.Provider"Microsoft.Jet.OLEDB
.4.0" conn.Open(Server.Mappath("northwind.mdb")) s
et rs Server.CreateObject("ADODB.recordset") sql
"SELECT Companyname, Contactname FROM
Customers" rs.Open sql, conn gt lttable border"1"
width"100" bgcolor"fff5ee"gt lttrgt ltfor each x
in rs.Fields response.write("ltth align'left'
bgcolor'b0c4de'gt" x.name "lt/thgt") nextgt lt/t
rgt ltdo until rs.EOFgt lttrgt ltfor each x
in rs.Fieldsgt lttdgtltResponse.Write(x.value
)gtlt/tdgt ltnext rs.MoveNextgt
lt/trgt ltloop rs.close conn.close gt lt/tablegt lt/bod
ygt lt/htmlgt
16
Another example, make data on page sortable by
user
lthtmlgt ltbodygt lttable border"1"
width"100"gt lttrgt lttdgtlta href"demo_sort2.asp?sor
tcompanyname"gtCompanylt/agtlt/tdgt lttdgtlta
href"demo_sort2.asp?sortcontactname"gtContact
Namelt/agtlt/tdgt lt/trgt lt dim conn,rs,sort sort"comp
anyname" if Request.QueryString("sort")ltgt"" then
sortRequest.QueryString("sort") end if set
connServer.CreateObject("ADODB.Connection") conn.
Provider"Microsoft.Jet.OLEDB.4.0" conn.Open(Serve
r.Mappath("northwind.mdb")) set
rsServer.CreateObject("ADODB.recordset") rs.Open
"SELECT Companyname,Contactname FROM Customers
ORDER BY " sort,conn do until rs.EOFgt
lttrgt ltfor each x in rs.Fieldsgt
lttdgtltResponse.Write(x.value)gt lt/tdgt ltnext
rs.MoveNextgt lt/trgt ltloop rs.close conn.cl
ose gt lt/tablegt lt/bodygt lt/htmlgt
17
Using the Recordset Object
This example returns the value of the first
column in the first two records ALFKI ANTON This
example returns the value of the first three
columns in the first record ALFKI Alfreds
Futterkiste Maria Anders
ltbodygt lt set connServer.CreateObject("ADODB.Conn
ection") conn.Provider"Microsoft.Jet.OLEDB.4.0" c
onn.Open(Server.Mappath("northwind.mdb")) set rs
Server.CreateObject("ADODB.recordset") rs.Open
"Select from Customers", conn 'The first number
indicates how many records to copy 'The second
number indicates what recordnumber to start
on prs.GetRows(2,0) response.write("ltpgtThis
example returns the value of the first column in
the first two recordslt/pgt") response.write(p(0,0)
) response.write("ltbrgt") response.write(p(0,1)) re
sponse.write("ltpgtThis example returns the value
of the first three columns in the first
recordlt/pgt") response.write(p(0,0)) response.writ
e("ltbrgt") response.write(p(1,0)) response.write("lt
brgt") response.write(p(2,0)) rs.close conn.close
gt lt/bodygt lt/htmlgt
18
ADO.NET

• Under .NET, ADO hasnt changed much but the way
  you use it has
• VB has changed extensively under .NET so we wont
  address its use with VB (developer community
  isnt real happy about this)
• the ADO API is pretty consistent across the MS
  supported .NET languages
• Consists of the namespaces and classes that
  manage accessing backend databases.
• five namespaces
• the most common are
• Ststem.Data
• System.Data.OleDB
• System.Data.SqlClient
• hundreds of classes

19
the .NET Data Provider
.NET Data Provider
DataReader
Connection
Command
Transaction
Parameters
DB
SelectCommand
UpdateCommand
InmsertCommand
DeleteCommand
20
.NET DataSet
DataSet
DataTableCollection
DataTable
DataTable
DataRowCollection
DataColumnCollection
ConstraintCollection
DataRelationshipCollection
21
System.Data Namespace

• Consists mainly of classes that make up ADO.NET
• Allows a developer to build components that
  manage data from data sources

22
System.Data.OleDb Namespace

• Ole DB .Net Data Provider
• Designed to be a replacement for ODBC
• Used for accessing different kinds of data stores
  uniformly
• non-relational databases
• Dbase, Foxpro, Access
• Spreadsheets

23
System.Data.SqlClient Namespace

• .NET data provider for MS SQL Server
• classes bypass OLE DB to interact directly with
  SQL Server
• increase performance for SQL Server access

24
MySql.Data.MySqlClient

• namespace containing classes for accessing MySQL
  databases
• provided by MySQL organization

25
IBM.Data.DB2 namespace

• namespace for using IBM DB2 databases with .NET
• Provided by IBM with the DB2 product

26
Oracle.DataAccess.Client

• namespace used for accessing Oracle databases
  from .NET
• provided by Oracle with Oracle product