source:
http://www.dotnetbips.com/displayarticle.aspx?id=286
begin:
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.
Create a new web project in VS.NET with C# as the language.Add a web form called ProductCatalog.aspx to itDrag and drop a DataGrid control on it.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();
}
Call this function in the Page_Load event handler
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
BindGrid();
}
}
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.
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
Add another web form to the above project called cart.aspxDrag and drop a DataGrid on the web form.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.
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.
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
Name :
Bipin Joshi
Email :
Profile :
Bipin
Joshi is the webmaster of DotNetBips.com. He is the founder of
BinaryIntellect Consulting (www.binaryintellect.com) - a company
providing training and consulting services on .NET framework. He
conducts intensive training programs in Thane/Mumbai for developers. He
is also a Microsoft MVP (ASP.NET) and a member of ASPInsiders.