Working with Data in ASP'NET Peter Lee Senior Technology Specialist Microsoft Hong Kong

1
Working with Data in ASP.NETPeter LeeSenior
Technology SpecialistMicrosoft Hong Kong
2
Overview

• Overview of ADO.NET
• Connecting to a Data Source
• Accessing Data with DataSets
• Using Stored Procedures
• Accessing Data with DataReaders
• Binding to XML Data

3
Overview of ADO.NET

• The ADO.NET Object Model
• RecordSets vs. DataSets
• Using Namespaces

4
The ADO.NET Object Model
.ASPX Page
DataReader
Command
Company Northwind Traders
Database
Connection
DataSetCommand
DataView
DataView
DataSet
List-Bound Control
.ASPX Page
5
RecordSets vs. DataSets

• DataSet
• Multiple Tables
• Includes Relationship
• Navigate via Relationship
• Disconnected
• Transmit XML File

• Recordset
• One Table
• Based on Join
• Move Row by Row
• Connected or Disconnected
• COM Marshalling

6
Using Namespaces

• Use the Import Construct to Declare Namespaces
• Namespaces Used with ADO.NET Include
• System.Data
• System.Data.ADO
• System.Data.SQL
• System.Data.XML
• System.Data.SQLTypes

lt_at_ Import Namespace"System.Data" gt lt_at_ Import
Namespace"System.Data.SQL" gt
7
Connecting to a Data Source

• Using SQLConnection
• Using ADOConnection

Dim strConn As String _ "serverlocalhost
uidsapwd databasenorthwind" Dim conn As
SQLConnection New SQLConnection(strConn)
Dim strConn As String "Provider SQLOLEDB.1 "
_ "Data Sourcelocalhost uidsa pwd "
_ "InitialCatalognorthwind" Dim conn As
ADOConnection New ADOConnection(strConn)
8
Accessing Data with DataSets

• Using DataSets to Read Data
• Storing Multiple Tables in a DataSet
• Using DataViews
• Displaying Data in the DataGrid Control
• Demonstration Displaying Data in a DataGrid
• Using Templates
• Using the Repeater Control
• Demonstration Displaying Data in a Repeater
  Control

9
Using DataSets to Read Data

• Create the Database Connection
• Store the Query in a DataSetCommand
• Create and Populate the DataSet with DataTables

Dim cmdAuthors As SQLDataSetCommand cmdAuthors
New SQLDataSetCommand _ ("select from
Authors", conn)
Dim ds As DataSet ds New DataSet() cmdAuthors.Fi
llDataSet(ds, "Authors")
10
Storing Multiple Tables in a DataSet

• Add the First Table
• Add the Subsequent Table(s)

command New SQLDataSetCommand _ ("select
from Authors", conn) command.FillDataSet(ds,
"Authors")
command.SelectCommand New SQLCommand _
("select from Books", conn) command.FillDataSet(
ds, "Books")
Books
DataSet
Authors
Data Tables
11
Using DataViews

• DataViews Can be Customized to Present a Subset
  of Data from a DataTable
• The DefaultView Property Returns the Default
  DataView for the Table
• Setting Up a Different View of a DataSet

Dim dv as DataView dv ds.Tables("Authors").Defau
ltView
Dim dv as DataView dv New DataView
(ds.Tables("Authors")) dv.RowFilter "state
'CA'"
12
Displaying Data in the DataGrid Control

• Create the Control
• Bind to a DataView

ltaspDataGrid id"dgAuthors" runat"server" /gt
dgAuthors.DataSourceds.Tables("Authors").DefaultV
iew dgAuthors.DataBind()
13
Using Templates
HeaderTemplate


ItemTemplate
SeparatorTemplate
AlternatingItemTemplate
FooterTemplate
14
Using the Repeater Control

• Create the Control and Bind to a DataView
• Display Data in Templated Elements

ltaspRepeater id"repList" runat"server"gt lttempla
te name"ItemTemplate"gt lt
Container.DataItem("au_lname") gt lt/templategt lt/as
pRepeatergt
15
Using Stored Procedures

• Calling Stored Procedures
• Passing Parameters
• Calling Action Stored Procedures
• Demonstration Calling Stored Procedures

16
Calling Stored Procedures

• Stored Procedures Provide Security for Database
• Set up the DataSetCommand
• Run the Stored Procedure and Store Returned
  Records

Dim cmd as SQLDataSetCommand cmd New
SQLDataSetCommand() With cmd.SelectCommand .Activ
eConnection conn .CommandText
"ProductCategoryList" .CommandType
CommandType.StoredProcedure End With
cmd.FillDataSet(ds, "Categories")
17
Passing Parameters

• Create Parameter, Set Direction and Value, Add to
  the Parameters Collection
• Run Stored Procedure

workParam New SQLParameter("_at_CategoryID",
_ SQLDataType.Int) workParam.Direction
ParameterDirection.Input workParam.Value
CInt(txtCatID.Text) cmd.SelectCommand.Parameters.
Add (workParam)
ds new DataSet() cmd.FillDataSet(ds, "Products")
18
Calling Action Stored Procedures

• Use SQLCommand Object
• Call the ExecuteNonQuery Method
• Retrieve Output Parameters

Dim myCmd As SQLCommand New SQLCommand
_ ("OrdersCount", conn)
conn.Open() myCmd.ExecuteNonQuery() conn.Close()
curSales myCmd.Parameters("_at_ItemCount").Value
19
Accessing Data with Datareader

• Creating a DataReader
• Reading Data from a DataReader
• Demonstration Accessing Data Using DataReaders
• Using DataSets vs. DataReaders

20
Creating a DataReader

• Create and Open the Database Connection
• Create the DataReader From a Command Object
• Close the Connection

Dim conn As SQLConnection New SQLConnection _
("serverlocalhostuidsapwddatabasepubs") con
n.Open()
Dim cmdAuthors As SQLCommand New SQLCommand
_ ("select from Authors", conn) Dim dr As
SQLDataReader cmdAuthors.Execute(dr)
21
Reading Data from a DataReader

• Call Read for Each Record
• Returns false when there are no more records
• Call Get for Each Field
• Parameter is the ordinal position of the field
• Call Close to Free Up the Connection

myReader.Read() lblName.Text myReader.GetString(
1) ", " _ myReader.GetString(2) myReader.Clo
se()
22
Using DataSets vs. DataReaders

• DataSet
• Create a database connection
• Store query in DataSetCommand
• Populate DataSet with FillDataSet method
• Create DataView
• Bind DataView to list-bound control

• DataReader
• Create a database connection
• Open the database connection
• Store query in Command
• Populate DataReader with Execute method
• Call Read for each record, and Get for each field
• Manually display data
• Close the DataReader and the connection

23
Binding to XML Data

• Overview of XML
• Reading XML Data into a DataSet
• Demonstration Reading XML Data into a DataSet

24
Overview of XML

• Machine-Readable and Human-Readable Data
• Defines the Data Content and Structure
• Separates Structure From Presentation
• Allows You to Define Your Own Tags and Attributes

ltemployeegt ltnamegtJakelt/namegt
ltsalarygt25000lt/salarygt ltregiongtOhiolt/regiongt lt/e
mployeegt
25
Reading XML Data into a DataSet

• Read the XML File
• Read the Contents of the File Stream
• Read Data From the StreamReader into a DataSet

fs New FileStream _ (Server.MapPath("schemada
ta.xml"), _ FileMode.Open, FileAccess.Read)
Reader New StreamReader(fs)
ds.ReadXml(Reader)
26
Review

• Overview of ADO.NET
• Connecting to a Data Source
• Accessing Data with DataSets
• Using Stored Procedures
• Accessing Data with DataReaders
• Binding to XML Data