Saturday 9 May 2009

Continuing with the shopping cart

In the previous post we updated the system to use the 3.5 version of the framework. Now we are going to continue with that and plug some of the elements together such as the dropdown and the catalog page.

The DropdownDemo.aspx file demonstrated how to make a simple cascading dropdown in .NET. This next part will show how to add that particular functionality to our catalog page so that we can show products from a particular specialisation.

Now I use the naming Category, Sub Category and Specialisation to show the three layers of the cascade. The way that the tables are set up shows how this works

Keys

As you can see, each Sub Category must have a parent Category and each Specialisation must have a Sub Category. The foreign key constraints ensure that this relationship is maintained correctly.

What we need now is a way to connect the products to the Specialisation table and we do this using a link table. The means that every product can be part of one or more specialisations allowing you to have common products appearing in more than one spec.

This table just contains an identity column which serves as the primary key and 2 other integer columns representing the ProductID and the SpecID which are both foreign keys. You could also create a unique constraint on the two columns meaning that a product couldn’t be added to the same specialisation twice

So the database diagram looks something like this

DatabaseLayout

So lets look at adding the cascading dropdowns to the catalog page and see where it leads.

So if we open the source of the DropdownDemo.aspx file we can copy the three dropdowns into our existing catalog.aspx page

<table class="style1">
    <tr>
        <td>
            Category</td>
        <td>
            <asp:DropDownList ID="Category" runat="server" Height="20px" Width="300px"
                AutoPostBack="True">
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            Sub Category</td>
        <td>
            <asp:DropDownList ID="SubCategory" runat="server" Width="300px"
                AutoPostBack="True">
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            Specialisation</td>
        <td>
            <asp:DropDownList ID="Specialisation" runat="server" Width="300px">
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            &nbsp;</td>
    </tr>
</table>

Paste it in just below the label for the shopping cart.

Now we need to wire it up.

So copy the three bind functions from the code behind file and add them to the code behind file of catalog.aspx

private void BindCategory()
      {
          Category.DataSource = CategoryBl.GetAllCategories();
          Category.DataValueField = "Id";
          Category.DataTextField = "Name";
          Category.DataBind();
      }

      private void BindSubCat()
      {
          SubCategory.DataSource = CategoryBl.GetAllSubCats(Int32.Parse(Category.SelectedValue));
          SubCategory.DataValueField = "Id";
          SubCategory.DataTextField = "Name";
          SubCategory.DataBind();
      }

      private void BindSpec()
      {
          Specialisation.DataSource = CategoryBl.GetAllSpecs(Int32.Parse(SubCategory.SelectedValue));
          Specialisation.DataValueField = "Id";
          Specialisation.DataTextField = "Name";
          Specialisation.DataBind();
      }

Next we can copy the event handler methods from the dropdowndemo code behind file to the catalog code behind file.

private void SubCategory_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindSpec();
        }

        private void Category_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindSubCat();
            BindSpec();
        }

And the next bit of copying is adding the event handlers in the OnInit method of the page. So just copy the 2 lines and add them to the same part in the Catalog.aspx.cs file

Category.SelectedIndexChanged += Category_SelectedIndexChanged;
SubCategory.SelectedIndexChanged += SubCategory_SelectedIndexChanged;

Finally lets wire this up for the page load so copy the three lines from the page load event and paste them after the _Catalog.DataBind();

BindCategory();
BindSubCat();
BindSpec();

Right then, compile and check its all working that you are getting your dropdowns to work.

At this stage the dropdowns are fairly useless as they don’t actually filter the data for us.

So we need to add some methods to our DataAccess and Business classes to allow this.

Firstly we need to create a new stored procedure that will return all the product data for a given specialisation id.

CREATEPROCEDURE dbo.usp_select_AllProductsBySpec
(
@SpecID INT
)
AS
SELECT     dbo.Product.Id, dbo.Product.Name, dbo.Product.Description, dbo.Product.Cost
FROM         dbo.Product INNER JOIN
                      dbo.SpecProduct ON dbo.Product.Id = dbo.SpecProduct.ProductID
WHERE     (dbo.SpecProduct.SpecID = @SpecID)

This procedure does a simple join to the products table and returns all the appropriate products.

Now we need to make sure we can retrieve that data from the database with our data layer. So we need to create a function that does that in our ProductDal.cs file

public List<Product> GetAllProducts(int catId)
      {
          SqlParameter[] sqlParameters = {
                                             DbHelper.MakeInParam("SpecID", SqlDbType.Int, 4, catId)
                                         };
          var dataSet = new DbHelper().RunProcDs("usp_select_AllProductsBySpec", sqlParameters);
          return Helpers.Common.CreateList<Product>(dataSet);
      }

'** NOTE: This may already have been in the solution previously so I have tweaked the names. Just make sure before blindly copying and pasting in! **

Now we need a function in our business layer to access this function. That is created in the ProductBl.cs file.

public static List<Product> GetAllProducts(int specID)
       {
           return new ProductDal().GetAllProducts(specID);
       }

Now that we have that, we need to do some more in the catalog.aspx.cs file to make this work.

Firstly we need to change how the existing _Catalog.aspx repeater is bound.

So in the page load event in Catalog.aspx.cs remove the 2 lines that currently bind the repeater

Create a new function that binds the repeater to the SelectedValue of the Specialisation dropdown

private void BindProducts()
       {
           _Catalog.DataSource = ProductBl.GetAllProducts(Int32.Parse(_Specialisation.SelectedValue));
           _Catalog.DataBind();
       }

And we add the function call after the BindSpec() in the BindSpec function

private void BindSpec()
     {
         _Specialisation.DataSource = CategoryBl.GetAllSpecs(Int32.Parse(_SubCategory.SelectedValue));
         _Specialisation.DataValueField = "Id";
         _Specialisation.DataTextField = "Name";
         _Specialisation.DataBind();
         BindProducts();
     }

After this we need to add an event handler for the Specialisation so that it will rebind the _Catalog repeater each time the selection changes

Specialisation.SelectedIndexChanged += _Specialisation_SelectedIndexChanged; // in OnInit

void _Specialisation_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindProducts();
        }

Lastly we need to set the Specialisation dropdown to auto postback so in the page source, add AutoPostBack=”true” so that it looks like this

<asp:DropDownList ID="Specialisation" runat="server" Width="300px" AutoPostBack="true">
                   </asp:DropDownList>

You should be able to compile the page and view it. You will probably not see any data because you will not have populated the link table.

So if you open the table SpecProduct and enter 1 in the ProductID field and 1 in the SpecID field, you should see some information. This assumes that you have a Product with an Id of 1 and also a spec with an Id of 1.

I have created an updated version of the project which also includes sample data and sample links for the first 3 specs.

Spec1

Spec2

Spec3

You can download the solution from here

In case you are wondering I created the sample product data from the AdventureWorks database using the following query

INSERT INTO [ShoppingCartDemo].[dbo].[Product] ([Name],Description,Cost)
SELECT [Name], 'Some Description', ROW_NUMBER() OVER(ORDER BY [Name] ASC)*2 AS Cost FROM AdventureWorks.Production.Product

The ROW_NUMBER() function allowed me to generate sample costs hence why they are all multiples of 2

No comments: