ASP.NET Shopping Cart with Mysql by ODBC Connection Tutorial

ASP.NET Shopping Cart with MySQL by ODBC connection Tutorial

According to the .NET project, here is a sample project for students as a tutorial. Please first look at the following link if you didn’t. This tutorial is based on the following one.

First, create a Schema named “shoppingcart” in your MySQL, here we use the MySQL Workbench, you can use any software to create and edit the MySQL database.

Create four tables named “books”, ”controltable”, ”ordertable”, and “user”

Here are the values for each table to be set up:

Add your own userid and password into the “user” table, there isn’t a user registration function in this tutorial!Try to add more than one user and password to test the session.

Add number “0” as OrderNumber into the “controltable” table. This value is like a unique index which will increase by 1 each time when an item is placed into the shopping cart.

Add the following data into the “books” table:

Create a new blank website by using visual studio.

Add these new items(Default.aspx, shop.aspx, ShoppingCart.aspx) and a new folder named “App_Code”, add a new item named “UserManager.cs” into folder “App_Code”.

web.config

Add the following code into “web.config”

connectionStrings

addname="MySQLConnStr"connectionString="DRIVER={MySQL ODBC 5.1 Driver};Database=ShoppingCart;Server=localhost;UID=root;PWD=root;"/>

<addname="mybooksConnectionString"connectionString="Driver={MySQL ODBC 5.1 Driver};server=localhost;uid=root;pwd=root;database=ShoppingCart;"

providerName="System.Data.Odbc" />

</connectionStrings

Remember to change the “Driver”, “server”, “uid”, “pwd”, “database” to your own configuration according to your MySQL.

Default.aspx

Default.aspx controls the user login section.

Here are the components in the Default.aspx:

Type / ID
Label / Label1
TextBox / Userid
RequiredFieldValidator / rfvUserid
Label / Label2
TextBox / Pwd
RequiredFieldValidator / rfvPwd
Button / LogButton
Label / Msg

Drag them one by one from the Toolbox and change their properties according to the following code:

asp:LabelID="Label1"runat="server"Text="User ID:"</asp:Label

asp:TextBoxID="Userid"runat="server"</asp:TextBox

asp:RequiredFieldValidatorID="rfvUserid"runat="server"

ControlToValidate="Userid"ErrorMessage="Please enter User ID"</asp:RequiredFieldValidator

</div

asp:LabelID="Label2"runat="server"Text="Password:"</asp:Label

asp:TextBoxID="Pwd"runat="server"TextMode="Password"</asp:TextBox

br/>

asp:RequiredFieldValidatorID="rfvPwd"runat="server"ControlToValidate="Pwd"

ErrorMessage="Please enter password"</asp:RequiredFieldValidator

p

asp:ButtonID="LogButton"runat="server"onclick="LogButton_Click"

Text="Login"style="height: 26px"/>

</p

p

asp:LabelID="Msg"runat="server"Text="Label"</asp:Label

</p

Here is the Layout of Default.aspx

Default.aspx.cs

Add the following using statements:

using System.Configuration;

using System.Data.Odbc;

Then, add a public string variable before the Page_Load function:

publicstring strConnection;

Then add the following codes to the function LogButton_Click:

string userid,pwd;

userid = Userid.Text.ToString();

pwd = Pwd.Text.ToString();

string mySel="SELECT count(*) as iCount from user where userid='"+userid+"'";

OdbcConnection myConn = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

myConn.Open();

OdbcCommand myCmd1=newOdbcCommand(mySel,myConn);

OdbcDataReader Dr1;

Dr1=myCmd1.ExecuteReader();

Dr1.Read();

string Count=Dr1["iCount"].ToString();

Dr1.Close();

myCmd1.Connection.Close();

string DrPwd;

if(Count!="0")

{

mySel="SELECT * from user where UserID='"+userid+"'";

OdbcCommand myCmd=newOdbcCommand(mySel,myConn);

myCmd.Connection.Open();

OdbcDataReader Dr;

Dr=myCmd.ExecuteReader();

Dr.Read();

DrPwd=Dr["Password"].ToString();

Dr.Close();

if (DrPwd == pwd)

{

Session["logid"] = userid;

Response.Redirect("shop.aspx");

}

else

Msg.Text = "Wrong password!";

}

else

Msg.Text = "User ID is not exist";

}

UserManager.cs

Here is the code in file UserManager.cs, it is for checking the login session, “UserManager”inheritsfrom “System.Web.UI.Page”, and all the pages except Default.aspx will inherit from “UserManager”, so that to make sure the session for the correct userid is available.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

///<summary>

/// Summary description for UserManager

///</summary>

publicclassUserManager : System.Web.UI.Page

{

public UserManager()

{

this.Load += newEventHandler(UserManager_Load);

}

void UserManager_Load(object sender, EventArgs e)

{

if (Session["logid"] == null)

{

Response.Redirect("~/Default.aspx");

}

}

}

shop.aspx

This page controls the section of search items, and add item to Cart.

Here are the components you need from Toolbox:

Type / ID
TextBox / TextBox1
Button / Button1
GridView / GridView1
SqlDataSource / SqlDataSource1
HyperLink / HyperLink1
Label / Label1

Now, set up the SqlDataSource1 according to the following code:

asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:mybooksConnectionString %>"

ProviderName="<%$ ConnectionStrings:mybooksConnectionString.ProviderName %>"

SelectCommand="SELECT ID, Name, Authors, Publisher, ISBN, Copyright, Price FROM shoppingcart.books"</asp:SqlDataSource

Then, set up GridView1 according to the following codes:

asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"

DataSourceID="SqlDataSource1"OnRowCommand="GridView1_RowCommand">

Columns

asp:ButtonFieldButtonType="Button"CommandName="AddToCart"Text="AddToCart"/>

asp:BoundFieldDataField="ID"HeaderText="ID"InsertVisible="False"

SortExpression="ID"/>

asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

asp:BoundFieldDataField="Authors"HeaderText="Authors"

SortExpression="Authors"/>

asp:BoundFieldDataField="Publisher"HeaderText="Publisher"

SortExpression="Publisher"/>

asp:BoundFieldDataField="ISBN"HeaderText="ISBN"SortExpression="ISBN"/>

asp:BoundFieldDataField="Copyright"HeaderText="Copyright"

SortExpression="Copyright"/>

asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

</Columns

</asp:GridView

Here are the other components:

asp:TextBoxID="TextBox1"runat="server"</asp:TextBox

asp:ButtonID="Button1"runat="server"onclick="Button1_Click"Text="Button"/>

And:

asp:HyperLinkID="HyperLink1"runat="server"NavigateUrl="~/ShoppingCart.aspx">Go to Shopping Cart</asp:HyperLink

br/>

br/>

asp:LabelID="Label1"runat="server"Text="Label"</asp:Label

The layout will be like this:

shop.aspx.cs

First, add the using statements in to shop.aspx.cs

using System.Configuration;

using System.Data.Odbc;

Then, change the partial class shop to inherit the “UserManager”, like this:

publicpartialclassshop : UserManager

Add the following code in to Page_Load function, this will control the user login session:

if (Session["logid"] != null)

{

Response.Write("User Name: "+Session["logid"]);

if (!IsPostBack)

{

}

}

Here are the other functions: Button1_Click, GridView1_RowCommand, AddShopCart, OrderNumber, UpdateOrderNumber,

Based on the knowledge, try to understand the logic, the interaction between shop.aspx and shop.aspx.cs

protectedvoid Button1_Click(object sender, EventArgs e)

{

string res = "SELECT * FROM books WHERE ( Name like '%" + TextBox1.Text.ToString() + "%') Order by ID";

SqlDataSource1.SelectCommand = res;

GridView1.DataSourceID = "SqlDataSource1";

GridView1.DataBind();

}

protectedvoid GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == "AddToCart")

{

int index = Convert.ToInt32(e.CommandArgument);

GridViewRow row = GridView1.Rows[index];

AddShopCart(row.Cells[1].Text.ToString());

}

}

privatevoid AddShopCart(string ItemId)

{

string ord = OrderNumber();

if (ord != "Bad order")

{

int ordernumber = Convert.ToInt32(ord);

ordernumber += 1;

OdbcConnection C_n = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

OdbcCommand cm = newOdbcCommand("Insert INTO OrderTable VALUES('" + ordernumber + "', '" + ItemId + "', '" + "101" + "','" +DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+ "','" + Session["logid"].ToString() + "')", C_n);

Label1.Text += DateTime.Now.ToString();

C_n.Open();

OdbcDataReader dr = cm.ExecuteReader();

C_n.Close();

UpdateOrderNumber(ordernumber);

}

}

privatestring OrderNumber()

{

OdbcConnection Or_Cn = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

OdbcCommand Or_Cm = newOdbcCommand("Select OrderNumber from ControlTable", Or_Cn);

Or_Cn.Open();

OdbcDataReader Or_rd = Or_Cm.ExecuteReader();

if (Or_rd.Read())

{

return Or_rd["OrderNumber"].ToString();

}

else

{

return"Bad order";

}

}

privatevoid UpdateOrderNumber(int UpdatedNumber)

{

OdbcConnection Op_Cn = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

OdbcCommand Op_Cm = newOdbcCommand

("Update ControlTable Set OrderNumber=" + UpdatedNumber, Op_Cn);

Op_Cn.Open();

OdbcDataReader Op_rd = Op_Cm.ExecuteReader();

Op_Cn.Close();

}

ShoppingCart.aspx

This page controls the section of check out and delete from shopping cart.

Here are the components you need from Toolbox:

Type / ID
GridView / GridView1
SqlDataSource / SqlDataSource1
Button / Check_Out_Selected
Button / Check_Out_All
Button / Confirm
Button / DoNotConfirm
Label / Label1
HyperLink / HyperLink1

The properties of SqlDataSource1is like this:

asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:mybooksConnectionString %>"

ProviderName="<%$ ConnectionStrings:mybooksConnectionString.ProviderName %>"

SelectCommand="SELECT b.OrderID,b.Status, a.ID, a.Name, a.Authors, a.Publisher, a.ISBN, a.Copyright, a.Price FROM shoppingcart.books a, OrderTable b WHERE (a.ID = b.ItemId)"</asp:SqlDataSource

The properties of GridView1is like this:

asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"

DataSourceID="SqlDataSource1"OnRowCommand="GridView1_RowCommand"

AllowPaging="True"AllowSorting="True">

Columns

asp:TemplateField

ItemTemplate

asp:CheckBoxID="CheckBox"runat="server" />

</ItemTemplate

</asp:TemplateField

asp:BoundFieldDataField="OrderID"HeaderText="OrderID" InsertVisible="False"

SortExpression="OrderID"/>

asp:BoundFieldDataField="Status"HeaderText="Status"

SortExpression="Status"/>

asp:ButtonFieldCommandName="DeleteFromCart"Text="Delete"/>

asp:BoundFieldDataField="ID"HeaderText="ID"SortExpression="ID"

InsertVisible="False"/>

asp:BoundFieldDataField="Name"HeaderText="Name"

SortExpression="Name"/>

asp:BoundFieldDataField="Authors"HeaderText="Authors"

SortExpression="Authors"/>

asp:BoundFieldDataField="Publisher"HeaderText="Publisher"

SortExpression="Publisher"/>

asp:BoundFieldDataField="ISBN"HeaderText="ISBN"

SortExpression="ISBN"/>

asp:BoundFieldDataField="Copyright"HeaderText="Copyright"

SortExpression="Copyright"/>

asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

</Columns

</asp:GridView

Here are the other properties of the components:

asp:ButtonID="Check_Out_Selected"runat="server"

onclick="Check_Out_Selected_Click"Text="Check Out Selected"/>

br/>

asp:ButtonID="Check_Out_All"runat="server"Text="Check Out All"

onclick="Check_Out_All_Click"/>

br/>

br/>

asp:ButtonID="Confirm"runat="server"Enabled="False"onclick="Confirm_Click"

Text="Confirm the transcation"/>

&nbsp;

asp:ButtonID="DoNotConfirm"runat="server"Enabled="False"

onclick="DoNotConfirm_Click"Text="Do Not Confirm"/>

br/>

Total Price:

asp:LabelID="Label1"runat="server"Text="0"</asp:Label

br/>

asp:HyperLinkID="HyperLink1"runat="server"NavigateUrl="~/shop.aspx">Go to Shop</asp:HyperLink

Here is the layout of ShoppingCart.aspx:

ShoppingCart.aspx.cs

First, add two using statements:

using System.Configuration;

using System.Data.Odbc;

Then change the partial class ShoppingCart to inherit UserManager, like this:

publicpartialclassShoppingCart : UserManager

Add the following codes into Page_Load function:

if (!IsPostBack)

{

pload();

}

Here are the other functions in ShoppingCart.aspx.cs:

protectedvoid GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

try

{

if (e.CommandName == "DeleteFromCart")

{

int index = Convert.ToInt32(e.CommandArgument);

GridViewRow row = GridView1.Rows[index];

DeleteFromCart(row.Cells[1].Text.ToString());

}

}

catch (Exception ex)

{

Response.Write("Please don't click 'Go Back' button on the browser! " + ex.Message);

}

pload();

}

privatevoid DeleteFromCart(string OrderID)

{

string ord = OrderNumber();

if (ord != "Bad order")

{

int ordernumber = Convert.ToInt32(ord);

OdbcConnection C_n = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

OdbcCommand cm = newOdbcCommand("delete from OrderTable where OrderID='" + OrderID + "'", C_n);

C_n.Open();

OdbcDataReader dr = cm.ExecuteReader();

C_n.Close();

}

}

privatestring OrderNumber()

{

OdbcConnection Or_Cn = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString);

OdbcCommand Or_Cm = newOdbcCommand("Select OrderNumber from ControlTable", Or_Cn);

Or_Cn.Open();

OdbcDataReader Or_rd = Or_Cm.ExecuteReader();

if (Or_rd.Read())

{

return Or_rd["OrderNumber"].ToString();

}

else

{

return"Bad order";

}

}

privatevoid pload()

{

string logid = Session["logid"].ToString();

if (Session["logid"] != null)

{

Response.Write("User Name: "+Session["logid"]);

using (OdbcConnection connection = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString))

{

connection.Open();

string res = "Select b.OrderID,b.Status, a.ID, a.Name, a.Authors, a.Publisher, a.ISBN, a.Copyright, a.Price from books as a INNER JOIN OrderTable as b ON (a.ID=b.ItemId) and(b.Status='101') and (b.CustomerID='" + Session["logid"].ToString() + "')";

SqlDataSource1.SelectCommand = res;

GridView1.DataSourceID = "SqlDataSource1";

GridView1.DataBind();

connection.Close();

}

}

}

protectedvoid Check_Out_Selected_Click(object sender, EventArgs e)

{

decimal price = 0;

for (int i = 0; i <= GridView1.Rows.Count - 1; i++)

{

CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox");

if (cbox.Checked == true)

{

price += Convert.ToDecimal(GridView1.Rows[i].Cells[10].Text);

}

}

Label1.Text = price.ToString();

Check_Out_Selected.Enabled = false;

Check_Out_All.Enabled = false;

GridView1.Enabled = false;

Confirm.Enabled = true;

DoNotConfirm.Enabled = true;

price = 0;

}

protectedvoid Check_Out_All_Click(object sender, EventArgs e)

{

decimal price = 0;

for (int i = 0; i <= GridView1.Rows.Count - 1; i++)

{

CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox");

cbox.Checked = true;

price += Convert.ToDecimal(GridView1.Rows[i].Cells[10].Text);

}

Label1.Text = price.ToString();

Check_Out_Selected.Enabled = false;

Check_Out_All.Enabled = false;

GridView1.Enabled = false;

Confirm.Enabled = true;

DoNotConfirm.Enabled = true;

price = 0;

}

protectedvoid Confirm_Click(object sender, EventArgs e)

{

using (OdbcConnection connection = newOdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString))

{

for (int i = 0; i <= GridView1.Rows.Count - 1; i++)

{

CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox");

if (cbox.Checked == true)

{

string sqlstr = "update OrderTable set Status='100' where OrderID='" + GridView1.Rows[i].Cells[1].Text.ToString() + "'";

OdbcCommand Or_Cm = newOdbcCommand(sqlstr, connection);

connection.Open();

OdbcDataReader Or_rd = Or_Cm.ExecuteReader();

connection.Close();

//Label1.Text += "OrderID" + GridView1.Rows[i].Cells[1].Text.ToString();

//SqlDataSource1.SelectCommand = sqlstr;

}

}

connection.Open();

GridView1.DataSourceID = "SqlDataSource1";

GridView1.DataBind();

connection.Close();

pload();

}

Check_Out_Selected.Enabled = true;

Check_Out_All.Enabled = true;

GridView1.Enabled = true;

Confirm.Enabled = false;

DoNotConfirm.Enabled = false;

Label1.Text = "0";

}

protectedvoid DoNotConfirm_Click(object sender, EventArgs e)

{

Check_Out_Selected.Enabled = true;

Check_Out_All.Enabled = true;

GridView1.Enabled = true;

Confirm.Enabled = false;

DoNotConfirm.Enabled = false;

Label1.Text = "0";

}

After doing all these, you can run the website now!

Default.aspx

shop.aspx

ShoppingCart.aspx

Here the value of “Status” in “OrderTable” has two group of values, one is “101”, the other is “100”. You can open the MySql Workbench to check the values in the table of “OrderTable”. “101” means the item is in the shopping cart but not been checked out yet.“100” means the item is already been checked out.