Transcript ADO.NET Slides - FOCIT

Developing Web
Applications Using
Microsoft® Visual
Studio® 2008
Module 5: Accessing Data with Microsoft ADO.NET and
Visual Studio 2008
• Overview of ADO.NET
• Connecting to a Database
• Accessing Data
• Accessing Multiple Tables
Lesson: Overview of ADO.NET
• What Is ADO.NET?
• The ADO.NET Object Model
• DataSets and DataReaders
• Accessing Data with ADO.NET
What Is ADO.NET?
ADO.NET provides a set of classes for working with
data. ADO.NET is:
• 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 in
the .NET Framework
The ADO.NET Object Model
DataSet
DataTable
DataTable
ODBC
Data Provider
SQL Server
Data Provider
OLE DB .NET
Data Provider
ODBC
sources
SQL Server
7.0 (and later)
OLEDB sources
(SQL Server 6.5)
Oracle
Data Provider
Oracle
sources
DataSets and 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
2008 Designer
Manually coded
Accessing Data with ADO.NET
1
Client makes request
2
Create a SqlDataSource
3
Return the data to the client
4
Client manipulates the data
5
Update the data
6
Use the SqlDataSource to open a
database connection, update the
database, and close the connection
Database
Web
server
SqlDataSource
GridView
Control
Client
Lesson: Connecting to a Database
• Generating a Connection by Using Server Explorer
• The DataAdapter Object Model
• Generating a DataSet
• Creating a Connection Programmatically
Generating a Connection by Using Server Explorer
• In Server Explorer, right-click Data Connections, and then
click Add Connection.
• Configure the connection
The DataAdapter Object Model
DataSet
DataAdapter
SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
Command
Command
Command
Command
Connection
SELECT
UPDATE
INSERT
Database
DELETE
Generating a DataSet
• Creating a DataSet
[Visual C#]
DataSet myDataSet =
new DataSet();
[Visual Basic]
Dim myDataSet As _
New DataSet()
• Filling the DataSet
[Visual C#]
myDataAdapter1.Fill(ds);
myDataAdapter2.Fill(ds);
[Visual Basic]
myDataAdapter1.Fill(ds)
myDataAdapter2.Fill(ds)
Creating a Connection Programmatically
• Creating a SqlConnection instance
[Visual C#]
string connectionString = "data source=localhost; " +
"initial catalog=northwind; integrated security=true";
SqlConnection connection = new SqlConnection(connectionString);
[Visual Basic]
Dim connectionString As String = "data source=localhost; " & _
"initial catalog=northwind; integrated security=true"
Dim connection As New SqlConnection(connectionString)
• Setting connection string parameters

Connection timeout

Password

Data source

Persist security info

Initial catalog

Provider

Integrated security

User ID
Lesson: Accessing Data
• Binding Data to Controls by Using the IDE
• Creating a Command Object
• Creating a DataReader
• Retrieving Data by Using a DataReader
• Creating a DataSet
• Displaying a DataSet in a List-Bound Control
• Handling Errors
Binding Data to Controls by Using the IDE
• Add a GridView control to the Web Form
• Bind the GridView control to a SqlDataSource control
that contains the connection and query information
• SqlDataSource properties:

ConnectionString. The connection string to connect to the
database.

ProviderName. The database type.
• GridView properties:

Columns. The set of columns to be shown in the control

DataSourceID. The control ID of a data source
Creating a Command Object
• ExecuteReader. Returns a DataReader object
• ExecuteScalar. Returns a single scalar value object
• ExecuteNonQuery. Executes a command that does not
return any rows
• ExecuteXmlReader. Returns an XmlReader object
[Visual C#]
myCommand.Connection.Open();
SqlDataReader myDataReader = myCommand.ExecuteReader();
// Process the results.
myCommand.Connection.Close();
[Visual Basic]
myCommand.Connection.Open()
Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
' Process the results.
myCommand.Connection.Close()
Creating 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
Retrieving Data by Using 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

[Visual C#]
while (myDataReader.Read()) {
text += myDataReader[1];
text += myDataReader["field"];
text +=
myDataReader.GetDateTime(2);
}
• Close the DataReader
• Close the connection
[Visual Basic]
Do While myDataReader.Read()
text &= myDataReader(1)
text &= myDataReader ("field")
text &=
myDataReader.GetDateTime(2)
Loop
Creating a DataSet
• Create and populate a DataSet with DataTable objects
 Fill
method executes the SelectCommand
[Visual C#]
DataSet myDS = new DataSet();
myDA.Fill(myDataSet,
"Authors");
[Visual Basic]
Dim myDS As New DataSet()
myDA.Fill(myDataSet, _
"Authors")
• Access a DataTable
[Visual C#]
myDataSet.Tables[
"Authors"].Rows.Count;
...
string text = "";
foreach(DataRow row in
myDS.Tables["Authors"].Rows)
{
text += row[1];
text += row["LastName"];
}
[Visual Basic]
myDS.Tables( _
"Authors").Rows.Count
...
Dim row As DataRow
Dim text As String
For Each row in
myDS.Tables("Authors").Rows
text &= r(1)
text &= r("LastName")
Next
Displaying DataSet Data in List-Bound Controls
• Set the properties
Property
Description
DataSource
The DataSet containing the data
DataMember
The DataTable in the DataSet
DataTextField
The field in the DataTable that is displayed
DataValueField
The field in the DataTable that becomes the
value of the selected item in the list
• Fill the DataSet, then call the DataBind method
[Visual C#]
myDataAdapter.Fill(myDataSet)
employeesList.DataBind();
[Visual Basic]
myDataAdapter.Fill(myDataSet)
employeesList.DataBind()
Handling Exceptions
• Connection will not open

Connection string is invalid

Server or database not found

Login failed
• DataAdapter cannot create
a DataSet

Invalid SQL syntax

Invalid table or field name
[Visual C#]
catch (System.Data.SqlClient.SqlException ex1)
[Visual
Basic]
{
Catch
ex1 As System.Data.SqlClient.SqlException
switch(ex1.Number)
Select
Case ex1.Number
{
...
...
Case
case 18452
18452:
errorsLabel.Text
errorsLabel.Text =
= errorsLabel.Text
errorsLabel.Text &
+ _
("Invaliduser
username");
name")
("Invalid
... break;
End Select
...
End Try
}
}
Lesson: Accessing Multiple Tables
• Storing Data From Multiple Tables
• Creating Relationships
• Programmatically Navigating Between Tables by
Using Relationships
Storing Data From Multiple Tables
• Add the first table
[Visual Basic]
C#]
customersDataAdapter = new SqlDataAdapter _
("select * from Customers", connection1)
connection1);
customersDataAdapter.Fill(myDataSet, "Customers")
"Customers");
• Add the subsequent table(s)
[Visual Basic]
C#]
ordersDataAdapter = new SqlDataAdapter _
("select * from Orders", connection2)
connection2);
customersDataAdapter.Fill(myDataSet, "Orders")
"Orders");
connection1
Customers
Orders
DataSet
connection2
Creating Relationships
• Identify parent column
• Identify child column
parentColumn
Customers table
DataRelation
• Create DataRelation
DataSet
childColumn
Orders table
[Visual Basic]
C#]
Dim
cORelation
As DataRelation
DataRelation
coDataRelation;
Dim
parentColumn
As DataColumn,
childColumn As DataColumn
DataColumn
parentColumn,
childColumn;
parentColumn = _
myDataSet.Tables("Customers").Columns("CustomerID")
myDataSet.Tables["Customers"].Columns["CustomerID"];
childColumn = _
myDataSet.Tables("Orders").Columns("CustomerID")
myDataSet.Tables["Orders"].Columns["CustomerID"];
cODataRelation = New
new DataRelation("CustomerOrders"
DataRelation("CustomerOrders",_
,
parentColumn,
childColumn)
parentColumn,
childColumn);
myDataSet.Relations.Add(cODataRelation)
myDataSet.Relations.Add(cODataRelation);
Programmatically Navigating Between Tables by
Using Relationships
[Visual Basic]
ds.Tables(index).Rows(index).GetChildRows("relation")
ds.Tables(index).Rows(index).GetParentRow("relation")
[Visual C#]
ds.Tables[index].Rows[index].GetChildRows("relation");
ds.Tables[index].Rows[index].GetParentRow("relation");
Orders
Customers
GetChildRows
DataSet
GetParentRow
Lab: Accessing Data with Microsoft ADO.NET and
Visual Studio 2008
• Exercise 1: Connecting to the Doctors Database
• Exercise 2: Paging and Selection in a GridView Control
• Exercise 3: Implementing a SqlDataReader
• Exercise 4: (If Time Permits) Viewing Doctors from
All Cities
Logon information
Virtual machine
2310C-LON-DEV-08
User name
Student
Password
Pa$$w0rd
Estimated time: 45 minutes
Lab Scenario
Master Page
Logon Page
login.aspx
Lab Web
Application
Benefits
Home Page
Default.aspx
benefitsMaster.master
Page Header
ASPState
header.ascx
Menu Component
Registration
Benefits.cs or Benefits.vb
register.aspx
TempDB
Web.
config
Life Insurance
life.aspx
Retirement
retirement.aspx
Prospectus
prospectus.aspx
LINQ to SQL
Classes
Medical
medical.aspx
Doctors
doctors.aspx
Dentists
dental.aspx
User Control
nameDate.ascx
XML Web
Service
DentalService1.asmx
Doctors.dbml
XML Files
Doctors
Dentists
Lab Review
Review Questions
• How can you add a connection to a database?
• What controls are created when you drag a table from
Server Explorer to a Web page?
• How can you enable paging for a GridView control?
• How can you add a select column to a GridView control?
• When you use Connection and SqlDataReader objects,
what must you do?
Module Review and Takeaways
• Review Questions
• Real-World Issues and Scenarios
• Best Practices