data adaper

Data Adapter

Dataset and .Net data provider

Ambrish Kirkire Institute...
Sector 15
Contact Number:9820761200

through pooling we use a same connection or create new 
connection.

Execute nonquery effects only for DML like Update delete 
and insert statements only,

for select statement we have to choose ExecuteScalar or 
ExecuteReader methods. these methods can return datareader 
and gives the result for your query.

Dataset is disconnected method where Datareader treated as 
connected method.

Connected methods gives faster performance where as 
disconnected get low in speed and performence.

connected  can hold the data of single table, where as 
disconnected can hold multiple tables of data.

Disconnected are the forward only and readonly objects 
where as connected or not.

connected data needs connection to be created to access
hence slower while disconnected is in memory data thats
faster access.

to access connected data you use ADO.NET whereas for
disconnected you do not use.

disconnected-data can be accessed from multiple tables in a
dataset.
connected- .net runtime creats an instance of the datatable
to hold data.

in disconnected data you can create a window to the
datatable in the form of a dataview used to sort and filter
data..which cannot be done in connected data access

connected you need to use a read only forward only data
reader,disconnected you cannot.

disconnected data in a dataset can be serialized/deserialized.

Data Reader:-
Data Reader is a read only and forward only data
You can access one table at a time.
Data reader cant persist the data.
Data Reader is connected architecture.
Data Reader is faster than Data Adapter
Data Reader is faster than dataset operation
Data Set:
Data Set is disconnected architecture.
Data Set is persist the data.
You can access more than one table
Data Set is main for storage tool in ADO.Net

Data Reader is a forward only and read only data
DataSet is used to maintain relationships between multiple
tables.

Data Reader can't persist the data
Data Set can persist the data

ADO object model includes five components.
They are:
SqlConnection Object,SqlCommand Object,SqlDataReader 
object,Dataset object,SqlDataAdapter object.

Not Sql ADO Object Model Only.But ADO.Net has the following 
methods also

1.OLEDBProvider
2.SQLProvider
3.ODBCProvider

the above all has the Connection,Command,Data Reader, Data 
Set, Data Adapter Also.So Finally List Only Common Object 
Models , They are

 1. Connection Objects
 2. Command Objects
 3. DataReader Object
 4. DataSet Object
 5. DataAdapter Object

The main object model of an ADO.Net architecture is "Data 
Provider". It provides data access to SQl server ,Oracle, 
Access in order to retreive,update and insert data in the 
datastore.

the main objects of ADO.Net object model are....

1.connection object : used to establish connection to 
database.

2.command object: it is used to execute commands and stored 
procedures.

3.dataAdapter object: used to establish connection between 
datastore and dataset.

4.dataReader object: used to read data from dataset in 
forward only.

SqlConnection conn = new SqlConnection("Data 
Source=MYSERVER;Initial Catalog=Northwind;User 
Id=sa;Password=sa;");
            conn.Open();
            DataTable dt=new DataTable();
            SqlDataAdapter da = new SqlDataAdapter("select 
* from Employees", conn);
            da.Fill(dt);
            testGV.DataSource = dt;

Drag and drop the table on browser,difultly it shows grid 
view ..

we can fill grid view by using sqldataadapter and dataset.
the code will be as follows........

using System.data.sqlclient (add namespace)

in code part.... 

sqlconnection con=new sqlconnection("server=(the server 
which u use);database=(database in which your table 
located);user id=;password=");

sqldataadapter da=new sqldataadapter("select * from 
table",con);
dataset ds=new dataset();
da.fill(ds); 
gridview1.datasourse=ds;
gridview1.databind();

Because it suppport the disconnected architecture :)

Data Reader worked as conventional Record Set of VB
and
Data Reader worked in connected mode only

Because it supported dissconnected architecture

because it satisfy read only property from which we can only
retrieve data only(forword) not write to int(backword).

DataReaader is connected object and one can process the 
rows that are returned by query, one at a time. It discards 
every row after you have gone through it and so it is 
extremely fast.It contains only read-only data, so no 
updates are allowed using DataReader
objects.

datareader execute data from table one by one at each
calling of it.and to fetch next value from database it
contains pointer value(index) of next value . thats why if
is forward only. in the oops concept it works on the concept
of one-way linklist

A DataReader is a stream of data that is returned from a 
database query. When the query is executed, the first row 
is returned to the DataReader via the stream. The stream 
then remains connected to the database, poised to retrieve 
the next record. The DataReader reads one row at a time 
from the database and can only move forward, one record at 
a time. As the DataReader reads the rows from the database, 
the values of the columns in each row can be read and 
evaluated, but they cannot be edited.

As we know that DataReader is connected architecture it 
cannot persists the contents. it reads the contents one by 
one record from the dataset until all the records completed.

 The main thing is data reader uses pointers to fetch the 
next record(index).hence these are the forward only objects

Because it supports connected architecture.

ADO has the recordset and connected archtecture where as 
ADO.net has disconnected archtecture and DATASET

1. ADO used connected data usage, while ADO.net used  
disconnected data environment.
2. ADO used OLE DB to access data and is COM-based, while
ADO.net uses XML as the format for transmitting data to and
from your database and web application.
3. In ADO, Record set, is like a single table or query
result, while in ADO.net Dataset, can contain multiple
tables from any data source.
4. In ADO, it is sometime problematic because firewall
prohibits many types of request, while in ADO.net there is
no such problem because XML is completely firewall-proof.

ADO is a com base library that supports only connected 
data. This means that when we access the data (view, edit 
and update) it is affected in real-time,since the 
connection is being used all the time. ADO uses the objects 
as reference data known as Recordset object. Hence it 
basically gives a single table view of the data. We can 
also join tables to create a new set of records in ADO.ADO 
allows to create only client-side cursors. Using ADO we can 
persist records in XML format and XML integration is not 
possible. 

ADO.NET is a CLR based library that supports disconnected 
recordsets. This means that When we access data, ADO.NET 
makes a copy of the data using XML and holds the connection 
to pull down the data or to make any requested updates as 
long as we need. The best method to follow is 
ADO.NET.ADO.NET uses various objects known as Dataset that 
allow to access data in various methods. Hence it allows to 
store the relational model of the database and helps us to 
access /update the data in each related table individually. 
ADO.NET supports both client-side and server-side cursors. 
Also the cursors are handled as classes in ADO.NET 
providing a way to create efficient applications. Using 
ADO.NET we can manupulate records in XML format. Also 
ADO.NET 2.0 provides Multiple Active ResultSets (MARS) 
which allows to have two datareader on one connection open 
at sametime.

Following are some major differences between both

1.As in classic ADO we had client and server side cursors 
they are no more present in ADO.NET. Note it's a 
disconnected model so they are no more applicable.

2. Locking is not supported due to disconnected model.

3. All data persist in XML as compared to classic ADO where 
data persisted in Binary format also.

4. Ado.net uses Dataset where as Ado uses RecordSet.

5.Ado.Net uses Dataproviders where as classic Ado uses COM 
model.

6.Ado.net can able to works on multiple tables where as Ado 
can works on only on etable.

The DataTable is an in-memory representation of a block of 
data. We can create our own tables in code using a DataSet 
and the types defined in the System.Data.OleDb or 
System.Data.SqlClient namespaces. The following are the 
core properties that are used while creating a DataTable. 

CaseSensitive:   Indicates whether string comparisons in 
the table are case-sensitive or not.

ChildRelations:  Returns the collection of child relations 
of the DataTable (if any).

Columns:   Returns the collection of columns that belong to 
this table.

Constraints: Gets the constraints maintained by this table.

DataSet: Gets the dataset that contains this table.

DefaultView: Gets a customized view of the table that may 
include a filtered view or a cursor position.

MinimumCapacity: Gets/Sets the initial number of rows in 
the table.

ParentRelations: Gets the collection of parent relations 
for this table.

PrimaryKey: Gets/Sets a primary key for the table.

Rows: Returns the collection of rows that belong to this 
table.

TableName: Gets/Sets the name of the table.

Datatable is a sql class which is used to create a temporary
table and working on it at run time.

In ADO.NET, DataTable objects are used to represent the 
tables in a DataSet.
A DataTable represents one table of in-memory relational 
data; the data is local to the .NET-based application in 
which it resides.
A DataTable is a collection of DataRow and DataColumn 
objects.
The schema, or structure of a table is represented by 
columns and constraints.
You define the schema of a DataTable using DataColumn 
objects as well as ForeignKeyConstraint and 
UniqueConstraint objects.

DataTable tbl=new DataTable;
DataColumn ID=new
Datacolumn("ID",System.Type.GetType("System.Int"));
DataColumn Name=new
DataColumn("NAME",System.Type.GetType("System.String"));
tbl.Columns.add("ID");
tbl.Columns.Add("NAME");
Datarow dr =null;;
for (int i=0;i<100;i++)
{
dr=tbl.NewRow();
}
datagridview1.datasource=tbl;