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

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

What is ADO.NET?

Using Namespaces

Using the Imports statement to import namespaces

Namespaces used with ADO.NET include:

System.Data

System.Data.SqlClient

System.Data.OleDb

Imports System.DataImports System.Data.SqlClientImports System.DataImports System.Data.SqlClient

DataSet

SQL Server .NET Data Provider

OLE DB .NET Data Provider

SQL Server 7.0(and later)

OLEDB sources(SQL Server 6.5)

OleDbConnectionOleDbConnection

OleDbDataAdapterOleDbDataAdapterSqlDataAdapterSqlDataAdapter

SqlConnectionSqlConnection

DataTable

DataTable

The ADO.NET Object Model

Using the ADO.NET Object Model

SQL Server 2000

DataSet

DataTable

DataTable

Physical storage

OleDb Database

SqlDataAdapterSqlDataAdapter

SqlConnectionSqlConnection

DataTable

Web server memory

OleDbDataAdapterOleDbDataAdapter

OleDbConnection

OleDbConnection

What is a Dataset?

Accessing Data with ADO.NET

DatabaseDatabase

4. Return the DataSet to the Client

5. Client manipulates the data

2. Create the SqlConnection and SqlDataAdapter objects

3. Fill the DataSet from the DataAdapter and close the connection

SqlDataAdapter

SqlConnection

List-Bound

Control

List-Bound

Control

1. Client makes request1111

2222

3333

4444

5555

6. Update the DataSet

7. Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection

6666

7777

ClientClient

Web serverWeb server

DataSet

The DataAdapter Object Model

sp_SELECT

CommandCommand

SelectCommand UpdateCommand InsertCommand DeleteCommand

DataAdapter

CommandCommand CommandCommand CommandCommand

ConnectionConnection

sp_UPDATE sp_INSERT sp_DELETE

Database

DataSetDataSet

DataReaderDataReader

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…

and then fill…

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

DataAdapter1.Fill(ds)DataAdapter2.Fill(ds)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

ListBox

CheckBoxList

RadioButtonList

DataGrid

DataList

Repeater

Displaying DataSet Data in List-Bound Controls

Set the properties

Fill the DataSet, then call the DataBind methodDataAdapter1.Fill(ds)lstEmployees.DataBind()DataAdapter1.Fill(ds)lstEmployees.DataBind()

PropertyPropertyPropertyProperty DescriptionDescriptionDescriptionDescription

DataSourceDataSource The DataSet containing the data The DataSet containing the data

DataMemberDataMember The DataTable in the DataSet The DataTable in the DataSet

DataTextFieldDataTextField The field in the DataTable that is displayed The field in the DataTable that is displayed

DataValueFieldDataValueField The field in the DataTable that becomes the value of the selected item in the list

The field in the DataTable that becomes the value of the selected item in the list

Using DataSets vs. DataReaders

Supported by Visual Studio .NET tools

Slower access

Forward-only

Bind to one control only

Based on one SQL statement from one database

Read-only

Manually coded

Faster access

Forward and backward scanning of data

Bind to multiple controls

Includes multiple tables from different databases

Read/write access to data

DataReaderDataSet

Disconnected Connected

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

2. Create a Command object

3. Create a DataReader from the Command object

4. Call the ExecuteReader method

5. Use the DataReader object

6. Close the DataReader object

7. Close the Connection object Use Try…Catch…Finally error handling

1111

2222

3333

4444

5555

6666

7777

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

Close the DataReader

Close the connection

Do While myReader.Read() str &= myReader(1) str &= myReader("field") str &= myReader.GetDateTime(2)Loop

Do While myReader.Read() str &= myReader(1) str &= myReader("field") str &= myReader.GetDateTime(2)Loop

Binding a DataReader to a List-Bound Control

Create the Control

Bind to a DataReader

dgAuthors.DataSource = drdgAuthors.DataBind()

dgAuthors.DataSource = drdgAuthors.DataBind()

<asp:DataGrid id="dgAuthors" runat="server" /><asp:DataGrid id="dgAuthors" runat="server" />

SQL Server Security

ClientClient

Send the username and password in

clear text.

Do not send the username and

password. Just send that the user has been authenticated.

Mixed modeauthenticationMixed mode

authentication

Windows onlyauthenticationWindows onlyauthentication

SQL ServerOnly ASPNET account

is granted access

SQL ServerOnly ASPNET account

is granted accessWeb ServerWindows authentication

Web ServerWindows authentication

or…or…

SQL ServerEach user account added

to SQL Server logins group

SQL ServerEach user account added

to SQL Server logins group

Web ServerDefault ASP.NET settings

Web ServerDefault ASP.NET settings

Here is the username and

password

Creating the Connection

Using SqlConnection

Setting connection string parameters

Connection timeout

Data source

Initial catalog

Integrated security

Dim strConn As String = "data source=localhost; " & _ "initial catalog=northwind; integrated security=true"Dim conn As New SqlConnection(strConn)

Dim strConn As String = "data source=localhost; " & _ "initial catalog=northwind; integrated security=true"Dim conn As New SqlConnection(strConn)

Password

Persist security info

Provider

User ID

Store the query in a DataAdapter

The DataAdapter constructor sets the SelectCommand property

Set the InsertCommand, UpdateCommand, and DeleteCommand properties if needed

Creating a DataAdapter

Dim da As New SqlDataAdapter _("select * from Authors", conn)

Dim da As New SqlDataAdapter _("select * from Authors", conn)

da.SelectCommand.CommandText da.SelectCommand.Connection

da.SelectCommand.CommandText da.SelectCommand.Connection

Creating a DataSet

Create and populate a DataSet with DataTables

Fill method executes the SelectCommand

Access a DataTable

Dim ds As New DataSet()da.Fill(ds, "Authors")

Dim ds As New DataSet()da.Fill(ds, "Authors")

Dim r As DataRowDim str As StringFor Each r in _ ds.Tables("Authors").Rows str &= r(2) str &= r("au_lname")Next

Dim r As DataRowDim str As StringFor Each r in _ ds.Tables("Authors").Rows str &= r(2) str &= r("au_lname")Next

ds.Tables("Authors").Rows.Countds.Tables("Authors").Rows.Count

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

Setting up a different view of a DataSet

Dim dv As DataView = ds.Tables("Authors").DefaultView Dim dv As DataView = ds.Tables("Authors").DefaultView

Dim dv As New DataView (ds.Tables("Authors"))dv.RowFilter = "state = 'CA'"

Dim dv As New DataView (ds.Tables("Authors"))dv.RowFilter = "state = 'CA'"

Binding a DataSet to a List-Bound Control

Create the control

Bind to a DataSet or a DataView

dg.DataSource = dsdg.DataMember = "Authors"dg.DataBind()

dg.DataSource = dsdg.DataMember = "Authors"dg.DataBind()

<asp:DataGrid id="dg" runat="server" /><asp:DataGrid id="dg" runat="server" />

Storing Multiple Tables

Add the first table

Add the subsequent table(s)

daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1)daCustomers.Fill(ds, "Customers")

daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1)daCustomers.Fill(ds, "Customers")

Orders

Customers

daOrders = New SqlDataAdapter _ ("select * from Orders", conn2)daOrders.Fill(ds, "Orders")

daOrders = New SqlDataAdapter _ ("select * from Orders", conn2)daOrders.Fill(ds, "Orders")

conn2conn1

DataSet

Creating Relationships

Identify parent column

Identify child column

Create DataRelation

Dim dr As New DataRelation _ ("name", parentCol, _ childCol)ds.DataRelations.Add(dr)

Dim dr As New DataRelation _ ("name", parentCol, _ childCol)ds.DataRelations.Add(dr)

Dim parentCol As DataColumn = _ ds.Tables("Customers").Columns("CustomerID")

Dim parentCol As DataColumn = _ ds.Tables("Customers").Columns("CustomerID")

Dim childCol As DataColumn = _ ds.Tables("Orders").Columns("CustomerID")

Dim childCol As DataColumn = _ ds.Tables("Orders").Columns("CustomerID")

Orders table

Customers table

DataSet

parentCol

childCol

DataRelation

Programmatically Navigating Between Tables Using Relationships

ds.Tables(index).Rows(index).GetChildRows("relation")ds.Tables(index).Rows(index).GetParentRow("relation")

ds.Tables(index).Rows(index).GetChildRows("relation")ds.Tables(index).Rows(index).GetParentRow("relation")

Customers Orders

GetChildRows

GetParentRowDataSet

Visually Navigating Between Tables Using Relationships

Dim tableView As DataViewDim currentRowView As DataRowView

tableView = New DataView(ds.Tables("Customers"))currentRowView = tableView(dgCustomers.SelectedIndex)dgChild.DataSource = currentRowView.CreateChildView("CustOrders")

Dim tableView As DataViewDim currentRowView As DataRowView

tableView = New DataView(ds.Tables("Customers"))currentRowView = tableView(dgCustomers.SelectedIndex)dgChild.DataSource = currentRowView.CreateChildView("CustOrders")

Customers Orders

CreateChildView

DataRowView

DataView

DataSet