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
S E D A T A B A
The ADO.NET Object Model
Data Provider Handles communication with a physical data store.
Connection Adapter DataSet Provides general disconnected access to data The actual data Knows how to do SQL commands on the database Command 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
I E C L N T 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 of a Command object ExecuteReader method 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 Like the column defintions from a SQL CREATE TABLE statement Name, Data Type, Max Length, Allows Nulls Rows 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
S E D A T A B A
Summary
Data Provider DataAdapter Connection Command DataReader DataSet ADO.NET
I E C L N T 18