Module 13 :

ADO.NET 2.0

By

SRIRAM . B
Objective
 Introduction to ADO.NET & Features of ADO.NET
 ADO.NET Architecture
 Connecting database through Connection Object
 Executing Queries through Command Object
 Retrieving records through DataReader
 Connect to a database by using DataAdapter
 Dataset Object Model
 Accessing Databases through Dataset
 Command Builder
 Data Relation Object
 ADO.NET 2.0 Features
Introduction to
ADO.NET
Introduction to ADO.NET

 It is a set of classes used with Microsoft.NET Framework language to

access data in a relational, table oriented format.

 ADO.NET classes are located in the System.Data.dll assembly.

 This includes all of the System.Data Namespace and one class from
the System.XML Namespace.

 ADO.NET takes its name from ADO(ActiveX Data Objects)

 Is a model used by Visual Basic .NET / C# applications to

communicate with a database for retrieving, accessing, and
updating data.

 Uses a structured process flow to interact with a database

Diff. between ADO & ADO.NET
ADO ADO.NET
 Connected Model  Disconnected Model
 Join  Data Relation Object
 Recordset  DataSet
 Data Transmission by TCP/IP  Data Transmission by
XML
 Stateless
 Limited Security
 Stateful

 Locking is possible
 Better Security
 Data persist in Binary format  Locking is not reqd.
 Data persist in XML
Diff. between ADO Recordset &
ADO.NET Dataset
ADO.NET Dataset ADO Recordset
 Holds more than one table  Holds only one table
 You can retrieve data from two
databases like oracle, Sql server  Not Possible
and merge them in one dataset

 Representation using XML

 Can be transmitted on HTTP

 Representation using COM

 Cannot be transmitted on
HTTP
Namespaces in ADO.NET
 System.Data
 System.Data.OleD
B
 System.Data.SqlCl
ient
 System.XML
Features of
ADO.NET
Features of ADO.NET

 Disconnected data architecture — Applications

connect to the database only while retrieving
and updating data.

 Data cached in datasets — ADO.NET is based

on a disconnected data structure. Therefore,
the data is retrieved and stored in datasets.

 Data transfer in XML format — ADO.NET uses

XML for transferring information from a
database into a dataset and from the dataset
to another component.

 Interaction with the database is done through

 ADO.NET
Architecture
ADO.NET Architecture
Data Provider & Data Consumer
Object
Data Provider Data Consumer
Object Object
 Connection  Data Set
 Command  Data Table
 Command Builder  Data Row
 DataReader  Data Column
 DataAdapter  Data Relation
ADO.NET Data
Provider
DataProvider

 Classes are specific to a data source

 Is used for connecting to a database, retrieving data, and
storing the data.
 Is of two types:

OLE DB data provider -> Provides optimized access to
SQL Server 6.5, Oracle,
Sybase, DB2/400 & Microsoft Access.

SQL Server data provider –> Provides optimized access
to SQL Server 2000/7.0
Components of Data
Provider
Components of DataProvider
 Connection
 Command
 Command Builder
 DataReader
 DataAdapter
Connection
Object
Connection Object

 Used to establish a connection with a data

source

 A OLEDB Connection object is used with an

OLEDB Provider

 A SQL Connection object uses TDS with

Microsoft SQL Server

Some commonly used properties , methods and

events :-
property - ConnectionString property
method - Open()method, Close()method
event - StateChange event
Connection String Property
 Provides information that defines a connection to a
data store by using string of parameters
 Parameters
 Provider
 Connection
 Initial Catalog
 Data Source
 Password
 User ID
 Integrated Security
Connection String Property For SQL
Server 2000/2005
 For Windows based authentication ( By Active Directory
Service)

SqlConnection conn = new

SqlConnection("DataSource=stg1\\sqlexpress; Initial

Catalog=Northwind;Integrated Security=True");

 For SQL Server authenication

SqlConnection conn = new

SqlConnection("DataSource=stg1\\sqlexpress; Initial

Catalog=Northwind;Integrated Security=True");
 Connection String Property For Oracle, MS
Access & SQL Server 6.5
 For Oracle
OleDbConnection o = new OleDbConnection(“Provider = MSDAORA;
Data Source= ORACLE817; Userid = OLEDB; Password=
OLEDB;”)
 For MS Access

OleDbConnection o = new OleDbConnection(“Provider =

Microsoft.Jet.OLEDB 4.0; Database Source=
c:\bin\LocalAccess40.mdb; ”)
 For SQL Server 6.5
OleDbConnection o = new OleDbConnection(“Provider = SQLOLEDB;
Data Source= STG1; Initial Catalog= pubs; Userid = sa ;
Password= sa;”)
Connection Pooling

 Is the process of keeping connections active and pooled so that

they can be efficiently reused

 Connection with identical connection strings are pooled together

and can be reused without re-establishing the connection.

 If the connection string is different, then a new connection will

be opened, and connection pooling won't be used.

 Advantage :- Improved Application performance & Scalability

Enhancement.

 Parameters :- Connection Lifetime, Max Pool Size, Min Pool Size,

Pooling
Connection Pooling Action
Command
Object
Command Object

 Is a SQL statement or a stored procedure that

is used to retrieve, insert, delete, or modify
data from a data source.

 Is an object of the OleDbCommand or SQLCommand

class.
Command Object Properties
 Connection -> A reference to a connection object that the
command will use to communicate with the database. You can select an
existing connection from the list or create a new connection

 CommandType -> A value specified by the CommandType

enumeration, indicating what type of command you want to execute
Text. It may be a SQLStatement /StoredProcedure/TableDirect

 CommandText -> The command is to execute. It depends on the

value of the command type property.

 Parameters -> A collection of objects of the type SQLParameter

or OLEDbParameter. You use this collection to pass parameters into the
command and to retrieve output parameters from the command.
Command Object - Methods
 Methods
 ExecuteScalar() ->
Executes a command that returns a single value
 ExecuteReader() ->
Executes a command that returns a set of rows
 ExecuteNonQuery() ->

Executes a command that updates the database

or changes the database structure. The methods
returns the number of rows affected.
 ExecuteXMLReader() ->
Executes a command that returns an XML result
Example 1 – Execute Scalar
using System;
using System.Data;
using System.Data.SqlClient;
namespace SriConsole.ADO.NET
{
public class ExecuteScalar1
{
static void Main(string[] args)
{
String empname;

SqlConnection Con = new SqlConnection("data

source=.\\sqlexpress;initial catalog=db;
Integrated Security=SSPI");
Con.Open();
Example 1 – Execute Scalar

SqlCommand Cmd = new SqlCommand("Select empname

from emp where empid=2",Con);

empname = Cmd.ExecuteScalar().ToString ();

Console.WriteLine(empname);

Con.Close();
Console.ReadLine();
}
}
}
Example 2 – Execute Scalar
using System;
using System.Data;
using System.Data.SqlClient;
namespace SriConsole.ADO.NET
{
public class ExecuteScalar2
{
static void Main(string[] args)
{
String empname;
int exp;

SqlConnection Con = new SqlConnection("data

source=.\\sqlexpress;initial catalog=db;
Integrated Security=SSPI");
Con.Open();
Example 2 – Execute Scalar
SqlCommand Cmd = new SqlCommand("Select max(exp)
from emp ",Con);

empname = Cmd.ExecuteScalar().ToString ();

exp= Convert.ToInt32(Cmd.ExecuteScalar ());

Console.WriteLine(empname);
Console.WriteLine (exp);

Con.Close();
Console.ReadLine ();
}
}
}
Example 1 – Execute Reader
using System;
using System.Data;
using System.Data.SqlClient;

namespace SriConsole.ADO.NET
{
public class ExecuteReader
{
static void Main(string[] args)
{

SqlConnection Con = new SqlConnection("data

source=.\\sqlexpress;initial catalog=db;
Integrated Security=SSPI");

Con.Open();
Example 1 – Execute Reader..
SqlCommand Cmd = new SqlCommand("Select * from emp",Con);
SqlDataReader dr = Cmd.ExecuteReader();
while(dr.Read())
{
int empid = Convert.ToInt32(dr.GetValue(0));
string empname= dr.GetString(1);
int exp = Convert.ToInt32(dr.GetValue(2));
Console.WriteLine (empid+""+empname+""+exp);
}

dr.Close();
Con.Close();
Console.ReadLine ();
}
}
}
Example 1 – Execute NonQuery()
using System;
using System.Data;
using System.Data.SqlClient;
namespace SriConsole.Ado.net
{

public class SqlInsert

{

static void Main(string[] args)

{

SqlConnection Con =
new SqlConnection("data source=.\\sqlexpress;
initial catalog=db; Integrated Security=SSPI");

Con.Open();
Example 1 – Execute NonQuery()..

SqlCommand Cmd = new SqlCommand("insert into emp

values(10,'J',10,'d3')",Con);
Cmd.ExecuteNonQuery();
Con.Close();
Console.ReadLine();
}
}
}
Example 2 – Execute NonQuery()
using System;
using System.Data;

using System.Data.SqlClient;
namespace SriConsole.ADO.NET
{
public class SQLUserInput
{
static void Main(string[] args)
{
SqlConnection Con = new
SqlConnection("data source=.\\sqlexpress;initial
catalog=db; Integrated Security=SSPI");

Con.Open();
Example 2 – Execute NonQuery()..
for(int i=0;i<=2;i++)
{
Console.WriteLine("Enter the eid");
int empid = Convert.ToInt32(Console.ReadLine());

Console.WriteLine("Enter the ename");

string empname = Console.ReadLine ();

Console.WriteLine("Enter the sal");

int exp = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter the dept name");
string dname = Console.ReadLine();
Example 2 – Execute NonQuery()..
SqlCommand cmd = new SqlCommand ("insert into emp
values("+empid+",'"+empname+"',"+exp+")",Con);

cmd.ExecuteNonQuery();
Con.Close();
}
}
}
}
Command
Builder
Command Builder

 It builds “Parameter” objects automatically

 Used to build SQL commands for data modifications
from objects based on a single table query.
 Are of two types :-
 SQLCommandBuilder for SQL Server
 OleDbCommandBuilder for OLEDB
Example – Command Builder
using System;
using System.Data;
using System.Data.SqlClient;
namespace SriConsole.ADO.NET
{
public class CBuilder
{
public static void Main()
{

SqlConnection c = new SqlConnection("data

source=.\\sqlexpress;initial
catalog=db;Integrated Security=SSPI");

c.Open();
Example – Command Builder..
SqlDataAdapter da = new SqlDataAdapter("select *
from employee",c);
DataSet ds=new DataSet();
da.Fill(ds);
DataRow dr=ds.Tables[0].NewRow();
dr["empid"]=107;
dr["empname"]="k";
ds.Tables[0].Rows.Add(dr);
SqlCommandBuilder cb=new SqlCommandBuilder(da);
da.Update(ds.Tables[0]);
Console.WriteLine(ds.Tables[0].Rows[0][1]);
Console.WriteLine(ds.Tables[0].Rows.Count);

}
}
}
DataReader
Data Reader
 Is used to retrieve data from a data source in a read-
only and forward-only mode.
 Stores a single row at a time in the memory.
 Are of two types :-
•SQLDataReader for SQL Server
•OleDbDataReader for OLEDB
Commonly used methods:
• Read()
• Close()
• NextResult()
DataAdapter
DataAdapter
 Creates a dataset and updates the database.
 Handles data transfer between the database and
the dataset through its properties and methods.
 Displays the data through the process of table
mapping.
 Are of two types:
• SqlDataAdapter
• OleDbDataAdapter
 Methods :- Fill, FillSchema, Update
DataAdapter Methods
 Fill
Executes the command and fill the Dataset object with
data from the datasource
 FillSchema

Uses the Select command to extract just the schema for a

table from the datasource, and creates an empty table in the
Dataset object with all the corresponding constraints.
 Update

Calls the respective Insert Command, Update Command or

Delete Command for each inserted, updated or deleted row in
the dataset so as to update the original datasource with the
changes made to the content of the DataSet. Dataset can
update more than one table.
Example 1 - DataAdapter
using System;
using System.Data;
using System.Data.SqlClient;

namespace SriConsole.ADO.NET
{
public class SelectAdapter
{
static void Main(string[] args)
{
SqlConnection Con = new SqlConnection("data
source=.\\sqlexpress;initial catalog=db; user
id=sa");
Con.Open();
Example 1 - DataAdapter..
SqlDataAdapter da = new SqlDataAdapter ("Select *
from emp",Con);
DataSet ds = new DataSet();
da.Fill(ds,"emp");
Console.WriteLine(ds.Tables[0].Rows[0]
[0].ToString());
Console.ReadLine();

}
}
}
Data Consumer Object

 Data Set

 Data Table

 Data Row

 Data Column

 Data Relation
DataSet
DataSet Object Model
DATASET

DataRelationCollection DataTableCollection ExtendedProperties

DataRelation
DataTable

DataRowCollection DataView PrimaryKey DataColumnCollection

Data Row DataColumn

DataSet
 Is present as a DataSet class in the System.Data
namespace.
 Has its own object model.
 Is a disconnected, cached set of records that are
retrieved from a database.
 DataSet Object
•Collection of DataTable (Collection of Data Row &
Data Column)
•Constraints Collection (Primary Keys,
Constraints,Default Values)
•Parent -Child Relation between tables by DRO
•Dataview( Data can be searched, filtered or
manipulated while displaying data )
Types of DataSet

 Typed DataSet
 Untyped
DataSet
Typed DataSet

 Is derived from the DataSet class and has an

associated XML schema, which is created at the time
of the creation of the dataset.

 Can be customized after creation.

 Supports Intellisense and auto‑completion for the

elements of the syntax while writing code.
Untyped of DataSet

 Does not have any associated XML schema,

therefore, the structure of an untyped dataset is not
known at the compile time.

 Represents tables and columns as collections.

 Does not support Intellisense and auto‑completion

for the elements of the syntax.
DataSet Methods
 Accept Changes
It commits all the changes since last time “Accept
Changes” has
been executed.
 Reject Changes
It uncommits the changes
Tracking Changes in DataSet
For tracking down changes Dataset has two methods
which comes as rescue
“GetChanges” and “HasChanges”.
 GetChanges

Returns dataset which are changed since it was

loaded or since Accept changes was executed.

 HasChanges

Indicates that has any changes been made since the dataset
was loaded or accept changes method was executed.
Diff. Between Dataset & DataReader
 Dataset is a disconnected architecture, while DataReader has a live
connection while reading data. To catch data & pass to different tier
“Dataset” forms the best choice using XML.

 When application needs to access data from more than one table
“Dataset” forms the best choice where as “DataReader” holds only one
table.

 DataReader provides Forward only & Read only access to data And does
not support for moving back while reading records.

 Dataset can persist contents while DataReader can't persists contents.

 Dataset is slower than DataReader because it has relations, multiple

tables etc..
DataTable
DataTable
 Provides a “NewRow” method to add a new row to DataTable.

 It has “DataRowCollection” object which has all rows in a

DataTable object.

 Methods of “DataRowCollection” object :-

 Add -> Add a new row in DataTable

 Remove -> Removes a DataRow object from DataTable

 RemoveAt -> Removes a DataRow object from DataTable

depending on
index position of the DataTable.
DataView
DataView
 Represents a complete table or can be small section of rows
depending on the some criteria

 Used for sorting and finding data in the DataTable.

 Methods :-
 Find :- Takes array of values and returns the index
of the row
 FindRow :- Takes array of values but returns a
collection of DataRow
 AddNew :- Adds a new row to the DataView
 Delete :- Deletes the specified row from DataView

If we want to manipulate data of DataTable object create

DataView of the DataTable object and use these
functionalities :- AddNew, Delete
DataRelation
Object
DataRelationObject
 It defines the navigational relationship between
two tables. Typically, two tables are linked through
a single field that contains the same data.
Example 1 – DataRelation Object
using System;
using System.Data;
using System.Data.SqlClient;

namespace SriConsole.ADO.NET
{
class DRO
{

static void Main(string[] args)

{
try
{

SqlConnection con=new SqlConnection("data

source=.\\sqlexpress;initial catalog=db;Integrated
Security=SSPI");
con.Open();
Example 1 – DataRelation Object..
DataSet ds=new DataSet();

SqlDataAdapter da1=new SqlDataAdapter("select * from

dept",con);
da1.Fill(ds,"dept");
SqlDataAdapter da2=new SqlDataAdapter("select * from
emp",con);
da2.Fill(ds,"emp");

DataRelation dr=ds.Relations.Add("emp
details",ds.Tables["dept"].Columns["dno"],ds.Tables[
"emp"].Columns["dno"]);
Example 1 – DataRelation Object..
foreach(DataRow dt in ds.Tables["dept"].Rows)
{
Console.WriteLine("\t Department No:"+dt["dno"]);

foreach(DataRow dt1 in dt.GetChildRows(dr))

{
Console.WriteLine("\t Employee No:"+dt1["empid"]);
Console.WriteLine("\t Employee Name:"+dt1["empname"]);
}
}
con.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
}
}
}
ADO.NET 2.0
Features
ADO.NET 2.0 Features

 Bulk Copy
 Asynchronous Processing
 Generic Coding with DB Provider
Factories
 Transaction Management
 State Management
Bulk Copy
Bulk Copy
 The SqlBulkCopy feature in ADO.NET 2.0 enables us
to copy a large volume of data between a source
data store and a destination data table.

 This class can be used to specify the source and the

target data sources for this copy operation.
Asynchronous
Processing
Synchronous with Asynchronous Calls
Asynchronous Processing
 In the earlier version of ADO.NET, the ExecuteReader,
ExecuteScalar and the ExecuteNonQuery methods used to
block the current executing thread. However, ADO.NET 2.0
supports asynchronous data access mode.

 In ADO.NET 2.0, these methods come with Begin and End

methods that support asynchronous execution.

Async Operations in ASP.NET
Async Operations in ASP.NET
WaitAll
Wait Any
Async Implemented Methods
Common Provider
Factory
Common Provider Factory
 In the earlier version of ADO.NET, if we wanted to implement a provider
independent Data Access Layer, we had to implement the Factory Design
Pattern where a class would have been responsible for returning the specific
type of Command,Data Reader, DataAdapter or Connection.

 In ADO.NET 2.0 we can create provider-independent data access code even

without referencing the provider-specific classes using the
System.Data.Common namespace that exposes a number of factory classes.

 The DbProviderFactory class contains two methods called the

GetFactoryClasses method and the Getfactory method. While the former is
responsible for retrieving all the providers supported, the later can be used
to retrieve the specific provider.
Code - DB Provider Factory
DbProviderFactory dbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClie
nt");

DbConnection dbConnection =
dbProviderFactory.CreateConnection();
Hierarchy
System.Data.Common.DbProvider.Fa
ctory
DB Provider Factory
Transactions
DB Transactions
Transaction – Concurrency Problems
Transactions Isolation Level
Settings Isolation Level
Transactions in ASP.NET 1.1
Reset Isolation Levels
Save Point in Transactions
Transactions
Demo
Setting the Save Point
Transaction
Handling
Transaction Scope
Transactions Completed Event
Setting Isolation Level
Session State
Configuration
Session State Configuration
Session State Management
In Process Mode
State Server Mode
SQL Server Mode
Disabling Session State
Demo
Session Ends
Exercise
Relax