Transcript ADO.NET - WordPress.com

ADO
ActiveX Data Object
ADO
•ActiveX Data Objects (ADO) is Microsoft’s latest
database object model.
•The goal of ADO is to allow VB developers to use a
standard set of objects to refer to any OLE DB source.
•Is Microsoft ActiveX Component.
•Automatically installed with Microsoft IIS Server
•ADO is a programming interface to access data in a
database
ActiveX Data Objects (ADO)
• Performs same functions as ADO with
following improvements:
– Simpler command set
– Faster execution (sometimes)
– Uses less memory
– Consumes less disk space
– Supports any database to which Access can
connect
Universal Data Access (UDA)
• It supports high performance data access to
relational , nonrelational, and legacy data
sources.
• Components
– The OLE DB
– The Core of the UDA architecture
– Provides low level access to any data source
Microsoft UDA Architecture
ADO Objects
Connection
Command
Recordset
Errors
Parameters
Fields
Error
Parameter
Field
Connection
Allows control over the connection to the data source
Recordset
Contains the records that are the result of a query
Command
Executes database commands and queries
Error
Contains information about Errors from ADO
Field
Represents a field in a data set
Parameter
Works with Command to set parameters for stored
procedures or parameter queries
Property
Allows you to access ADO object Properties
Establish Reference to ADO
Before using ADO, you must establish a reference to the ADO library.
Project, References, Microsoft ActiveX Data Objects 2.5 Library
Basic Steps to executing a query using ADO
1. Create a Connection Object.
2. Specify the connection string to connect to the database of interest.
3.
Open the connection
4. Create a Recordset object. The Recordset object contains the results of
the query after execution.
5. Specify the SQL text. When you open a recordset, you can either use a
table, a stored procedure, or string containing a SQL statement.
6. Open the recordset.
7. Opening the recordset executes the query and returns the records to
the recordset object. The records are accessible through the recordset
object now.
Connection Object
A Connection object represents a unique session with a data source.
In the case of a client/server database system, it may be
equivalent to an actual network connection to the server.
Important Properties and Methods
•
•
•
•
•
•
ConnectionString
ConnectionTimeout
Mode
CursorLocation
Provider
Open and Close
Connection Object
Create ADODB Connection
Dim connAVB As ADODB.Connection
Set connAVB = New ADODB.Connection
connAVB.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.3.51;" & "Data Source=" &
App.Path & "\AVB.mdb;Mode=readwrite“
connAVB.Open
Connection Object
'Create ADODB Connection
Dim connAVB As ADODB.Connection
Set connAVB = New ADODB.Connection
connAVB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51;" &
"Data Source=" & App.Path & "\AVB.mdb;Mode=readwrite“
connAVB.Open
‘code
connAVB.Close
Set connAVB = Nothing
Recordset Object
Represents the entire set of records from a base table or the results of an
executed command.
You use Recordset objects to manipulate data from a provider.
Important Properties and Methods
• CursorLocation
• CursorType
• LockType
• Mode
• Open, Close
• MoveFirst, MoveLast, MoveNext, and MovePrevious
• BOF, EOF
• Update, AddNew, Delete
• GetString, GetRows
CursorLocation
Cursor refers to system resources needed to manage a set of
data.
Specifies the location of the cursor service.
Constant Value Description
adUseClient -Uses client-side cursors supplied by a local
cursor. Use for desktop applications.
adUseServer - Default. Uses data-provider or driver-supplied
cursors.
These cursors are sometimes very flexible and
allow for additional sensitivity to changes others make to
the data source.
Cursor Types
Dynamic cursor — allows you to view additions, changes, and deletions
by other users; allows all types of movement through the Recordset
Keyset cursor — behaves like a dynamic cursor, except that it prevents
you from seeing records that other users add, and prevents access to
records that other users delete. Data changes by other users will still
be visible.
Static cursor — provides a static copy of a set of records for you to use
to find data or generate reports; always allows bookmarks and
therefore allows all types of movement through the Recordset.
Additions, changes, or deletions by other users will not be visible.
This is the only type of cursor allowed when you open a client-side
Recordset object.
Forward-only cursor — allows you to only scroll forward through the
Recordset. Additions, changes, or deletions by other users will not be
visible. This improves performance in situations where you need to
make only a single pass through a Recordset.
Lock Types
LockType is important when you have multiple users accessing the same
data.
Lock Type
Constant
Result
N/A
adLockUnSpecified
Read Only
adLockReadOnly
No edits
Optimistic
adLockOptimistic
Locked as each update submitted.
BatchOptimistic adLockBatchOptimistic
Locked when all updates
submitted
Pessimistic
Locked from Recordset creation
adLockPessimistic
Opening a RecordSet
Public Sub MoveX()
' connection and recordset variables
Dim rstAuthors As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQLAuthors As String
' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=sqloledb;Data Source=a-iresmi2000;Initial
Catalog=pubs;User Id=sa;Password=;"
Cnxn.Open strCnxn
' Open recordset from Authors table
Set rstAuthors = New ADODB.Recordset
rstAuthors.CursorLocation = adUseClient
Opening a RecordSet
strSQLAuthors = "SELECT au_id, au_fname, au_lname, city, state
FROM Authors ORDER BY au_lname”
rstAuthors.Open strSQLAuthors, strCnxn, adOpenStatic,
adLockOptimistic, adCmdText
rstAuthors.MoveFirst
numRecords = rstAuthors.RecordCount
city = rstAuthors!city
rstAuthors.Close
Cnxn.Close
Set rstAuthors = Nothing
Set Cnxn = Nothing
End Sub
ADO.NET
• ADO.NET is a new, improved, and greatly expanded
version of ADO that was developed for the Microsoft
.NET initiative
• It incorporates all of the functionality of ADO and
facilitates the transformation of XML documents to
and from database data
• It uses datasets, which is an in-memory, fullyfunctioned, independent databases
Role of ADO.NET
• ADO.NET serves as an intermediary between
all types of .NET applications and the DBMS
and database
Data Provider
• A .NET data provider is a library of classes that
provides ADO.NET services
• Microsoft’s provides three data providers
– OLE DB data provider can be used to process any OLE
DB-compliant data source
– SQLClient data provider is purpose-built for use with SQL
Server
– OracleClient data provider is purpose-built for use with
Oracle
Data Provider Components
Data Provider Components
• A connection object is similar to the OBDC’s connection
object
• A command object is created on an established
connection
• A data reader provides read-only, forward-only, fast
access to database data
• An application can get and put data to and from the
database using the command object
• A dataset is an in-memory database that is disconnected
from any regular database
– It distinguishes ADO.NET from the previous data access
technology
The ADO.NET Dataset
• A dataset is an in-memory database that is disconnected from
any regular database
• Datasets can have
– Multiple tables, views, and relationships
• Tables may have surrogate key (auto increment columns),
primary keys, and be declared as unique
– Referential integrity rules and actions
– The equivalent of triggers
• Datasets may be constructed from several different databases
and managed by different DBMS
Dataset Advantages
• Dataset contents and its XML schema can be easily
formatted as an XML document
• Also, XML schema documents can be read to create the
structure of the dataset, and XML documents can be read
to fill the dataset
• Datasets are needed to provide a standardized, nonproprietary means to process database views
– This is important for the processing of views with
multiple multi-value paths
Dataset Disadvantages
• Because dataset data are disconnected from regular
database, only optimistic locking can be used when
updating the regular database with the dataset
• In the case of conflict, either the dataset must be
reprocessed or the data change must be forced onto the
database, causing the lost update problem
• Thus, datasets cannot be used for applications in which
optimistic locking is problematical
– Instead, the ADO.NET command object should be used
JDBC & Servlet
Outline
HTML Forms
 Redirecting Request to other resources
 Tomcat
 Functions in JDBC & Servlet

HTML Forms
An interface controls to collect data from the user and transmit it to server.
Element in Forms

TEXT CONTROLS:
<INPUT TYPE="TEXT" NAME="NAME" VALUE="INIT">

PASSWORD FIELDS:
<INPUT TYPE="PASSWORD" NAME="PASSWORD">

TEXT AREAS:
<TEXTAREA NAME="RESUME" ROWS=5 COLS=30>INPUT YOUR RESUME HERE
</TEXTAREA>

Checkbox
<input type="checkbox" name="checkbox" checked>
<input type="checkbox" name="checkbox">

Radio Button
<input type="radio" name="radio" checked>
<input type="radio" name="radio">
Cont.


List
<select name="list">
<option value="Item 1">Item 1</option>
<option value="Item 2">Item 2</option>
<option value="Item 3">Item 3</option>
</select>
Multilist
<select name="multilist" size="3" multiple>
<option value="Item 1">Item 1</option>
<option value="Item 2">Item 2</option>
<option value="Item 3">Item 3</option>
</select>
Cont.




Submit Button
<input type="submit" name="submit" value="Submit">
Reset Button
<input type="reset" name="reset" value="Reset Fields">
Image Button
<input type="image" name="image" src="go.gif">
File
<input type="file" name="file">
Tomcat


A light web server that supports servlet & JSP.
It can be integrated in Apache, IIS
What is JDBC & Servlet?
JDBC (Java DataBase Connectivity) provides
functions to access database system.
 Servlet enables java for CGI programs.
 Setup JDBC environment:

JDBC : Establishing a Connection

loading the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

making the connection
String url = "jdbc:oracle:thin:@ra.msstate.edu:1521:ACAD";
Connection con = DriverManager.getConnection(url, “loginName", “Password");
Statement

Create a statement
Statement stmt = con.createStatement();

Two methods of statement
1. executeUpdate()
create, alter, drop a table
Or
insert, delete, update data
2. executeQuery()
select
Create Table

String createTableCoffees = "CREATE
TABLE COFFEES " + "(COF_NAME
VARCHAR(32), SUP_ID INTEGER, PRICE
FLOAT, " + "SALES INTEGER, TOTAL
INTEGER)";
stmt.executeUpdate(createTableCoffees);
Query Data from a Table

stmt.executeQuery (“select * from customer”);

ResultSet rs = stmt.executeQuery( "SELECT COF_NAME,
PRICE FROM COFFEES");
Display Result


Method next()
Initially the cursor is above the first row of data. After call the method
next(), the cursor is pointing to the first row of data.
A Sample
while (rs.next())
{
String s = rs.getString ("COF_NAME");
float n = rs.getFloat ("PRICE");
System.out.println (s + " " + n);
}
References: http://java.sun.com/docs/books/tutorial/jdbc/index.html