ADO Data Handling in ASP

1
ADOData Handling in ASP

• Dave Sussman

2
Agenda

• Why?
• The ADO object model
• Objects in detail
• Data binding templating
• Data manipulation
• Data modification

3
Why ADO?

• The Internet changes everything
• Inherently disconnected
• Inherently disparate
• XML
• An emergent data format
• Heterogeneous data sources

4
What is ADO

• Evolution of ADO
• ADO is not a replacement
• It sits alongside ADO
• Designed for disconnected use
• Distributed data
• XML format

5
The ADO Object Model
TableMappings
ADODataSetCommand
UpdateCommand
SelectCommand
DeleteCommand
InsertCommand
Parameters
ADOCommand
Parent
ActiveConnection
Properties
Properties
ADOConnection
6
The ADO Object Model
DataSet
Relations
Tables
ChildColumns
Columns
ChildTable
ParentColumns
ParentTable
DefaultView
ChildRelations
ParentRelations
Rows
Constraints
7
ADO Providers

• SQLProvider
• Direct access to SQL Server (via Tabular Data
  Stream)
• Extremely fast
• ADOProvider
• Access to ADO data stores
• Preserves access to non SQL Server data

8
Connections

• Connection to a data source

Dim conDB AS New ADOConnection ConDB.ConnectionSt
ring "ProviderSQLOLEDB . . ." conDB.Open
Dim conDB AS New SQLConnection ConDB.ConnectionSt
ring "Server . . ." conDB.Open
9
Commands

• Provide ability to execute statements
• Command objects
• SQLCommand
• For use with the SQLProvider
• ADOCommand
• For use with the ADOProvider
• Parameters Collection

10
Commands
Dim cmd AS New ADOCommand cmd.ActiveConnection
conDB cmd.CommandText "select from authors
where state?" cmd.Parameters.Add("state",
ADODB.Type.Char, 2) cmd.Parameters("state").Value
"CA" cmd.Execute
cmd.CommandText "usp_AuthorsByState" cmd.Parame
ters.Add("state", ADODB.Type.Char,
2) cmd.Parameters("state").Value
"CA" cmd.Execute
11
The DataReader

• Forward only
• Read only
• Only one record in memory at a time

Dim rdr As ADODataReader cmd.Execute(rdr) While
myReader.Read() Response.Write(rdr.Item("au_lnam
e")) End While
DataGrid1.DataSource rdr
12
DataSets and DataTables

• DataSet is not a Recordset
• Contains one or more DataTables
• Can be related
• Can be from different data stores
• In-memory cache of data

13
DataSetCommand

• Links DataSet to data source
• Provides mappings between tables and columns
• Contains four commands
• SelectCommand
• InsertCommand
• UpdateCommand
• DeleteCommand
• Two methods
• FillDataSet
• Update

14
DataViews

• Custom view of a DataTable
• Supports
• Filtering
• Sorting
• Searching
• Data binding
• Every DataTable has a DefaultView

15
Using DataSets
Dim myConnection As ADOConnection Dim myCommand
As ADODataSetCommand Dim ds As New
DataSet Dim ConnStr As String Dim SQL
As String ConnStr "ProviderSQLOLEDB . .
." SQL "select from Products" myConnection
New ADOConnection(ConnStr) myCommand New
ADODataSetCommand(SQL, myConnection) myCommand.Fi
llDataSet(ds, "Products") DataGrid1.DataSourceds
.Tables("Products").DefaultView
16
ADO Scenario
Data Store
Business Object
XML
Connection
Connection
17
Data Binding

• Bind to
• Hash tables, Lists, Arrays, Collection,
  DataReader, DataTable, DataView, DataSet
• Binding Order
• Set DataSource
• Call DataBind method

18
Binding a ListBox to an Array
ltaspListBox id"Categories" runat"server" /gt
Dim catList As New ArrayList catList.add ("North
Face Sunspot") catList.add ("Polar
Star") catList.add ("Big Sur") catList.add
("Cascade") catList.add ("Everglades") catList.add
("Parka") catList.add ("Sierra") ' now bind the
listbox to the array Categories.DataSource
catList Categories.DataBind()
19
Binding a CheckBoxList to an Array
ltaspCheckBoxList id"Categories" runat"server"
/gt
Dim catList As New ArrayList catList.Add ("North
Face Sunspot") catList.Add ("Polar
Star") catList.Add ("Big Sur") catList.Add
("Cascade") catList.Add ("Everglades") catList.Add
("Parka") catList.Add ("Sierra") ' now bind the
listbox to the array Categories.DataSource
catList Categories.DataBind()
20
Binding to a DataReader
Dim cmd As New ADOCommand Dim rdr As New
DataReader cmd.ActiveConnection
conDB cmd.CommandText "usp_AuthorsByState" cmd.
Parameters.Add("state", ADODB.Type.Char,
2) cmd.Parameters("state").Value
"CA" cmd.Execute(rdr) DataGrid1.DataSource rdr
21
Advanced List Controls

• Provide repeatable binding
• Repeater
• No UI
• DataList
• Default UI that can be overwritten
• DataGrid
• Default grid UI that can be overwritten

22
Templating

• Customising look of controls
• List Controls
• DataList
• Repeater
• DataGrid
• Custom Controls
• User written templating
• Templates
• Any control can expose any number of templates
• Header, Item, AlternatingItem, SeperatorItem,
  Footer

23
Templating a DataList
ltaspDataList id"DataGrid1" border"0"
RepeatDirection"Horizontal" RepeatColumns"4"
runat"server"gt lttemplate name"ItemTemplate"
gt lttablegt lttrgtlttdgt
ltaspHyperLink ImageURL'lt Container.DataItem("I
URL") gt' NavigateURL 'lt
Container.DataItem("DURL") gt'
target"_blank" runat"server" /gt
lt/tdgtlt/trgt lttrgtlttdgt ltbgtlt
Container.DataItem("ProductName") gtltbrgtlt/bgt
ltfont size"-1"gt lt
Container.DataItem("ProductCode") gtltbrgt
lt Container.DataItem("UnitPrice") gt
lt/fontgt lt/tdgtlt/trgt lt/tablegt
lt/templategt lt/aspDataListgt
24
Templating a DataGrid
ltaspDataGrid id"DataGrid1" AutoGenerateColumns"
false" GridLines"None"
HeaderStyle-BackColor"Tan"
ItemStyle-BackColor"Bisque"
runat"server"gt ltproperty name"Columns"gt
ltaspBoundColumn HeaderText"Name"
DataField"ProductName" /gt ltaspBoundColumn
HeaderText"Price" DataField"UnitPrice"/gt
ltaspTemplateColumn HeaderText"Picture"gt
lttemplate name"ItemTemplate"gt ltaspImage
ImageURL'lt Container.DataItem("URL") gt'
runat"server" /gt lt/templategt
lt/aspTemplateColumngt lt/propertygt lt/aspDataGrid
gt
25
Binding Attributes

• Specialised syntax

attribute'lt item_to_bind_to gt'

• For example

ltform runat"server"gt ltaspDropDownList
id"Categories" runat"server" /gt ltpgt
Selection ltaspLabel id"SelectedValue"
Text'lt Categories.SelectedItem.Text
gt' runat"server" /gt
lt/pgt lt/formgt
26
XML Support

• Evolution of ADO and MSXML
• .NET XML MSXML 3.0
• Core part of .NET framework
• Industry Standard
• DOM
• Level 1 and some of Level 2
• Validation
• DRD, XDR (XSD in beta 2)
• XSL/T and X-Path (complete in beta 2)
• SAX
• SOAP

27
ADO Architecture
Web/Win Form Controls
VS .NET Designers
myDataSet
Managed Provider
Cust
DataReader
DataSet-Command
Command
Connection
28
ADO and XML

• Great XML support in DataSet
• Language Neutral
• Great Visual Studio.NET integration
• Retains ADO Model
• Retains MSXML Model

29
XML Architecture
30
XML Objects

• XmlReader a 'pull' parser
• Stream and document support
• XmlWriter outputs well formed XML
• Stream and document support
• XmlNavigator
• X-Path support
• XmlDataDocument
• Data friendly DOM

31
Strong Typing

• Maps ADO Data onto objects

If UnitCost gt Table("Products").Column("UnitPrice"
) Then
If UnitCost gt Products.UnitPrice Then

• Advantages
• Actual data structure becomes irrelevant
• Statement completion

32
Summary

• ADO is not a replacement for ADO
• DataSets provide disconnected model
• Multiple sets of data
• Easy access to XML Data
• Industry Standard
• Interoperability
• Firewall penetration

33
Questions?

• Dave Sussman