Transcript ADO.NET - Part II

ADO .NET – part II
August 2004
[ Marmagna Desai]
CONTENTS







ADO vs ADO .NET
ADO .NET – Managed providers
Connecting to Database
 SqlConnection
Selecting Database
 SqlCommand
 SqlDataReader
Inserting Data
 SqlCommand
Datasets
 Selecting Data
 Updating Data
Conclusion
ADO vs ADO .net
Ultimate goal of ADO and ADO.net is
same: Providing data access API.
Though there are many differences.
ADO relies on COM
 ADO.net relies on “managed-providers”
defined by CLR

ADO vs ADO .net ……[cont]
Main Differences:
ADO
ADO.NET
Connection Model
Connection Oriented
Model is used mostly.
Disconnected Model is
used: Messeage-like
Model.
Disconnected Access
Provided by RecordSet
Provided by
DataAdapter and
Dataset
Data Representation
One Recordset for one
Table
One Dataset for many
interrelated tables with
relationships
Data Exchange
Binary Mode – Firewall
Problem
Use XML and XSD
schema types are fully
supported
XML Support
Limited
Robust Support
ADO vs ADO .net ……[cont]
Connection Model:

ADO:



Client application needs to be connected always to data-server
while working on the data.
These results in open connections for long time and thus data
can not be accessed in parallel.
ADO .NET

Client disconnects connection immediately the data is
processed. This will cache data at client side to achieve better
performance.
Hence ADO .net creates “disconnected version of RecordSet
object
ADO vs ADO .net ……[cont]
Data Representation:


Recordsets are generated one per table. This does not support
hierarchical structure of data access. It will be programmer’s
responsibility to define relationships among different recordsets.
Rercordsets can not support data accessing from multiple sources.
Above limitations are resolved by implementation of Dataset objects in
ADO.net mode
ADO vs ADO .net ……[cont]
Data Passing:

ADO objects communicate in binary mode hence it will be really difficult
to pass data across firewalls.

ADO .net use XML for passing data
ADO .net Managed Providers
ADO .net provides following three classes



DBConnection
DBCommand
DBDataReader
These classes are never used directly. The inherited set of classes
called “managed providers” are used for different functionalities of
data access
ADO .net Managed Providers
These are the different providers




Provider optimized for SQL 7
OLE – DB provider
A generic ODBC provider
Provider for Oracle
These providers gathers information on various data sources and how
to interact with them

E.g.
SQL data provider uses private protocol (tabular data stream) to provide
information and access methods on SQL server.
Connecting to Database
Following methods are very similar to ADO. They are connection
oriented and hence leave open connection while retrieving the data
from source.


SQL managed provider contains the class called SqlConnection.
This class is used to connect to the sql database.

Code:
[ Please see next slide]
Code…..
Dim connStr as String =
“server=localhost;uid=uid;pwd=pwd;database=northwind;"
Dim conn as New SqlConnection (connStr)
Conn.Open()
“Access Data Records
Conn.Close()
This is very similar to ADO program. Only the SqlConnection will be
replaced with ADODB.Connection.
Selecting Data
SqlCommand class is defined in ADO .net to provide different
functionalities like insert,update,delete etc. on selected database.
It is similar to ADODB.Command class in ADO.
SqlCommand retrieves and place data on objects called SqlDataReader
SqlCommand does this using ExecuteReader() method.
[Please see next slide for Code]
Code
Dim conn As SqlConnection = Nothing
Try
conn = New SqlConnection(connStr)
Dim cmd As New SqlCommand("Select StateCode, " &
"StateName From States", conn)
conn.Open()
Dim reader As SqlDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Me.ddRegion.DataSource = reader
Me.ddRegion.DataBind()
Me.ddRegion.Items.Insert(0, New ListItem("Select One:", ""))
Catch exp As Exception
lblOutput.Text = "Error occurred: " + exp.Message
Finally
If (conn.State <> ConnectionState.Closed) Then conn.Close()
End Try
Explanation
A connection is made using SqlConnection
SqlCommand is used to construct a query
ExecuteReader is used to generate SqlDataReader object
Bind data to DropDownList Server name ddRegion
Inserting Data
SqlCommand can be used to perform
insert,delete,update etc. operations on data.
ExecuteNonQuery method is used for this
purpose.
This method does not allow results to return
to the database.
[Please see code in next slide]
Code
Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand("CustInsert", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@CustomerID",
Me.txtCustomerID.Text)
cmd.Parameters.Add("@Name", Me.txtName.Text)
cmd.Parameters.Add("@Company", Me.txtCompany.Text)
cmd.Parameters.Add("@Title", Me.txtTitle.Text)
cmd.Parameters.Add("@Address", Me.txtAddress.Text)
cmd.Parameters.Add("@City", Me.txtCity.Text)
cmd.Parameters.Add("@Region",
Me.ddRegion.SelectedValue)
cmd.Parameters.Add("@Zip", Me.txtZip.Text)
Code ….[cont]
Try
conn.Open()
cmd.ExecuteNonQuery()
lblOutput.Text = "Your information was successfully
saved!"
Catch exp As Exception
lblOutput.Text = "An error occurred:" + exp.Message
Finally 'Ensure connection is closed
If (conn.State <> ConnectionState.Closed) Then
conn.Close()
End Try
Datasets

Datasets are new to ADO .net.

It access data in Disconnected in maner

It stores data in-memory and process it.
Datasets….[cont]
Following are main features
 Datasets does not interact with data source directly.
 Data adapter is used to fill this gap. : SqlDataAdapter class
 SqlDataAdapter class provides functionalies such as insert, delete
and update.
 SqlDataAdapter also provides method called Fill() which is used to
fill up the Dataset.
 Once dataset is filled with data, it can define relationships among
gathered data from different source.
 Dataset uses XML to transmit data among different components.
 Different views of data stored in dataset can be created using
dataview calss.
Code
Following code shows how to query database using SqlDataAdapter and fill
the Dataset.
Dim conn As SqlConnection = Nothing
Try
conn = New SqlConnection(connStr)
‘Create DataAdapter
Dim da As New SqlDataAdapter("Select StateCode, " & _
"StateName From States", conn)
‘Create DataSet
Dim ds As New DataSet
‘Fill DataSet
da.Fill(ds, "States")
Code …cont
‘Access a DataTable in the DataSet and create a bindable view
Dim view As DataView = ds.Tables(0).DefaultView
Me.ddRegion.DataSource = view
Me.ddRegion.DataBind()
Me.ddRegion.Items.Insert(0, New ListItem("Select One:", ""))
Catch exp As Exception
lblOutput.Text = "Error occurred: " + exp.Message
Finally
If (conn.State <> ConnectionState.Closed) Then conn.Close()
End Try
Conclusion
Basically ADO .net adds following
extended capabilities to ADO.




Disconnected modeling
Relational Database query and in-memory storage
Hierarchical structure of data
XML based transmission of data among components
These additional features makes ADO .net considerable
advancement in Microsoft Technology
Q?
THANKS!!