private static void InsertEmployee( ref int ID, string Name, string Title, string Address, decimal Salary, DateTime JoinedDate, byte?Children) { try { string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp]("; str += "@ID int OUTPUT," + "@Name nvarchar(30),"; str += "@Title varchar(20)," + "@Address varchar(30),"; str += "@Salary money," + "@JoinedDate datetime,"; str += "@Children tinyint)"; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; Output outputID = new Output(ID); caller.CallVoidProc("[dbo].[sp_InsertEmp]", outputID, Name, Title, Address, Salary, JoinedDate, Children); ID = outputID.GetInt(); } catch (System.Exception ex) { Debug.Print(ex.Message); } }
private static DataSet GetJoinedDate(int StaffID, ref DateTime dt) { DataSet ds = null; try { string str = "CREATE PROCEDURE [dbo].[sp_GetJoinedDate](@StaffID int,@JoinedDate datetime output)"; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; Output outputDT = new Output(dt); ds = caller.CallDataSetProc("[dbo].[sp_GetJoinedDate]", StaffID, outputDT); dt = outputDT.GetDateTime(); } catch (System.Exception ex) { Debug.Print(ex.Message); } return(ds); }
//Checkout Button (only visible if gridview has data) protected void btnCheckout_Click(object sender, EventArgs e) { shoppingCart = (ArrayList)Session["ShoppingCart"]; JavaScriptSerializer js = new JavaScriptSerializer(); APICalls api = new APICalls(); SPCaller spc = new SPCaller(); Customer c = new Customer(); c.Email = Session["Username"].ToString(); c.CustomerID = spc.GetCustomerIDByEmail(c.Email); //Adding each product into the database foreach (Product p in shoppingCart) { //String jsonCheckout = js.Serialize(p); try { bool data = api.RecordPurchase(url, p.ID.ToString(), p.Quantity, "1", "0", DateTime.Now.ToString(), DateTime.Now.TimeOfDay.ToString(), c); if (data == true) { Response.Redirect("Confirmation.aspx", false); } else { lblMessage.Text = "A problem occurred while checking out."; } } catch (Exception ex) { lblMessage.Text = "Error: " + ex.Message; } } }
protected void Session_End(object sender, EventArgs e) { if (Session["ShoppingCart"] != null) { ArrayList cart = (ArrayList)Session["ShoppingCart"]; BinaryFormatter serializer = new BinaryFormatter(); MemoryStream memStream = new MemoryStream(); serializer.Serialize(memStream, cart); byte[] cartBytes = memStream.ToArray(); SPCaller spc = new SPCaller(); DBConnect objDB = new DBConnect(); SqlCommand objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.CommandText = "TP_StoreCart"; int custID = spc.GetCustomerIDByEmail(Session["Username"].ToString()); objCommand.Parameters.AddWithValue("@CustomerID", custID); objCommand.Parameters.AddWithValue("@Cart", cartBytes); objDB.DoUpdateUsingCmdObj(objCommand); } if (Session["WishList"] != null) { ArrayList wishList = (ArrayList)Session["WishList"]; BinaryFormatter serializer = new BinaryFormatter(); MemoryStream memStream = new MemoryStream(); serializer.Serialize(memStream, wishList); byte[] wlBytes = memStream.ToArray(); SPCaller spc = new SPCaller(); DBConnect objDB = new DBConnect(); SqlCommand objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.CommandText = "TP_StoreWishList"; int custID = spc.GetCustomerIDByEmail(Session["Username"].ToString()); objCommand.Parameters.AddWithValue("@CustomerID", custID); objCommand.Parameters.AddWithValue("@WishList", wlBytes); objDB.DoUpdateUsingCmdObj(objCommand); } }
public static void InsertEmployee( ref int ID, string Name, string Title, string Address, decimal Salary, DateTime JoinedDate, byte?Children) { try { string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp]("; str += "@ID int OUTPUT," + "@Name nvarchar(30),"; str += "@Title varchar(20)," + "@Address varchar(30),"; str += "@Salary money," + "@JoinedDate datetime,"; str += "@Children tinyint)"; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); string location = System.Reflection.Assembly.GetExecutingAssembly().Location; string dir = System.IO.Path.GetDirectoryName(location); string xmlPath = System.IO.Path.Combine(dir, "signature.xml"); signature.Save(xmlPath); SPSignature signature2 = new SPSignature(); signature2.Load(xmlPath); SPCaller caller = new SPCaller(signature2); caller.ConnectionStr = ConnectionStr; Output outputID = new Output(ID); caller.CallVoidProc("[dbo].[sp_InsertEmp]", outputID, Name, Title, Address, Salary, JoinedDate, Children); ID = outputID.GetInt(); } catch (System.Exception ex) { Debug.Print(ex.Message); } }
private static int GetNum() { int n = -1; try { string str = "CREATE PROCEDURE [dbo].[sp_GetNum] "; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; n = caller.CallIntProc("[dbo].[sp_GetNum]"); } catch (System.Exception ex) { Debug.Print(ex.Message); } return(n); }
private static DataSet GetAllEmployee() { DataSet ds = null; try { string str = "CREATE PROCEDURE [dbo].[sp_GetAllEmployee]"; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; ds = caller.CallDataSetProc("[dbo].[sp_GetAllEmployee]"); } catch (System.Exception ex) { Debug.Print(ex.Message); } return(ds); }
private static string GetChildrenCode(int StaffID) { string code = null; try { string str = "CREATE PROCEDURE [dbo].[sp_GetChildren](@StaffID int )"; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; code = caller.GenDataSetProcCode("[dbo].[sp_GetChildren]", StaffID); } catch (System.Exception ex) { Debug.Print(ex.Message); } return(code); }
private static string GetNumCode() { string code = null; try { string str = "CREATE PROCEDURE [dbo].[sp_GetNum] "; // Parse the stored procedure signature SPSignature signature = new SPSignature(str); SPCaller caller = new SPCaller(signature); caller.ConnectionStr = ConnectionStr; code = caller.GenIntProcCode("[dbo].[sp_GetNum]"); } catch (System.Exception ex) { Debug.Print(ex.Message); } return(code); }
protected void btnEmpty_Click(object sender, EventArgs e) { gvCart.DataSource = new DataTable(); gvCart.DataBind(); Session.Remove("WishList"); Response.Redirect("EmptyWishList.aspx", false); DBConnect objDB = new DBConnect(); SPCaller spc = new SPCaller(); SqlCommand objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.CommandText = "TP_EmptyWishList"; int custID = spc.GetCustomerIDByEmail(Session["Username"].ToString()); objCommand.Parameters.AddWithValue("@CustomerID", custID); objCommand.Parameters.Add("@New", SqlDbType.VarBinary, -1); objCommand.Parameters["@New"].Value = DBNull.Value; objDB.DoUpdateUsingCmdObj(objCommand); }
protected void btnLogin_Click(object sender, EventArgs e) { string username; string password; username = txtEmail.Text; password = txtPassword.Text; DBConnect db = new DBConnect(); DataSet ds = new DataSet(); SqlCommand objcommand = new SqlCommand(); //if dropdown selection is Customer if (ddlLoginType.Text == "Customer") { objcommand.CommandType = CommandType.StoredProcedure; objcommand.CommandText = "TP_CustomerLogin"; objcommand.Parameters.AddWithValue("@username", username); objcommand.Parameters.AddWithValue("@password", password); ds = db.GetDataSetUsingCmdObj(objcommand); if (ds.Tables[0].Rows.Count == 0) { lblLoginErrorMessage.Text = "Incorrect username or password"; } else { if (chkbxRememberMe.Checked == true) { Response.Cookies["Username"].Value = txtEmail.Text; Response.Cookies["Password"].Value = txtPassword.Text; Response.Cookies["Username"].Expires = DateTime.Now.AddDays(15); Response.Cookies["Password"].Expires = DateTime.Now.AddDays(15); } else { Response.Cookies["Username"].Expires = DateTime.Now.AddDays(-1); Response.Cookies["Password"].Expires = DateTime.Now.AddDays(-1); } Session["AccountType"] = 0; Session["Username"] = username; Session["Password"] = password; //check if customer already has cart in db SPCaller spc = new SPCaller(); DBConnect objDB = new DBConnect(); SqlCommand objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.CommandText = "TP_GetCart"; int custID = spc.GetCustomerIDByEmail(Session["Username"].ToString()); objCommand.Parameters.AddWithValue("@CustomerID", custID); DataSet myDS = objDB.GetDataSetUsingCmdObj(objCommand); if (myDS.Tables[0].Rows[0][0] != System.DBNull.Value) { byte[] cartBytes = (byte[])myDS.Tables[0].Rows[0][0]; BinaryFormatter deserializer = new BinaryFormatter(); MemoryStream ms = new MemoryStream(cartBytes); ArrayList shoppingCart = (ArrayList)deserializer.Deserialize(ms); Session["ShoppingCart"] = shoppingCart; } objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.CommandText = "TP_GetWishList"; objCommand.Parameters.AddWithValue("@CustomerID", custID); myDS = objDB.GetDataSetUsingCmdObj(objCommand); if (myDS.Tables[0].Rows[0][0] != System.DBNull.Value) { byte[] wlBytes = (byte[])myDS.Tables[0].Rows[0][0]; BinaryFormatter deserializer = new BinaryFormatter(); MemoryStream ms = new MemoryStream(wlBytes); ArrayList wishList = (ArrayList)deserializer.Deserialize(ms); Session["WishList"] = wishList; } Response.Redirect("CustomerHome.aspx"); } } //if dropdown selection is Merchant if (ddlLoginType.Text == "Merchant") { objcommand.CommandType = CommandType.StoredProcedure; objcommand.CommandText = "TP_MerchantLogin"; objcommand.Parameters.AddWithValue("@username", username); objcommand.Parameters.AddWithValue("@password", password); ds = db.GetDataSetUsingCmdObj(objcommand); if (ds.Tables[0].Rows.Count == 0) { lblLoginErrorMessage.Text = "Incorrect username or password"; } else { if (chkbxRememberMe.Checked == true) { Response.Cookies["Username"].Value = txtEmail.Text; Response.Cookies["Password"].Value = txtPassword.Text; Response.Cookies["Username"].Expires = DateTime.Now.AddDays(15); Response.Cookies["Password"].Expires = DateTime.Now.AddDays(15); } else { Response.Cookies["Username"].Expires = DateTime.Now.AddDays(-1); Response.Cookies["Password"].Expires = DateTime.Now.AddDays(-1); } Session["AccountType"] = 1; Session["Username"] = username; Session["Password"] = password; Response.Redirect("MerchantHome.aspx", false); } } }