8/20/2019 ADO.NET connection in C#

1/23

Software ProgrammingADO.NET

8/20/2019 ADO.NET connection in C#

2/23

Contents

•

Introduction ADO.NET•  The Connection Object

•  The Command Object

• Reading Data with the DataReader

• or!ing with Disconnected Data " The DataSet andDataAda#ter

• Adding Parameters to Commands

• $sing Stored Procedures

8/20/2019 ADO.NET connection in C#

3/23

Introduction ADO.NET

•

ADO%" Acti&e Data Objects.•  The #rimar' data access techno(og' to connect into a

database from .NET is ADO.NET.

• ADO.NET use for )*+, connect to a database )*-,mani#u(ate the data.

• ADO.NET has su##ort for /0)E1tensib(e /ar!u# 0angdata re#resentation.

8/20/2019 ADO.NET connection in C#

4/23

Introduction ADO.NET

•

ADO.NET was bui(t for Online or Connected   mode aofine or disconnected  mode.

• connected /ethod

• hen 'ou are ma!ing use of the connected (a'er2 'our code b

e1#(icit(' connect to and disconnect from the under('ing data

• hen 'ou are using ADO.NET in this manner2 'ou interact wit

data store using connection objects, command objects, areader objects.

8/20/2019 ADO.NET connection in C#

5/23

Introduction ADO.NET

•

 disconnected /ethod•  The disconnected (a'er2 a((ows 'ou to obtain a set of DataTable ob

)contained within a DataSet, that functions as a c(ient"side co#' of te1terna( data.

• hen 'ou obtain a DataSet  using a re(ated data adapter object2 tconnection is automatica((' o#ened and c(osed on 'our beha(f.

•  This a##roach he(#s 3uic!(' free u# connections for other ca((ers.

•  The c(ient recei&es a DataSet 2 it is ab(e mani#u(ate the contents wicost of networ! .

• If the c(ient wishes to submit the changes bac! to the data store2 th

adapter )in conjunction with a set of S40 statements, is used once u#date the data source2 at which #oint the connection is c(osed imm

8/20/2019 ADO.NET connection in C#

6/23

ADO.NET Architecture or Diagram &iew

8/20/2019 ADO.NET connection in C#

7/23

Data Pro&iders

•

 The data #ro&ider is used to interact between thedatabase and your program.

• 5unction of the Data Pro&ider%

• Connecting to the database

• Retrie&ing data from the database

• $#dating the data in the database

• De(eting the data in the database2

• Other functions .

• ADO.NET su##orts mu(ti#(e data #ro&iders2 each of who#timi6ed to interact with a s#eci7c D8/S.

8/20/2019 ADO.NET connection in C#

8/23

Data Pro&iders cont..

•

ADO.NET9s main three Data Pro&iders are•  /icrosoft S40 Ser&er Data Pro&ider

• O0ED8 Data Pro&ider

• OD8C Data Pro&ider .

8/20/2019 ADO.NET connection in C#

9/23

Data Pro&iders

•

Ca((ing base c(ass of Data #ro&ider in 'our #rogram.• using System.Data.OleDb:

• using System.Data.ODBC:

• using System.Data.SqlClient;

8/20/2019 ADO.NET connection in C#

10/23

Data Pro&iders• 5our !e' c(asses are used as a #art of the data #ro&ide

mean Data #ro&ider name,

 xxxConnection Used to connect to the database.S!lConnection"

 xxxCommand Used to execute a command against thedatabase.S!lCommand"

 xxxData#eader $ special class used to get a set o% data%rom the database that you can &iew. This

can be &iewed only se!uentially, and thedata can't be changed. #etrie&al o% !ueryresults %rom the Data SourceS!lData#eader"

 xxxData$dapter Used to get a set o% data %rom thedatabase that you can then manipulate.(ou use this to ma)e the changes to thedatabase as well 

S!lData$dapter"

8/20/2019 ADO.NET connection in C#

11/23

O0ED8 Data Pro&ider

•

O(eDb Data #ro&ider is using for Access Database.• O(eDb9s c(assess are

• OleDbConnection

• OleDbCommand

• OleDbDataReader

•

 OleDbDataAdapter

8/20/2019 ADO.NET connection in C#

12/23

Creating a Connection to the Database

•

Database is Access2 so we are going to use O(eDb dat#ro&ider.

•  To o#en a database2 7rst thing 'ou must ;connect9 todatabase.

•  

8/20/2019 ADO.NET connection in C#

13/23

Creating a Connection to the Database

•

$sing this c(ass2 'ou create a connection object%

• As 'ou can see2 ;conn9 is created in the same wa' thaobjects are created.

8/20/2019 ADO.NET connection in C#

14/23

Creating a Connection to the Database

•

Connection sting%

8/20/2019 ADO.NET connection in C#

15/23

Creating an O(edbCommand Object

•

O(eDbCommand object a((ows 'ou to s#ecif' what t'#interaction 'ou want to #erform with a database.

• 5or e1am#(e2 'ou can do se(ect2 insert2 modif'2 and decommands on rows of data in a database tab(e.

• I am going to se(ect data from database.

8/20/2019 ADO.NET connection in C#

16/23

Open Database connection.

•

Now we are going to o#en a connection to Database u;conn9 object.

8/20/2019 ADO.NET connection in C#

17/23

E1ecuting Command Object

• ?ow 'ou e1ecute a command de#ends on the resu(t 'ou e1#from the command.

•  

8/20/2019 ADO.NET connection in C#

18/23

Retrie&ing Data with a ;DataReader9

• A s#ecia( c(ass has been created to enab(e 'ou to ease@cient(' read data from a database.This is theDataReader.

•  The DataReader are that it can on(' read data )it canwrite,

• It does on(' a forward read. This means 'ou can go ththe data on(' once.

8/20/2019 ADO.NET connection in C#

19/23

After e1ecution

• After it is e1ecuted2 m'DataReader wi(( contain the resu(ts of t

command that 'ou assigned.

•  This resu(t wi(( most (i!e(' be a set o% records from the datab

•  

8/20/2019 ADO.NET connection in C#

20/23

After e1ecution

8/20/2019 ADO.NET connection in C#

21/23

Read Data from DataReader

8/20/2019 ADO.NET connection in C#

22/23

5ina( Coding

8/20/2019 ADO.NET connection in C#

23/23

Summar'

• Connection

• Command

• Data reader

• et the &a(ue