Introduction to ADO.NET

Introduction
ADO.NET is a set of libraries included with
the Microsoft .NET Framework that help
you communicate with various data stores
from .NET applications

Objectives of ADO.NET
The ADO.NET libraries include classes for
•connecting to a data source,
•submitting queries, and
•processing results.
•You can also use ADO.NET as a robust, hierarchical,
disconnected data cache to work with data off line.

Evolution of Technology
Visual Basic 3 Data Access Objects (DAO).
(local file-based databases)
Visual Basic 4 Remote Data Objects (RDO).
(larger server-based databases)
Visual Basic 5 and Visual
Studio 97
ODBCDirect
Visual Basic 6 and Visual
Studio 6
ADO

Why ADO.NET ?
•ADO will never handle XML data as efficiently as
ADO.NET does
•You cannot combine the contents of multiple
Recordset objects in ADO
•The ADO cursor engine does not, for example,
provide a way to submit pending changes to your
database via stored procedures
•ADO allows you to submit cached changes to
databases, but it does not give you control over the
logic used to submit updates
•ADO was built for COM-based application

Benefits of ADO.NET
• greater XML support,
• easier disconnected data access,
• more control over updates, and
• greater update flexibility

ADO Vs ADO.NET
•In ADO, the Recordset object stores the results of
your queries. You can call its Open method to fetch
the results of a query and call its Update (or
UpdateBatch) method to submit changes stored
within the Recordset to your database
•The objects that comprise the disconnected half of
the ADO.NET object model do not communicate
directly with the connected objects. This is a major
change from previous Microsoft data access object
models

Data Set
The central disconnected object, DataSet,
allows you to
sort,
•search,
•filter,
•store pending changes, and
•navigate through hierarchical data
•Work with XML

Dataset Vs Recordset
•The ADO.NET DataSet, is comparable in functionality
to the ADO Recordset
• However, the DataSet does not communicate with your
database
• In order to fetch data from your database into a DataSet,
you pass the DataSet into the Fill method of a connected
ADO.NET object—the DataAdapter.
• Similarly, to submit the pending changes stored in your
DataSet to your database, you pass the DataSet to the
DataAdapter object’s Update method

What is a Data Provider
•A .NET data provider is a collection of classes designed to
allow you to communicate with a particular type of data store
•The .NET Framework includes three such providers, the SQL
Client .NET Data Provider , the Oracle Client and the OLE
DB .NET Data Provider.
•Third party DB vendors provide their own Data providers
like Oracle’s Data Provider for .NET (ODP.NET)
•The OLE DB .NET Data Provider lets you communicate with
various data stores through OLE DB providers
•The SQL Client .NET Data Provider is designed solely to
communicate with SQL Server databases, version 7 and later.

What is common to all Data
Providers
•Each .NET data provider implements the same base classes—
Connection, Command, DataReader, Parameter, and
Transaction—although their actual names depend on the
provider
•For example, the SQL Client .NET Data Provider has a
SqlConnection object, and the OLE DB .NET Data Provider
includes an OleDbConnection object
•Regardless of which .NET data provider you use, the
provider’s Connection object implements the same basic
features through the same base interfaces

Data Provider Namespaces
Each .NET data provider has its own namespace
•System.Data.OleDb
•System.Data.SqlClient
•System.Data.OracleClient

IDbConnection Interface
Represents an open connection to a data
source, and is implemented by .NET
Framework data providers that access
relational databases.
Namespace: System.Data
Assembly: System.Data (in system.data.dll)
Syntax
public interface IDbConnection :
IDisposable

IDataAdapter interface
Allows an object to implement a
DataAdapter, and represents a set
of methods and mapping action-
related properties used to fill and
refresh a DataSet and update a
data source.
Assembly: System.Data (in
system.data.dll)
C# Syntax
public interface IDataAdapter

IDbDataAdapter Interface
Represents a set of command-related
properties that are used to fill the
DataSet and update a data source, and
is implemented by .NET Framework data
providers that access relational
databases.
system.data.dll)
Syntax
public interface IDbDataAdapter :
IDataAdapter

IDataReader Interface
Provides a means of reading one or more
forward-only streams of result sets obtained
by executing a command at a data source,
and is implemented by .NET Framework data
providers that access relational databases.
Syntax
public interface IDataReader : IDisposable,
IDataRecord

IdbCommand Interface
Represents an SQL statement that is
executed while connected to a data
source, and is implemented by .NET
Framework data providers that access
system.data.dll)
Syntax
public interface IDbCommand :
IDisposable

IDbTransaction Interface
Represents a transaction to be performed
at a data source, and is implemented by
.NET Framework data providers that access
Syntax
public interface IDbTransaction :
IDisposable

OleDBConnection Vs
SqlConnection
//Open and close a connection using the OLE DB .NET Data Provider.
OleDbConnection cnOleDb = new OleDbConnection();
cnOleDb.ConnectionString
= "Provider=Provider=SQLOLEDB; "Data Source=(local);InitialCatalog=North
wind;...";
cnOleDb.Open(); .
cnOleDb.Close();
----------------------------------------------------------------------------------
-//Open and close a connection using the SQL Client .NET Data Provider.
SqlConnection cnSql = new SqlConnection();
cnSql.ConnectionString = "Data Source=(local);" +
"Initial Catalog=Northwind;...";
cnSql.Open();
cnSql.Close();

Parameter Object
Query without parameters :
SELECT CustomerID, CompanyName, Compa
nyName, Phone FROM Customers WHERE
CustomerID = 'ALFKI'
Query with parameters :
SELECT CustomerID, CompanyName, Compa
nyName, Phone FROM Customers WHERE
CustomerID = ?
To use a parameterized Command object, you
create Parameter objects for each of the parameters
in your query and append them to the Command
object’s Parameters collection

What is Data Adapter Object
•The DataAdapter object represents a new concept for
Microsoft data access models; it has no true equivalent in
ADO or DAO
•DataAdapter objects act as a bridge between your database
and the disconnected objects in the ADO.NET object model
•The DataAdapter object’s Fill method provides an efficient
mechanism to fetch the results of a query into a DataSet or a
DataTable so you can work with your data off line.
•Use DataAdapter objects to submit the pending changes
stored in your DataSet objects to your database

Command Builder
•The ADO.NET DataAdapter object exposes a number
of properties that are actually Command objects.
•For instance, the SelectCommand property contains a
Command object that represents the query you’ll use to
populate your DataSet object.
•The DataAdapter object also has UpdateCommand,
InsertCommand, and DeleteCommand properties that
correspond to Command objects you use when you
submit modified, new, or deleted rows to your database,
respectively

DataAdapter.Update
•With a DataAdapter object, you can set the UpdateCommand,
InsertCommand, and DeleteCommand properties to call the
stored procedures that will modify, add, or delete rows in the
appropriate table in your database.
•Then you can simply call the Update method on the
DataAdapter object and ADO.NET will use the Command
objects you’ve created to submit the cached changes in your
DataSet to your database

Data Table Object
•The ADO.NET DataTable object is similar to the ADO and DAO
Recordset objects.
•A DataTable object allows you to examine data through
collections of rows and columns.
•You can store the results of a query in a DataTable through the
DataAdapter object’s Fill method

Populating a Data Table
string strSQL = "SELECT CustomerID, CompanyName FRO
M Customers";
string strConn = "Provider=SQLOLEDB;Data Source=(local);..
.“
OleDbDataAdapter daCustomers = new OleDbDataAdapter(s
trSQL, strConn);
DataTable tblCustomers = new DataTable();daCustomers.Fill
(tblCustomers);

DataRow and DataColumn
•Once you’ve fetched the data from your database and stored
it in a DataTable object, that data is disconnected from the
server
•You access the contents of a DataTable through its Rows
property, which returns a collection of DataRow objects
•If you want to examine the structure of a DataTable, you use
its Columns property to retrieve a collection of DataColumn
objects
•The DataTable class also lets you define constraints, such as
a primary key, on the data stored within the class

Data Column Object
•Each DataTable has a Columns collection, which is a
container for DataColumn objects.
•The Columns collection and DataColumn objects can be
roughly compared to the Fields collection and Field objects
in ADO and DAO.
•However, a DataColumn object doesn’t actually contain
the data stored in your DataTable. Instead, it stores
information about the structure of the column
•For example, DataColumn exposes a Type property that
describes the data type (such as string or integer) that the
column stores.
•DataColumn has other properties such as ReadOnly,
AllowDBNull, Unique, Default, and AutoIncrement

Expression property
•The DataColumn class also exposes an Expression
property, which you can use to define how the data in
the column is calculated
•DataColumn col = new DataColumn();
•col.ColumnName = "ItemTotal";
•col.DataType = typeof(Decimal);
•col.Expression = "UnitPrice * Quantity";

Data Row Object
•To access the actual values stored in a DataTable object, you
use the object’s Rows collection, which contains a series of
DataRow objects
•DataRow row;
•row = MyTable.Rows[0];
•Console.WriteLine(row[0]);
•Console.WriteLine(row["CustomerID"]);
•Console.WriteLine(row[MyTable.Columns["CustomerID"]])
;

Looping a recordset in ADO
Dim strConn As String, strSQL As String
Dim rs As ADODB.RecordsetstrConn = "Provider=SQLOLEDB;
Data Source=(local);..."strSQL = "SELECT CustomerID, Company
Name FROM Customers“
Set rs = New ADODB.Recordsetrs.CursorLocation = adUseClient
rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCm
dText
Do While Not rs.EOF MsgBox rs("CustomerID") rs.MoveNext
Loop

Looping a DataTable
string strSQL, strConn;
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strC
onn);
DataTable tbl = new DataTable();
da.Fill(tbl);
foreach (DataRow row in tbl.Rows)
Console.WriteLine(row[0]);

Updation and DataRow
•The DataRow object is also the starting point for your
updates
•You can call the BeginEdit method of a DataRow object,
change the value of some columns in that row through the
Item property, and then call the EndEdit method to save the
changes to that row
•When you change the contents of a row, the DataRow
object caches those changes so that you can submit them to
your database at a later time

DataSet Object
•You can think of a DataSet object as the container for a
number of DataTable objects (stored in the DataSet
object’s Tables collection)
•Any changes you make to the data are simply cached in
each DataRow
•You can use the GetChanges method to extract just the
modified rows from your DataSet to update the Database
•You can use the DataSet class’s Merge method to
combine the contents of two DataSet objects into a
single DataSet
•You can create a DataSet object and populate its Tables
collection with information without having to
communicate with a database

Data Relation Object
•You can use a DataRelation object to indicate a relationship between
different DataTable objects in your DataSet
•DataSet dsNorthwind;
•dsNorthwind.Relations.Add("CustomersOrders",dsNorthwind.Tabl
es["Customers"].Columns["CustomerID"], dsNorthwind.Tables["Or
ders"].Columns["CustomerID"]);
•foreach (DataRow rowCustomer in dsNorthwind.Tables["Custom
ers"].Rows)
•{Console.WriteLine("Orders for customer " +
• rowCustomer["CompanyName"].ToString());
•foreach (DataRow rowOrder in rowCustomer.GetChildRows("Cus
tomersOrders"))

Data View Object
•Once you’ve retrieved the results of a query into a DataTable
object, you can use a DataView object to view the data in
different ways
•If you want to sort the contents of a DataTable object based on
a column, simply set the DataView object’s Sort property to the
name of that column
•You can also use the Filter property on DataView so that only
the rows that match certain criteria are visible.

Typed Data Set
•Let’s say we have a simple table named Orders that
contains two columns, CustomerID and CompanyName.
•DataSet ds;
• DataSet.Console.WriteLine(ds.Tables["Customers"]
.Rows[0]["CustomerID"]);
•Can be replaced by a Strongly Typed data set :
•CustomersDataSet ds;
•Console.WriteLine(ds.Customers[0].CustomerID);

Another Typed Data Set
Before using Typed Data Set :
DataSet ds;
DataRow rowNewCustomer;
rowNewCustomer = ds.Tables["Customers"].NewRow();
rowNewCustomer["CustomerID"] = "ALFKI";rowNewCustomer["
CompanyName"] = "Alfreds Futterkiste";
ds.Tables["Customers"].Rows.Add(rowNewCustomer);
After using Typed Data set:
ds.Customers.AddCustomersRow("ALFKI", "Alfreds Futterkiste")

Introduction to ADO.NET

In this document

More Related Content

What's hot

Similar to Introduction to ADO.NET

More from rchakra

Recently uploaded

Introduction to ADO.NET