What is ADO.NET?

ADO.NET is not a different technology. ADO.NET, as a set of classes (Framework) that can be
used to interact with data sources like Databases and X! files. ADO stands for icrosoft Acti"eX
Data Ob#ects.
What are .NET Data Providers?
Databases onl$ understand %&!. 'f a .NET a((lication ()eb, )indows, *onsole etc.) has to retrie"e
data, then the a((lication needs to
1. *onnect to the Database
2. +re(are an %&! *ommand
3. E,ecute the *ommand
4. -etrie"e the results and dis(la$ in the a((lication
a!"le ADO.NET code to connect to #$ erver Data%ase and retrieve data .
Notice that we are usin. #$&onnection, #$&o!!and and #$Data'eader classes.
All the ob#ects are (refi,ed with the word #$.
All these classes are (resent in yste!.Data.(l&lient names(ace.
%o, we can sa$ that the .NET data "rovider for #$ erver is yste!.Data.(l&lient.
%/l*onnection con 0 new %/l*onnection(1data source0.2 database0%am(le2 inte.rated
securit$0%%+'1)2
%/l*ommand cmd 0 new %/l*ommand(1%elect 3 from tbl+roduct1, con)2
con.O(en()2
%/lData-eader rdr 0 cmd.E,ecute-eader()2
4rid5iew6.Data%ource 0 rdr2
4rid5iew6.Data7ind()2
con.*lose()2
Different .NET Data Providers
Data +ro"ider for %&! %er"er 8 %$stem.Data.%/l*lient
Data +ro"ider for Oracle 8 %$stem.Data.Oracle*lient
Data +ro"ider for O!ED7 8 %$stem.Data.OleDb
Data +ro"ider for OD7* 8 %$stem.Data.Odbc
Please note that) de"ending on the "rovider, the followin. ADO.NET ob#ects ha"e a different (refi,
6. &onnection 8 %&!*onnection, Oracle*onnection, OleDb*onnection, Odbc*onnection etc
9. &o!!and 8 %&!*ommand, Oracle*ommand, OleDb*ommand, Odbc*ommand etc
:. Data'eader 8 %&!Data-eader, OracleData-eader, OleDbData-eader, OdbcData-eader etc
;. DataAda"ter 8 %&!DataAda(ter, OracleDataAda(ter, OleDbDataAda(ter, OdbcDataAda(ter etc
The Dataet o%*ect is not "rovider s"ecific. Once we connect to a Database, e,ecute command,
and retrie"e data into .NET a((lication. The data can then be stored in a Data%et and work
inde(endentl$ of the database.
#$&onnection in ADO.NET
<nderstand about the followin. ob#ects.
1. *onnection
2. *ommand
3. Data-eader
4. DataAda(ter
+. Data%et
&onnection, &o!!and, Data'eader and DataAda"ter ob#ects are (ro"iders s(ecific
and Dataet is (ro"ider inde(endent.
The first thing that ,e ,ill have to do, when workin. with databases is to create a connection
ob#ect. There are 9 wa$s to create an instance of (l&onnection class as shown below.
1- &reate an instance of (l&onnection class .sing the constr.ctor that ta/es
&onnectiontring "ara!eter
%/l*onnection connection 0 new %/l*onnection(1data source0.2 database0%am(leD72
inte.rated securit$0%%+'1)2
2- ==First create an instance of %/l*onnection class usin. the (arameter8less constructor
%/l*onnection connection 0 new %/l*onnection()2
==Then set the *onnection%trin. (ro(ert$ of the connection ob#ect
connection.*onnection%trin. 0 1data source0.2 database0%am(leD72 inte.rated
securit$0%%+'12
To create a connection o%*ect ,ith ,indo,s a.thentication
strin. *onnection%trin. 0 1data source0.2 database0%am(leD72 inte.rated securit$0%%+'12
To create a connection o%*ect ,ith #$ erver a.thentication
strin. *onnection%trin. 0 1data source0.2 database0%am(leD72 user id0$<serName2
(assword0$+assword12
The 0data so.rce0 is the na!e or 1P Address of the #$ erver that we want to connect to. 'f $ou
are workin. with a local instance of s/l ser"er, $ou can #ust s(ecif$ DOT (.). 'f the ser"er is on a
network, then use Name or '+ address.
a!"le ADO.NET code that
1. *reates a connection
2. The created connection ob#ect is then (assed to the command ob#ect, so that the command ob#ect
knows on which s/l ser"er connection to e,ecute this command.
3. E,ecute the command, and set the command results, as the data source for the .rid"iew control.
4. *all the Data7ind() method
+. *lose the connection in the finall$ block. *onnections are limited and are "er$ "aluable.
*onnections must be closed (ro(erl$, for better (erformance and scalabilit$.
Note2 *onnections should be o(ened as late as (ossible, and should be closed as earl$ as (ossible
(rotected "oid +a.e>!oad(ob#ect sender, E"entAr.s e)
?
==*reate the connection ob#ect
%/l*onnection connection 0 new %/l*onnection30data so.rce4.5 data%ase4a!"le6Test6D75
integrated sec.rity4P10-55
tr$
?
== +ass the connection to the command ob#ect, so the command ob#ect knows on which
== connection to e,ecute the command
(l&o!!and c!d 4 ne, (l&o!!and30elect 8 fro! t%lProd.ct1nventory0) connection-5
== O(en the connection. Otherwise $ou .et a runtime error. An o(en connection is
== re/uired to e,ecute the command
connection.O"en3-5
9rid:ie,1.Datao.rce 4 c!d.E;ec.te'eader3-5
9rid:ie,1.Data7ind3-5
@
catch (E,ce(tion e,)
?
== Aandle E,ce(tions, if an$
@
finall$
?
== The finall$ block is .uaranteed to e,ecute e"en if there is an e,ce(tion.
== This ensures connections are alwa$s (ro(erl$ closed.
connection.&lose3-5
@
@
We can also .se 0.sing0 state!ent to "ro"erly close the connection as sho,n %elo,.
)e donBt ha"e to e,(licitl$ call &lose3- method, when .sing is used. The connection will be
automaticall$ closed for us.
(rotected "oid +a.e>!oad(ob#ect sender, E"entAr.s e)
?
.sing 3(l&onnection connection 4 ne, (l&onnection30data so.rce4.5
data%ase4a!"le6Test6D75 integrated sec.rity4P10--
?
%/l*ommand cmd 0 new %/l*ommand(1%elect 3 from tbl+roduct'n"entor$1, connection)2
connection.O(en()2
4rid5iew6.Data%ource 0 cmd.E,ecute-eader()2
4rid5iew6.Data7ind()2
@
@
(l&o!!and &lass2
%/l*ommand class is used to (re(are an %&! statement or %tored+rocedure that we want to e,ecute
on a %&! %er"er database.
The follo,ing are the !ost co!!only .sed !ethods of the (l&o!!and class.
1- E;ec.te'eader 8 -eturns more than a single val.e.
2- E;ec.teNon#.ery 8 +erform an 1nsert) <"date or Delete o(eration
3- E;ec.tecalar 8 -eturns a single 3scalar- val.e.
)e will be usin. t%lProd.ct1nventory table for our e,am(les. The table is shown below for $our
reference.
t%lProd.ct1nventory
6) ExecuteReader: returns multi(le rows of data usin. E;ec.te'eader() method.
(rotected "oid +a.e>!oad(ob#ect sender, E"entAr.s e)
?
.sing 3(l&onnection connection 4 ne, (l&onnection30data so.rce4.5
data%ase4a!"le6Test6D75 integrated sec.rity4P10--
?
==*reate an instance of %/l*ommand class usin. the (arameter less constructor
(l&o!!and c!d 4 ne, (l&o!!and3-5
==%(ecif$ the command, we want to e,ecute usin. the *ommandTe,t (ro(ert$
c!d.&o!!andTe;t 4 0elect 1d)Prod.ctNa!e)#.antityAvaila%le fro!
t%lProd.ct1nventory05
==%(ecif$ the connection, on which we want to e,ecute the command
==usin. the *onnection (ro(ert$
c!d.&onnection 4 connection5
connection.O"en3-5
==%&! statement that we want to e,ecute return multi(le rows of data,
==use E,ecute-eader() method of the command ob#ect.
9rid:ie,1.Datao.rce 4 c!d.E;ec.te'eader3-5
9rid:ie,1.Data7ind3-5
@
@
9) E;ec.tecalar3- !ethod2 statement returns a sin.le "alue
(rotected "oid +a.e>!oad(ob#ect sender, E"entAr.s e)
?
.sing 3(l&onnection connection 4 ne, (l&onnection30data so.rce4.5
data%ase4a!"le5 integrated sec.rity4P10--
?
==*reate an instance of %/l*ommand class, s(ecif$in. the T8%&! command
==that we want to e,ecute, and the connection ob#ect.
(l&o!!and c!d 4 ne, (l&o!!and30elect &o.nt31d- fro! t%lProd.ct1nventory0)
connection-5
connection.O"en3-5
==statement that we want to e,ecute return a sin.le "alue,
==use E,ecute%calar() method of the command ob#ect.
==%ince the return t$(e of E,ecute%calar() is ob#ect, we are t$(e castin. to int datat$(e
int Total'o,s 4 3int-c!d.E;ec.tecalar3-5
'es"onse.Write30Total 'o,s 4 0 = Total'o,s.Totring3--5
@
@
:) The follo,ing e;a!"le "erfor!s an 1nsert) <"date and Delete o"erations on a %&!
ser"er database usin. the E,ecuteNon&uer$() method of the %/l*ommand ob#ect.
(rotected "oid +a.e>!oad(ob#ect sender, E"entAr.s e)
?
usin. (%/l*onnection connection 0 new %/l*onnection(1data source0.2
database0%am(le>Test>D72 inte.rated securit$0%%+'1))
?
==*reate an instance of %/l*ommand class
==that we want to e,ecute, and the connection ob#ect.
(l&o!!and c!d 4 ne, (l&o!!and30insert into t%lProd.ct1nventory val.es 31>3) ?A""le?)
1>>-0) connection-5
connection.O"en3-5
==%ince we are (erformin. an insert o(eration, use E,ecuteNon&uer$()
==method of the command ob#ect. E,ecuteNon&uer$() method returns an
==inte.er, which s(ecifies the number of rows inserted
int ro,sAffected 4 c!d.E;ec.teNon#.ery3-5
-es(onse.)rite(1'nserted -ows 0 1 C rowsAffected.To%trin.() C 1Dbr=E1)2
==%et to *ommandTe,t to the u(date /uer$. )e are reusin. the command ob#ect,
==instead of creatin. a new command ob#ect
c!d.&o!!andTe;t 4 0."date t%lProd.ct1nventory set #.antityAvaila%le 4 1>1 ,here 1d 4
1>105
==use E,ecuteNon&uer$() method to e,ecute the u(date statement on the database
rowsAffected 0 cmd.E,ecuteNon&uer$()2
-es(onse.)rite(1<(dated -ows 0 1 C rowsAffected.To%trin.() C 1Dbr=E1)2
==%et to *ommandTe,t to the delete /uer$. )e are reusin. the command ob#ect,
==instead of creatin. a new command ob#ect
c!d.&o!!andTe;t 4 0Delete fro! t%lProd.ct1nventory ,here 1d 4 1>205
==use E,ecuteNon&uer$() method to delete the row from the database
rowsAffected 0 cmd.E,ecuteNon&uer$()2
-es(onse.)rite(1Deleted -ows 0 1 C rowsAffected.To%trin.() C 1Dbr=E1)2
@
@