Transcript Basics of ADO.NET

Session 8:
ADO.NET
Overview

Overview of ADO.NET

What is ADO.NET?

Using Namespaces

The ADO.NET Object Model

What is a DataSet?

Accessing Data with ADO.NET

Creating a Connection to a Database

Displaying a DataSet in a List-Bound Control

Accessing Data with DataSets

Accessing Data with DataReaders

Using Multiple Tables
What is ADO.NET?
ADO.NET provides a set of classes for working with
data. ADO.NET provides:

An evolutionary, more flexible successor to ADO

A system designed for disconnected environments

A programming model with advanced XML support

A set of classes, interfaces, structures, and
enumerations that manage data access from within
the .NET Framework
Using Namespaces

Using the Imports statement to import namespaces
Imports System.Data
Imports System.Data.SqlClient

Namespaces used with ADO.NET include:

System.Data

System.Data.SqlClient

System.Data.OleDb
The ADO.NET Object Model
DataTable
DataSet
DataTable
SqlDataAdapter
OleDbDataAdapter
SQL Server .NET
Data Provider
OLE DB .NET
Data Provider
OleDbConnection
SqlConnection
SQL Server 7.0
(and later)
OLEDB sources
(SQL Server 6.5)
Using the ADO.NET Object Model
What is a Dataset?
DataSet
DataTable
DataTable
DataTable
SqlDataAdapter
SqlConnection
Web server memory
Physical storage
OleDbDataAdapter
OleDbConnection
SQL Server 2000
OleDb Database
Accessing Data with ADO.NET
1
1.
Client makes request
2.
2
Create the SqlConnection and SqlDataAdapter objects
3.
3
4.
4
Fill the DataSet from the DataAdapter
and close the connection
Return the DataSet to the Client
5.
5
Client manipulates the data
6.
6
Update the DataSet
7.
7
Use the SqlDataAdapter to open
the SqlConnection, update the
database, and close the
connection
Database
List-Bound
Control
Client
Web server
SqlConnection
SqlDataAdapter
DataSet
The DataAdapter Object Model
DataSet
DataAdapter
SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
Command
Command
Command
DataReader
Command
Connection
sp_SELECT
sp_UPDATE
sp_INSERT
Database
sp_DELETE
Generating a DataSet

You can generate a DataSet…



…through the UI…
Creates a DataSet that allows you to access data as an
object
…or through code…
Dim ds As New DataSet()
and then fill…
DataAdapter1.Fill(ds)
DataAdapter2.Fill(ds)
What are List-Bound Controls?

Controls that connect to a data source and display the
data

List-bound controls include the following:

DropDownList

DataGrid

ListBox

DataList

CheckBoxList

Repeater

RadioButtonList
Displaying DataSet Data in List-Bound Controls

Set the properties
Property

Description
DataSource
DataMember
DataTextField
 The DataSet containing the data
DataValueField
 The field in the DataTable that becomes the
value of the selected item in the list
 The DataTable in the DataSet
 The field in the DataTable that is displayed
Fill the DataSet, then call the DataBind method
DataAdapter1.Fill(ds)
lstEmployees.DataBind()
Using DataSets vs. DataReaders
DataSet
DataReader
Read/write access to data
Read-only
Includes multiple tables from
different databases
Based on one SQL statement
from one database
Disconnected
Connected
Bind to multiple controls
Bind to one control only
Forward and backward
scanning of data
Forward-only
Slower access
Faster access
Supported by Visual Studio
.NET tools
Manually coded
What is a DataReader?

Forward-only, read-only

Fast access to data

Connected to a data source

Manage the connection yourself

Manage the data yourself, or bind it to a list-bound
control

Uses fewer server resources
Creating a DataReader

To use a DataReader:
1 Create and open the database connection
1.
2 Create a Command object
2.
3 Create a DataReader from the Command object
3.
4 Call the ExecuteReader method
4.
5 Use the DataReader object
5.
6 Close the DataReader object
6.
7 Close the Connection object
7.

Use Try…Catch…Finally error handling
Reading Data from a DataReader

Call Read for each record


Returns false when there are no more records
Access fields
 Parameter is the ordinal position or name of the field
 Get functions give best performance
Do While myReader.Read()
str &= myReader(1)
str &= myReader("field")
str &= myReader.GetDateTime(2)
Loop

Close the DataReader

Close the connection
Binding a DataReader to a List-Bound Control

Create the Control
<asp:DataGrid id="dgAuthors" runat="server" />

Bind to a DataReader
dgAuthors.DataSource = dr
dgAuthors.DataBind()
SQL Server Security
Web Server
Default ASP.NET settings
Mixed mode
authentication
Here is the
username and
password
or…
Client
Windows only
authentication
Web Server
Windows authentication
Send the username
and password in
clear text.
SQL Server
Each user account added
to SQL Server logins group
Do not send the
username and
password.
Just send that the user
has been authenticated.
SQL Server
Only ASPNET account
is granted access
Creating the Connection

Using SqlConnection
Dim strConn As String = "data source=localhost; " & _
"initial catalog=northwind; integrated security=true"
Dim conn As New SqlConnection(strConn)

Setting connection string parameters

Connection timeout

Password

Data source

Persist security info

Initial catalog

Provider

Integrated security

User ID
Creating a DataAdapter

Store the query in a DataAdapter
Dim da As New SqlDataAdapter _
("select * from Authors", conn)

The DataAdapter constructor sets the SelectCommand
property
da.SelectCommand.CommandText
da.SelectCommand.Connection

Set the InsertCommand, UpdateCommand, and
DeleteCommand properties if needed
Creating a DataSet

Create and populate a DataSet with DataTables

Fill method executes the SelectCommand
Dim ds As New DataSet()
da.Fill(ds, "Authors")

Access a DataTable
ds.Tables("Authors").Rows.Count
Dim r As DataRow
Dim str As String
For Each r in _
ds.Tables("Authors").Rows
str &= r(2)
str &= r("au_lname")
Next
Using a DataView

A DataView can be customized to present a subset of
data from a DataTable

The DefaultView property returns the default DataView
of the table
Dim dv As DataView = ds.Tables("Authors").DefaultView

Setting up a different view of a DataSet
Dim dv As New DataView (ds.Tables("Authors"))
dv.RowFilter = "state = 'CA'"
Binding a DataSet to a List-Bound Control

Create the control
<asp:DataGrid id="dg" runat="server" />

Bind to a DataSet or a DataView
dg.DataSource = ds
dg.DataMember = "Authors"
dg.DataBind()
Storing Multiple Tables

Add the first table
daCustomers = New SqlDataAdapter _
("select * from Customers", conn1)
daCustomers.Fill(ds, "Customers")

Add the subsequent table(s)
daOrders = New SqlDataAdapter _
("select * from Orders", conn2)
daOrders.Fill(ds, "Orders")
Customers
conn1
conn2
DataSet
Orders
Creating Relationships

Identify parent column
Dim parentCol As DataColumn = _
ds.Tables("Customers").Columns("CustomerID")

Identify child column
Dim childCol As DataColumn = _
ds.Tables("Orders").Columns("CustomerID")

Create DataRelation
Dim dr As New DataRelation _
("name", parentCol, _
childCol)
ds.DataRelations.Add(dr)
parentCol
Customers table
DataSet
DataRelation
childCol
Orders table
Programmatically Navigating Between Tables Using
Relationships
ds.Tables(index).Rows(index).GetChildRows("relation")
ds.Tables(index).Rows(index).GetParentRow("relation")
Orders
Customers
GetChildRows
DataSet
GetParentRow
Visually Navigating Between Tables Using
Relationships
Dim tableView As DataView
Dim currentRowView As DataRowView
tableView = New DataView(ds.Tables("Customers"))
currentRowView = tableView(dgCustomers.SelectedIndex)
dgChild.DataSource = currentRowView.CreateChildView("CustOrders")
Customers
DataView
DataRowView
CreateChildView
DataSet
Orders