Here is what the solution directory looks like in Visual Web
Developer:
Figure 3
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:
Figure 4
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:
Figure 5
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):
Figure 6
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:
Listing 1
int categoryId = 0;
int pageIndex = 0;
int pageSize = 10;
int numTotalProducts = 0;
string categoryName = String.Empty;
ProductsTableAdapter products = newProductsTableAdapter();
MyDalLayer.ProductsDataTable products =
products.GetProductsByCategoryId (categoryId,pageIndex,pageSize,ref categoryName,
ref numTotalProducts);
foreach (MyDalLayer.ProductsRow product inproducts)
{
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:
Figure 7
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):
Listing 2
<div id="productmenu">
<h5>Products</h5>
<ul>
<asp:repeater ID="ProductNav"DataSourceID="CategoryDataSource" runat="server">
<ItemTemplate>
<li>
<a href="Products.aspx?categoryid=<%#Eval("CategoryId") %>"><%#Eval("Name")%></a>
(<%#Eval("ProductCount") %>)
</li>
</ItemTemplate>
</asp:repeater>
</ul>
</div>
<asp:ObjectDataSource ID="CategoryDataSource" TypeName="MyDalLayerTableAdapters.CategoriesTableAdapter"
SelectMethod="GetCategories" 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:
Figure 8
And in source-view:
Figure 9
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”).
Listing 3
<div id="Navigation"class="navigation" runat="server">
<div id="leftnav">
<a id="PreviousPageNav"runat="server"> << Previous Products</a>
</div>
<div id="rightnav">
<a id="NextPageNav"runat="server">More Products >></a>
</div>
<div id="numnav">
<asp:Label ID="PagerLocation"runat="server" />
</div>
</div>
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):
Listing 3
protected void ProductDataSource_Selected(objectsender,
ObjectDataSourceStatusEventArgs e)
{
// Retrieve output parameter values returnedfrom the "GetProductsByCategoryId"
// method invoked by the ObjectDataSourcecontrol on the ProductsTableAdapter class
int productCount =(int)e.OutputParameters["CategoryProductCount"];
string categoryName =(string)e.OutputParameters["CategoryName"];
// Retrieve pageIndex and categoryId fromquerystring, 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
UpdateTitles(categoryName);
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”):
Listing 4
void UpdatePagerLocation(int pageIndex, intpageSize, 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):
Listing 5
void UpdateNextPrevLinks(int categoryId, intpageIndex, 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:
Listing 6
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
Figure 10
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.