public ExecuteQuery ( string sql, string parameters, string values ) : |
||
sql | string | |
parameters | string | |
values | string | |
Результат |
//table not ready yet protected void btnShipStatus_Click(object sender, EventArgs e) { DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); //eventually going to need something like a 'where shipdate > datetime.now' for pending orders string sql = "select * from orders"; DataSet ds = new DataSet(); string[] s = { }; ds = da.ExecuteQuery(sql, s, s); gvShipStatus.DataSource = ds.Tables[0]; gvShipStatus.DataBind(); //code for tablesorter ready gridviews if (this.gvShipStatus.Rows.Count > 0) { gvShipStatus.UseAccessibleHeader = true; gvShipStatus.HeaderRow.TableSection = TableRowSection.TableHeader; gvShipStatus.FooterRow.TableSection = TableRowSection.TableFooter; } //end s = null; sql = null; }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string sql = "select * from categories;"; DataSet ds = new DataSet(); string[] s = { }; ds = da.ExecuteQuery(sql, s, s); repeater1.DataSource = ds.Tables[0]; repeater1.DataBind(); s = null; sql = null; } }
protected void btnClickMe_Click(object sender, EventArgs e) { DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string sql1 = "Update categories set CategoryName = @catname where categoryid = @catid"; //the parameters must be in the order they appear in the sql above! string[] s1 = { "@catname", "@catid" }; string[] r1 = { txtsearch.Text, "1" }; da.ExecuteNonQuery(sql1, s1, r1); //Rob wrote all of this code.... string sql = "select * from categories where categoryName = @categoryname"; DataSet ds = new DataSet(); string[] s = {"@categoryname"}; string[] r = {txtsearch.Text}; ds = da.ExecuteQuery(sql, s, r); repeater1.DataSource = ds.Tables[0]; repeater1.DataBind(); s = null; sql = null; }
private void GetOrderInfo() { string Orders_OrderDate_Start = txtOrders_OrderDate_Start.Text; string Orders_OrderDate_End = txtOrders_OrderDate_End.Text; string Orders_NetTotal_Start = txtOrders_NetTotal_Start.Text; string Orders_NetTotal_End = txtOrders_NetTotal_End.Text; string Customer_CustomerID = txtCustomer_CustomerID.Text; string Customer_FName = txtCustomer_FName.Text; string Customer_LName = txtCustomer_LName.Text; string Customer_UserName = txtCustomer_UserName.Text; string Customer_City = txtCustomer_City.Text; string Customer_State = txtCustomer_State.Text; string Items_ProductName = txtItems_ProductName.Text; DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); DataSet ds = new DataSet(); string s1; string s2 = ""; int fields = CountUsedFields(); int p; string[] p1 = new string[fields]; string[] v1 = new string[fields]; s1 = "SELECT o.* FROM Orders o " + "RIGHT OUTER JOIN Customer c ON o.CustomerID = c.CustomerID "; if (Items_ProductName != "") { s1 += "RIGHT OUTER JOIN OrderItems oi ON o.ORDERID = oi.OrderID " + "INNER JOIN Items i ON oi.ItemID = i.ItemID AND oi.VendorID = i.VendorID "; } s1 += "WHERE "; if (Orders_OrderDate_Start != "") { s2 += "AND o.OrderDate >= @OrderDate "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@OrderDate"; v1[p] = Orders_OrderDate_Start; } if (Orders_OrderDate_End != "") { s2 += "AND o.OrderDate <= @OrderDate "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@OrderDate"; v1[p] = Orders_OrderDate_End; } if (Orders_NetTotal_Start != "") { s2 += "AND o.NetTotal >= @NetTotal "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@NetTotal, "; v1[p] = Orders_NetTotal_Start + ", "; } if (Orders_NetTotal_End != "") { s2 += "AND o.NetTotal <= @NetTotal "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@NetTotal, "; v1[p] = Orders_NetTotal_End + ", "; } if (Customer_CustomerID != "") { s2 += "AND c.CustomerID = @CustomerID "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@CustomerID, "; v1[p] = Customer_CustomerID + ", "; } if (Customer_FName != "") { s2 += "AND c.FName = @FName "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@FName, "; v1[p] = Customer_FName + ", "; } if (Customer_LName != "") { s2 += "AND c.LName = @LName "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@LName, "; v1[p] = Customer_LName + ", "; } if (Customer_UserName != "") { s2 += "AND c.UserName = @UserName "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@UserName, "; v1[p] = Customer_UserName + ", "; } if (Customer_City != "") { s2 += "AND c.City = @City "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@City, "; v1[p] = Customer_City + ", "; } if (Customer_State != "") { s2 += "AND c.State = @State "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@State, "; v1[p] = Customer_State + ", "; } if (Items_ProductName != "") { s2 += "AND i.ProductName = @ProductName "; p = 0; while (p1[p] != "") { p += 1; } p1[p] = "@ProductName, "; v1[p] = Items_ProductName + ", "; } s2 = s2.TrimStart('A', 'N', 'D', ' '); s1 += s2; ds = da.ExecuteQuery(s1, p1, v1); gvOrders1.DataSource = ds.Tables[0]; gvOrders1.DataBind(); if (this.gvOrders1.Rows.Count > 0) { gvOrders1.UseAccessibleHeader = true; gvOrders1.HeaderRow.TableSection = TableRowSection.TableHeader; gvOrders1.FooterRow.TableSection = TableRowSection.TableFooter; } }
private string txn_id; //unique transaction id #endregion Fields #region Methods protected void Page_Load(object sender, EventArgs e) { //Post back to either sandbox or live string strSandbox = "https://www.sandbox.paypal.com/cgi-bin/webscr"; string strLive = "https://www.paypal.com/cgi-bin/webscr"; HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strSandbox); //Set values for the request back req.Method = "POST"; req.ContentType = "application/x-www-form-urlencoded"; byte[] param = Request.BinaryRead(HttpContext.Current.Request.ContentLength); string strRequest = Encoding.ASCII.GetString(param); strRequest += "&cmd=_notify-validate"; req.ContentLength = strRequest.Length; //for proxy //WebProxy proxy = new WebProxy(new Uri("http://*****:*****@orderID"; DataSet ds6 = new DataSet(); // make arrays for paramaters and input string[] s6 = { "@orderID" }; string[] v6 = { orderID }; ds6 = da6.ExecuteQuery(comm6, s6, v6); // returns a 1 if the item exists if not the transaction is a dummy grossTotal = decimal.Parse(ds6.Tables[0].Rows[0].ItemArray[0].ToString()); // subtract shipping to compare to gross total decimal total = decimal.Parse(grossTotal.ToString("n2")) - decimal.Parse(mc_shipping); //clear s6 = null; v6 = null; // make sure customer paid the correct amount // total < 0 for reversals if (grossTotal.ToString("n2") == total.ToString("n2") || total < 0) { // check to see if email returned is ours if (receiver_email == "*****@*****.**") // make sure the receiver email is ours { // count how many orderIDs that have not been verified exist in the orders table DAL.DataAccess da5 = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); // make command statement string comm5 = "SELECT COUNT(OrderID) FROM Orders WHERE TXNID = @txnID"; DataSet ds5 = new DataSet(); // make arrays for paramaters and input string[] s5 = { "@txnID" }; string[] v5 = { txn_id }; ds5 = da5.ExecuteQuery(comm5, s5, v5); // returns one item txnID = ds5.Tables[0].Rows[0].ItemArray[0]; //clear s5 = null; v5 = null; if (int.Parse(txnID.ToString()) == 0) { if (payment_status == "Completed") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus, Date = @date = @paymentStatus WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Completed", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is pending if (payment_status == "Pending") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Pending", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Processed if (payment_status == "Processed") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Processed", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Refunded // parent_txn_id = old txn_id if (payment_status == "Refunded") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Refunded", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Reversed // parent_txn_id = old txn_id if (payment_status == "Reversed") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Reversed", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Canceled_Reversal // parent_txn_id = old txn_id if (payment_status == "Canceled_Reversal") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Canceled Reversal", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Voided if (payment_status == "Voided") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Voided", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } // if payment status is Denied if (payment_status == "Denied") { // update total of orders table for the customer DAL.DataAccess da2 = new DAL.DataAccess( ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); string comm2 = "UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID"; // empty array string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" }; string[] v2 = { txn_id, "Denied", orderID, datetime.ToString() }; da2.ExecuteNonQuery(comm2, p2, v2); // clear p2 = null; v2 = null; } } //abandon session Session.Abandon(); Session.Clear(); } } } // end of try catch (SqlException) { // nothing } catch (Exception) { // nothing } // string paymentStatus = HttpUtility.UrlDecode(Request.Form["payment_status"].ToString()); } else if (strResponse == "INVALID") { //log for manual investigation } }
// check to see if item is on sale private bool isItemOnSale() { GetItems(); // get the customerID of the user who is logged on DAL.DataAccess da4 = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); // make command statement string comm4 = "SELECT DiscountedPrice FROM Items WHERE ItemID = @itemid"; //"SELECT Count(*) FROM Orders"; //WHERE CustomerID = @customerID AND TXNID = @txnID"; DataSet ds4 = new DataSet(); // make arrays for paramaters and input string[] s4 = { "@itemID" }; string[] v4 = { itemID.Text }; ds4 = da4.ExecuteQuery(comm4, s4, v4); // returns one item object item = ds4.Tables[0].Rows[0].ItemArray[0]; //clear s4 = null; v4 = null; // if the items discounted price // is blank the item is not discounted if (item.ToString() == "") { return false; } return true; }
protected void btnSearch_Click(object sender, EventArgs e) { string s1; string[] p1 = { "@VendorID" }; string[] v1 = { txtVendorID.Text }; DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); DataSet ds = new DataSet(); s1 = "SELECT VendorID,IsActive,VendorName,MainPhone,ContactName,ContactEmail, " + "ContactPhone, Website, Address, Address2, City, State, Zip, Country " + "FROM Vendor WHERE VendorID = @VendorID"; ds = da.ExecuteQuery(s1, p1, v1); cboxIsActive.Checked = Convert.ToBoolean(ds.Tables[0].Rows[0]["IsActive"].ToString()); txtVendorName.Text = ds.Tables[0].Rows[0]["VendorName"].ToString(); txtMainPhone.Text = ds.Tables[0].Rows[0]["MainPhone"].ToString(); txtContactName.Text = ds.Tables[0].Rows[0]["ContactName"].ToString(); txtContactEmail.Text = ds.Tables[0].Rows[0]["ContactEmail"].ToString(); txtContactPhone.Text = ds.Tables[0].Rows[0]["ContactPhone"].ToString(); txtWebsite.Text = ds.Tables[0].Rows[0]["Website"].ToString(); txtAddress.Text = ds.Tables[0].Rows[0]["Address"].ToString(); txtAddress2.Text = ds.Tables[0].Rows[0]["Address2"].ToString(); txtCity.Text = ds.Tables[0].Rows[0]["City"].ToString(); txtState.Text = ds.Tables[0].Rows[0]["State"].ToString(); txtZip.Text = ds.Tables[0].Rows[0]["Zip"].ToString(); txtCountry.Text = ds.Tables[0].Rows[0]["Country"].ToString(); txtVendorID.Enabled = false; cboxIsActive.Enabled = true; txtVendorName.Enabled = true; txtMainPhone.Enabled = true; txtContactName.Enabled = true; txtContactEmail.Enabled = true; txtContactPhone.Enabled = true; txtWebsite.Enabled = true; txtAddress.Enabled = true; txtAddress2.Enabled = true; txtCity.Enabled = true; txtState.Enabled = true; txtZip.Enabled = true; txtCountry.Enabled = true; }
private void BindGridRepeater() { if (System.Web.HttpContext.Current.User.Identity.IsAuthenticated || Session["AnonymousUserName"] != null) { // fill up gridview // instantiate class DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient"); // sql command string comm = "SELECT Orders.OrderID, Orders.CustomerID, OrderItem.ItemID, OrderItem.Price, OrderItem.TotalPrice, OrderItem.Quantity, Items.ItemID, Items.ProductName, Items.Description, Items.PhotoLocation, Items.QuantityAvailable, Items.MinQuantity, Items.VendorID FROM Orders, OrderItem, Items WHERE Orders.OrderID = OrderItem.OrderID and OrderItem.ItemID = Items.ItemID and Orders.CustomerID = @customerID AND Orders.TXNID = @txnID"; // data set DataSet ds = new DataSet(); // empty array string[] p = { "@customerID", "@txnID" }; string[] v = { GetCustomerID(), "" }; ds = da.ExecuteQuery(comm, p, v); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); // clear p = null; v = null; // fill up repeater // instantiate class Order order = new Order(); order.CustomerId = int.Parse(GetCustomerID()); order.TxnId = ""; OrderDA orderDA = new OrderDA(); Collection<Order> getOrder = orderDA.Get(order); rptOne.DataSource = getOrder; rptOne.DataBind(); // clear p = null; v = null; } else { items.InnerHtml = "<h1>" + "Your Shopping Cart is Empty." + "</h1>"; } }