ADO.NET is the strategic application-level interface for providing data access services in
the Microsoft .NET Platform. You can use ADO.NET to access data sources using new
.NET Data Providers, as well as existing OLE DB Data Providers using the OLE DB
.NET Data Provider.
ADO.NET Does Not Depend On Continuously Live Connections
In traditional client/server applications, components establish a connection to a database
and keep it open while the application is running. For a variety of reasons, this approach
is impractical in many applications:
Open database connections take up valuable system resources. In most cases,
databases can maintain only a small number of concurrent connections. The
overhead of maintaining these connections detracts from overall application
In ASP.NET Web applications, the components are inherently disconnected from
each other. The browser requests a page from the server; when the server has
finished processing and sending the page, it has no further connection with the
browser until the next request. Under these circumstances, maintaining open
connections to a database is not viable, because there is no way to know whether
the data consumer (the client) requires further data access.
A model based on always-connected data can make it difficult and impractical to
exchange data across application and organizational boundaries using a connected
architecture. If two components need to share the same data, both have to be
connected, or a way must be devised for the components to pass data back and
Data Can Be Cached in Datasets
A dataset is a cache of records retrieved from a data source. It works like a virtual data
store: A dataset includes one or more tables based on the tables in the actual database,
and it can include information about the relationships between those tables and
constraints on what data the tables can contain.
Datasets Are Independent of Data Sources
Data Is Persisted as XML
Comparison of ADO.NET and ADO
In-memory Representations of Data
In ADO, the in-memory representation of data is the recordset. In ADO.NET, it is the
dataset. There are important differences between them.
Number of Tables
A recordset looks like a single table
If a recordset is to contain data from multiple database tables, it must use a JOIN query, which
assembles the data from the various database tables into a single result table.
In contrast, a dataset is a collection of one or more tables.
The tables within a dataset are called data tables; specifically, they are DataTable objects
If a dataset contains data from multiple database tables, it will typically contain multiple
That is, each DataTable object typically corresponds to a single database table or view.
In this way, a dataset can mimic the structure of the underlying database.
A dataset usually also contains relationships
A relationship within a dataset is analogous to a foreign-key relationship in a database —
that is, it associates rows of the tables with each other.
Because the dataset can hold multiple, separate tables and maintain information about
relationships between them, it can hold much richer data structures than a recordset,
including self-relating tables and tables with many-to-many relationships.
Data Navigation and Cursors
In ADO you scan sequentially through the rows of the recordset using the ADO
In ADO.NET, rows are represented as collections, so you can loop through a table as you
would through any collection, or access particular rows via ordinal or primary key index.
DataRelation objects maintain information about master and detail records and provide a
method that allows you to get records related to the one you are working with
A cursor is a database element that controls record navigation, the ability to update data,
and the visibility of changes made to the database by other users.
ADO.NET does not have an inherent cursor object, but instead includes data classes that
provide the functionality of a traditional cursor.
For example, the functionality of a forward-only, read-only cursor is available in the
ADO.NET DataReader object
Minimized Open Connections
In ADO.NET you open connections only long enough to perform a database operation,
such as a Select or Update. You can read rows into a dataset and then work with them
without staying connected to the data source.
In ADO the recordset can provide disconnected access, but ADO is designed primarily
for connected access.
There is one significant difference between disconnected processing in ADO and
In ADO you communicate with the database by making calls to an OLE DB provider.
In ADO.NET you communicate with the database through a data adapter (an
OleDbDataAdapter or SqlDataAdapter object), which makes calls to an OLE DB
provider or the APIs provided by the underlying data source.
The important difference is that in ADO.NET the data adapter allows you to control how
the changes to the dataset are transmitted to the database — by optimizing for
performance, performing data validation checks, or adding any other extra processing.
Sharing Data Between Applications
Transmitting an ADO.NET dataset between applications is much easier than transmitting
an ADO disconnected recordset.
To transmit an ADO disconnected recordset from one component to another, you use
To transmit data in ADO.NET, you use a dataset, which can transmit an XML stream.
The transmission of XML files offers the following advantages over COM
Richer data types
COM marshalling provides a limited set of data types — those defined by the COM
standard. Because the transmission of datasets in ADO.NET is based on an XML format,
there is no restriction on data types. Thus, the components sharing the dataset can use
whatever rich set of data types they would ordinarily use.
ADO.NET offers another performance advantage, in that ADO.NET does not require
data-type conversions. ADO, which requires COM marshalling to transmit records sets
among components, does require that ADO data types be converted to COM data types.
A firewall can interfere with two components trying to transmit disconnected ADO
recordsets. Remember, firewalls are typically configured to allow HTML text to pass, but
to prevent system-level requests (such as COM marshalling) from passing.
Because components exchange ADO.NET datasets using XML, firewalls can allow
datasets to pass.
Namespaces to work with Data
The System.Data namespace consists mostly of the classes that constitute the ADO.NET
Constraint Represents a constraint that can be enforced
on one or more DataColumn objects.
ConstraintCollection Represents a collection of constraints for a
ConstraintException Represents the exception that is thrown
when attempting an action that violates a
DataColumn Represents the schema of a column in a
DataColumnChangeEventArgs Provides data for the ColumnChanging
DataColumnCollection Represents a collection of DataColumn
objects for a DataTable.
DataException Represents the exception that is thrown
when errors are generated using ADO.NET
DataRelation Represents a parent/child relationship
between two DataTable objects.
DataRelationCollection Represents the collection of DataRelation
objects for this DataSet.
DataRow Represents a row of data in a DataTable.
Provides data for the RowChanged,
DataRowChangeEventArgs RowChanging, OnRowDeleting, and
DataRowCollection Represents a collection of rows for a
DataRowView Represents a customized view of a DataRow
exposed as a fully featured Windows Forms
DataSet Represents an in-memory cache of data.
DataTable Represents one table of in-memory data.
DataTableCollection Represents the collection of tables for the
DataView Represents a databindable, customized view
of a DataTable for sorting, filtering,
searching, editing, and navigation.
OleDbType-- Specifies the data type of a field, a property, or an System.Data.OleDb.OleDbParameter
System.Data.Common – Common shared (or overridden ) by individual data providers
System.Data.SqlTypes – sqlsever datatypes
Working with Data Readers
Demonstration 1: Working with Connection,Command,Datareaders
Demonstration2 : Working with DML Operations
Demonstration3: Working with Procedures
public class a
public static void Main()
cn = new OleDbConnection("Provider=MSDAORA.1;Password=tiger;User
OleDbCommand com=new OleDbCommand();
com.CommandText = "in1";
com.CommandType = System.Data.CommandType.StoredProcedure;
Retrieving Return Values and Output Parameters
Create procedure getauthorcount as
Return (select count(*) from authors)
1) Open the Connection to the database- cn
Dim c As SqlCommand = New SqlCommand("getauthorcount", cn)
c.CommandType = CommandType.StoredProcedure
Dim p As SqlParameter = c.Parameters.Add("ReturnValue",
p.Direction = ParameterDirection.ReturnValue
Dim i As Integer = c.Parameters("ReturnValue").Value
Input,Output, InputOutput, returnvalue
Improving Performance with Connection Pooling:
Connection pooling is enabled for both oledb and sqlclient connections by default.
Oledbconnection pooling is handled by the oledb .net provider
Sqlclient connection pooling is handled by windows 2000 services
Connection LifeTime – default 0 that is conn should never get destroyed
Connection Reset - Indicates whether connections should be reset when they are
returned to the pool(true)
Enlist -- Indicates whether a connection should be automatically enlisted in the
current transaction context. The default value is true
Max Pool Size – 100
Min Pool Size – 0
Pooling - true
public class connectionpooling
public static void Main()
connectionpooling myconnectionpooling = new connectionpooling();
public void Run()
"connection reset=false;" +
"connection lifetime=5;" +
"min pool size=1;" +
"max pool size=50";
SqlConnection myConnection1 = new SqlConnection(connString);
SqlConnection myConnection2 = new SqlConnection(connString);
SqlConnection myConnection3 = new SqlConnection(connString);
// Open two connections.
Console.WriteLine ("Open two connections.");
// Now there are two connections in the pool that matches the
// Return the both connections to the pool.
Console.WriteLine ("Return both of the connections to the
// Get a connection out of the pool.
Console.WriteLine ("Open a connection from the pool.");
// Get a second connection out of the pool.
Console.WriteLine ("Open a second connection from the pool.");
// Open a third connection.
Console.WriteLine ("Open a third connection.");
// Return the all connections to the pool.
Console.WriteLine ("Return all three connections to the pool.");
catch (Exception e)
// Display the error.
3 levels – Database, ado.net, page
SqlConnection cn=new SqlConnection(“constring”);
SqlCommand cm1=new SqlCommand(“update1”);
SqlCommand cm2=new SqlCommand(“update2”);
<%@ transaction=”RequiresNew” %>
Disabled – no trans
Supported – if there supports
Required – if not there create a new
RequiresNew – always new
After you enable transactions for an ASP.NET page, the page
can use two methods from the ContextUtil Class to explicitly
commit or rollback a transaction: the SetComplete and SetAbort
Specifying a Command Behavior:
When you call the ExecuteReader() method of the command object
you can pass an optional CommandBehavior Parameter.
The command Behavior enumeration has the following values:
1)CloseConnection :- Automatically closes an open database connection after
the datareader is closed
2)KeyInfo :- Retrieves column and primary key with the data
3) SchemaOnly :- Retrieves column and table schema info without retrieving
4) SingleRow :- Optimizes command to retrieve only a single row. If multiple
rows are returned, additional rows are discarded
The CloseConnection command behavior is useful when you want to
return a DataReader from a function
Public SqlDataReader GetAuthors()
cm=new SqlCommand(“select * from authors”, cn);
Retrieving Table Schema Info
ExecuteXmlReader (SQL Provider Only)
As its name implies this method will execute the command and return
an XmlReader object to the caller.
SQL Server permits a SQL clause to be extended with a FORXML
Dim c As SqlCommand = New SqlCommand("select * from authors FOR XML
Dim xr As XmlReader = c.ExecuteXmlReader()
DataSets and DataReaders:
The two sets of classes are useful in different applications.
1)If you simply need to grab some database records and quickly display them
on a webpage, you should use the command and DataReader classes
If, On the other hand you need to work with a disconnected and memory
resident representation of database records, you need to use DataSets
2)Further more it to work, the DataReader must remain conncted to a
database table. A datareader is tied down to its underlying data source. This
means for example that you cannot cache a DataReader in your server’s
memory so that the same datareader can be used on multiple pages or over
multiple requests to the same page.
A dataset enables you to represent the results of a database query in yours
Because a DataSet provides you with a memory-resident representation of
data, you can work with the result of a database query as a whole. For ex a
dataset includes methods for sorting,filtering and returning a count of the
records from a database query.
3)DataReader is a forward only recordset
DataSets are very useful if you want to represent the same set of records over
and over again in multiple pages or across multiple requests to the same
If you need to display the diff. Set of records whenever you request a page,
use a datareader.
If you need to display the same set of records when u reques a page, you
should use a Dataset
A DataTable is a memory resident representation of a database
table in a dataset.
Typically, you create a DataTable from an existing database
However, you also have the option of creating a DataTable and
add it to DataSet.
A DataTable is collection of rows, represented by its Rows
property, and a collection of columns represented by columns property.
Dim t As DataTable
Dim c As DataColumn
Dim r As DataRow
t = New DataTable()
c = New DataColumn("id",GetType(Int32))
c = New DataColumn("name")
c = New DataColumn("age")
c = New DataColumn("sal")
r = t.NewRow
r("id") = 1
r("name") = "sss"
r("age") = 20
r("sal") = 10000
r = t.NewRow
r("id") = 2
r("name") = "sss2"
r("age") = 23
r("sal") = 20000
For Each r In t.Rows
For Each c In t.Columns
TextBox1.Text += r(c) & " "
TextBox1.Text += vbCrLf
Setting DataTable Properties:
CaseSensitive-Determines whether comparisions when searching,sorting or
filtering records are case sensitive
MinimumCapacity:- specifies the initial number of rows that the DataTable
accepts (default is 25)
PrimaryKey-specifies the array of columns to use as the primarykeys for the
TableName-name of the DataTable
Setting DataColumn properties in DataTable:
AllowDBNull – enables or disables null values in the
AutoIncrement – creates a column that increments its values
AutoIncrementSeed – a Long value that represents the initial
value for an autoincrement column
AutoIncrementStep- represents long increment value
DataType- specifies the .NET type of the column
Expression – creates calculated or aggregate functions
ReadOnly – A Boolean value which indicates the column allows
Creating calculated and aggregate columns
2)dad-select * from products
--Demonstration on Multiple Tables
--Demonstration on Data Grid
--Demonstraion on XML Integration
If a DataSet contains multiple DataTables , you can define
The most common type is parent/child relation
To define the relationship both tables must share a common key
A DataSet has a Relations Property that represents a collection of
1)assume that u have two tables with the names products and
categories and have a common column categoryid
3)dp,dc – datarows
4)for each dp in ds.tables(“catgories”). Rows
for each dc in dp.getchildrows(“relation1”)
Represents a particular view on a DataTable. You can use
Dataview to display a filtered or sorted view of the rows in a data table
SqlDataAdapter dad= new SqlDataAdapter(“select * from
Finding rows in DataView:
dv.Find(“smith”) – if it finds return the row no
To check constraint for a table u can iterate through
DataColumn pk=new DataColumn;
ForienKeyConstraint and UniqueConstraint