Transcript Introduction to ADO.NET - University of South Florida

Introduction to ADO.NET
Microsoft ADO.NET 2.0 Step by Step
Rebecca M Riordan
Microsoft Press, 2006
1
Introduction to ADO.NET
“Active Data Objects”



A set of class defintions included in the
.NET Framework
Objects collaborate to provide .NET
applications (relatively) easy access to
databases.
Designed for scalability.


100’s or 1000’s of clients on a database server
Offload functionality from server to client
2
The ADO.NET Object Model
Data Provider
T
Handles
communication
with a physical
data store.
A
Connection
D
A
B
Adapter
DataSet
Provides general
The actual data
disconnected access to
data
Knows how to do SQL commands
on the database
Command
A
S
E
C
L
I
E
N
T
Reader
A light weight object for
sequential read-only access to a
query result.
ADO.NET
http://msdn.microsoft.com/en-us/library/a6cd7c08.aspx
3
Data Provider

Collection of Class Definitions

Specific to a data source



MS SQL Server
Oracle
(others)

Same set of classes for each data source

Class name differs



SqlDataConnection vs OracleDataConnection
SqlCommand vs OracleCommand
etc.
4
Data Provider Objects
Connection



Controls communication with data source
Properties:

ConnectionString



Identifies server and database
User ID and password (if external)
Methods


Open
Close
5
Connection String
MS SQL Server Examples
connStr = "server=scorpius.eng.usf.edu; User ID=turnerr;
Password=xxxxxxx"
connStr = "server=mssql06.discountasp.net; database=DB_110074;
User ID=rollinsturn; Password=xxxxx"
connStr = "server=(local)\\VSDOTNET; database=Bulk_Mail_Addresses;
Trusted_Connection=yes";
6
Data Provider Objects

Command Object


Knows how to execute a SQL command on a server
Properties:

CommandText




A SQL statement to be executed at the data source.
SqlCommand1.CommandText = "SELECT * FROM Address_List";
Can be changed by the program.
Methods



ExecuteReader
ExecuteScalar
ExecuteNonquery
7
Data Provider Objects





DataReader
A fast, low-overhead object, similar to a
StreamReader for file input.
Provides forward-only, read-only stream of
data from a data source
Created by calling the ExecuteReader method
of a Command object
 Never with “new”
Connection must remain open while the
DataReader is used.
8
Data Provider Objects

There are two kinds of “Adapters”

Data Adapter



Present in ADO 1.0
Continued in ADO 2.1
Table Adapter

New in ADO 2.0
9
DataAdapter

Provides access to a collection of data from a
data source




Pemits client to close connection while processing
the data.
Effectively provides a local cache
Client accesses the cache rather than the actual
database.
Actual database can be updated when desired.
10
Table Adapter


Includes a DataAdapter and a Connection
object.
Improves functionality and ease of use
of the original DataAdapter.
11
Data Provider Objects

DataAdapter

Contains four Command Objects:






SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
Uses SelectCommand to fill a DataSet
Uses other command objects to transmit
changes back to the data source
12
The DataSet

In-memory copy of data

No connection to a database.


No knowledge of source or destination of
that data that it contains.
Simple form of relational database


Has a collection of tables
Has a collection of DataRelations
13
The DataSet
From ADO.NET 2.0 Step by Step
14
The DataSet

The DataTable

Columns



Rows


Like the column defintions from a SQL
CREATE TABLE statement
Name, Data Type, Max Length, Allows Nulls
The data
Constraints


Foreign Key
Unique
15
The DataSet

Data Relation

Programmatic interface for navigating from a
master row in one table to related rows in
another.


Example: Order ID in an Invoice to matching
Order IDs in all Line Items for that invoice.
Does not enforce referential integrity

A foreign key constraint does that.
16
The DataSet


A DataSet object can exist independently
of any database.
We will only use DataSets to hold data
retrieved from a database.
17
Summary
Data Provider
D
DataAdapter
DataSet
A
C
T
L
A
B
Connection
I
Command
A
S
E
E
N
DataReader
T
ADO.NET
18