ADO.NET
Objectives
Introduce
Show
Microsoft® ADO.NET
the evolution of ADO to ADO.NET the primary components of ADO.NET
Introduce
Contents
Differences Between ADO and ADO.NET
Benefits of ADO.NET
ADO.NET Core Concepts and Architecture
The ADO.NET Object Model The DataSet and Data Views Managed Providers
ADO vs. ADO.NET
ADO
Designed for connected access Tied to the physical data model The RecordSet is the central data container RecordSet is one (1) table that contains all the data
Retrieving data from > 1 table or source requires a database JOIN Data is “flattened”: lose relationships; navigation is sequential
Data types are bound to COM/COM+ data types Data sharing via COM marshalling Problems marshalling through firewalls (DCOM, binary)
ADO vs. ADO.NET
ADO.NET
Designed for disconnected access Can model data logically! The DataSet replaces the RecordSet DataSet can contain multiple tables
Retrieving data from > 1 table or source does not require a JOIN Relationships are preserved: navigation is relational
No data type conversions required XML, like HTML, is plaintext: “Firewall friendly”
ADO.NET Goals
New
applications have become loosely coupled based on the Web application model
data architecture,
Disconnected
Tight integration with XML, data representation with the ability to combine data from multiple and varied data sources. facilities for interacting with a database, all native to the .NET Framework.
Common
Optimized
ADO.NET and the .NET Framework
Microsoft .NET Framework
Web Services User Interface
Data and XML
ADO.NET XML ... ...
Base Classes Common Language Runtime
Core Concepts and Architecture
The
ADO.NET Object Model
DataSet - core component of the disconnected architecture .NET Framework data provider
Used for connecting to a database, executing commands, and retrieving results.
core objects that make up a .NET Framework data provider.
.Net Hierarchy & Core Object
Core
object
Connection - provides connectivity to a data source Command - enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information
DataReader - provides a high-performance stream of data from the data source.
DataAdapter - provides the bridge between the DataSet object and the data source
.Net
hierarchy
System.Data.SqlTypes System.Data.Odbc
System.Data System.Data.OleDb System.Data.SqlClient
System.Data.Common System.Data.OracleClient
How ADO.NET Works …
Client (Console,Win,Web)
DataSet
Command
DataSetCommand
DataReader
Connection
Managed Provider
Data Source
System.Data Namespace
Contains
the basis and bulk of ADO.NET
namespace
Data-centric Provides
the means to work on and with your data!
Classes and methods to manipulate your data Ability to create views of your data Means to logically represent your data Enables the use of XML to view, share, and store data
Introducing the Objects…
System.Data
DataSet DataTable DataRow DataColumn DataRelation
Contains
the “main” classes of ADO.NET of data of a database table
In-memory cache In-memory cache Used
Used Used
to manipulate a row in a DataTable
to define the columns in a DataTable
to relate 2 DataTables to each other
DataViewManager Used
to create views on DataSets
Putting the Objects Together…
DataSet
Tables
DataTable
DataRow(s)
DataView
Relations
DataRelation DataRelation
DataColumn Constraint(s)
DataViewManager
DataTable
DataTable
Working Data - The DataSet
An
in-memory cache of data from a data source to represent and manipulate data
Universal data container Not just for use with databases
Common way
Logical
or physical representation of data to be disconnected from the data source
Designed
Connect, execute query, disconnect
Can
use XML
To read and write data To read and write XMLSchema
XML and the DataSet
DataSet
can read/write XML for its data and/or schema
You can create or modify data in a DataSet using XML You can create or modify the DataSets schema using XML
XML-related
DataSet methods for reading:
ReadXml: Reads an XML schema and data into the DataSet ReadXmlSchema: Reads an XML schema into the DataSet
And
for writing:
property: sets the namespace for serialization
WriteXml, WriteXmlSchema
Namespace
.NET Data Providers Hierarchy
.Common
Contains classes shared by both
System.Data
.SqlClient SqlCommand SqlConnection SqlDataReader SqlDataAdapter
.OleDb OleDbCommand OleDbConnection OleDbDataReader OleDbDataAdapter
OleDbConnection and SqlConnection
Represent Create,
a unique session with a data source
open, close a connection to a data source and methods to perform transactions
example:
Functionality
OleDbConnection
String conStr="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=NWIND_RW.MDB"; OleDbConnection aConn = new OleDbConnection(conStr); aConn.Open(); // Execute Queries using OleDbDataAdapter Class aConn.Close();
OleDbDataAdapter Class
Bridge
between the DataSet and the data store
to modify the DataSet and data source
Inherited from the DataAdapter class
Means
data store
DataAdapter
DataSet
OleDbDataAdapter Class
Properties
of Interest:
DeleteCommand: The delete command expressed in SQL InsertCommand: Gets or sets insert command SelectCommand: Gets or sets select command UpdateCommand: Gets or sets update command TableMappings: Maps source table and a DataTable
OleDbCommands retrieved or set by command properties
Implements abstract
methods of the DataAdapter class:
public abstract int Fill( DataSet dataSet ); public abstract int Update( DataSet dataSet );
OleDbCommand Class
Represents
a query to execute on the data source
May be a SQL statement or stored procedure
Properties
of Interest:
Connection: Get or set the data source connection CommandText: Get or set the query (text) command
A SQL statement or the name of the stored procedure
CommandType: Get/set how the command is interpreted
Text, StoredProcedure, or TableDirect
CommandTimeout: The seconds until connection timeout
OleDbDataReader
Forward-only
“Lightweight”
data access
programming model
Less overhead than using OleDbDataAdapter
Instantiated & Ties
returned by OleDbCommand.ExecuteReader
up the OleDbCommand until it is finished reading
OleDbDataReader
Properties
of Interest:
FieldCount: Returns the number of fields in the result set RecordsAffected: Number of affected records
Methods
to retrieve data:
By column type and/or index: GetValue; GetString; etc. Read(): Advances reader to next record NextResult(): Advanced to next result set in batch GetValues(): Gets the current row
Benefits of ADO.NET
Interoperability through use of XML
Open standard for data that describes itself Human readable and decipherable text Used internally but accessible externally
Can use XML to read and write and move data
Scalability through the disconnected DataSet
Connections are not maintained for long periods Database locking does not occur
Locking support with ServiceComponents Optimistic locking otherwise
Maintainability
Separation of data logic and user interface
Summary
ADO.NET
It
is the evolution of ADO
is a disconnected, Web-centric model
Flexible
in its ability to work with data your ability to logically organize data support for XML
Increases Extensive
Facilitates working with and sharing data
Interacts
with a wide variety of data sources