Transcript Building Data-Driven Web Sites in ASP.NET 2.0

WEB325
Building Data-Driven Web Sites in
ASP.NET 2.0
Rob Howard
[email protected]
Telligent Corporation
Contact and Slides
Rob Howard
[email protected]
www.telligent.com
Telligent
.NET Software Development Company
Builds Community Server (communityserver.org)
Download Slides & Demos
www.rob-howard.net
Will be posted ASAP
Review: ASP.NET 1.X
ASP.NET 1.X
Introduced data controls
Introduced data binding concepts
Working with data in ASP.NET 1.X
Easy and simple
Too much code still required though
ASP.NET 2.0
Specifically addresses common scenarios
Makes working with data even easier
Agenda
Safer connection string storage
Data binding gets easier
Declarative data binding
Working with the new data controls
Caching data
Connection String Storage
Persistence and declarative referencing
Stored in *.config
Avoid hard-coding within pages/code
Can be optionally encrypted
Built-in design time support
Promote use for best practices
Enable optional encrypting of values in config
Admin Support
MMC Admin Tool Support
Configuration API Support
Config Connection Strings
Web.config:
<connectionStrings>
<add name="pubs“ ProviderName=“…”
connectionString=“…” />
</connectionStrings>
Page.aspx:
<asp:SqlDataSource Id=“MySource”
ConnectionString=“<%$ connectionStrings:pubs %>”
SelectCommand=“select au_id from authors”
runat=“server”/>
Code.vb:
Dim connstr As String =
ConfigurationSettings.ConnectionStrings(“pubs”)
Connection Strings
Data Binding Gets Easier
Expressions are cleaner
Support hierarchical (XML) data binding
<!-- ASP.NET 1.x data binding expression -->
<%# DataBinder.Eval (Container.DataItem, "Price") %>
<!-- Equivalent ASP.NET 2.0 data binding expression -->
<%# Eval ("Price") %>
<!-- XML data binding -->
<%# XPath ("Price") %>
Declarative Data Binding
Controls bound at appropriate time
Wire-up done through properties
Page lifecycle knowledge not required
Name
Description
SqlDataSource
Connects data-binding controls to SQL databases
AccessDataSource
Connects data-binding controls to Access databases
XmlDataSource
Connects data-binding controls to XML data
ObjectDataSource
Connects data-binding controls to data components
SiteMapDataSource
Connects site navigation controls to site map data
SqlDataSource
Data binding to SQL Database
Microsoft SQL Server, Oracle, DB2, etc.
Two-way data binding supported
SelectCommand
InsertCommand, UpdateCommand, and
DeleteCommand
Optional caching of query results
Parameterized operation
Using SqlDataSource
<asp:SqlDataSource
ID="Titles"
ConnectionString="<%$ connectionStrings:pubs %>"
RunAt="server“
SelectCommand="select title_id, title, price
from titles"
/>
<asp:DataGrid
DataSourceID="Titles"
RunAt="server" />
SqlDataSource Properties
Name
Description
ConnectionString
Connection string used to connect to data source
SelectCommand
Command used to perform queries
InsertCommand
Command used to perform inserts
UpdateCommand
Command used to perform updates
DeleteCommand
Command used to perform deletes
DataSourceMode
Specifies whether DataSet or DataReader is used
(default = DataSet)
ProviderName
Specifies provider (default = SQL Server .NET provider)
SQL Data Binding
SqlDataSource and Caching
Support declarative caching
Name
Description
EnableCaching
Specifies whether caching is enabled (default = false)
CacheDuration
Length of time in seconds results should be cached
CacheExpirationPolicy
Specifies whether cache duration is sliding or absolute
CacheKeyDependency
Creates dependency on specified cache key
SqlCacheDependency
Creates dependency on specified database entity
Caching Query Results
<asp:SqlDataSource
ID="Countries"
RunAt="server"
SelectCommand="select ..."
EnableCaching="true" CacheDuration="60" />
<asp:DropDownList
ID="MyDropDownList"
DataSourceID="Countries“
DataTextField="country"
AutoPostBack="true"
RunAt="server" />
Parameterized Commands
Parameter properties
Parameterized database commands
Example:
Get value for WHERE clause in SelectCommand
from query string parameter or item selected in
drop-down list
Example:
Get value for WHERE clause in DeleteCommand
from GridView
Parameters Properties
Name
Description
SelectParameters
Specifies parameters for SelectCommand
InsertParameters
Specifies parameters for InsertCommand
UpdateParameters
Specifies parameters for UpdateCommand
DeleteParameters
Specifies parameters for DeleteCommand
FilterParameters
Specifies parameters for FilterExpression
Parameter Types
Name
Description
Parameter
Binds a replaceable parameter to a data field
ControlParameter
Binds a replaceable parameter to a control property
CookieParameter
Binds a replaceable parameter to a cookie value
FormParameter
Binds a replaceable parameter to a form field
ProfileParameter
Binds a replaceable parameter to a profile property
QueryStringParameter
Binds a replaceable parameter to a query string parameter
SessionParameter
Binds a replaceable parameter to a session variable
Using ControlParameter
<asp:SqlDataSource
ID="Countries" RunAt="server"
SelectCommand="select distinct ..." />
<asp:SqlDataSource
ID="Customers" RunAt="server"
SelectCommand="select * from customers where country=@Country">
<SelectParameters>
<asp:ControlParameter Name="Country"
ControlID="MyDropDownList"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:DropDownList
ID="MyDropDownList" DataSourceID="Countries"
DataTextField="country" AutoPostBack="true" RunAt="server" />
<asp:DataGrid DataSourceID="Customers" RunAt="server" />
Calling Stored Procedures
<asp:SqlDataSource ID="Countries" RunAt="server"
ConnectionString="server=localhost;database=northwind;..."
SelectCommand="proc_GetCountries" />
<asp:SqlDataSource ID="Customers" RunAt="server"
ConnectionString="server=localhost;database=northwind;..."
SelectCommand="proc_GetCustomers">
<SelectParameters>
<asp:ControlParameter Name="Country" ControlID="MyDropDownList"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:DropDownList ID="MyDropDownList" DataSourceID="Countries"
DataTextField="country" AutoPostBack="true" RunAt="server" />
<asp:DataGrid DataSourceID="Customers" RunAt="server" />
CREATE PROCEDURE proc_GetCustomers
@Country nvarchar (32) AS
SELECT * FROM Customers
WHERE Country = @Country
GO
CREATE PROCEDURE proc_GetCountries AS
SELECT DISTINCT Country
FROM Customers
ORDER BY Country
GO
SqlDataSource
Data Source Capabilities
Common data operations get easier
Sorting and paging
Selecting, updating, inserting, deleting
Page developer
Sets properties to enable operations
Ex., UpdateCommand
“Smart” data-bound controls
Use these capabilities directly
Ex., <asp:GridView>, <asp:DetailsView>
Sorting, Paging, Updating
Data Source Paging
Previous demo does paging in UI layer
SqlDataSource returns all data rows
Performs paging by rendering subset of rows
Paging supported on data source interface
Select (int startRowIndex, int maxRows)
Requires user-defined procedure or custom code
Data-bound controls
Don’t need to page in the UI layer
Useful (and required) for large amounts of data
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
Data source applies parameters to command
Relies on naming convention for parameters
Keys, OldValues formatted with “original_” prefix
OldValuesParameterFormatString defines prefix
Programmability
Data source events
Enable custom processing
Use event to:
Manipulate command and parameters
Cancel operations
Handle errors
Retrieve return values and output params
Rows affected
Data-bound controls expose similar events
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
<asp:SqlDataSource ID=“MySource” …
OnSelecting=“MySource_Selecting”
SelectCommand=“sp_GetUserPreferences”
runat=“server”/>
ObjectDataSource
Most applications encapsulate data logic
Best practice and simplifies maintenance
Embedding SQL code is not recommended
ObjectDataSource
Bind to custom business objects
Visual Studio data components
Parallels SqlDataSource in object model
<asp:ObjectDataSource ID=“MySource”
TypeName=“CustomersDB”
SelectMethod=“GetCustomersByRegion”
UpdateMethod=“UpdateCustomer”
runat=“server”/>
ObjectDataSource
Web Page
<asp:ObjectDataSource
ID = ObjectDataSource1
TypeName = OrdersComponent
SelectMethod = GetOrders
UpdateMethod = UpdateOrder
DeleteMethod = DeleteOrder
DataSourceID = ObjectDataSource1
Returns IEnumerable of Orders
• Order.OrderID
• Order.OrderName
• Order.OrderDate
OrderItemsComponent
OrdersComponent
CompaniesComponent
Northwind
Database
ObjectDataSource
Select method can return
any Object
IEnumerable list
Collection or array
GetProducts() -> ProductCollection
GetProductsDataSet() -> DataSet
GetProduct (int productId) -> Product
ObjectDataSource
Update, Insert, Delete methods
Take individual fields or data item object
UpdateProduct (int id, String name, double price)
UpdateProduct (Product p)
// p.Name, p.Price, etc.
DeleteProduct (int id)
For automatic updates/inserts/deletes
Property or parameter names must match
selected fields for GridView/DetailsView
Key Properties
Name
Description
TypeName
Type name of data component
SelectMethod
Method called on data component to perform queries
InsertMethod
Method called on data component to perform inserts
UpdateMethod
Method called on data component to perform updates
DeleteMethod
Method called on data component to perform deletes
EnableCaching
Specifies whether caching is enabled (default = false)
Key Properties (cont’d)
Name
Description
CacheDuration
Length of time in seconds data should be cached
SqlCacheDependency
Creates dependency on specified database entity
SelectParameters
Specifies parameters for SelectMethod
InsertParameters
Specifies parameters for InsertMethod
UpdateParameters
Specifies parameters for UpdateMethod
DeleteParameters
Specifies parameters for DeleteMethod
Initialization and Clean-Up
Data operation methods
Can identify static methods
Can identify instance methods
If instance methods are used:
New class instance on each call
Must have public default constructor
ObjectCreated and ObjectDisposing
Events to initialize or clean-up
Using ObjectDataSource
What about DataSets?
“A middle tier data access layer for Web and client”
Works for both Web and client applications
Created from
DB schema
Dynamic SQL
Stored Procedures
Exposes methods to “Fill” and “Fetch”
Auto-generated Insert/Update/Delete
What about DataSets?
Supports partial classes for custom logic
Built in support for common tasks
Select, Update, Insert, Delete
Batch updates, Conflict resolution*
Works with Web services and remoting
DataTable can be standalone
(XmlSerializable, ReadXml)
Exposes “GetBy” for stateless calls
Can be used with custom base classes
GridView Control
Enhanced DataGrid control
Renders sets of records as HTML tables
Built-in sorting, paging, selecting,
updating, and deleting support
Supports rich assortment of field types,
including CheckBoxFields
Declared in <Columns> element
Highly customizable UI
GridView Example
<asp:SqlDataSource
ID="Employees"
RunAt="server"
SelectCommand="select lastname, .../>
<asp:GridView
DataSourceID="Employees"
Width="100%"
RunAt="server" />
Rendered GridView
GridView Field Types
Name
Description
BoundField
Renders columns of text from fields in data source
ButtonField
Renders columns of buttons (push button, image, or link)
CheckBoxField
Renders Booleans as check boxes
CommandField
Renders controls for selecting and editing GridView data
HyperLinkField
Renders columns of hyperlinks
ImageField
Renders columns of images from URL text
TemplateField
Renders columns using HTML templates
Specifying Field Types
<asp:SqlDataSource ID="Employees" RunAt="server"
ConnectionString="server=localhost;database=northwind;..."
SelectCommand="select photo, lastname, firstname, title from
employees" />
<asp:GridView DataSourceID="Employees" Width="100%"
RunAt="server"
AutoGenerateColumns="false" >
<Columns>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<%# Eval ("firstname") + " " + Eval ("lastname") %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Title" DataField="title" />
</Columns>
</asp:GridView>
Rendered Output
DetailsView Control
Renders individual records
Pair with GridView for master-detail views
Or use without GridView to display
individual records
Built-in paging, inserting, updating,
deleting
Uses same field types as GridView
Declared in <Fields> element
Highly customizable UI
DetailsView Example
<asp:SqlDataSource ID="Employees" RunAt="server"
ConnectionString="server=localhost;database=northwind;..."
SelectCommand="select employeeid, photo, ... from employees" />
<asp:DetailsView DataSourceID="Employees" RunAt="server"
AllowPaging="true" AutoGenerateRows="false"
PagerSettings-Mode="NextPreviousFirstLast">
<Fields>
<asp:BoundField HeaderText="Employee ID" DataField="employeeid" />
<asp:BoundField HeaderText="Date Hired" DataField="hiredate" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<%# Eval ("firstname") + " " + Eval ("lastname") %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Title" DataField="title" />
</Fields>
</asp:DetailsView>
Rendered Output
Master-Detail
SQL Cache Invalidation
New cache dependency type
Embodied in SqlCacheDependency class
Configured through
<sqlCacheDependency> configuration
section
Links cached items to database entities
ASP.NET application cache
ASP.NET output cache
Compatible with SQL Server 7, 2000,
2005
Using SqlCacheDependency with the
Application Cache
Cache.Insert ("Products", products,
new SqlCacheDependency ("Northwind", "Products");
Database name
Table name
Using SqlCacheDependency with the
Output Cache
<%@ OutputCache Duration="60" VaryByParam="None"
SqlDependency="Northwind:Products" %>
Database name
Table name
Using SqlCacheDependency with
SqlDataSource
<asp:SqlDataSource ID="Countries" RunAt="server"
ConnectionString="server=localhost;database=northwind;..."
SelectCommand="select distinct country from customers order by country"
EnableCaching="true" CacheDuration="60000"
SqlCacheDependency="Northwind:Customers" />
<asp:DropDownList ID="MyDropDownList" DataSourceID="Countries"
DataTextField="country" AutoPostBack="true" RunAt="server" />
Database name
Table name
Cache Configuration
<cache>
Enable/disable application cache
Enable/disable item expiration and more
<outputCache>,
<outputCacheSettings>
Enable/disable output caching
Enable/disable disk-based persistence
Set maximum size per app and more
<sqlCacheDependency>
SQL Cache Invalidation
Review
Simplified data binding
Data source controls
Data controls
GridView and DetailsView controls
Editing with GridView and DetailsView
Caching
SQL Cache Invalidation
Cache configuration
Your Feedback
is Important!
Please Fill Out a Survey for
This Session on CommNet
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.