ADO .Net

ADO.NETADO.NET
.NET Data Access and Manipulation
Dr. Sonali Vyas

OverviewOverview
What is ADO.NET?
ADO.NET Architecture
Ado.NET Data Providers
ADO.NET Core Objects
Disconnected Data Objects
Connected Data Objects
Understanding .NET Data Providers

What is ADO.NET?What is ADO.NET?
A data-access technology that enables
applications to connect to data stores and
manipulate data contained in them in
various ways
An object oriented framework that allows
you to interact with database systems

Objective of ADO.NETObjective of ADO.NET
Support disconnected data architecture,
Tight integration with XML,
Common data representation
Ability to combine data from multiple
and varied data sources
Optimized facilities for interacting with a
database

ADO.NET ArchitectureADO.NET Architecture

ADO.NETADO.NET Data ProvidersData Providers
Core namespace: System.Data
.NET Framework data providers:
Data Provider Namespace
SQL Server System.Data.SqlClient
OLE DB System.Data.OleDb
ODBC System.Data.Odbc
Oracle System.Data.OracleClient

ADO.NET Core ObjectsADO.NET Core Objects
Object Description
Connection Establishes a connection to a specific data source.
(Base class: DbConnection)
Command Executes a command against a data source. Exposes
Parameters and can execute within the scope of a
Transaction from a Connection. (The base class:
DbCommand)
DataReader Reads a forward-only, read-only stream of data from a
data source. (Base class: DbDataReader)
DataAdapter Populates a DataSet and resolves updates with the
data source. (Base class: DbDataAdapter)
DataTable Has a collection of DataRows and DataColumns
representing table data, used in disconnected
model
DataSet Represents a cache of data. Consists of a set of
DataTables and relations among them

Connection objectConnection object
Connects to databases.
Two provider-specific classes
oSqlConnection
oOleDbConnection.
Connections can be opened in two ways:
oExplicitly by calling the Open method on the
connection
oImplicitly when using a DataAdapter.
Connections handle transactions

Connection (Cont.)Connection (Cont.)
String connString = string.Empty;
connString = “server = sqlexpress; integrated security =
true”; // Window Authentication
//OR
connString = “server = sqlexpress; user id = sa; password =
1234567”; // SQL Authentication
SqlConnection conn = new SqlConnection(connString);
Conn.Open();
//
// Code
//
Conn.Close();

ConnectedConnected DataData ObjectsObjects
ADO.NET’s connected architecture relies on a
consistent database connection to access data and
perform any operations on the retrieved data.
ADO.NET offers the following objects to help you
build your application with a connected
architecture:
• Command
• DataReader

Command ObjectCommand Object
 Information submitted to a database as a query via a
Connection object
 Two provider-specific classes
o SqlCommand
o OleDbCommand
 Input and output parameters are supported, along with
return values as part of the command syntax
 Results are returned in the form of streams. Accessed
by:
o DataReader object
o DataSet object via a DataAdapter

Command (Cont.)Command (Cont.)
Commands have several different methods for executing
SQL. The differences between these methods depend on
the results you expect from the SQL.
Queries return rows of data (result sets), but the INSERT,
UPDATE, and DELETE statements don’t.

DataReader ObjectDataReader Object
Provides methods and properties that deliver a
forward-only stream of data rows from a data
source
When a DataReader is used, parts of the
ADO.NET model are cut out, providing faster
and more efficient data access

Connected Data Access ModelConnected Data Access Model

Steps of Data Acces : ConnectedSteps of Data Acces : Connected
EnvironmentEnvironment
Create connection
Create command (select-insert-update-delete)
Open connection
If SELECT -> use a DataReader to fetch data
If UPDATE,DELETE, INSERT -> use command
object’s methods
Close connection

Disconnected Data ObjectsDisconnected Data Objects
ADO.NET’s disconnected architecture offers
flexible application design and helps organizations
save database connections. ADO.NET offers the
following objects to help you build your
application with a disconnected architecture:
•DataAdapter
• DataSet

DataAdapter ObjectDataAdapter Object
Provides a set of methods and properties to
retrieve and save data between a DataSet and
its source data store
Allows the use of stored procedures
Connects to the database to fill the DataSet and
also update the database

DataSet ObjectDataSet Object
Replaces the ADO Recordset
Represents a cache of data that contains tables,
columns, relationships, and constraints, just like
a database
Regardless of where the source data comes
from, data can all be placed into DataSet
objects
• Tracks changes that are made to the data it
holds before updating the source data
 DataTable
 DataColumn
 DataRow

DataSet (Cont.)DataSet (Cont.)

Disconnected Data Access ModelDisconnected Data Access Model

Steps of Data Access: DisconnectedSteps of Data Access: Disconnected
EnvironmentEnvironment
Defining the connection string
Defining the connection
Defining the command
Defining the data adapter
Creating a new DataSet object
SELECT -> fill the dataset object with the result of
the query through the data adapter
Reading the records from the DataTables in the
datasets using the DataRow and DataColumn
objects
UPDATE, INSERT or DELETE -> update the
database through the data adapter

Pros and ConsPros and Cons
Connected Disconnected
Database
Resources
- +
Network Traffic - +
Memory Usage + -
Data Access - +

Understanding .NET Data ProvidersUnderstanding .NET Data Providers

Commonly Used SqlClient ClassesCommonly Used SqlClient Classes

Commonly Used OleDb ClassesCommonly Used OleDb Classes

Commonly Used Odbc ClassesCommonly Used Odbc Classes

SummarySummary
This presentation was about two main types of
data access that are provided from ADO.NET;
Connected Data Objects and Disconnected Data
Objects. Both types have their own advantages
to fulfill the full-functionality to access data. Both
types have their own main components.
•Connected Data Objects : Connection,
Command, and DataReader.
•Disconnected Data Objects : DataSet,
DataAdapter, DataTable, DataColumn and
DataRow.

ADO .Net

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to ADO .Net

Similar to ADO .Net (20)

Recently uploaded

Recently uploaded (20)

ADO .Net