Building a Simple Storefront with LINQ

LINQ is a very promising and very powerful technology that allows various types of datasources to be queried in a unified way via the use of providers, such as LINQ to Objects, LINQ to SQL and LINQ to XML. Using LINQ in a regular Windows application is simple enough. All one has to do is drop a query straight into an assignment and then use the variable appropriately (for example, by looping over it and displaying the results). But what about web applications? How does one hook up LINQ and ASP.NET? And what features does ASP.NET provide to make LINQ usage simple?

The Adventure Works Cycles Sample Database

The Adventure Works Cycles sample database provides an example database with commerce data already in it. However, the full database is a bit too complex for our purposes, so we'll be using the light version of the database, which packs a smaller amount of data into only a handful of tables. This version of the sample database is better suited to explore basic concepts.

If you don't already have the database, it's a free download.

Download the appropriate installer for AdventureWorksLT. Then simply run the installer, which sets up the tables and inserts the data, providing everything that's needed to get started.

Create a new empty web site and add the Adventure Works database:

In the database explorer, you can see the tables provided:

In this article, we'll be using the Product tables. Let's take a quick look at each of these tables, starting with the Product table:

This table is, of course, where the basic details of each product are stored. Each product is assigned a unique ID, and each product is also assigned a ProductCategoryID and ProductModelID. The ProductCategoryID field corresponds to an entry in the ProductCategory table, and the ProductModelID field corresponds to an entry in the ProductMode table. We'll get to these tables in a moment, but take a look at the ThumbNailPhoto field. This stores a picture of the item in binary format. In order to display the image for each product, we'll need to create a page that extracts the binary representation of the picture from the database and then displays it.

ProductCategory Table

Next, take a look at the ProductCategory table, which stores the various categories that products may be arranged in:

The table is simple enough. Each category has a ProductCategoryID field which identifies it, along with a Name field. No surprises. What interests us here is the ParentProductCategoryID field. Top-level categories won't have any value in this field (which is why "Allow Nulls" is checked for this field), but a subcategory will have the ProductCategoryID of its parent category in this field. Our sample database only has two category levels, top-level categories, which only contain other categories, and subcategories, which contain the products, so it won't be too difficult to work with this table.

Recall how the Product table defined a ProductModelID field. Each product can be modeled after a product model. For example, say we have a product model called Mountain-100, representing a mountain bike. From this model, we can have, for example, various colors. A product, then, might be named Mountain-100 Silver, or Mountain-100 Black. Let's look at the ProductModel table:

Each model has a ProductModelID field and a Name field, both serving to identify it, as well as a CatalogDescription field. We won't be using this field. Instead, we'll be using descriptions from the ProductDescription table:

The ProductModelProductDescription table provides a link between the ProductModel table and the ProductDescription table by matching a ProductModelID with a ProductDescriptionID:

The Plan

We're not building anything complex here-only enough to demonstrate LINQ through ASP.NET. So, our plan will be quite simple. We'll create a master page to unify the site's layout. The master page will just feature the title of our site, Adventure Works Cycles. Then, we'll create a home page that displays information about some of the latest items added to the database. However, this information will have to include a picture of the item, so we'll need to create a page that displays a given item's picture.

Next, we'll create a page where a user can browse all of the products in the store. One ListBox will provide a list of top-level categories from which the user can choose, and another ListBox will provide a list of subcategories corresponding to the chosen top-level category. When a subcategory is shown, a list of all the items in that category will be displayed.

However, displaying all of the products in a given category might not be what we want, so after we complete the basic product browse functionality, we'll create an alternative browse page, this time browsing all of the product models in a given category.

Creating the Master Page

First, we need to create a master page for our web site. Go ahead and add a new Master Page item to the project. The default name of MasterPage.master will do just fine. Next, add a stylesheet. Again, the default StyleSheet.css name will work. Replace the default master page content with this:

<%@ Master Language="C#" AutoEventWireup="true" CodeFile=
"MasterPage.master.cs"
 Inherits="MasterPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-
transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

 <title>Adventure Works Cycles</title>

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

</head>

<body>

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

 <div id="header">

 <h1><a id="titleLink" href="Default.aspx">Adventure Works Cycles</a></h1>

 <em>Quality Cycles Since 1903</em>

 </div>

 <div id="main">

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

 </asp:ContentPlaceHolder>

 </div>

 </form>

</body>

</html>

And put the following CSS in the stylesheet:

body 

{

 background-color: #F0FFFF; /* Azure */

 color: Black;

 margin: 0px;

 text-align: center;

 width: 100%;

}

#header

{

 background-color: #87CEEB; /* SkyBlue */

 border-bottom: solid 5px Orange;

 margin: 0px;

}

h1

{

 margin: 0px;

}

#titleLink

{

 color: inherit;

 text-decoration: none;

}

#main

{

 margin-left: auto;

 margin-right: auto;

 width: 70%;

}

We've just created a very basic layout and given it a little style-enough for our purposes.

Displaying Product Pictures

Before we create the home page, we have to create a page that will query the database for a given product's picture and then output that picture to the user. Recall that a binary representation of the picture is stored in the Product table in the ThumbNailPhoto field. Given the ProductID, we need to extract the value of this field and write it out as a bitmap image. This isn't very hard to do.

First, however, we need to set up the proper database classes. Add a new LINQ to SQL classes item to the web site. Name it AdventureWorks.dbml. Then, drag the Product table from the Database Explorer over to the Object Relational Designer (accessed by clicking on AdventureWorks.dbml). You should now see a visual representation of the table:

Now we're ready to get to work. Create a new page called ProductPicture.aspx. Be sure not to select a master page. Of course, what matters in this page is not the markup, but the code in the codebehind file. So, delete everything in ProductPicutre.aspx except for the very first line:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile=
"ProductPicture.aspx.cs" 
Inherits="ProductPicture" %>

C# will be doing the lifting here with the Page_Load method. The simplest way to do what we want is like this:

protected void Page_Load(object sender, EventArgs e)

{

 AdventureWorksDataContext db = new AdventureWorksDataContext();

 var thumbnail = (from p in db.Products

 where p.ProductID == int.Parse(Request.QueryString["id"])

 select p.ThumbNailPhoto).First();

 Response.ContentType = "image/bmp";

Response.BinaryWrite(thumbnail.ToArray());

}

In the above code, we first create an AdventureWorksDataContext object so that we can access the Product table. Then, we run a query, getting the product whose ProductID matches the value of the id in the query string. Note how, at the end of the query, we use First(). This gets the very first result retrieved and returns only that result. Finally, we specify the content type and then write the image out. So, in order to get, for example, the picture associated with a product whose ProductID is 797, we'd access the page like this:

ProductPicture.aspx?id=797

We can further refine the code to only work with a valid ProductID. To do this, we simply need to check that the ProductID is specified in the query string, and then we need to check whether a product with that ProductID does indeed exist:

if (Request.QueryString["id"] != null)

{

 AdventureWorksDataContext db = new AdventureWorksDataContext();

 var thumbnail = (from p in db.Products

 where p.ProductID == int.Parse(Request.QueryString["id"])

 select p.ThumbNailPhoto).FirstOrDefault();

 if (thumbnail != null)

{

 Response.ContentType = "image/bmp";

Response.BinaryWrite(thumbnail.ToArray());

}

}

Accessing the page with an invalid ProductID will now result in a blank page rather than a nasty error message.

Check back next week for the second part of this three-part series!