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]

CodeDim conn As SqlConnection = Nothing

Tryconn = 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 = readerMe.ddRegion.DataBind() Me.ddRegion.Items.Insert(0, New ListItem("Select One:", ""))Catch exp As ExceptionlblOutput.Text = "Error occurred: " + exp.MessageFinallyIf (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.StoredProcedurecmd.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 = NothingTryconn = New SqlConnection(connStr)‘Create DataAdapterDim da As New SqlDataAdapter("Select StateCode, " & _"StateName From States", conn)‘Create DataSetDim ds As New DataSet‘Fill DataSetda.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!!