diff between ado and ado.net

8/8/2019 Diff Between Ado and Ado.net

1/27

Microsoft ADO.NETMicrosoft ADO.NET

Vijaya Lakshmi ByriVijaya Lakshmi ByriSupport EngineerSupport EngineerDeveloper Support, VB WebDataDeveloper Support, VB WebData

Microsoft CorporationMicrosoft Corporation


2/27

2

OverviewOverview MicrosoftMicrosoft ADO.NET componentsADO.NET components

Differences between ADO.NET and classicDifferences between ADO.NET and classic

ADOADO When and how to use classic ADO inWhen and how to use classic ADO in

Microsoft .NET applicationsMicrosoft .NET applications


3/27

3

Introduction to ADO.NET

Introduction to ADO.NET ADO.NET is a natural evolution of ADO, builtADO.NET is a natural evolution of ADO, built

around naround n--tier development and architecture,tier development and architecture,

with XML at its core.with XML at its core. ADO.NET is the new set of classes thatADO.NET is the new set of classes that

exposes data access services to the .NETexposes data access services to the .NETprogrammer.programmer.

It is an integral part of the class Framework,It is an integral part of the class Framework,which contains the entire library of classeswhich contains the entire library of classesthat Microsoft provides with .NET, includingthat Microsoft provides with .NET, includingthe base classes for the primitive systemthe base classes for the primitive systemtypes, I/O, network, data, and XML.types, I/O, network, data, and XML.


4/27

4

ADO.NET ComponentsADO.NET Components Managed providersManaged providers

DataSetsDataSets


5/27

5

Managed ProviderManaged Provider The .NET data provider is a set ofThe .NET data provider is a set of

components including the Connection,components including the Connection,

Command, DataReader, and DataAdapterCommand, DataReader, and DataAdapterobjects.objects.

The .NET data provider is designed to beThe .NET data provider is designed to belightweight, creating a minimal layer betweenlightweight, creating a minimal layer betweenthe data source and your code, increasingthe data source and your code, increasingperformance while not sacrificingperformance while not sacrificingfunctionality.functionality.


6/27

6

Managed Provider ComponentsManaged Provider Components


7/27

7

Types of Managed ProvidersTypes of Managed Providers OLEDB managed providerOLEDB managed provider

SQL managed providerSQL managed provider

ODBC managed providerODBC managed provider


8/27

8

OLEDB Managed ProviderOLEDB Managed Provider Uses native OLE DB through COMUses native OLE DB through COM

interoperability to enable data access.interoperability to enable data access.

To use the OLE DB .NET data provider, youTo use the OLE DB .NET data provider, youmust also use an OLE DB provider. Themust also use an OLE DB provider. Thefollowing providers are compatible withfollowing providers are compatible withADO.NET:ADO.NET:

Microsoft OLE DB Provider for SQL ServerMicrosoft OLE DB Provider for SQL Server Microsoft OLE DB Provider for OracleMicrosoft OLE DB Provider for Oracle

Microsoft.Jet.OLEDB.4.0 OLE DB Provider for JetMicrosoft.Jet.OLEDB.4.0 OLE DB Provider for Jet

These classes are located in theThese classes are located in the

System.Data.OleDb namespace.System.Data.OleDb namespace.


9/27

9

Sample OLEDB ConnectionSample OLEDB ConnectionDim OLEDBCn as OLEDBConnectionDim OLEDBCn as OLEDBConnection

Dim strConn as stringDim strConn as string

StrConn = "Provider=MSDAORA.1;DataStrConn = "Provider=MSDAORA.1;DataSource=dseoracle8; userSource=dseoracle8; userid=demo;password=demo;"id=demo;password=demo;"

OLEDBCn = New OLEDBCOnnectionOLEDBCn = New OLEDBCOnnection

OLEDBCn.Connectionstring = strconnOLEDBCn.Connectionstring = strconn

OLEDBCn.Open()OLEDBCn.Open()


10/27

10

SQL Managed ProviderSQL Managed Provider It uses its own protocol to communicate withIt uses its own protocol to communicate with

Microsoft SQL ServerMicrosoft SQL Server

These classes are located in theThese classes are located in theSystem.Data.SqlClient namespaceSystem.Data.SqlClient namespace


11/27

11

Sample SQL ConnectionSample SQL ConnectionDim SQLCn as SQLConnectionDim SQLCn as SQLConnection

Dim strConn as StringDim strConn as String

StrConn = "Data Source=Vijayab1; userStrConn = "Data Source=Vijayab1; userid=sa;password=Password1;Initialid=sa;password=Password1;InitialCatalog=Northwind;"Catalog=Northwind;"

SQLCn = New SQLConnectionSQLCn = New SQLConnection

SQLCn.Connectionstring = strconnSQLCn.Connectionstring = strconn

SQLCn.Open()SQLCn.Open()


12/27

12

ODBC Managed ProviderODBC Managed Provider The ODBC .NET data provider is an addThe ODBC .NET data provider is an add--onon

component to the .NET Framework SDK B2.component to the .NET Framework SDK B2.

It provides access to native ODBC drivers theIt provides access to native ODBC drivers thesame way the OLE DB .NET data providersame way the OLE DB .NET data providerprovides access to native OLE DB providers.provides access to native OLE DB providers.

The ODBC .NET data provider is intended toThe ODBC .NET data provider is intended to

work with all compliant ODBC drivers.work with all compliant ODBC drivers.


13/27

13

ODBC Managed ProviderODBC Managed Provider(2)(2) Only the following drivers have been testedOnly the following drivers have been tested

with the ODBC .NET data provider:with the ODBC .NET data provider:

Microsoft SQL ODBC DriverMicrosoft SQL ODBC Driver Microsoft ODBC Driver for OracleMicrosoft ODBC Driver for Oracle

Microsoft Jet ODBC DriverMicrosoft Jet ODBC Driver

These classes are located in theThese classes are located in the

System.Data.Odbc namespaceSystem.Data.Odbc namespace The ODBC .NET data provider also requiresThe ODBC .NET data provider also requires

the installation of MDAC 2.6 or laterthe installation of MDAC 2.6 or later


14/27

14

Sample ODBC ConnectionSample ODBC ConnectionDim odbccn As Odbc.OdbcConnection = NewDim odbccn As Odbc.OdbcConnection = New

Odbc.OdbcConnection("Driver={SQLOdbc.OdbcConnection("Driver={SQLSERVER};SERVER=Vijayab1;UID=sa;PWD=PasswordSERVER};SERVER=Vijayab1;UID=sa;PWD=Password

1;DATABASE=Northwind;")1;DATABASE=Northwind;")Dim odbcda As Odbc.OdbcDataAdapter = NewDim odbcda As Odbc.OdbcDataAdapter = New

Odbc.OdbcDataAdapter("Select * from employees",Odbc.OdbcDataAdapter("Select * from employees",odbccn)odbccn)

Dim odbcds As New DataSet()Dim odbcds As New DataSet()

odbcda.Fill(odbcds, "Employees")odbcda.Fill(odbcds, "Employees")

Me.DataGrid1.DataSource =Me.DataGrid1.DataSource =

odbcds.Tables("Employees")odbcds.Tables("Employees")


15/27

15

Managed Provider ComponentsManaged Provider ComponentsCommand ObjectCommand Object

Represents a SQL statement or storedRepresents a SQL statement or storedprocedure to execute at a data source.procedure to execute at a data source.


16/27

16

Managed Provider ComponentsManaged Provider ComponentsDataReaderDataReader

DataReader to retrieve a readDataReader to retrieve a read--only, forwardonly, forward--

only stream of data from a database.only stream of data from a database.


17/27

17

DataReader ExampleDataReader ExampleDim SQLcn As SqlClient.SqlConnectionDim SQLcn As SqlClient.SqlConnection

Dim cmd As SqlClient.SqlCommandDim cmd As SqlClient.SqlCommand

Dim dr As SqlClient.SqlDataReaderDim dr As SqlClient.SqlDataReader

Dim strconn As StringDim strconn As String

SQLcn = New SqlClient.SqlConnection()SQLcn = New SqlClient.SqlConnection()

strconn = "Data Source=byri;integrated security=SSPI;Initialstrconn = "Data Source=byri;integrated security=SSPI;Initialcatalog=Northwind"catalog=Northwind"

SQLcn.ConnectionString = strconnSQLcn.ConnectionString = strconn

SQLcn.Open()SQLcn.Open()

cmd = New SqlClient.SqlCommand("Select * from Products")cmd = New SqlClient.SqlCommand("Select * from Products")

cmd.Connection = SQLcncmd.Connection = SQLcndr = cmd.ExecuteReader(CommandBehavior.CloseConnection)dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

While dr.ReadWhile dr.Read

Me.ListBox1.Items.Add(dr.GetValue(1))Me.ListBox1.Items.Add(dr.GetValue(1))

End WhileEnd While

dr.Close()dr.Close()


18/27

18

Managed Provider ComponentsManaged Provider ComponentsDataAdapterDataAdapter

Represents a set of data commands and aRepresents a set of data commands and adatabase connection which are used to filldatabase connection which are used to fill

the DataSet and update the data source.the DataSet and update the data source.


19/27

19

DataAdapter PropertiesDataAdapter Properties SelectCommandSelectCommand

InsertCommandInsertCommand

DeleteCommandDeleteCommand UpdateCommandUpdateCommand

TableMappingsTableMappings


20/27

20

DataSetDataSetDataSet

Tables

Table

Columns

Column

Constraints

Constraint

Rows

Row

Relations

Relation


21/27

21

DataSet ExampleDataSet ExampleDim strconn As StringDim strconn As String

strconn = "Provider=SQLOLEDB.1;Data Source=byri;Integratedstrconn = "Provider=SQLOLEDB.1;Data Source=byri;Integrated

Security=SSPI;Persist Security Info=False;InitialSecurity=SSPI;Persist Security Info=False;InitialCatalog=Northwind;"Catalog=Northwind;"

Dim OLEDBCn As OleDb.OleDbConnection = NewDim OLEDBCn As OleDb.OleDbConnection = New

OleDb.OleDbConnection(strconn)OleDb.OleDbConnection(strconn)

'OLEDBCn.ConnectionString = strconn'OLEDBCn.ConnectionString = strconn

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select *Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select *from Products", OLEDBCn)from Products", OLEDBCn)

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()

da.SelectCommand = cmdda.SelectCommand = cmd

OLEDBCn.Open()OLEDBCn.Open()

Dim ds As New DataSet()Dim ds As New DataSet()

da.Fill(ds, "Products")da.Fill(ds, "Products")


22/27

22

Differences Between ADO andDifferences Between ADO andADO.NETADO.NET

ADOADO ADO.NETADO.NETUses RecordsetUses Recordset Uses DataSetUses DataSet

Requires JOIN queryRequires JOIN query Supports DataRelationSupports DataRelation

Provided by the RecordSetProvided by the RecordSetbut typically supportsbut typically supports

connected access,connected access,represented by therepresented by theConnection objectConnection object

Communicates to aCommunicates to adatabase with standardizeddatabase with standardized

calls to the DataAdaptercalls to the DataAdapterobjectobject


23/27

23

Differences Between ADO andDifferences Between ADO andADO.NETADO.NET (2)(2)

ADOADO ADO.NETADO.NET

Uses serverUses server--side and clientside and client--side cursorsside cursors

The architecture isThe architecture isdisconnected so cursorsdisconnected so cursorsare not applicableare not applicable

Database locks and activeDatabase locks and active

database connectionsdatabase connections

Does not retain databaseDoes not retain database

lockslocks


24/27

24

When toU

se ADO in .NETWhen toU

se ADO in .NETApplicationsApplications

Client/server applicationsClient/server applications

ServerServer--side cursorsside cursors

Pessimistic lockingPessimistic locking


25/27

25

How to

Use ADO

How to

Use ADO

Set a reference to Microsoft ActiveXSet a reference to Microsoft ActiveX DataDataObject 2.Object 2.xx library by selecting COM tab (clicklibrary by selecting COM tab (clickProject and then References)Project and then References)

The ADODB object should be usedThe ADODB object should be used


26/27

26

Using ADO in .NETUsing ADO in .NETExample:Example:

Dim cn As New ADODB.ConnectionDim cn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim rs As New ADODB.Recordset

Dim strconn As StringDim strconn As String

strconn = "Provider=SQLOLEDB.1;Data Source=Byri;Integratedstrconn = "Provider=SQLOLEDB.1;Data Source=Byri;Integrated

Security=SSPI;Persist security info=false;Initial Catalog=Northwind;"Security=SSPI;Persist security info=false;Initial Catalog=Northwind;"

cn.Open(strconn)cn.Open(strconn)

cn.CursorLocation = ADODB.CursorLocationEnum.adUseClientcn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

rs.Open("select * from products", cn,rs.Open("select * from products", cn,

ADODB.CursorTypeEnum.adOpenStatic,ADODB.CursorTypeEnum.adOpenStatic,

ADODB.LockTypeEnum.adLockReadOnly)ADODB.LockTypeEnum.adLockReadOnly)

Dim DA As OleDb.OleDbDataAdapterDim DA As OleDb.OleDbDataAdapter

DA = New OleDb.OleDbDataAdapter()DA = New OleDb.OleDbDataAdapter()

Dim DS As New DataSet()Dim DS As New DataSet()

DA.Fill(DS, rs, "RECORDSET")DA.Fill(DS, rs, "RECORDSET")


27/27

diff between ado and ado.net

Documents