Archives / 2006 / January
  • VS 2005 Add-ins and Extensibility

    One of the powerful features of Visual Studio is the extensibility model it provides that enable advanced developers to extend and enhance the built-in product.  Using this you can build wizards, menus, integrate with the text-editor, etc, etc.  The current build of the VS 2005 Web Application Project (which introduces an entirely new web project system) is actually built entirely using these public APIs.

  • ListControl.AppendDataBoundItems Property in ASP.NET 2.0

    Bilal Haidar recently did a nice blog post that called out a small but useful new feature on the ListControl base class (which is the base control for a variety of input controls) in ASP.NET 2.0.  Specifically it is a property called "AppendDataBoundItems", and it controls whether the items within an existing list are replaced or appended-to when the control is databound (with ASP.NET 1.1 the items were always replaced).

  • Code Snippets in Visual Web Developer and VS 2005

    One of the nice new features of Visual Web Developer (which is free) and VS 2005 is the new "Code Snippet Support" that has been added.  This handy feature allows you to quickly find and use code-libraries of common patterns (examples: get/set property definitions, data access code, file IO, exception handling blocks, etc).

  • Adding Custom Properties to XML Site Maps in ASP.NET 2.0

    I published a step by step tutorial on using Master Pages and Site Navigation last week.  One of the questions that someone posted in the comments was whether there was an ability with Site Navigation to add custom properties or settings on a per-node basis.  For example: a custom icon property (eg: imageUrl) for each item in the Site Map that could then be later rendered by the new 2.0 menu control on a page:

  • Creating Custom Parameter Types for DataSource Controls in ASP.NET 2.0

    I've posted in the past about how to create your own custom datasource controls using ASP.NET 2.0.   One of the other cool new ASP.NET 2.0 extensibility points is the ability to create your own custom datasource parameter objects.  These can then be used with any datasource control (including built-in ones like ObjectDataSource or SqlDataSource -- or any other custom ones).  That way, you could write code like this (where I have a sortable, pagable GridView binding against a business object or DAL like this):

  • Google Maps Control for ASP.NET 2.0

    I found this article recently which I thought was pretty cool. It describes how to build and use an ASP.NET Server Control that makes Google Map integration easy within ASP.NET applications:

  • Data Tutorial #2: Building our Master Page and Site Navigation Structure

    June 22nd 2006 Update: We've now published a whole series of new data tutorials based on this origional post.  You can read all about it here.


    This past weekend I posted a step-by-step tutorial on how to build a strongly-typed DAL (data access layer) using Visual Web Developer (which is free) and ASP.NET 2.0.


    My plan over the next few weeks is to post many follow-up samples that show various ways to use this DAL to build common data scenarios using ASP.NET 2.0 (master details, filtering, sorting, paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing, hierarchical drill-down, optimistic concurrency, and more).


    Before doing that, I wanted to setup a common site structure to help organize all of these samples.  I wanted to make sure that all the samples in the site shares a consistent look and feel, and that the site has an easy to navigate site structure.  Thankfully this is now very easy using the new ASP.NET 2.0 Master Page and Site Navigation features (and can be done without having to write any code).


    Here is a screen-shot of the sample site skeleton I put together:



    You can download the site sample here.  The below set of tutorials walks-through how I built it:


    Step 1: Adding a master page


    Below is the basic site structure we were left with after building our data acess layer for the Northwinds database in my previous blog post.  It has a strongly typed DAL that goes against the Northwinds database:



    What I want to-do now is add a Master Page to the site.  Master Pages is a new feature in ASP.NET 2.0 that enables me to define a common layout structure and look and feel that I can easily apply to multiple (or all) pages across a site/app. 


    To add a Master Page, right click on the project and choose “Add New Item”.  Pick the Master Page Template from the “Add New Item” dialog and name it “Site.Master”:



    I want my site to use a CSS based layout approach.  As such, I am using <div> elements to organize the structure (as opposed to <table> elements).  Here is the HTML I added:


    <%@ Master Language="VB" CodeFile="Site.master.vb" Inherits="Site" %>


    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">


    <html xmlns="">

    <head runat="server">

        <title>Northwind Data Samples</title>

        <link rel="stylesheet" type="text/css" href="stylesheet.css" />




        <div id="wrapper">


            <form id="Form1" runat="server">


                  <div id="header">

                      <span class="title">Northwind Data Tutorials</span>

                      <span class="breadcrumb">Todo: Breadcrumb will go here...</span>



                  <div id="content">

                    <asp:ContentPlaceHolder ID="MainContent" runat="server">




                <div id="navigation">

                    Todo: Menu will go here...






        <div id="footer">

            <a href=""></a>





    Notice the <asp:contentplaceholder> control that is in the middle <div> element.  This is a new ASP.NET 2.0 control that I can use to define regions of the master template that can be “filled-in” or replaced by pages that use this master.  You can have any number of <asp:ContentPlaceHolder> controls you want within a master-page – all you need to-do is make sure that each has a unique “id” value.  For the sample above I’ve added one <asp:contentplaceholder> and named it “MainContent” (since it will be where pages on the site fill in their content).


    I am also then using an external CSS stylesheet (“stylesheet.css”) to define the CSS for the page.  When in design-view the Master Page looks like this:



    Step 2: Create a Home Page based on the Master Page


    Now that I have my Master Page defined, I can go ahead and build pages using it.  To build one, right click on the Project and choose “Add New Item”, and select the “Web Form” item:



    Notice that I’ve selected the “Select master page” checkbox near the bottom of the dialog.  This tells Visual Web Developer that you want to have this new page use a Master Page.  When you click the “add” button it will then ask you to pick the Master Page to use:



    When I select the “Site.Master” file we defined above, it will create a new Default.aspx file like so:


    <%@ Page Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>


    <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">



    Visual Web Developer has automatically added an <asp:content> control for the “MainContent” contentplaceholder we defined earlier (note the “ContentPlaceHolderId attribute defines which contentplaceholder we want to override).  I can then fill this content region in with the unique content I want to add to the page:


    <%@ Page Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Home" %>


    <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">


        <h1>Welcome to the Northwind Data Samples Site</h1>


        <p>This site is being built as part of a set of tutorials that show off some of the new data access

        and databinding features in ASP.NET 2.0 and Visual Web Developer.</p>


        <p>Overtime, it will include a host of samples that demonstrate: building a DAL (data access layer)

        using strongly typed TableAdapters and DataTables, master details, filtering, sorting,

        paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing,

        hierarchical drill-down, optimistic concurrency, and more. </p>


        <p>Please subscribe to <a href=""></a>

        to follow along as they are published.</p>




    Note that the page will automatically pick up the CSS stylesheet from the master-page (ASP.NET will also automatically “rebase” the CSS url when the .aspx page is in a sub-directory – so you don’t have to worry about fully qualifying the style-sheet or doing weird “../” hacks).


    Note also that I have set the “Title” attribute on the <%@ Page %> directive to “Home”.  This attribute allows me to declaratively provide the page title, even though the <head> element is defined within our Master Page.


    When I switch into design-view on the page, Visual Web Developer will automatically show me a merged view of the page that combines both the Master Page and deriving Page:



    ASP.NET will also merge the content together at runtime – and send down a single html page when a browser requests this page.  The beauty of this model is that the Master Page layout is defined in one single place – so if I need to make a change I can update one file, and have every file that is based on it within the site immediately update.


    Step 3: Adding More Pages to the Site


    I can use the Master Page to quickly build several more pages for the site.  Specifically, I decided to add two top level section pages “Basic Data Scenarios” and “Advanced Data Scenarios” that I think I might want to use to group several samples around.  I then built several stub sample pages in directories that will live within them.


    After adding many files to the project (all based on the Master Page file), my directory structure looks like this:



    Step 4: Defining a Site Map for the Site


    One challenge I am going to have as I build-out my site is keeping the organizational structure of it in shape (especially if I keep adding samples each week).  I’m going to want to have some type of menu system that helps users on the site navigate their way around.  What I want to make sure I avoid is having to manually build and then update this menu structure within HTML every-time I make a change.  Instead, what I want to-do is to define the site link structure with a clean data-model that I can then dynamically data-bind my UI against.  The good news is that ASP.NET 2.0 makes this easy with the new Site Navigation system.


    Using the Site Navigation system I can define the logical “site map” structure of what my site looks like – specifically how the site structure is logically laid out (this can be different to how they are physically organized on disk), and how the different pages are organized in relation to each other.  I can then access this structure at runtime using the new “SiteMap” property on each ASP.NET page and user-control.  What is powerful about this API is that I can also use it to keep track of where the current request is within the site structure – as well as dynamically lookup a request’s relation to other urls within the site (for example: what is the “parent, “sibling” and “child” nodes in the site-map relative to the current request).   Even fancier, I can integrate the Site Map system with the new ASP.NET 2.0 Role Management security features – so that I can view the structure through the “trimmed view” of what a visiting user has permission to see (for example: pages that are secured only for users in an admin role wouldn’t show up in the Site Navigation model when a guest is visiting the site).  The combination of all these features makes it very easy to quickly build menu navigation and bread-crumb UI.  You can also use this module to help your site integrate better with search engines like Google.


    To define our Site Navigation structure, I’m going to use the built-in XML Site Map Provider that ships with ASP.NET 2.0.  Alternatively, if I wanted to store the site-map structure in a database I could have configured my site to use the cool new SQL Site Map Provider (the beauty of the ASP.NET 2.0 provider model is that all the code and data-binding logic to work against the Site Navigation system stays the same regardless of which provider implementation you have configured). 


    The XML-file based provider uses XML files that by default have the name “Web.SiteMap” to define the site hierarchy.  To create one of these files, right click on the project and choose “Add New Item” and the “Site Map” item:



    This will create an XML file with a default schema for defining a site-layout.  Note that Visual Web Developer provides automatic intellisense for this XML structure.


    For my particular sample, I choose to define the site structure like so:


    <?xml version="1.0" encoding="utf-8" ?>

    <siteMap xmlns="" >


        <siteMapNode url="Default.aspx" title="Home"  description="Home">


            <siteMapNode url="Samples_Basic/BasicSamples.aspx" title="Basic Data Samples"  description="Basic Data Samples">

              <siteMapNode url="Samples_Basic/Sample1.aspx" title="Samples 1"  description="Samples 1" />

              <siteMapNode url="Samples_Basic/Sample2.aspx" title="Samples 1"  description="Samples 2" />

              <siteMapNode url="Samples_Basic/Sample3.aspx" title="Samples 1"  description="Samples 3" />

              <siteMapNode url="Samples_Basic/Sample4.aspx" title="Samples 1"  description="Samples 4" />



            <siteMapNode url="Samples_Advanced/AdvancedSamples.aspx" title="Advanced Data Samples"  description="Advanced Data Samples">

              <siteMapNode url="Samples_Advanced/Sample1.aspx" title="Samples 1"  description="Samples 1" />

              <siteMapNode url="Samples_Advanced/Sample2.aspx" title="Samples 1"  description="Samples 2" />

              <siteMapNode url="Samples_Advanced/Sample3.aspx" title="Samples 1"  description="Samples 3" />

              <siteMapNode url="Samples_Advanced/Sample4.aspx" title="Samples 1"  description="Samples 4" />



            <siteMapNode url="About.aspx" title="About"  description="About" />






    It has a top-level node called “Home” – and then three sub-nodes – “Basic Samples”, “Advanced Samples” and “About”.  The “Basic Samples” and “Advanced Samples” then have several sub-nodes beneath them. 


    Note that ASP.NET will automatically cache the Site Maps’ XML file so that it doesn’t get read on each request – instead it will only be parsed and processed on the first request to the application, and then on subsequent requests the cached version will be used (note: this will automatically get re-generated anytime the file changes).


    I can then programmatically use the SiteMap.CurrentNode property within an ASP.NET page at runtime to get back a SiteMapNode object that represents where the current request is within the above Site Map definition – as well as what its parent, children, and sibling node urls are (and what their friendly names are as well).


    Step 5: Data-Building a Site Navigation Menu Structure


    ASP.NET 2.0 introduces a new concept called “data source” controls – which are control objects that provide a standard way to expose data that UI controls can then bind against.  The data source model is extensible, so you can easily build your own Data Source controls to plug into the system (this blog post points to how to-do this).  One of the built-in data-source controls that ASP.NET 2.0 ships with is the <asp:sitemapdatasource> control – which makes it super easy to databind any UI controls against the Site Navigation data model.


    ASP.NET 2.0 ships with built-in <asp:treeview> and <asp:menu> controls, which can be used to create menu and tree-view structures based on the site-map structure.  To add and then data-bind the <asp:menu> control to a <asp:sitemapdatasource> control on a page, I could simple add this markup to the Site.Master file (replacing the previous to-do menu comment):


    <div id="navigation">

    <asp:Menu ID="foo" DataSourceID="SiteMapDataSource1" runat="server">



          <asp:SiteMapDataSource ID="SiteMapDataSource1" ShowStartingNode="false" runat="server" />



    I would then have a fly-out menu for navigating around the site.


    Alternatively, if I want even greater control over the HTML generated, I could use more basic (but also more flexible) controls – like the ASP.NET Repeater control. 


    For example, I could use the <asp:repeater> to create an html <ul></ul> list like so:


    <div id="navigation">



                <a href="default.aspx">Home</a>



            <asp:Repeater ID="foo" DataSourceID="SiteMapDataSource1" runat="server">



                        <a href='<%#Eval("url") %>'><%#Eval("Title") %></a>







    <asp:SiteMapDataSource ID="SiteMapDataSource1" ShowStartingNode="false" runat="server" />


    With our Web.SiteMap file defined like it is above, this would then generate the below html at runtime:


    <div id="navigation">



                 <a href="default.aspx">Home</a>



                  <a href='/DALWalkthrough/Samples_Basic/BasicSamples.aspx'>Basic Data Samples</a>



                 <a href='/DALWalkthrough/Samples_Advanced/AdvancedSamples.aspx'>Advanced Data Samples</a>



                 <a href='/DALWalkthrough/About.aspx'>About</a>





    If I wanted to show the next level of hierarchy in the SiteMap as well, I could add another <asp:repeater> within the first one to also generate a sub-hierarchy of <ul><li><ul> elements.  For example:


    <asp:Repeater ID="foo" DataSourceID="SiteMapDataSource1" runat="server" enableviewstate="false">



                <a href='<%#Eval("url") %>'><%#Eval("Title") %></a>



                    <asp:Repeater ID="bar" DataSource='<%#Container.DataItem.ChildNodes() %>' runat="server">


                            <li><a href='<%#Eval("url") %>'><%#Eval("Title") %></a></li>









    Note that VB allows me to write Container.DataItem.ChildNodes() as a direct data-bound expression.  In C# I would need to cast like so: ((SiteMapNode) Container.DataItem).ChildNodes()


    This would then generate the below HTML markup:


    <div id="navigation">


            <li><a href="default.aspx">Home</a></li>

            <li><a href='/DALWalkthrough/Samples_Basic/BasicSamples.aspx'>Basic Data Samples</a>


                    <li><a href='/DALWalkthrough/Samples_Basic/Sample1.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Basic/Sample2.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Basic/Sample3.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Basic/Sample4.aspx'>Samples 1</a></li>



            <li><a href='/DALWalkthrough/Samples_Advanced/AdvancedSamples.aspx'>Advanced Data Samples</a>


                    <li><a href='/DALWalkthrough/Samples_Advanced/Sample1.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Advanced/Sample2.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Advanced/Sample3.aspx'>Samples 1</a></li>

                    <li><a href='/DALWalkthrough/Samples_Advanced/Sample4.aspx'>Samples 1</a></li>



            <li><a href='/DALWalkthrough/About.aspx'>About</a>





    I can then use a standard CSS styling approach to customize the look and feel of this structure however I want.  Rachel Andrew has a great book that I use called “The CSS Anthology: 101 Essential Tips, Tricks & Hacks” that provides a really nice scenario based tutorial approach to using CSS.  I used a technique she came up with in chapter 4 to make the markup above look like this when I add some CSS to my StyleSheet.css file:



    And now I have a nice looking menu for my site, data-bound to the Site Navigation system, which is in turn data-driven from my web.sitemap file.


    Step 6: Adding a “Breadcrumb” navigation control to the page


    The last touch I want to add to my site is support for a “bread-crumb” UI paradigm near the top of the page that will help users easily figure out where they currently are within the application.  The good news is that this is super easy with ASP.NET 2.0 and the Site Navigation system.


    All I need to-do is add the new <asp:SiteMapPath> control to my “header” <div>:


    <div id="header">

        <span class="title">Northwind Data Tutorials</span>

        <span class="breadcrumb">

            <asp:SiteMapPath ID="Breadcrumb"  runat="server"></asp:SiteMapPath>




    This will then output the site hierarchy of the current node relative to the root node of the site map.  For example, if I was on “Sample1” within the “Basic Data Samples” section of the site, the above control would automatically output this:



    If I click on the “Basic Data Samples” hyperlink (which is automatically generated by the breadcrumb – or I could just use the menu link), it would adjust to:



    No code is required.




    I now have the basic site structure and layout defined for the sample site I am going to use to build my data samples.  It has a consistent, centralized, layout and look and feel structure by using the new ASP.NET 2.0 Master Page feature.  And my site and link structure is nicely encapsulated by the ASP.NET 2.0 Site Navigation system, which I’m also using to dynamically generate a navigation menu and breadcrumb UI for the site:



    Best of all, I didn’t need to write any code to enable this, and I still get full WYSIWYG designer support within Visual Web Developer.


    Hope this helps.  Lots of data samples are now going to follow…




  • Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0

    June 22nd 2006 Update: We've now published a whole series of new data tutorials based on this origional post.  You can read all about it here.


    One of my goals over the next few weeks is to publish a number of quick tutorial postings that walkthrough implementing common data binding design patterns using ASP.NET 2.0 (master details, filtering, sorting, paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing, hierarchical drill-down, optimistic concurrency, etc, etc).


    To help keep these samples shorter, and to help link them together, I’m going to use a common DAL (data access layer) implementation across the samples that is based on SQL Server’s Northwind sample database.


    To build the DAL layer I decided to use the new DataSet Designer that is built-into Visual Web Developer (which you can download for free) as well as VS 2005, and which provides an easy way to create and encapsulate data access components within an application.  I’ve used it for a few samples lately, and have found it pretty useful and flexible.  I really like the fact that it enables me to avoid having to write tedious data access code, while still preserving full control over my SQL data logic (I also like the fact that it enables me to use both standard SQL statements as well as SPROCs).


    One of the things that I’ve noticed is that there aren’t a lot of end-to-end tutorials that show off how to build and use a DAL with the designer (one exception is the great write-up by Brian Noyes – who includes samples of how to use it with SPROCs).  What I’ve tried to-do with the below set of tutorials is provide an end-to-end, step-by-step, walkthrough that covers all of the core concepts involved in creating and consuming a DAL built with the data designer. 


    Using the data designer and ASP.NET 2.0 together, you should be able to create a core DAL implementation and build from scratch a data-driven UI web app on top of an existing database very quickly (~10-15 minutes to build an application from scratch that supports master/details filtering along with data paging, sorting, insertion, and editing).


    The final result of the below walkthroughs can be downloaded here.  This download includes all of the code samples as well as the DAL built up below.


    I’ll then be posting lots of (much shorter! <g>) blog posting over the next few weeks that show off common data-UI patterns that use it.


    Tutorial 1: Creating a Web Project and Connecting To the Database


    To begin with, create an empty new ASP.NET Web Site Project (File->New Web Site) within Visual Web Developer.  We can then connect and load our Northwinds database in the IDE.  To-do this you can follow one of two approaches:


    Option 1: Connect to a local or remote SQL 2000 or SQL 2005 database that has the Northwinds sample installed.  To-do this, go to the “Server Explorer” property window in Visual Web Developer (choose View->Server Explorer if it isn’t currently visible), right-click on the “Data Connections” node and choose to add a new database connection.  You can then walkthrough a wizard to pick your SQL database and load the Northwinds database in the IDE:



    Option 2: If you don’t have a SQL 2000 or SQL 2005, you can alternatively use the free SQL 2005 Express Edition.  Download this .zip file containing the Northwinds.mdf database file, and copy it to the “App_Data” directory underneath your web site’s root directory.  Click the “Refresh” button in the solution explorer, and it will appear in the Solution Explorer:



    SQL Express databases within the App_Data directory will automatically show-up as a listed item in the Server Explorer property window (note: I sometimes get a timeout error the very first time I expand a newly copied sql express database here – I think because it is generating the log file the first time it is accessed.  If this happens just click it again and it seems to immediately expands):



    After the Northwinds database has been loaded using either Option #1 or Option #2 above, you can expand, view, edit, and add to any of the tables, stored procedures, views, triggers, etc with the database.  You can also use the query builder to test out and run queries against the database (or right-click on a table and choose “Show Table Data” to see all of it).


    Tutorial 2: Launching the DataSet Designer


    To create our Northwind DAL, right-click on the project node in the solution explorer, and select “Add New Item”.  Then select the “DataSet” option and name it “Northwind.xsd”:



    This will prompt me as to whether I want to add it under the “App_Code” directory.  When I click “yes” it will bring up a data-design surface, and (if in a web project) automatically launch the “Create TableAdapter” wizard (if you are in a class library project you need to right click and choose “Add->Table Adapter to launch this wizard):



    Tutorial 3: Creating our First Table Adapter


    The “Create TableAdapter” wizard will first prompt me for the database to use, and provide a list of all database connections currently registered in Visual Web Developer’s server-explorer window (note: you can also create new connections from this wizard if you want). 


    After I choose the Northwind database I want to use, it will prompt me for where to store the database connection-string.  By default it will avoid hard-coding it within your code, and will instead save it within the new <connectionStrings> section of your web.config file (or app.config file if you are in a Class Library Project).  Note that with .NET 2.0 you can now optionally encrypt configuration file values to keep them secure, as well as use the new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool to change it later (ideal for administrators).  Pick a name for what you want to call it:



    You can then choose how you want to configure the TableAdapter – you can use either SQL Statements that you embed within your DAL layer, or stored procedures (SPROCs) that you call from it:



    For our first table adapter we’ll use a SQL Statement.  You can either type this in directly within the next window:



    Or alternatively launch the query-builder from that wizard step to graphically construct it (one handy feature with it is the ability to execute test queries to validate results):



    Once we’ve finished building and testing our query, we will return back to the “Enter a SQL Statement” page.  Before moving to the next page, we’ll want to click the “Advanced Options” button to verify what type of operations we want the designer to generate:



    The above dialog shows the default settings that are set when you run the “Create TableAdapter” wizard from within a VS 2005 Web Site Project (these are the most common data patterns we typically see developer’s use with stateless web applications).  When you run the wizard from within a Class Library Project or from a Windows Client Project, the “Use Optimistic Concurrency” checkbox will be selected by default as well. 


    For the purposes of this DAL walkthrough, we want to turn off optimistic concurrency (note: one of my later blog postings on optimistic concurrency will change that setting – but I will be showing straight updates first).


    When we click next on the wizard again, we’ll be prompted for the method names we want to create in our DAL to run our Category SQL query.  There are two patterns of data usage that this data wizard can generate – one is the “Fill” pattern used with DataSets, that will generate a method that accepts a DataSet or DataTable parameter that we want to add category information to.  The second pattern of data usage is one where we will generate a method that will simply return a new DataTable containing our category information.  This later approach is the one we’ll be primarily using in all of our later tutorials, and is more typical with stateless web-applications.


    In the wizard I’m going to name this DataTable method “GetAllSuppliers”.  I will also keep the “GenerateDBDirectMethods” checkbox to automatically create default Insert, Update and Delete methods based off of my Select query on the component:



    The wizard will then list all of the DAL methods it will create as part of this wizard process for, and when I hit finish add them to our data designer:



    What I now have is a strongly-typed DataAdapter class (by default named “NorthwindTableAdapters.SuppliersTableAdapter”) with a “GetAllSuppliers” method that I can use to execute a SQL query and obtain back a strongly-typed DataTable results class (by default named “Northwind.SuppliersDataTable”). 


    I could use these objects to easily obtain all of the Suppliers information from the Northwind database and output it within a page like so:

    Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

    Dim suppliers As Northwind.SuppliersDataTable

    Dim supplier As Northwind.SuppliersRow


    suppliers = suppliersAdapter.GetAllSuppliers()


    For Each supplier In suppliers

    Response.Write("Supplier: " & supplier.CompanyName & "<br>")


    Notice that I don’t need to write any manual ADO.NET code, construct a SQL query, or manage connections at all in the above code – all of that is encapsulated by the SuppliersDataTable and SuppliersTableAdapter that we now have in our project.  Notice also how the SuppliersTableAdapter class is strongly typed – meaning I get intellisense and compilation checking on the “suppliersAdapter.GetSuppliers()” method.  I can also access each property returned in the Suppliers result in a strongly typed fashion (for example: supplier.CompanyName or supplier.SupplierID).


    I could alternatively write the below .aspx page and associated .aspx.vb code-behind file to easily databind and output the results in a grid:




    <%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SupplierTest2.aspx.vb" Inherits="SuppliersTest2" %>




        <title>Simple Category Listing</title>

        <link href="StyleSheet.css" rel="stylesheet" type="text/css" />



        <form id="form1" runat="server">


            <div class="datatable">


                <h1>Simple Category Listing</h1>


                <asp:GridView ID="GridView1" runat="server">










    Imports NorthwindTableAdapters


    Partial Class SuppliersTest2

        Inherits System.Web.UI.Page


        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


            Dim suppliersAdapter As New SuppliersTableAdapter


            GridView1.DataSource = suppliersAdapter.GetAllSuppliers()



        End Sub


    End Class

    Which then generates the below output at runtime:



    Note that when using the new ASP.NET ObjectDataSource control we will be able to eliminate the need to write any code for the scenario above (which even now only has three lines), as well as to automatically enable paging, sorting and editing on the GridView without any page code needed.  I’ll cover how to-do this in a later blog posting on using this DAL.  My goal with the sample above was just to show how you could procedurally databind the GridView using techniques you might already be familiar with in ASP.NET 1.1.


    Tutorial 4: Adding Parameterized Methods within Table Adapters


    It is usually pretty rare in web applications to want to request all of the data within a database table.  More typically you’ll want to use SQL queries to retrieve only the subset of data that you need.  The data designer makes it super easy to create multiple strongly-typed data-methods within a DAL to help with the parameterized SQL operations.


    To add one to our Suppliers object, right-click on it and choose “Add Query”:



    Once again we can choose either a stored procedure, or a SQL statement.  For the SQL statement you can choose to return multiple rows, or just a single value (useful for things like SELECT Count(*) commands, or to retrieve a single value):



    For this parameterized query I’m going to return multiple rows, and will be filtering the data by country value.  I can express a parameter value using a “@parametername” syntax within my SQL statement like so (note: there can be any number of parameters in the SQL statement):



    I can then name this parameterized data method “GetSuppliersByCountry”:



    And now I have two methods I can use within my SuppliersTableAdapter to get Supplier data (GetAllSuppliers and GetSuppliersByCountry).  Note that the designer is fully re-entrant, meaning you can at any point re-configure a data method (for example: to change the SQL statement, add/remove parameters, etc).  Just right click and select the “configure” method to-do so:



    You can also use the designer to run any of the data methods.  Just right-click and select “Preview Data” to execute them and see the results (note how any parameters can be specified as part of this operation):



    And in code I can now easily build a page that uses this new method like so:




    <%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SuppliersByCountry.aspx.vb" Inherits="SuppliersByCountry" %>



    <head runat="server">

        <title>Suppliers By Country Sample</title>

        <link href="StyleSheet.css" rel="stylesheet" type="text/css" />



        <form id="form1" runat="server">


            <h1>Suppliers By Country:</h1>



                Select a country: <asp:TextBox ID="CountryTxt" runat="server" />

                                  <asp:Button ID="SearchBtn" Text="Search" runat="Server" />           



            <div class="datatable">


                <asp:GridView ID="GridView1" runat="server">










    Imports NorthwindTableAdapters


    Partial Class SuppliersByCountry

        Inherits System.Web.UI.Page


        Protected Sub SearchBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchBtn.Click


            Dim suppliersAdapter As New SuppliersTableAdapter


            GridView1.DataSource = suppliersAdapter.GetSuppliersByCountry(CountryTxt.Text)



        End Sub


    End Class

    Which will generate a Suppliers search page:



    Note that I did not have to write any ADO.NET code, manually create parameter collections, manage connection objects etc.  I only had to write 3 lines of code total to build the above sample from scratch using the Visual Web Developer data and page designers.


    Tutorial 5: Using Insert, Update, and Delete DBDirect Commands on TableAdapters


    Because we kept the default “GeneratedDBDirect Methods” checkbox selected, the Create Table Adapter wizard automatically added default Insert, Update and Delete methods to the SuppliersTableAdapter.  You can see these, as well as edit/customize them further, by selecting the SuppliersTableAdapter object within the DataSet Designer and then looking at the property-grid (note: you must select the SuppliersTableAdapter heading to have these methods show up in the property grid – they won’t show up if you just select the Suppliers heading):



    Using the “CommandText” property in the property-grid, you can pull up a query designer for each of the default statements and customize them:



    You can also optionally add your own custom Insert/Update/Delete methods to each TableAdapter.  For example, if I wanted to add a custom Insert method that had the additional behavior of returning the new identity column value of a newly created Supplier row (which has an auto-increment property set for the primary key), I could do so by right-clicking on the SuppliersTableAdapter and choosing “New Query”:



    I’ll then pick doing the INSERT command with a SQL statement, and choose to create an Insert:



    The DataSet designer will then automatically suggest the below SQL statement for me (it looks at the SELECT statement you entered earlier to suggest a default INSERT statement):



    Note that the “SELECT @@Identity” statement at the end will return the newly created primary key from the insert operation.  I could further customize the INSERT operation however I want.


    I can then use the wizard to name the method “InsertSupplier” (or any other name I want).  The wizard will then add this method to the SuppliersAdapter.  The last step I’ll do (since I want to return the @@Identity value from the insert), is to change the type of the InsertSupplier method from “NonQuery” to “Scalar”:



    You could then write the code below to add a new supplier, and then update the supplier values, and then delete the supplier within the Suppliers table:

    Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter


    Dim supplierId As Integer


    supplierId = supplierAdapter.InsertSupplier("Microsoft" _

                                                , "ScottGu" _

                                                , "General Manager" _

                                                , "One Microsoft Way" _

                                                , "Redmond" _

                                                , "USA" _

                                                , "98004" _

                                                , "425-555-1212")


    supplierAdapter.Update("Microsoft" _

                           , "Someone Else" _

                           , "New title" _

                           , "New Address" _

                           , "New City" _

                           , "UK" _

                           , "New Zip" _

                           , "New Number" _

                           , supplierId)



    Note that I did not have to write any ADO.NET code, manually create any parameter collections, or manage connection objects etc.  Because the data designer generates typed methods and DataTables, I’ll get both intellisense/compilation-checking as well as type validation within my DAL (so for example: if I try to pass an integer instead of a DateTime it would give me a compile error).


    Tutorial 6: Using DataTables to Insert/Update/Delete Suppliers


    Tutorial 5 above showed how to use data methods directly on the SuppliersTableAdapter to manipulate individual rows within our database.  Alternatively, developers can also work to perform these operations using the SuppliersDataTable and SuppliersDataRow objects.  These are particularly useful when adding/updating/deleting multiple rows at a time (these updates can optionally be batched to the database in one database call using this approach). 


    The below example demonstrates how to retrieve all of the suppliers in the US with a single database call, then conditionally update some of the supplier’s zip-codes, and then add a new supplier.  We’ll then update the database with all of the additions/changes:

    Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

    Dim suppliers As Northwind.SuppliersDataTable

    Dim supplier As Northwind.SuppliersRow


    ' Obtain all Suppliers in the US

    suppliers = supplierAdapter.GetSuppliersByCountry("USA")


    ' Loop through all suppliers and update any 98042 postcodes to 98004

    For Each supplier In suppliers


    If supplier.PostalCode = "98052" Then

                supplier.PostalCode = "98004"

    End If




    ' Create a New Supplier Just for Fun

    supplier = suppliers.NewSuppliersRow()


    ' Set new data properties on supplier2 row

    With supplier


    .CompanyName = "Microsoft"

          .ContactName = "ScottGu"

    .Address = "One Microsoft Way"

          .ContactTitle = "General Manager"

          .City = "Redmond"

          .PostalCode = "98052"

          .Country = "USA"

          .Phone = "425-555-1212"


    End With


    ' Add New Supplier to SuppliersDataTable



    ' Update Database with all changes (updates + additions)


    Note that there are fancier ways to-do filtering (and sorting) operations that I’m not using above, but I wanted to keep this tutorial simple as opposed to overload it with too many concepts.


    Tutorial 7: Putting the Northwinds DAL Together


    The above tutorials hopefully provide the basic background needed to create DAL’s using the DataSet designer and then use them from code.


    Using the above knowledge, you should be able to go off and quickly create the following strongly-typed TableAdapters with associated data methods pretty easily (note that the designer and query builder can be used to avoid having to manually write any of the SQL queries below – and it will auto-suggest subsequent queries for each adapter after you add the first one).






    SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

    FROM           Suppliers




    SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

    FROM           Suppliers

    WHERE        Country=@Country




    SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

    FROM           Suppliers

    WHERE        SupplierId=@SupplierID




    SELECT        DISTINCT Country

    FROM           Suppliers






    SELECT        CategoryID, CategoryName, Description

    FROM           Categories




    SELECT        CategoryID, CategoryName, Description

    FROM           Categories

    WHERE        CategoryId=@CategoryId 






    SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

    FROM           Employees




    SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

    FROM           Employees

    WHERE        EmployeeID=@EmployeeID




    SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

    FROM           Employees

    WHERE        ReportsTo = @ReportsTo






    SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                        (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

    FROM           Products




    SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                        (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

    FROM           Products

    WHERE        SupplierID=@SupplierID




    SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

                        (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

    FROM           Products

    WHERE        CategoryID=@ CategoryID


    Most of the above TableAdapters are pretty straight-forward and simple.  The one that is a little more advanced is the ProductsTableAdapter.  Specifically, there in addition to retrieving the Products table columns I’m also retrieving the CompanyName from the Suppliers table that maps to the Product’s SupplierId column (it has a foreign key relationship to the Suppliers table).  The above SQL statement will add this as a read-only “SupplierName” column on our ProductsDataTable.


    This will allow me to be more efficient from a database perspective when I want to build a list UI on top of my Products table, and save me from having to hit the database an extra time per-row to retrieve this value when displaying a product list on the site.  


    The good news is that the data designer can still infer enough from the product adapter’s SQL statements to still automatically generate the correct INSERT, UPDATE and DELETE commands for the ProductsTableAdapter.  In cases where you are doing more advanced JOINS across multiple tables and merging results from multiple places, the data designer might not be able to generate these automatically.  The good news is that you can still define these manually within the designer (just click on the table-adapter, and then within its property grid choose to create a new “Insert” (or update or delete) command and define your own logic).


    When finished, my Northwinds data design-surface looks like this (note how the designer will automatically map and detail foreign-key relationships):



    When I hit save on the Northwinds.xsd file (where all of the adapters, definitions and relations are declaratively stored in an XML file), I’ll be able to program and data-bind against any of the objects defined within it.


    Tutorial 8: Adding Custom Code to the DAL


    One of the nice things about the data designer is that the .xsd file that declaratively stores all of the relevant definitions gets translated into either VB or C# code at compile and runtime.  This means that you can easily step through it within the debugger, and always understand “what is it doing for me under the covers”. 


    To easily see what code has been generated by the data designer, just open the “Class View” property window in Visual Web Developer or VS 2005 (if it isn’t visible select the View->Class View menu item), and navigate to the respective namespaces and types to see the object model of them.  You can then right-click and choose “Browse Definition” to jump to the generated code for any method:



    In addition to viewing the generated code, you can also add to it and extend it in many ways.  You can do this either by sub-classing the generated objects, or alternatively by using the new “Partial” keyword feature now supported by the .NET 2.0 language compilers.  The partial keyword enables you to add any method, property or events you want to the classes generated within the DAL, and they will be compiled together into a single generated type.  Because the partial class code you write lives in a separate file from the files generated by the designer, you do not have to worry about the designer ever clobbering or overwriting your code.


    To see a simple example of how you could use this feature, I could easily add an additional “GetProducts()” method to each row of Suppliers within my Northwind DAL (allowing me to easily obtain the ProductDataTable for the specific Supplier I have a reference to).  Enabling this is as easy as adding a “SuppliersRow.vb” file in my app_code directory and adding this code to it:

    Partial Public Class NorthWind


        Partial Public Class SuppliersRow


            Public Function GetProducts() As ProductsDataTable


                Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter

                Return productsAdapter.GetProductsBySupplier(Me.SupplierID)


            End Function


        End Class


    End Class

    This tells the VB compiler that I want to add a “GetProducts()” method to the SuppliersRow class (I’m using the “SupplierID” property on the supplier’s row to automatically figure out the subset of products by Supplier – note that I can write “Me.SupplierId” – since this method is part of the SuppliersRow class).


    Once I add this I can then write this code to easily hierarchically list each product produced by each Supplier:

    Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

    Dim suppliers As Northwind.SuppliersDataTable

    Dim supplier As Northwind.SuppliersRow


    suppliers = suppliersAdapter.GetAllSuppliers()


    For Each supplier In suppliers


    Response.Write("Supplier: " & supplier.CompanyName & "<br>")


          Dim products As Northwind.ProductsDataTable

          Dim product As Northwind.ProductsRow


          products = supplier.GetProducts()


          For Each product In products

                Response.Write("------- Product: " & product.ProductName & "<br>")




    And I could easily build this page using a <asp:DataList> and <asp:Repeater> control to databind a list of suppliers, and then allow a user to click any of the suppliers to list a hierarchical drill-down of their products:



    I’d do this by databinding the suppliers against the DataList:



    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender


    Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter


          DataList1.DataSource = suppliersAdapter.GetAllSuppliers()



    End Sub

    And then using the “Select” feature of DataList to have the selected item template hierarchically retrieve and display the products (this way only the selected supplier’s products are retrieved):




    <h1>Supplier/Product Drilldown:</h1>


    <p>Click a Supplier to List Its Products</p>


    <asp:DataList ID="DataList1" runat="server">



                <asp:linkbutton ID="Details" CommandName="Select" runat="server"><%#Eval("CompanyName") %></asp:linkbutton>





                <div class="selectedsupplier">

                    <strong><%#Eval("CompanyName") %></strong>


                    <asp:Repeater ID="ProductsList" DataSource='<%# Container.DataItem.Row.GetProducts() %>' runat="server">













    Hopefully the above set of tutorials provides a useful (and somewhat exhaustive -- sorry for the length) walkthrough of the core features and capabilities you can easily take advantage of with the DataSet designer in Visual Web Developer and VS 2005 to build easy data access layers. 


    Over the next few weeks I’m going to be posting many (short) blog posting that walkthrough using the DAL I built above to implement common web data patterns using ASP.NET 2.0.


    Hope this helps,




  • Cool VS 2005 Debugging Visualizers for ASP.NET 2.0 Development

    One of the cool new features in VS 2005 is a debugging extensibility feature called "Debugger Visualizers".  Basically this provides a way for developers to build and use UI extensions within the VS debugger that provide richer data visualization and analysis of types and variables within a running program.

  • New ASP.NET 2.0 SQL Site Map Provider

    Jeff Prosise has a written a great sample and article for the February edition of MSDN magazine that walksthrough the new SQL Server based provider he built for the ASP.NET 2.0 Site Navigation System.  This enables you to store your site map structure in a database instead of using the default XML Provider that comes default.  Enabling it within an ASP.NET 2.0 site is as simple as configuring the provider in your web.config file -- no code needs to change in order to take advantage of it (and you can databind any page control to it using the new SiteMapDataSource control).

  • ASP.NET 2.0 SQL Table Profile Provider Released Today

    Hao and Stefan just shipped a cool new Profile Provider download on the ASP.NET Sandbox Site.  It provides two new profile provider implementations (with complete source code) that you can use.  One enables you to map profile properties directly to a SQL table in a database -- enabling you to perform richer queries on the back-end for your personalization data.  The second implementation enables you to map profile properties to stored procedures in a database -- enabling you to perform whatever custom data logic you want to persist your user personalization data. 

  • ASP.NET 2.0 Membership and Role Management Remote Admin Tool

    Peter Kellner has a good article on the new ASP.NET 2.0 Membership and Roles Features, and then put together a very useful sample that demonstrates how to implement a set of admin data-pages on top of the ASP.NET 2.0 Membership and Role Management system to allow you to remotely administer your users and roles.  His sample is available to download in source format -- so you can integrate it within your applications to provide a remote management experience for users/roles that works well in a hosting environment.

  • Encrypting Web.Config Values in ASP.NET 2.0

    One of the cool new features in the configuration system with ASP.NET 2.0 is the ability to encrypt any of the values stored within them (note: this works with any configuration section -- including ones you build yourself).  This is obviously important when storing sensitive information like connection strings -- and now enables you to avoid having to roll your own solution.

  • Cool ProfileView Control

    Joshua Flanagan has recently published his latest update to a new ProfileView control he has built that enables you to view/edit ASP.NET 2.0 Profile settings within a web-page.  It is a pretty cool control definitely worth checking out if you are using the new profile system. 

  • Row-Clickable GridView

    Teemu posted a cool sample of how to subclass the built-in ASP.NET 2.0 GridView to provide a row-clickable GridView control (where you can click on a row to switch into edit mode on it).  I haven't tried yet, but you should be able to combine this with the new <atlas:updatepanel> control to enable a single-click Ajax experience for data editing.

  • Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource

    Last weekend I posted about how to implement super efficient data-paging using the new ROW_NUMBER() within SQL 2005 (which is supported with the free SQL Express edition and up) and the new ASP.NET 2.0 GridView/ObjectDataSource controls.


    The BIG win with this approach is that it enables a web-application to efficiently retrieve only the specific rows it needs from the database, and avoid having to pull back dozens, hundreds, or even thousands of results to the web-server – only to then end up trimming them down to the 10-15 you end up displaying on a web-page (which is what most data-paging solutions do today).  This can lead to significant performance wins within your application (both in terms of latency and throughput), and reduce the load on your database.


    A few people have asked me to also provide a similar sample that demonstrates how to also implement efficient data-paging using an ASP.NET DataList or Repeater control instead (since neither of these have built-in paging UI semantics – but they do have the big benefit of allowing developers to have tight control over the html generated). 


    You can download a sample app that demonstrates how to-do this here.


    For the scenario in the sample I basically “borrowed” the UI used on several popular ecommerce web-sites (although I tried to be a good CSS citizen, and implemented it using CSS rules instead of a table-heavy format):



    It provides a list of product-categories on the left-hand side of the product listing page (the value in parenthesis indicates the number of products in each category), and then a 2 column layout for the products on the right (the default page size is 10 per page – although you can change this with a 1 line code-change if you want a different size).  You can page back and forward throughout the listing, and are provided UI that helps indicate where you are in the paging series (the Previous/More links also auto-hide if you are on the first or last page of the results).


    What is cool about the sample is that all of the data on the right-hand side of the screen (everything but the product category listing on the left) is retrieved with a single database call on each page view (so no multiple database trips).  This single database call also only returns the product rows of data we end up displaying on the given data page (so with the screen-shot above it returns 10 rows of data instead of all 97 rows in that product category).  This makes it super fast and efficient.


    The entire sample to implement this app is only about ~20 lines of total code (this includes all the code in both the UI and data code layers).


    How To Run and Use the Sample


    To setup the sample, follow the below steps:


    1) Have VS 2005 or Visual Web Developer and SQL Express installed.  Visual Web Developer and SQL Express are both free downloads and provide everything you need to build, debug and run the application.  You can download them from here.


    2) Download this .zip file containing the sample.  Expand it into any directory you want.


    3) Using Visual Web Developer or VS 2005, choose File->Open Web-site and point to the root directory of the sample sub-directory (the one with the files in it).


    4) Select the “Products.aspx” file and hit run.


    This will bring up a page like the screenshot above that lists products and allows you to page back and forth throughout a product category listing (note that the next/previous buttons will auto-hide if you are at the beginning or end of the listing).  Click on the left-hand side of the page to switch category listings.


    To add a new product category, run the “AddData.aspx” page:



    When you add a new product category, you can specify the number of products to add to it.  You can add dozens, hundreds, thousands or tens of thousands of products (the AddData.aspx page names the products sequentially for you automatically so you can simulate different sized loads).


    Because the data paging implementation in the sample uses an intelligent paging algorithm that only pulls the needed page rows to the web-server (by default only 10 rows per web request), your server won’t start to run into performance issues even if you have a category or result with tens of thousands of product results.


    How this Sample is Built


    Here is what the solution directory looks like in Visual Web Developer:



    It contains three pages – “Products.aspx”, “ProductDetails.aspx”, and “AddData.aspx” – that are each based on the “Site.master” master-page. 


    The database is implemented in Products.mdf and contains 2 simple tables – “Products” and “Categories” (note: you can create new SQL Express databases by select File->Add New Item->Database File).  Here is what they look like in the database designer built-into the free Visual Web Developer IDE:



    The Categories table contains a list of product categories, and the Products table contains a list of products contained within them. The ProductID column in the Products table is the primary key (and is automatically indexed), and an index has also been created on the CategoryId column (to create an index in the designer, just right click on a column and select “Indexes/Keys” to bring up the Index manager dialog).  These indexes are going to be important if we put 100,000s of products of entries into our Products table.


    The database has one stored procedure called “GetProductsByCategoryId” that was created using the ROW_NUMBER() approach described in David’s blog to enable us to retrieve only the specific product data rows we want from the Products database (this means that we retrieve just the 10 or so products we need for the current page instead of the 1000 products that might be in the product category:



    Notice that I’m also returning the total number of products in a category as well as the friendly name of the category as output parameters of the SPROC.  Originally I was fetching these with a separate database call, but to demonstrate how to implement a nice performance optimization I’m returning them along with our 10 rows of data as output parameters here.  The benefit of doing this optimization (versus a separate call to the database – which would actually be logically cleaner) is that it means you can retrieve all of the data you need with a single database query (which is also in turn only returning 10 rows of data).  For a high-volume page like this where performance matters, it is a performance optimization to seriously consider.


    The data access layer was then implemented using a DataSet designer (choose File->Add New Item->DataSet to create one), which allows us to avoid having to write any manual ADO.NET data access code – and instead just use strongly-typed data classes that are generated and maintained in the project automatically by defining methods/relationships using the DataSet designer. 


    The generated DataSet definitions for this sample are stored within the “MyDataLayer.xsd” file in the App_Code directory and contain two table-adapters (note: if you want to be advanced you can open and edit this XML file defining the DAL structure by right-clicking on it, choosing “Open With”, and selecting the XML source editor):



    The GetProductsByCategoryId() method goes against the SPROC we defined earlier, but all other methods are normal SQL queries defined within the DAL layer.  Once defined using the DataSet designer, it is possible to write procedural code like the code below to invoke and use a method defined with our new DAL components:


            int categoryId = 0;

            int pageIndex = 0;

            int pageSize = 10;

            int numTotalProducts = 0;

            string categoryName = String.Empty;


            ProductsTableAdapter products = new ProductsTableAdapter();


            MyDalLayer.ProductsDataTable products = products.GetProductsByCategoryId( categoryId,



                                                                                      ref categoryName,

                                                                                      ref numTotalProducts);


            foreach (MyDalLayer.ProductsRow product in products) {


                int productId = product.ProductId;

                string productDescription = product.Description;

                string productImage = product.ProductImage;


    The DAL components generated are strongly typed with both type and data relationship validation built-in.  You can add custom validation and/or code to the generated DAL components very cleanly via either code inheritance (subclass the DAL and override/add your own logic), or by adding a partial type to the project which will be compiled with the DAL (allowing you to avoid having to worry about the designer stomping on your code).  Here is an article that covers the DataSet designer in more detail. 


    In my previous blog sample on efficient data paging using the GridView control, I showed how to optionally build a custom business façade that then wraps our generated DAL layer (which was built using the DataSet designer like above), and in turn provides another layer of isolation and separation.  For this sample, though, I’m just going to use the DAL directly. 


    In terms of building the UI for the sample, the “Site.Master” page defines the outer “chrome” of all pages within the site, as well as the product listing on the left:



    Within the Site.Master master page, I’m using an <asp:repeater> control to dynamically build the list of products and associated links (note the use of the new ASP.NET 2.0 Eval() data-binding syntax that provides a much terser way to evaluate data-binding expressions against the container parent):


        <div id="productmenu">






                <asp:repeater ID="ProductNav" DataSourceID="CategoryDataSource" runat="server">



                            <a href="Products.aspx?categoryid=<%#Eval("CategoryId") %>"><%#Eval("Name")%></a>

                            (<%#Eval("ProductCount") %>)









        <asp:ObjectDataSource ID="CategoryDataSource"



                              runat="server" />


    I’m using declarative data-binding with the new ASP.NET 2.0 ObjectDataSource control to bind the <asp:repeater> against the “MyDalLayerTableAdapters.CategoriesTableAdapter” class and its GetCategories() data method – which is one of the data classes defined and built for us as part of our DataSet designer based DAL.


    The products.aspx page is then based on the Site.Master master page, and contains a <asp:DataList> control as well as some standard hyperlink html elements that we’ll use to programmatically control paging navigation:



    And in source-view:



    A few quick things to point out above: 1) the ItemTemplate within the DataList is using <divs> and CSS for styling and positioning, 2) Eval() supports an optional data-formatting syntax that I’m using to format the price of each product as currency, 3) I’ve disabled view-state for the page (since we don’t need it), and 4) the “tag-navigator” (bottom of the screen-shot) and tag-highlighting features in the free Visual Web Developer html source editor are pretty convenient to identify where your cursor is within the document (they dynamically update as you move the cursor around the html source).


    As you can see above, I’m using declarative data-binding with the ObjectDataSource control for this DataList control as well (alternatively I could have just written procedural code to invoke the ProductAdapter, set the datasource, and call databind on the control).  There are a couple of benefits to doing this the ObjectDataSource way – one is that it handles when to grab the data in the page lifecycle automatically.  Another is that the WYSIWYG page designer will offer to automatically generate default data templates for you within the DataList above when you wire one up to the ObjectDataSource (it will reflect on the data from the returned method and generate a default template based on it that you can then easily edit).  The last is that you can declaratively bind parameter values from other controls, querystring/form values, and the new ASP.NET Profile object – all without having to write any code yourself.  You can see this in action with this last point – where I’m declaratively specifying that the CategoryId and PageIndex values should be pulled from the QueryString (and a value of “0” should be used if it isn’t present). 


    The above markup is actually all we need in our page to bind to our DAL, retrieve 10 rows of data, and generate pretty output containing the results.  If you ran a page with just this, you’d be able to page back and forth through the product listing data by manually adding a “CategoryId” and “PageIndex” value to the querystring of the page (for example: Products.aspx?CategoryId=0&PageIndex=2). 


    Rather than force people to manually do this, though, obviously what we want to-do instead is provide some built-in navigation UI to enable this.  To accomplish this, I added a <div> section at the bottom of the page with some hyperlinks that we’ll use to page back and forth, as well as a label that I can use to output where the user currently is (specifically text that says something like: “1-10 of 56 Products”).


        <div id="Navigation" class="navigation" runat="server">


            <div id="leftnav">


                <a id="PreviousPageNav" runat="server"> &lt;&lt; Previous Products</a>



            <div id="rightnav">


                <a id="NextPageNav" runat="server">More Products &gt;&gt;</a>



            <div id="numnav">

                <asp:Label ID="PagerLocation" runat="server" />





    Note the use of standard hyperlinks above.  They have a runat=”server” attribute on them so that I can program against them on the server.  I chose to implement the paging semantics within this sample using standard HTTP GET requests for everything – instead of using post-backs.  Doing the navigation via post-backs would have been easier, but I wanted to enable users to easily bookmark pages (which will automatically persist the querystring values for me), as well as to enable cross-linking from things like search engines.


    To dynamically update the hyperlink values (as well as other elements of the page), I added an event-handler to the ObjectDataSource so that I’ll be called after it has fetched the data from our DAL (specifically: I’m using the “selected” event – I would have used the “selecting” event if I wanted to inject code immediately before the DAL was called):


        protected void ProductDataSource_Selected(object sender, ObjectDataSourceStatusEventArgs e) {


            // Retrieve output parameter values returned from the "GetProductsByCategoryId"

            // method invoked by the ObjectDataSource control on the ProductsTableAdapter class

            int productCount = (int) e.OutputParameters["CategoryProductCount"];

            string categoryName = (string)e.OutputParameters["CategoryName"];


            // Retrieve pageIndex and categoryId from querystring, pageSize pulled from ObjectDataSource

            int pageIndex = Convert.ToInt32(Request.QueryString["pageIndex"]);

            int categoryId = Convert.ToInt32(Request.QueryString["categoryid"]);

            int pageSize = Int32.Parse(ProductDataSource.SelectParameters["NumRows"].DefaultValue);


            // Update various page elements with data values


            UpdatePagerLocation(pageIndex, pageSize, productCount);

            UpdateNextPrevLinks(categoryId, pageIndex, pageSize, productCount);



    Notice above that I am using the event argument (specifically its OutputParameters collection) to retrieve the output parameter results from the DAL method (e.ReturnValue provides access to the return value of the method). 


    I’m retrieving other HTTP GET parameters from the Request.QueryString collection.  I’m using Convert.ToInt32() to convert them to integers instead of Int32.Parse() because Convert.ToInt32() will return a 0 value instead of throwing if the querystring isn’t specified (and so it saves me having to-do a null check).


    At the end of the event, I then call three helper methods that I’m using the update the page contents with the various data results.  The last two are used to customize the html navigation <div> above.  Specifically, the “UpdatePagerLocation” method emits the location text (“1-10 of 44 Products”):


        void UpdatePagerLocation(int pageIndex, int pageSize, int productCount) {


            int currentStartRow = (pageIndex * pageSize) + 1;

            int currentEndRow = (pageIndex * pageSize) + pageSize;


            if (currentEndRow > productCount)

                currentEndRow = productCount;


            PagerLocation.Text = currentStartRow + "-" + currentEndRow + " of " + productCount + " products";



    And then the “UpdateNextPrevLinks” just updates and auto shows/hides the <a> elements depending on whether we are at the beginning or end of the product listing (note: we are using the CategoryProductCount that was returned as an output parameter to calculate the total number of products in the category):


        void UpdateNextPrevLinks(int categoryId, int pageIndex, int pageSize, int productCount) {


            string navigationFormat = "products.aspx?categoryId={0}&pageIndex={1}";


            PreviousPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex - 1);

            PreviousPageNav.Visible = (pageIndex > 0) ? true : false;


            NextPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex + 1);

            NextPageNav.Visible = (pageIndex + 1) * pageSize < productCount ? true : false;



    Last but not least, I have a simple method that updates the Page’s title element (using the new ASP.NET 2.0 Page.Title property), as well as a <h1> header at the top of the page:


        void UpdateTitles(string title) {


            ProductHeader.Text = title;

            Page.Title = "Products: " + title;



    And that is all the code there is to the sample….


    One thing to play with on the ObjectDataSource is to adjust the “NumRows” parameter value. 


    For example, if you changed this to “4” instead of “10” (which is what the sample ships with), you’d get 4 rows of products per page:



    No additional code changes are required to enable this – just change the value in one place and you are good to go (no DAL, code-behind or other changes needed). 

    You can also experiment with the number of columns rendered by the DataList – try changing the “RepeatColumns” property on it to 1 or 3 to see a different layout.




    Because we only retrieve and return the rows needed for the current page of rendering from the database, and because we only use a single database

    call to retrieve all of the data for the product-listing page, the execution of the page should be very, very fast and scalable (even when you have thousands

    of results).  As you can see above, the code to implement this is pretty small and clean. 


    Because we are using standard navigational HTTP GET requests everywhere in the sample, no client-script is needed or emitted on the page (do a view-source

    in the browser and you’ll notice there isn’t a single line of javascript anywhere on the page).  All markup in the sample is also XHTML compliant and cross-browser.

    I used FireFox for all the screen-shots above – but obviously it also works with IE.


    Hope this helps,



  • GridView: Adding Confirmation before Deleting Items (using the OnClientClick property)

    I stumbled upon this blog post earlier today that I thought was pretty cool.  It describes a pretty easy way to add confirmation pop-ups for delete command (or update or edit or any other command) using a GridView control, or before any other post-back event in ASP.NET.  It takes advantage of the new "OnClientClick" property that button controls now have in ASP.NET 2.0 -- and which allow you to author client-side event handlers that can fire prior to post-back (you return true or false from these javascript handlers to indicate whether you want the post-back to continue).

  • Optimizing C# Refactoring Performance with Web Projects in VS 2005

    One complaint I've heard a lot is the performance of the new C# refactoring feature with web pages in VS 2005.  VS 2005 added support for enabling refactoring everywhere (for example: even within <% %> blocks defined inline with a .aspx page), which is nice from a feature-completeness perspective.  The problem, though, is that by checking every file and possible context, it can end up slowing down performance considerably.  The more pages you have in a project, the slower and slower refactoring gets (to the point of being unusable).

  • Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

    David Hayden blogged about a cool new ROW_NUMBER() function that SQL 2005 provides that got me excited and inspired to write a little code last night.


    You can download the sample app I built here. 


    Features of the Sample


    This sample is a self-contained ASP.NET application that demonstrates a few things:


    - How to implement a data-result search page built with ASP.NET 2.0 and Atlas that can present hundreds of thousands of row results using the ASP.NET 2.0 GridView control.  The results are formatted using a “paging” based UI model – where 15 results per page are displayed, and the user can skip from page to page to see their data.  For kicks I also added support for editing and deleting each individual row.


    - How to optimize the number of rows returned by the SQL Server to the middle-tier web-server.  This sample demonstrates how a large query result (think 1000s, 10s of thousands or 100s of thousands of rows) can be paged efficiently so that only the 15 rows currently on display in a page are ever retrieved by the web-server from the SQL database (this is done using the ROW_NUMBER() function that David describes above, as well as the support for optimized paging provided by the GridView and ObjectDataSource controls).  This avoids your web-server and SQL server grinding to a halt when you execute large queries, and makes for much more scalable performance.


    - How to easily implement Ajax UI support for paging/editing/deleting on top of hundreds of thousands of rows of data (so no full page refreshes – instead it only updates the portion of the page that changes).  This took me only 60 seconds to-do, and uses the same <atlas:updatepanel> control support I talked about in this earlier blog post. 


    - How to easily implement Ajax UI support for adding “auto-suggest” behavior to controls like text-boxes.  The December release of the Atlas Project provides a super-easy server control called the <atlas:autocompleteextender> control that you can point at a TextBox, and that will then call a web-service to provide a list of suggestions when a browser user starts typing in the text-box.  This sample demonstrates how to use this to auto-suggest items based on the contents in the database.


    - How to implement a business class façade/wrapper around a data access layer.  This is in turn used by the new ASP.NET 2.0 ObjectDataSource control for databinding scenarios.  For this simple sample, I could have just used the data access layer built by using File->Add New Item->DataSet from the ObjectDataSource control directly (like I did with my earlier To-do List sample), and basically eliminate the need to write any code in the app.  But I wanted to use this sample to help demonstrate how to build a richer business library layer abstraction that was separate from my data access layer.  The business layer implementation in this sample is pretty trivial (and doesn’t really add much value), but it demonstrates a skeleton of how/where you could easily add business logic rules that were cleanly separated from your data layer.


    How To Run and Use the Application


    To setup the sample, follow the below steps:


    1) Have VS 2005 or Visual Web Developer and SQL Express installed.  Visual Web Developer and SQL Express are both free downloads and provide everything you need to build, debug and run the application.  You can download them from here.


    2) Download this .zip file containing the sample.  Expand it into any directory you want.


    3) Using Visual Web Developer or VS 2005, choose File->Open Web-site and point to the root directory of the sample sub-directory (the one with the files in it).


    4) Select the “PagedData.aspx” file and hit run.


    This will bring up a page that looks like this:



    This allows you to query for lists by categories in your database.  The default database in the sample doesn’t have any list items, so first we’ll want to create some.  To-do this click the “Bulk Add” link.


    Create a category called “test” and add 100 items to it.  This will create a bunch of sample data in the database that will help simulate paging.  Then repeat this with a new category called “test2” and add 10,000 items to it.  Then repeat this with a new category called “test3” and add 100,000 items to it.   Note that this last category will probably take a few minutes to create (since the logic in my app is simple and just adds them one row at a time).



    Then click on the “Return to Paged Data Page” link and start typing a search string in the textbox.  Pause slightly after you type “tes” – and then notice how the auto-suggest box will list the three new categories you added:



    These are being populated using the <atlas:autocompleteextender> server control (which is hitting a web-service on the server to lookup the available categories – and so will return the three new ones we just created).


    Search using “test3” (the one with 100,000 items), and you’ll see this paged view of 100,000 items in the GridView:



    Each page of the screen contains 15 items from the database.  You can switch around to see the different pages.  Click the “…” link to go to pages 11-20.  Click “Last” to jump to the final page of data:



    Note that as you choose the different pages, the only thing that gets updated is the grid of data – the overall page does not refresh.  This is because the GridView control is wrapped using the <atlas:updatepanel> control which allows incremental Ajax refreshes of the page (no code required to accomplish this <g>).


    Page back a few screens, and then click “edit” on one of the rows.  Note that you can now edit and update its items.  You can also click “delete” on a row to remove one.  Both operations occur in an Ajax way:



    How is this Application Built


    Here is what the solution directory looks like in Visual Web Developer:



    It contains two pages – PagedData.aspx and AddData.aspx – that are each based on the Site.master master-page.  It also contains one web-service – categories.asmx – which is used by the <atlas:autocompleteextender> control.


    The database is implemented in PagingSample.mdf and contains 1 simple table called “Lists” (note: you can create new SQL Express databases by select File->Add New Item->Database File):



    ListId is the primary key, and the Category column has been marked to be indexed (note: this is important since we’ll be creating 100,000+ rows).  To set indexes, right click on a column in the table designer and choose “Indexs/Keys” and make sure that “Categories” is being indexed.


    The database has one stored procedure called “GetPagedLists” that was created using the ROW_NUMBER() approach described in David’s blog:



    Note: you can double-click on the sproc name in the solution explorer to open and edit it.


    The data access layer was implemented using a DataSet component (choose File->Add New Item->DataSet to create one). It is defined declaratively within the “MyDataLayer.xsd” file and contains two table-adapters:



    GetPagedListData goes against the SPOC above, but all other methods are normal SQL queries (note: the GetListCountByCategory method is defined as: SELECT COUNT(*) FROM Lists where category=@category and returns a scalar integer value).


    I could have just used the DAL layer directly from the UI tier, but instead chose to wrap the DAL with a business layer façade that looks like this:



    The two significant methods for our paging sample are “GetListCountByCategory” and “GetListsByCategory”.  Here are the simple implementations of them in our business façade layer (note: right now they just thinly wrap the data access layer and expose the row data as custom “ListItem” types – so they don’t provide much value over calling the DAL directly, but do demonstrate how you could add your own custom logic around it):



    Databinding a paged GridView control to this middle-tier object is easy (and automated if you are using the WYSIWYG designer in Visual Web Developer).  Just set the “AllowPaging=true” property on the GridView and set the PageSize value you want (for this sample I am using 15 rows per page).  Then point the GridView at an ObjectDataSource control, which is configured to use the ListManager business façade class.  It has the “AllowPaging” attribute set to true, and has the “SelectMethod” and “SelectCount” properties pointing to the “GetLisByCategory” and “GetListCountByCategory” methods above. 



    Now, when you run the application, the GridView binds against the ObjectDataSource which will invoke the “GetListCountByCategory” method on our “ListManager” business façade class to calculate the total number of rows to page, and then invoke the “GetListByCategory” method passing the appropriate “category” parameter along with the “startRowIndex” and “maximumRows” parameter values that correspond to the page index that the GridView is currently on. 


    The beauty is that because we only retrieve and return the 15 rows of data we actually need from the database as part of this operation, the result is fast and scalable.  As a browser user pages back and forth on the data within the GridView across the 100,000 rows of data, all UI paging logic and update/edit semantics are handled for us.


    The last little implementation note to call out is the use of the <atlas:AutoCompleteExtender> control to auto-suggest items within the Category TextBox.  This is a new control provided by the Atlas December drop, and is simple to implement.  Basically, you just add the control to your page and point at the TextBox you want to complete, along with a web-service URL and method to call when the browser user types three letters in the TextBox -- these are then passed to the web-service, which can provide a list of suggestions back:



    The implementation of the web-service method then looks like this within this sample:



    Hope this helps and proves to be a useful sample.  Thanks again to David for the blog post on the new ROW_NUMBER() function in SQL 2005 that makes this much easier to implement.


    Hope this helps – and happy new year!




    P.S. Note that with SQL 2000 you can use the same approach as above, although the SPROC ends up being slightly more complicated.