Download Source Code

Developing a Shopping Cart - Part 3

Introduction

In the previous article we saw how to create a shopping cart using session variables. Continuing the concept further this article will illustrate how to use store your shopping cart in a database. This technique is more robust and scalable that the previous two techniques.

Creating a database table

Before you proceed with any coding, you need to create the following table in the Northwind database of SQL Server.

CREATE TABLE [dbo].[ShoppingCart_Products] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50),
[ProductID] [int] NULL,
[ProductName] [varchar] (255),
[UnitPrice] [money] NULL ,
[Quantity] [int] NULL 
) ON [PRIMARY]

Developing a simple product listing page

We will first build a simple web form that lists Products table of Northwind database in a DataGrid.

1. Create a new web project in VS.NET with C# as the language.
2. Add a web form called ProductCatalog.aspx to it
3. Drag and drop a DataGrid control on it.
4. Write a function called BindGrid() as shown below:

private void BindGrid()
{
SqlDataAdapter da=
new SqlDataAdapter
("select * from products",
@"data source=.\vsdotnet;initial catalog=northwind;user id=sa");
DataSet ds=new DataSet();
da.Fill(ds,"products");
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
}

5. Call this function in the Page_Load event handler

private void Page_Load(object sender, System.EventArgs e)
{
	if(!Page.IsPostBack)
	{
		BindGrid();
	}
}

6. Write following code in the SelectedIndexChanged event of the DataGrid.

private void DataGrid1_SelectedIndexChanged
(object sender, System.EventArgs e)
{
CShoppingCartItem item=new CShoppingCartItem();
item.ProductID=
int.Parse(DataGrid1.SelectedItem.Cells[1].Text);
item.ProductName=
DataGrid1.SelectedItem.Cells[2].Text;
item.UnitPrice=
decimal.Parse(DataGrid1.SelectedItem.Cells[3].Text);
item.Quantity=1;
CShoppingCart.AddItem(Session.SessionID,item);
}

Here, create an instance of CSHoppingCartItem class and set its properties. We then call AddMethod of CShoppingCart class. Both of these classes are explained in the following text.

7. Drag and drop a button control on the web form and write following code in the it's click event handler.

private void Button1_Click
(object sender, System.EventArgs e)
{
Response.Redirect("cart.aspx");
}

Here, we are simply navigating to the cart.aspx page which displays the shopping cart.

The CShoppingCartItem class

This class represents a single item of the shopping cart and looks as shown below:

public class CShoppingCartItem
{
private int intProductID;
private string strProductName;
private decimal decUnitPrice;
private int intQuantity;

public int ProductID
{
	get
	{
		return intProductID;
	}
	set
	{
		intProductID=value;
	}
}
public string ProductName
{
	get
	{
		return strProductName;
	}
	set
	{
		strProductName=value;
	}
}
public decimal UnitPrice
{
	get
	{
		return decUnitPrice;
	}
	set
	{
		decUnitPrice=value;
	}
}
public int Quantity
{
	get
	{
		return intQuantity;
	}
	set
	{
		intQuantity=value;
	}
}
}

The CShoppingCart class

This is the most important class in our application because it actually performs the job of storing or retrieving shopping cart items into a database table. It consists of static methods and looks as shown below:

public class CShoppingCart
{
private static string connstr=
@"data source=.\vsdotnet;initial 
catalog=northwind;user id=sa";

public static 
void AddItem(string cartid,CShoppingCartItem item)
{
SqlConnection cnn=new SqlConnection(connstr);
SqlCommand cmd=new SqlCommand();
cmd.Connection=cnn;
cmd.CommandText=
"insert into ShoppingCart_Products(cartid,productid,
productname,unitprice,quantity) 
values(@cartid,@prodid,@prodname,@unitprice,@qty)";

SqlParameter p1=new SqlParameter("@cartid",cartid);
SqlParameter p2=new 
SqlParameter("@prodid",item.ProductID);
SqlParameter p3=new 
SqlParameter("@prodname",item.ProductName);
SqlParameter p4=new 
SqlParameter("@unitprice",item.UnitPrice);
SqlParameter p5=new 
SqlParameter("@qty",item.Quantity);

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);

cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

}
public static 
void UpdateQuantity
(string cartid,int productid,int newqty)
{
SqlConnection cnn=new SqlConnection(connstr);
SqlCommand cmd=new SqlCommand();
cmd.Connection=cnn;
cmd.CommandText=
"update ShoppingCart_Products 
set quantity=@qty where cartid=@cartid 
and productid=@prodid";

SqlParameter p1=new SqlParameter("@qty",newqty);
SqlParameter p2=new SqlParameter("@cartid",cartid);
SqlParameter p3=new SqlParameter("@prodid",productid);

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);

cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

}

public static void DeleteItem(string cartid,int productid)
{
SqlConnection cnn=new SqlConnection(connstr);
SqlCommand cmd=new SqlCommand();
cmd.Connection=cnn;
cmd.CommandText=
"delete from ShoppingCart_Products 
where cartid=@cartid and productid=@prodid";

SqlParameter p1=new SqlParameter("@cartid",cartid);
SqlParameter p2=new SqlParameter("@prodid",productid);

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

cnn.Open();

cmd.ExecuteNonQuery();

cnn.Close();
}

public static DataSet GetAll(string cartid)
{
SqlDataAdapter da=new SqlDataAdapter
("select * from ShoppingCart_Products 
where cartid='" + cartid + "'",connstr);
DataSet ds=new DataSet();
da.Fill(ds,"shoppingcart"); 
return ds;
}
}

This class uses SqlConnection, SqlCommand and SqlDataReader classes to perform various tasks such as INSERT, UPDATE and SELECT.

Creating the shopping cart web form

1. Add another web form to the above project called cart.aspx
2. Drag and drop a DataGrid on the web form.
3. Create a function called FillCartFromDb() as shown below:

private void FillCartFromDb()
{
DataSet ds=CShoppingCart.GetAll(Session.SessionID);
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
Button1_Click(null,null);
}

Here, we call the GetAll method of the CShoppingCart class which returns a DataSet. This DataSet acts as a datasource for the DataGrid control.

4. Drag and drop a button called Recalculate and write following code to its click event handler

private void Button1_Click
(object sender, System.EventArgs e)
{
decimal total=0;
try
{
foreach(DataGridItem dgi in DataGrid1.Items)
{
if(dgi.ItemType==ListItemType.Item 
|| dgi.ItemType==ListItemType.AlternatingItem)
{
TextBox t=(TextBox)dgi.Cells[3].Controls[1];
int quantity=int.Parse(t.Text);
decimal unitprice=Decimal.Parse(dgi.Cells[2].Text);
total=total + (unitprice * quantity);
CShoppingCart.UpdateQuantity
(Session.SessionID,int.Parse(dgi.Cells[0].Text),quantity);
}
}
}
catch
{
}
lblAmt.Text=total.ToString();
}

This code calculates the total amount of the items selected based on the quantity entered and displays it in a label.

5. Finally, we will write code to delete items from the cart.

private void DataGrid1_DeleteCommand
(object source, 
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
CShoppingCart.DeleteItem
(Session.SessionID,int.Parse(e.Item.Cells[0].Text));
FillCartFromDb();
}

Here, we simply simply delete a particular product by calling the DeleteItem method of CShoppingCart class.

Code Download

The complete working example is available for download. Please see the link at the top of the article.

Summary

In this article we saw how to use SQL Server database to store a shopping cart. This approach though requires more coding is  recommended for big sites. Since you are storing the data in a SQL server database, you are not putting any overhead on the web server (as against Session variables). Also, this approach is better than cookies because you are not dependent of client browser supporting cookies. In terms of performance this approach will however be slower than the other two techniques. However, overall it is more robust and scalable than cookies or sessions. In the next article I will illustrate how to create a single wrapper to all he three approaches so that without any code change you can switch between these three techniques.

About the author