philip wolfe senior consultant sogeti session 3 - leveraging data in asp.net 2.0 (level 200)

Philip WolfePhilip Wolfe

Senior ConsultantSenior Consultant

SogetiSogeti

Session 3 - Leveraging Session 3 - Leveraging Data in ASP.NET 2.0 Data in ASP.NET 2.0 (Level 200)(Level 200)

Session 3 - Leveraging Session 3 - Leveraging Data in ASP.NET 2.0 Data in ASP.NET 2.0 (Level 200)(Level 200)

AgendaAgenda

Simplified data bindingSimplified data binding

Data source controlsData source controls

Data controlsData controlsGridView and DetailsView controlsGridView and DetailsView controls

Editing with GridView and DetailsViewEditing with GridView and DetailsView

CachingCachingSQL cache dependenciesSQL cache dependencies

Cache configurationCache configuration

Simplified Data BindingSimplified Data Binding

Data binding expressions are now Data binding expressions are now simpler and support hierarchical simpler and support hierarchical (XML) data binding(XML) data binding

<%# Eval ("Price") %>

<%# XPath ("Price") %><%# XPathSelect ("Price") %>

<%# Bind ("Price") %>

<%# DataBinder.Eval (Container.DataItem, "Price") %>

Simplified Data BindingSimplified Data Binding

Data binding code has changed as Data binding code has changed as wellwell

//C# - version 2.0// It can all be done declaratively (in the aspx file)

//C# - version 1.xDataGrid1.DataSource = ProductsDataSet;DataGrid1.DataBind();

DataSource ControlsDataSource Controls

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

Declarative (no-code) data bindingDeclarative (no-code) data binding

SqlDataSourceSqlDataSource

Declarative data binding to SQL Declarative data binding to SQL databasesdatabases

Any database served by a managed Any database served by a managed providerprovider

Two-way data bindingTwo-way data bindingSelectCommand defines query semanticsSelectCommand defines query semantics

InsertCommand, UpdateCommand, and InsertCommand, UpdateCommand, and DeleteCommand define update DeleteCommand define update semanticssemantics

Optional caching of query resultsOptional caching of query results

Can call stored procedures or use SQLCan call stored procedures or use SQL

Parameterized operationParameterized operation

Using SqlDataSourceUsing SqlDataSource

<asp:SqlDataSource ID="Titles" RunAt="server" ConnectionString="server=localhost;database=pubs;integrated security=true" SelectCommand="select title_id, title, price from titles" /><asp:DataGrid DataSourceID="Titles" RunAt="server" />

<asp:SqlDataSource ID="Titles" RunAt="server" ConnectionString="..." SelectCommand="select title_id, title, price from titles" ProviderName="System.Data.OracleClient" />

SQL Server

Oracle

Best Practice using web.config

<asp:SqlDataSource ID="Authors" RunAt=" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" />

SqlDataSource and SqlDataSource and CachingCaching

SqlDataSource supports declarative SqlDataSource supports declarative caching of results through these caching of results through these properties:properties:

Name Description

EnableCaching Specifies whether caching is enabled (default = false)

CacheDuration Length of time in seconds results should be cached

CacheExpirationPolicySpecifies whether cache duration is sliding or absolute

CacheKeyDependencyCreates dependency on specified cache key

SqlCacheDependency Creates dependency on specified database entity

<asp:SqlDataSource ID="Countries" RunAt="server" ConnectionString="server=localhost;database=northwind;..." SelectCommand="select distinct country from customers order by country" EnableCaching="true" CacheDuration="60" /><asp:DropDownList ID="MyDropDownList" DataSourceID="Countries" DataTextField="country" AutoPostBack="true" RunAt="server" />

Caching Query ResultsCaching Query Results

Notes:Notes:EnableCaching must be true (not default)EnableCaching must be true (not default)

CacheDuration must be greater than CacheDuration must be greater than zerozero

DataSourceMode must be DataSet DataSourceMode must be DataSet (default)(default)

The data is cached in the application The data is cached in the application cachecache

Parameterized CommandsParameterized Commands

XxxParameters properties permit XxxParameters properties permit database commands to be database commands to be parameterizedparameterized

Example: Get value for WHERE clause in Example: Get value for WHERE clause in SelectCommand from query string SelectCommand from query string parameter or item selected in drop-down parameter or item selected in drop-down listlist

Example: Get value for WHERE clause in Example: Get value for WHERE clause in DeleteCommand from GridViewDeleteCommand from GridView

XxxParameter types specify source of XxxParameter types specify source of parameter valuesparameter values

XxxParameters PropertiesXxxParameters Properties

Name Description

SelectParameters Specifies parameters for SelectCommand

InsertParameters

UpdateParameters

DeleteParameters

FilterParameters Specifies parameters for FilterExpression

Specifies parameters for InsertCommand

Specifies parameters for UpdateCommand

Specifies parameters for DeleteCommand

XxxParameter TypesXxxParameter Types

Name Description

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

QueryStringParameterBinds a replaceable parameter to a query string parameter

SessionParameter Binds a replaceable parameter to a session variable

Parameter Binds a replaceable parameter to a data field

Using ControlParameterUsing ControlParameter

<asp:SqlDataSource ID="Countries" RunAt="server" ConnectionString="server=localhost;database=northwind;..." SelectCommand="select distinct country from customers order by country" />

<asp:SqlDataSource ID="Customers" RunAt="server" ConnectionString="server=localhost;database=northwind;..." 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 ProceduresCalling 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

CREATE PROCEDURE proc_GetCustomers@Country nvarchar (32) AS SELECT * FROM Customers WHERE Country = @Country

CREATE PROCEDURE proc_GetCountries AS SELECT DISTINCT Country FROM Customers ORDER BY Country

CREATE PROCEDURE proc_GetCountries AS SELECT DISTINCT Country FROM Customers ORDER BY Country

SqlDataSourceSqlDataSource

Creating a SqlDataSource with a SQL Creating a SqlDataSource with a SQL stmt.stmt.

Binding to a DropDownListBinding to a DropDownList

Caching a DataSourceCaching a DataSource

Creating a SqlDataSource with a Creating a SqlDataSource with a parameterparameter

Filtering a GridViewFiltering a GridView

ObjectDataSourceObjectDataSource

Declarative binding to data Declarative binding to data componentscomponents

Leverage middle-tier data access Leverage middle-tier data access componentscomponents

Keep data access code separate from UI Keep data access code separate from UI layerlayer

Two-way data bindingTwo-way data bindingSelectMethod, InsertMethod, SelectMethod, InsertMethod, UpdateMethod, and DeleteMethodUpdateMethod, and DeleteMethod

Optional caching of query resultsOptional caching of query results

Parameterized operationParameterized operation

Key ODS PropertiesKey ODS Properties

Name Description

TypeName Type name of data component

SelectMethod Method called on data component to perform queries

InsertMethod

UpdateMethod

DeleteMethod

EnableCaching Specifies whether caching is enabled (default = false)

Method called on data component to perform inserts

Method called on data component to perform updates

Method called on data component to perform deletes

Initialization and Clean-UpInitialization and Clean-Up

ObjectDataSource can target static ObjectDataSource can target static methods or instance methodsmethods or instance methods

If instance methods are used:If instance methods are used:ODS creates new class instance on each ODS creates new class instance on each callcall

Class must have public default Class must have public default constructorconstructor

Use ObjectCreated and Use ObjectCreated and ObjectDisposing events to perform ObjectDisposing events to perform specialized initialization or clean-up specialized initialization or clean-up work on data componentswork on data components

ObjectDataSourceObjectDataSource

Create a data access objectCreate a data access object

Create an ObjectDataSource Create an ObjectDataSource specifying the Type, SelectMethodspecifying the Type, SelectMethod

Create a data access object, Take 2Create a data access object, Take 2

Create a data access object, Take 3Create a data access object, Take 3

The GridView ControlThe GridView Control

Enhanced DataGrid controlEnhanced DataGrid controlRenders sets of records as HTML tablesRenders sets of records as HTML tables

Built-in sorting, paging, selecting, Built-in sorting, paging, selecting, updating, and deleting supportupdating, and deleting support

Supports rich assortment of field Supports rich assortment of field types, including ImageFields and types, including ImageFields and CheckBoxFieldsCheckBoxFields

Declared in <Columns> elementDeclared in <Columns> element

Highly customizable UIHighly customizable UI

GridView ExampleGridView Example

<asp:SqlDataSource ID="Employees" RunAt="server" ConnectionString="server=localhost;database=northwind;..." SelectCommand="select lastname, firstname, title from employees" />

<asp:GridView DataSourceID="Employees" Width="100%" RunAt="server" />

OutputOutput

GridView Field TypesGridView 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

HyperLinkField Renders columns of hyperlinks

TemplateField Renders columns using HTML templates

CommandField Renders controls for selecting and editing GridView data

ImageField Renders columns of images

Specifying Field TypesSpecifying 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:ImageField HeaderText="" DataField="photo" /> <asp:TemplateField HeaderText="Name"> <ItemTemplate> <%# Eval ("firstname") + " " + Eval ("lastname") %> </ItemTemplate> </asp:TemplateField> <asp:BoundField HeaderText="Title" DataField="title" /> </Columns></asp:GridView>

OutputOutput

GridView ControlGridView Control

Enable Paging Enable Paging

Enable SortingEnable Sorting

Remove some columnsRemove some columns

Change alignmentChange alignment

Add FormattingAdd Formatting

Create a Hyperlink ColumnCreate a Hyperlink Column

The DetailsView ControlThe DetailsView Control

Renders individual recordsRenders individual recordsPair with GridView for master-detail Pair with GridView for master-detail viewsviews

Or use without GridView to display Or use without GridView to display individual recordsindividual records

Built-in paging, inserting, updating, Built-in paging, inserting, updating, deletingdeleting

Uses same field types as GridViewUses same field types as GridViewDeclared in <Fields> elementDeclared in <Fields> element

Highly customizable UIHighly customizable UI

DetailsView ExampleDetailsView 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:ImageField HeaderText="" DataField="photo" /> <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>

OutputOutput

DetailsView ControlDetailsView Control

Create a datasource that selects a Create a datasource that selects a single rowsingle row

Enable Edit, Delete, and InsertEnable Edit, Delete, and Insert

Inserting, Updating, and Inserting, Updating, and DeletingDeleting

Data controls supply editing UIsData controls supply editing UIsAutoGenerateXxxButton propertiesAutoGenerateXxxButton properties

Insert/EditRowStyle propertiesInsert/EditRowStyle properties

Data source controls supply editing Data source controls supply editing logiclogic

Insert/Update/DeleteCommand Insert/Update/DeleteCommand propertiesproperties

Insert/Update/DeleteParameters Insert/Update/DeleteParameters propertiesproperties

Inserting/ed, Updating/ed, Deleting/ed Inserting/ed, Updating/ed, Deleting/ed eventsevents

Visual Studio supplies the glueVisual Studio supplies the glue

Editing with GridViewsEditing with GridViews

<asp:SqlDataSource ID="Employees" RunAt="server" ConnectionString="server=localhost;database=northwind;..." SelectCommand="select employeeid, lastname, firstname, from employees" UpdateCommand="update employees set lastname=@lastname, firstname= @firstname where employeeid=@original_employeeid"> <UpdateParameters> <asp:Parameter Name="EmployeeID" Type="Int32" /> <asp:Parameter Name="lastname" Type="String" /> <asp:Parameter Name="firstname" Type="String" /> </UpdateParameters></asp:SqlDataSource>

<asp:GridView DataSourceID="Employees" Width="100%" RunAt="server" DataKeyNames="EmployeeID" AutoGenerateEditButton="true" />

Edit buttonsPrimary key

Update command Update parameters

Conflict DetectionConflict Detection

First-in winsFirst-in winsUpdate fails if data has changedUpdate fails if data has changed

Structure UpdateCommand accordinglyStructure UpdateCommand accordingly

Set Set ConflictDetection="CompareAllValues"ConflictDetection="CompareAllValues"

Last-in winsLast-in winsUpdate succeeds even if data has Update succeeds even if data has changedchanged

Structure UpdateCommand accordinglyStructure UpdateCommand accordingly

Set Set ConflictDetection="OverwriteChanges"ConflictDetection="OverwriteChanges"

First-In-Wins UpdatesFirst-In-Wins Updates

<asp:SqlDataSource ID="Employees" RunAt="server" ConnectionString="server=localhost;database=northwind;..." SelectCommand="select employeeid, lastname, firstname, from employees" UpdateCommand="update employees set lastname=@lastname, firstname= @firstname where employeeid=@original_employeeid and lastname= @original_lastname and firstname=@original_firstname" ConflictDetection="CompareAllValues"> <UpdateParameters> <asp:Parameter Name="EmployeeID" Type="Int32" /> <asp:Parameter Name="lastname" Type="String" /> <asp:Parameter Name="firstname" Type="String" /> </UpdateParameters></asp:SqlDataSource>

<asp:GridView DataSourceID="Employees" Width="100%" RunAt="server" DataKeyNames="EmployeeID" AutoGenerateEditButton="true" />

Error DetectionError Detection

Controls fire events after database Controls fire events after database updatesupdates

GridView.RowUpdatedGridView.RowUpdated

DetailsView.ItemUpdatedDetailsView.ItemUpdated

SqlDataSource.Updated, etc.SqlDataSource.Updated, etc.

Event handlers receive "status" Event handlers receive "status" objectsobjects

Reveal whether database exception Reveal whether database exception occurredoccurred

Allow exceptions to be handled or Allow exceptions to be handled or rethrownrethrown

Reveal how many rows were affectedReveal how many rows were affected

SQL Cache DependenciesSQL Cache Dependencies

New cache dependency typeNew cache dependency typeEmbodied in SqlCacheDependency classEmbodied in SqlCacheDependency class

Configured through Configured through <sqlCacheDependency> configuration <sqlCacheDependency> configuration sectionsection

Links cached items to database Links cached items to database entitiesentities

ASP.NET application cacheASP.NET application cache

ASP.NET output cacheASP.NET output cache

Compatible with SQL Server 7, 2000, Compatible with SQL Server 7, 2000, 20052005

Preparing a DatabasePreparing a Database

Use Aspnet_regsql.exe or SqlCache-Use Aspnet_regsql.exe or SqlCache-DependencyAdmin to prepare DependencyAdmin to prepare database*database*

aspnet_regsql -S localhost -E -d Northwind -ed

Trusted connectionDatabase nameEnable database

* Not necessary for SQL Server 2005

Server name

Preparing a TablePreparing a Table

Use Aspnet_regsql.exe or SqlCache-Use Aspnet_regsql.exe or SqlCache-DependencyAdmin to prepare table*DependencyAdmin to prepare table*

aspnet_regsql -S localhost -E -d Northwind -t Products -et

Trusted connectionDatabase nameTable name

Server name

Enable table

* Not necessary for SQL Server 2005

Preparing Web.configPreparing Web.config

<configuration> <connectionStrings> <add name="NorthwindConnectionString" connectionString="server=localhost;database=northwind;..." /> </connectionStrings> <system.web> <caching> <sqlCacheDependency enabled="true" pollTime=“60000"> <databases> <add name="Northwind"

connectionStringName="NorthwindConnectionString" /> </databases> </sqlCacheDependency> </caching> <system.web></configuration>

Using Using SqlCacheDependency with SqlCacheDependency with the Application Cachethe Application CacheCache.Insert ("Products", products, new SqlCacheDependency ("Northwind", "Products");

Database name

Table name

Using Using SqlCacheDependency with SqlCacheDependency with the Output Cachethe Output Cache<%@ OutputCache Duration="60" VaryByParam="None" SqlDependency="Northwind:Products" %>

Database name

Table name

Using Using SqlCacheDependency with SqlCacheDependency with SqlDataSourceSqlDataSource<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

SQL Cache DependenciesSQL Cache Dependencies

Prepare DatabasePrepare Database

Prepare TablePrepare Table

Create a cache sectionCreate a cache section

Enable CachingEnable Caching

Enable SqlCacheDependencyEnable SqlCacheDependency

SummarySummary

New DataSource ControlsNew DataSource Controls

New Data UI ControlsNew Data UI Controls

Caching, Paging, Sorting, Editing with Caching, Paging, Sorting, Editing with minimal codingminimal coding

Thank YouThank You

Philip WolfePhilip Wolfe

[email protected]@funwith.net

www.philipwolfe.comwww.philipwolfe.com

philip wolfe senior consultant sogeti session 3 - leveraging data in asp.net 2.0 (level 200)

Documents