C# Corner Login

What is ADO.NET?
C# Curator Date Jun 10, 2019

322.5k 5 11

Download Free .NET & JAVA Files API

This article will provide an overview of ADO.NET In it, I’ll

provide the basics of ADO.NET, describe its advantages
over current data access technologies and briefly
introduce ADO.NET classes and namespaces as well as
show how to use them to write simple database
applications using Visual Studio .NET.
 
What Is ADO.NET?
 
ADO.NET stands for ActiveX Data Object is a database
access technology created by Microsoft as part of its
.NET framework that can access any kind of data
source. It’s a set of object-oriented classes that
provides a rich set of data components to create high-
performance, reliable and scalable database
applications for client- server applications as well as
distributed environments over the Internet and
intranets.
 
In the ADO.NET model, unlike ADO (in connected state)
and previous data access technologies applications
connect to the data sources when they are reading or
updating the data. After that the connection closes.
This is important because in client- server or
distributed applications, having connection resources
open all the time is one of the most resource-
consuming parts. You don’t have to connect to a data
source all the time; the only time you need to connect
to a data source is when you are reading and writing
final changes to a data source.
 
ADO .NET uses SQL queries and stored procedures to
read write update and delete data from a data source.
You use SQL queries through ADO.NET Command
object, which returns data in the form of DataReader
or DataSet objects. After that connection closes, you
use DataSet objects to work with the data and connect
to the data source again when you need to update the
data source.
 
A dataset is a collection of DataTable objects and
relationships among them. It works as a container that
stores returned data from a database in cached from.
You can fill a dataset with the data retrieved from
multiple tables of a database. Once you have a dataset
(which is disconnected data, stored on your local
machine), you treat the dataset changes final to the
actual database. You call the Update method to make
dataset changes final to the actual database. You can
even read and update a dataset from different data
sources.
You access a data source and fill a dataset via data
providers. The .NET framework provides three
different types of data providers: Sql, OleDb and
ODBC. Microsoft is also working on providing a data
provider for Oracle database and other Database
Management System (DBMS) suppliers may produce
their own data providers. You use a DataAdapter
object of a data provider and call its Fill method to fill a
dataset.
 
XML plays a major role in ADO.NET. The ADO.NET
model utilizes XML to store the data in cache and
transfer the data among applications. Datasets use
XML schemas to store and transfer data among
applications. You can even use this XML file from other
applications without interacting with the actual
dataset. You can use data among all kinds of
applications and components because XML is an
industry standard; you can transfer data via many
protocols, such as HTTP, because of XML’s text- based
nature.
 
Advantages of ADO .NET
 
ADO.NET offers several advantages over previous
Microsoft data access technologies, including ADO. The
following section will outline these advantages.
 
Single Object- Oriented API
 
The ADO.NET provides a single object-oriented set of
classes. There are different data providers to work with
different data sources, but the programming model for
all these data providers to work in the same way. So if
you know how to work with one data provider, you can
easily work with others. It’s just a matter of changing
class names and connection strings.
 
The ADO.NET classes are easy to use and to
understand because of their object- oriented nature. 
 
You can use more than one data provider to access a
single data source. For example, you can use ODBC or
OleDb data providers to access Microsoft access
databases.
 
Managed Code
 
The ADO .NET classes are managed classes. They take
all the advantages of .NET CLR, such as language
independency and automatic resource management.
All .NET languages access the same API. So if you know
how to use these classes in C#, you’ll have no problem
using them in VB.NET. Another big advantage is you
don’t have to worry about memory allocation and
freeing it. The CLR will take care of it for you.
 
Deployment
 
In real life, writing database application using ODBC,
DAO, and other previous technologies and deploying
on client machines was a big problem was somewhat
taken care in ADO except that three are different
versions of MDAC. Now you don’t have to worry about
that. Installing distributable .NET components will take
care of it.
 
XML Support
 
Today, XML is an industry standard and the most
widely used method of sharing data among
applications over the Internet. As discussed earlier in
ADO .NET data is cached and transferred in XML
format. All components and applications can share this
data and you can transfer data via different protocols
such as HTTP.
 
Visual Data Components
 
Visual Studio .NET offers ADO .NET components and
data– bound controls to work in visual form. That
means you can use these components as you use any
windows controls. You drag and drop these
components on windows and web forms set their
properties and write events. It helps programmers to
write less code and develop applications in no time. VS
.NET also offers the data form wizard, which you can
use to write full-fledged database applications without
writing a single line of code. Using these components
you can directly bind these components with data-
bound controls by setting these control’s properties at
design-time.
 
Performance and Scalability
 
Performance and scalability are two major factors
when developing web-based applications and services.
Transferring data one source to another is a costly
affair over the Internet because of connection
bandwidth limitations and rapidly increasing traffic.
Using disconnected cached data in XML takes care of
both of these problems.
 
Connections and Disconnected data
 
With ADO .NET you use as few connections as possible
and have more disconnected data. Both the ADO and
ADO .NET models support disconnected data but
ADO’S record set object wasn’t actually designed to
work with disconnected data. So there are
performance problems with that. However, ADO.NET’s
dataset is specifically designed to work with
disconnected data and you can treat a dataset as a
local copy of a database. In ADO.NET, you store data in
a dataset and close the make final changes to the data
source. The ADO model is not flexible enough for XML
users; ADO uses OLE-DB persistence provider to
support XML.
 
DataReader versus DataSet
 
The ADO.NET DataReader is used to retrieve read-only
(cannot update data back to a datasource) and
forward-only (cannot read backward/random) data
from a database. You create a DataReader by calling
Command.ExecuteReader after creating an instance of
the Command object.
 
Learn more about DataReader vs DataSet.
 
LINQ to DataSet
 
LINQ to DataSet API provides queries capabilities on a
cached DataSet object using LINQ queries. The LINQ
queries are written in C#. Learn more here: LINQ to
DataSet
 
LINQ to SQL
 
LINQ to SQL API provides queries against relational
databases without using a middle layer database
library. Learn more here: LINQ to SQL in C#
 
ADO.NET Entity Framework
 
The ADO.NET Entity Framework is designed to enable
developers to create data access applications by
programming against a conceptual application model
instead of programming directly against a relational
storage schema. The goal is to decrease the amount of
code and maintenance required for data-oriented
applications. For more information, see ADO.NET Entity
Framework.
ADO.NET Entity Framework and ADO.NET
 
ADO.NET Components
 
The ADO.NET is designed to work with multiple kinds
of data sources in same fashion. You can categorize
ADO.NET components in three categories:
disconnected, common or shared and the .NET data
providers. The disconnected components build the
basic ADO.NET architecture. You can use these
components (or classes) with or without data
providers. For example, you can use a DataTable object
with or without providers and shared or common
components are the base classes for data providers.
Shared or common components are the base classes
for data providers and shared by all data providers.
The data provider components are specifically
designed to work with different kinds of data sources.
For example, ODBC data providers work with ODBC
data sources and OleDb data providers work with OLE-
DB data sources.
 
The following figure represents the ADO.NET
components model and how they work together:

arctitecture.gif

A data provider is a set of components, such as

Connection, Command, DataAdapter and DataReader.
The Connection is the first component that talks to a
data source. The Connection object establishes a
connection to a data source and works as a connection
reference in Command and DataAdapter objects. A
Command object executes a SQL query and stored
procedures to read, add, update, and delete data of a
data source via a DataAdapter. A DataAdapter is a
bridge between a dataset and the connection. It uses
Command Object to execute SQL queries and stored
procedures.
 
All data providers share the ADO.NET common
components. These components represent the data.
Some of the common components are DataSet,
DataView, and DataViewManager. The DataSet uses
XML to store and transfer data between the
applications and the data provider. A DataSet is a set of
DataTable objects. A DataTable represents a database
table. The DataView and DataViewManager objects
provide single or multiple views of a dataset. You can
attach a DataView or a DataViewManager directly to
data–bound controls such as a DataGrid or DataList.
Other common components are DataTable, DataRow,
DataColumn and so on. Now, I’ll break down the
ADO.NET model to show how it works.
 
Connection Object
 
The Connection object is the first component of
ADO.NET that you should be looking at. A connection
sets a link between a data source and ADO.NET. A
Connection object sits between a data source and a
DataAdapter (via Command). You need to define a data
provider and a data source when you create a
connection. With these two, you can also specify the
user ID and password depending on the type of data
source.
 
Connection can also be connected to a Command
object to execute SQL queries, which can be used to
retrieve, add, update and delete data to a data source.
 
The Connection object also plays a useful role in
creating a transaction. Transactions are stored in
transactions objects, and transaction classes have all
those nice features for dealing with transactions such
as commit and rollback.
 
Learn more about SqlConnection here: SqlConnection
In ADO.NET
 
Command object
 
The Command object can execute SQL queries and
stored procedures. You can execute SQL queries to
return data in a DataSet or a DataReader object. To
retrieve add, update and delete data you use SELECT,
INSERT, UPDATE, and DELETE SQL queries. A
DataAdapter generated using the VS .NET Integrated
development Environment (IDE) has these queries.
 
Learn more about SqlCommand here: SqlCommand In
ADO.NET
 
The Command Builder
 
The SQL SELECT command is a fairly easy one to
construct. Even if you don’t know how to construct a
SQL SELECT command, the Query builder in visual
studio helps you. But notice there are three other
commands in figure 3-6 to construct: InsertCommand,
UpdateCommand, and DeleteCommand. These
commands can get quite complicated in .NET because
they require complex parameter objects and often
involve large lists of columns ADO.NET provides a nice
utility know as the CommandBuilder that automatically
builds these commands for you.
 
The DataAdapter Object
 
The DataAdapter object serves as a conduit between
the data source and the Dataset. The DataAdapter
knows about the DataSet and it knows how to
populate it. The Adapter also knows about the
connection to the data source.
 
Learn more about DataAdapter here: DataAdapter in
C#
 
DataSet Object
 
A DataSet object falls in disconnected components
series. You can use it with or without data providers.
The DataSet consists of a collection of tables, rows,
columns and relationships. Figure 3-10 illustrates these
relationships specifically that the DataSet contains a
collection of DataTables and the DataTable contains a
collection of DataRows, DataRelations, and
DataColumns. A DataTable maps to a table in the
database. The previous DataSet contains a DataTable
that maps to the Orders table because you filled it with
a SELECT query executed on the Order table.
 
Well, now you see how you can look at your tables, but
you still really haven’t seen any hard data yet. The data
in a DataSet is contained in the DataRow. A DataTable
in the DataSet consists of a collection of DataRow. Each
DataRow can be accessed via an index or the column
name. As you can see from figure 3-10, a data set has a
one-to-many relationship with DataTable. That means
a DataSet can have one or more than one DataTable
objects. Similarly, a DataTable can have one or more
than one DataRelation, DataRow, and DataColumn
objects.
 
Learn more about DataSets here: DataSet in C# 
 
DataSets in DataViews
 
Another thing you can do with the contents of your
DataSet is sort and filter them using DataViews. You
can have multiple Views of a dataset. A DataView is a
view of your data created according to certain criteria.
Each DataView has a one-to-one mapping to a
DataTable in a DataSet. For example, say you have
three tables in a dataset: table 1, table2, and table3.
Using three different data tables and data views, you
can represent this dataset in three different views.
Using sort and filters, you can even sort and filter the
data based on some criteria.
 
A dataview can directly attach to data-bound controls
such as a DataGrid, DataList, or a Combo box.
 
Learn more about DataView here: DataView in C#

ADO.NET ADO.NET Architecture

OUR BOOKS

C# Curator

This is a C# Corner community account used by

curators.

https://www.c-sharpcorner.com/members/puran-
mehra

60 43.5m 1

11 5

Type your comment here and press Enter Key

(Minimum 10 characters)

The ADO in ADO.NET doesn't refer to ActiveX Data

Object, actually, it was true for ADO though.
sumit rayakwar May 11, 2020
2142 16 0 0 0 Reply

I just began learning ADO.net and this helped make

some conceptually useful connections. Thanks.
Ru D Sep 25, 2019
2155 3 0 0 0 Reply

Good one Puran. Thank you!

Dennis Thomas Jan 05, 2018
912 1.9k 1.2m 1 0 Reply

(y) Great effort. Thanks

Sakeena Kazmi Mar 28, 2017
2142 16 0 1 0 Reply

superb!!
shiva Jul 23, 2009
2157 1 0 2 0 Reply

About Us Contact Us Privacy Policy Terms Media Kit

Sitemap Report a Bug FAQ Partners

C# Tutorials Common Interview Questions Stories

Consultants Ideas Certifications
©2023 C# Corner. All contents are copyright of their authors.