Data Access in ASP'NET 2'0

1
Data Access in ASP.NET 2.0

• Bradley Millington
• Program Manager
• Web Platform and Tools

2
Agenda

• Visual Web Developer 2005 SQL Server 2005
• All you need to build rich data-driven apps!
• Free Express Editions of both products
• ASP.NET Data Controls
• Enable declarative data binding in ASP.NET 2.0
• Scenarios Covered
• Creating and connecting to a database in VS
• Selecting and displaying data in a web page
• Sorting, paging, updating, deleting, inserting
  data
• Caching, filtering, master-details, parameters
• Binding to business objects (DAL, BLL)
• Data binding in custom templated UI
• Hierarchical data (XML, SiteMap)

3
ASP.NET 2.0 and Data Controls

• Data access/presentation too hard in ASP.NET V1
• No declarative model for data acquisition/manipula
  tion
• Common scenarios required 100s of lines of code
• 2.0 provides easy and powerful declarative model
• Handle stateless Web model for data scenarios
• Do not require developer to be aware of page
  lifecycle events
• Enable rich and pluggable data access storage
  providers
• Common UI scenarios with little to zero code
• Selecting and displaying data
• Sorting, paging, caching data
• Updating, inserting, deleting data
• Filtering, master-details (parameterization)

4
ASP.NET 2.0 and Data Controls

• Data Source Controls
• Non-UI controls (no rendering)
• Represent different backend data sources
• Databases, Business Objects, XML, or Web Services
• Can cache, sort, page, filter, update, delete,
  insert data
• Expose data through tabular or hierarchical
  interfaces
• Data-bound Controls
• UI controls to render data
• GridView, DetailsView, FormView, TreeView, Menu
• Auto-bind to data exposed from a data source
• Fetches data at the appropriate time in lifecycle
• Can take advantage of data source capabilities

5
Data Control Types
Database
Data-bound Control
ltaspGridView DataSourceIdMySource
runatserver/gt
ltaspTreeView DataSourceIdMySource
runatservergt
Business Object
Data Source Control
ltaspXmlDataSource IdMySource
DataFileBookstore.xml XPath/bookstore/bo
ok_at_genrefiction runatserver /gt
ltaspSqlDataSource IdMySource
ConnectionString SelectCommandselect
id from authors runatserver /gt
ltaspObjectDataSource IdMySource
TypeNameCustomersDB SelectMethodGetCusto
mersByRegion runatserver /gt
XML Document
6
Binding to Databases with SqlDataSource
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

7
Configured Connection Strings

• Persistence and declarative referencing of
  connection-strings from Web.Config
• Avoid hard-coding within pages/code
• Can be optionally encrypted
• VS supports at design time
• Promote use for best practices
• Enable optional encrypting of values in config
• Flexible Admin Support
• MMC Admin Tool Support
• Configuration API Support

8
Config Connection Strings
Web.config
ltconnectionStringsgt ltadd name"pubs
ProviderName connectionString
/gt lt/connectionStringsgt
Page.aspx
ltaspSqlDataSource IdMySource
ConnectionStringlt connectionStringspubs gt
SelectCommandselect au_id from authors
runatserver/gt
Code.vb
Dim connstr As String ConfigurationSetting
s.ConnectionStrings(pubs)
9
Binding to Objects

• Most applications encapsulate data logic from
  presentation layer as a best practice
• Embedding SQL code in a page is not ideal
• ObjectDataSource enables declarative binding to
  middle-tier objects
• Select, update, insert, delete, filter and cache
  data
• Supports custom paging and sorting

ltaspObjectDataSource IdMySource
TypeNameCustomersDB SelectMethodGetCusto
mers UpdateMethodUpdateCustomer
DeleteMethodDeleteCustomer
InsertMethodInsertCustomer runatserver
/gt
10
Binding to Objects

• Select method can return any Object or
  IEnumerable list, collection, or array
• GetProducts() -gt ProductCollection
• GetProductsDataSet() -gt DataSet
• GetProduct (int productId) -gt Product
• Update, Insert, Delete methods take individual
  fields or data item object
• UpdateProduct (int id, String name, double price,
  bool inStock)
• UpdateProduct (Product p) // p.Name, p.Price,
  p.InStock
• DeleteProduct (int id)
• Property or parameter names must match selected
  fields for GridView/DetailsView automatic
  updates/deletes/inserts

11
Binding to Objects
Web Page
ltaspObjectDataSource ID ObjectDataSource1 TypeN
ame OrdersComponent SelectMethod
GetOrders UpdateMethod UpdateOrder DeleteMethod
DeleteOrder
DataSourceID ObjectDataSource1

• Returns IEnumerable of Orders
• Order.OrderID
• Order.OrderName
• Order.OrderDate

12
Binding to Objects with ObjectDataSource
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

13
Data Source Paging

• Previous example does paging in UI layer
• ObjectDataSource returns all data rows
• GridView performs paging by rendering subset of
  rows
• Paging also supported on data source interface
• Select (int startRowIndex, int maxRows)
• Can be implemented in user-defined stored proc or
  custom method
• Data-bound controls (e.g., GridView) can use data
  source paging, if supported by data source
• Dont need to page in the UI layer
• Useful (required) for large amounts of data

14
Under the Hood Updates, Inserts, Deletes

• GridView extracts values from input controls,
  keys from viewstate (DataKeyNames)
• Dictionaries passed to data source operation
• Update Keys, Values , OldValues
• Delete Keys , OldValues
• Insert Values
• Data source applies values as parameters to
  command or method
• Merged with any statically defined parameters
• Names of parameter values match selected fields
• Can optionally set format string to differentiate
  Keys, OldValues from new Values
  OldValuesParameterFormatStringoriginal_0

15
Under the Hood Updates
ltaspObjectDataSource ID ObjectDataSource1 TypeN
ame ContactsComponent SelectMethod
GetContactUpdateMethod UpdateContactltSelectPar
ametersgt ltaspQueryStringParameter NameID
TypeInt32 QueryStringFieldContactID
/gtlt/SelectParametersgt ltUpdateParametersgt
ltaspParameter NameID TypeInt32 /gt
ltaspParameter NameName TypeString /gt
ltaspParameter NameState TypeString
/gt lt/UpdateParametersgtlt/aspObjectDataSourcegt
GridView DataKeyNames ID
16
Under the Hood Updates
ViewState
GridView DataKeyNames ID
Edit
GetContact (ID)
Request.QueryStringContactID
17
Under the Hood Updates
ViewState
Keys
GridView DataKeyNames ID
Values
Update
OldValues
UpdateContact(ID, Name, Company)
18
Under the Hood Updates
ViewState
GridView DataKeyNames ID
Edit
UpdateContact(ID, Name, Company, Original_ID)
19
Under the Hood Updates
ViewState
Keys
GridView DataKeyNames ID
Values
Update
OldValues
UpdateContact(ID, Name, Company, Original_ID)
OldValuesParameterFormatString Original_0
20
Programmability And Events

• Creating, Created
• Raised before/after object instantiated
• Can assign to ObjectInstance
• Selecting, Filtering, Updating, Deleting,
  Inserting
• Raised before operation
• Can optionally cancel event
• Can validate and manipulate parameters
• Selected, Updated, Inserted, Deleted
• Raised after operation complete
• Can check and handle exceptions
• Can obtain return values and out params
• Disposing
• Raised prior to releasing object
• Dispose() will be called if object implements
  IDisposable
• Can optionally cancel

21
Data Source Events
Page.aspx.vb
Sub MySource_Selecting(ByVal sender As Object,
ByVal e As SqlDataSourceCommandEventArgs) Dim
cmd As System.Data.SqlClient.SqlCommand
e.Command cmd.Parameters(UserId).Value
User.Identity.Name End Sub
Page.aspx
ltaspSqlDataSource IDMySource
OnSelectingMySource_Selecting
SelectCommandsp_GetUserPreferences
runatserver/gt
22
Filtering and Master-Details

• Select Method or Command may be parameterized
• GetCustomersByCountry (int countryCode) -gt
  CustomersCollection
• GetOrdersForCustomer (String customerId) -gt
  OrdersCollection
• GetProduct (int productId) -gt Product
• Data source parameter collections enable
  declarative associations to values
• QueryStringParameter
• ControlParameter
• SessionParameter
• FormParameter
• CookieParameter
• ProfileParameter
• Static Parameter

23
Filtering Data
Web Page
Orders.aspx?companyMicrosoft
ltaspObjectDataSource ID ObjectDataSource1 TypeN
ame OrdersComponent SelectMethod
GetOrdersBy ltSelectParametersgt
ltaspQueryStringParameter
NamecompanyName QueryStringFieldcompan
y/gt lt/SelectParametersgt
Northwind Database
24
Filtering Data
Web Page
ltaspObjectDataSource ID ObjectDataSource2 TypeN
ame CompaniesComponent SelectMethod
GetCompanies
Page Developer API
ltaspObjectDataSource ID ObjectDataSource1 TypeN
ame OrdersComponent SelectMethod
GetOrdersBy ltSelectParametersgt
ltaspControlParameter Name companyName
ControlID DropDownList1 /gt lt/SelectParameters
gt
Northwind Database
25
Master-Details (1 Page)
Web Page
ltaspGridView ID GridView1 AutoGenerateSelectBut
ton true
ltaspObjectDataSource ID ObjectDataSource2 TypeN
ame OrdersComponent SelectMethod
GetOrderBy ltSelectParametersgt
ltaspControlParameter Name orderID
ControlID GridView1 /gt lt/SelectParametersgt
Northwind Database
26
Master-Details (2 Page)
Details Page
Master Page
ltaspGridView ID GridView1 ltColumnFieldsgt
ltaspHyperLinkField /gt lt/ColumnFieldsgt
ltaspObjectDataSource ID ObjectDataSource2 TypeN
ame OrdersComponent SelectMethod
GetOrderBy ltSelectParametersgt
ltaspQueryStringParameter Name orderID
QueryStringField ID /gt lt/SelectParametersgt
Northwind Database
27
Filtering and Master-Details
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

28
Data Binding In Templates

• V1 data binding syntax too verbose
• lt DataBinder.Eval(Container.DataItem, field
  ,formatString) gt
• Simplified data binding syntax in ASP.NET 2.0
• lt Eval(field ,formatString) gt // 1-way
  databinding
• lt Bind(field ,formatString) gt // 2-way
  databinding
• New two-way data binding syntax
• Enables templated controls to retrieve input
  values, passed to automatic updates, inserts,
  deletes
• Supported in GridView, DetailsView controls
  (TemplateField)
• Support in new FormView control (fully-templated
  DetailsView)
• Not supported for DataList (V1 control)

29
Data Binding in Templates
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

30
Caching Data

• Caching is a best practice, but many developers
  dont do this its too hard in V1!
• Data sources can automatically cache data
• Manages cache key and dependencies for you
• Completely transparent to data-bound controls
• ltaspObjectDataSource EnableCachingtrue
  CacheDurationtime in seconds
  CacheExpirationPolicyabsolutesliding
  CacheKeyDependencyCustomers
  SelectMethodGetCustomers TypeNameCustomers
  DB runatserver /gt
• Can also manage caching yourself in business
  object layer

31
SQL Cache Invalidation

• Retains cache entry until database table changes
• Only invalidates when backend data is stale
• Built on SQL Server 2005 notifications
• Supported on SQL7, SQL2k, or SQL Express via
  polling
• Enabled with SqlCacheDependency property on
  OutputCache directive and data source controls
• SqlDataSource supports notifications or polling
• ObjectDataSource supports polling only
• Can use still use notifications in DAL/BLL code
• ltaspSqlDataSource
• EnableCachingtrue
• CacheDurationInfinite
• SqlCacheDependency conntableCommand
  Notification

32
Data Source Caching
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

33
Hierarchical Data

• Hierarchical data sources
• Expose data as parent-child relationships
• ltaspXmlDataSource/gt
• ltaspSiteMapDataSource/gt
• Hierarchical data-bound controls
• Use a navigator to walk the tree structure
• ltaspTreeView/gt
• ltaspMenu/gt
• Can also bind tabular (list) controls to
  hierarchical data
• Only top-level data items are rendered

34
Hierarchical Data

• Can also bind to XML in a template
• Can bind anywhere in the hierarchy
• New XPath databinding syntax
• XPath(books/genre/_at_name)
• Returns simple value, e.g., Fiction
• New XPathSelect syntax
• XPathSelect(books/genre_at_nameFiction)
• Returns a list, e.g., IEnumerable of book
  elements
• Can be enumerated directly, or bound to the
  DataSource property of a list control

35
Binding to Hierarchical Data
demo

• Bradley Millington
• Program Manager
• Web Platform and Tools

36
Summary

• Visual Web Developer Express and SQL Server
  Express make it easy to build data-driven apps!
• ASP.NET 2.0 data source controls dramatically
  simplify data-binding over v1.x
• Integrates easily with middle-tier data
  components and business objects
• Retains the flexibility of v1.x for complex
  scenarios requiring code
• Provides a model that third-party data providers
  can easily extend

37
Resources

• My Slides and Demos
• http//www.bradmi.net/presentations
• ASP.NET 2.0 Quickstarts
• http//www.asp.net/quickstart
• ASP.NET Forums
• http//www.asp.net/forums
• Nikhil Kothari's Blog
• http//www.nikhilk.net

38
(No Transcript)