private void populateDeliveryBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT name FROM ecommerce.DELIVERY_COMPANY" + " WHERE hasExpressDelivery = @hasExpressDelivery", con); cm1.Parameters.Add("@hasExpressDelivery", SqlDbType.Bit).Value = hasExpressDelivery; SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { deliveryBox.Items.Add(rd1["name"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.PRODUCT_CATEGORY", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["Code"].ToString()); item.SubItems.Add(rd1["Name"].ToString()); item.SubItems.Add(rd1["VAT"].ToString()); Cat_ListView.Items.Add(item); } } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private String getCategoryCode(String category_str) { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("SELECT Code FROM ecommerce.PRODUCT_CATEGORY " + "WHERE Name = @Name", con); cm1.Parameters.Add("@Name", SqlDbType.VarChar).Value = category_str; SqlDataReader rd1 = cm1.ExecuteReader(); rd1.Read(); return(rd1["Code"].ToString()); } FormValidation.showError("Category cannot be empty."); return(""); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); return(""); } finally { con.Close(); } }
private void generateTotalBids() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT COUNT(*) FROM ecommerce.BID WHERE auctionID = @auctionID", con); cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID; int count_bids = (int)cm1.ExecuteScalar(); totalbids_label.Text = count_bids.ToString(); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateBuyerBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT userName FROM ecommerce.REGULAR_USER", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { buyerBox.Items.Add(rd1["userName"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateDeliveryBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT DISTINCT Delivery_Company_Name FROM ecommerce.VIEW_ONGOING_SHIPPING", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { deliveryBox.Items.Add(rd1["Delivery_Company_Name"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateFormDetails() { labelPurchase.Text = purchaseID.ToString(); labelAmount.Text = amount.ToString("F"); SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM " + "ecommerce.VIEW_PENDING_PAYMENTS WHERE purchaseID = @purchaseID", con); cm1.Parameters.Add("@purchaseID", SqlDbType.Int).Value = purchaseID; SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { labelUsername.Text = rd1["Buyer_username"].ToString(); name_lbl.Text = rd1["Buyer_Name"].ToString(); billingBox.Text = rd1["Possible_Billing_Address"].ToString(); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private static void closeAuctions() { while (true) { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("ecommerce.sp_Close_All_Expired_Auctions", con); cm1.CommandType = CommandType.StoredProcedure; cm1.ExecuteNonQuery(); //Console.WriteLine("Executing ecommerce.sp_Close_All_Expired_Auctions"); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } Thread.Sleep(AUCTION_EXPIRE_DELAY); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.INTERNAL_OPERATION", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["operationID"].ToString()); item.SubItems.Add(rd1["commission"].ToString()); item.SubItems.Add(rd1["collectedVAT"].ToString()); item.SubItems.Add(rd1["paymentCode"].ToString()); item.SubItems.Add(rd1["date"].ToString()); InternalOperationLV.Items.Add(item); } } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populatePurchaseBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT purchaseID FROM ecommerce.VIEW_PENDING_PAYMENTS", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { purchaseBox.Items.Add(rd1["purchaseID"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); String usernameSeller = sellerBox.Text; String usernameBuyer = buyerBox.Text; String auctionID_str = auctionBox.Text; if (usernameSeller == "") { usernameSeller = "%"; } if (usernameBuyer == "") { usernameBuyer = "%"; } if (auctionID_str == "") { auctionID_str = "%"; } try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.VIEW_COMPLETED_PURCHASES " + "WHERE Seller_Username LIKE @Seller " + "AND Buyer_Username LIKE @Buyer " + "AND auctionID LIKE @auctionID", con); cm1.Parameters.AddWithValue("@Seller", usernameSeller); cm1.Parameters.AddWithValue("@Buyer", usernameBuyer); cm1.Parameters.AddWithValue("@auctionID", auctionID_str); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["purchaseID"].ToString()); item.SubItems.Add(rd1["auctionID"].ToString()); item.SubItems.Add(rd1["Buyer_Username"].ToString()); item.SubItems.Add(rd1["Seller_Username"].ToString()); item.SubItems.Add(rd1["Purchase_Final_Price"].ToString()); purchCompLV.Items.Add(item); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
public decimal getMinValidBid(int auctionID) { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); String query = "SELECT Min_Valid_Bid " + "FROM ecommerce.VIEW_MIN_VALID_BID " + "WHERE auctionID = @auctionID"; SqlCommand cm1 = new SqlCommand(query, con); cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID; SqlDataReader rd1 = cm1.ExecuteReader(); rd1.Read(); return(Convert.ToDecimal(rd1["Min_Valid_Bid"])); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); return(-1); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.BID WHERE auctionID = @auctionID", con); cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID; SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["bidID"].ToString()); item.SubItems.Add(rd1["Amount"].ToString()); item.SubItems.Add(rd1["userName_Buyer"].ToString()); item.SubItems.Add(rd1["Date"].ToString()); BidLV.Items.Add(item); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private String getSeller(int code) { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT userName_Seller FROM " + "ecommerce.PRODUCT WHERE Code = @Code", con); cm1.Parameters.Add("@Code", SqlDbType.Int).Value = code; SqlDataReader rd1 = cm1.ExecuteReader(); rd1.Read(); return(rd1["userName_Seller"].ToString()); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); return(""); } finally { con.Close(); } }
private void generateTotalCredits() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("SELECT SUM(amount) FROM ecommerce.CREDIT WHERE accountID = @accountID", con); cm1.Parameters.Add("@accountID", SqlDbType.Int).Value = Convert.ToInt32(accID_label.Text); decimal sum_credits = (decimal)cm1.ExecuteScalar(); totalcred.Text = sum_credits.ToString(); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.CREDIT WHERE accountID = @accountID", con); cm1.Parameters.Add("@accountID", SqlDbType.Int).Value = Convert.ToInt32(accID_label.Text); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["creditID"].ToString()); item.SubItems.Add(rd1["amount"].ToString()); item.SubItems.Add(rd1["paymentCode"].ToString()); item.SubItems.Add(rd1["date"].ToString()); CreditlistView.Items.Add(item); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateComboBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT DISTINCT username_Seller FROM ecommerce.VIEW_PRODUCT_DETAILS", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { sellerBox.Items.Add(rd1["username_Seller"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateCategoryBox() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm2 = new SqlCommand("SELECT Name FROM ecommerce.PRODUCT_CATEGORY", con); SqlDataReader rd2 = cm2.ExecuteReader(); while (rd2.Read()) { categoryBox.Items.Add(rd2["Name"].ToString()); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void company_bttn_Click(object sender, EventArgs e) { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT name FROM " + "ecommerce.DELIVERY_COMPANY JOIN ecommerce.SHIPPING " + "ON ecommerce.DELIVERY_COMPANY.name = ecommerce.SHIPPING.deliveryCompany " + "WHERE ecommerce.SHIPPING.Code = @Shipping_Code", con); cm1.Parameters.Add("@Shipping_Code", SqlDbType.Int).Value = shippingCode; SqlDataReader rd1 = cm1.ExecuteReader(); rd1.Read(); String delivery_company_name = rd1["name"].ToString(); Delivery_Comp f = new Delivery_Comp(delivery_company_name); f.Show(); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); if (con.State == ConnectionState.Open) { SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.[USER] AS us JOIN ecommerce.REGULAR_USER AS ru ON us.userName = ru.userName ORDER BY us.userName", con); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["userName"].ToString()); item.SubItems.Add(rd1["Name"].ToString()); item.SubItems.Add(rd1["Email"].ToString()); item.SubItems.Add(rd1["TIN"].ToString()); item.SubItems.Add(rd1["Address"].ToString()); listViewRU.Items.Add(item); } } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); String usernameBuyer = buyerBox.Text; String purchaseID_str = purchaseBox.Text; String paymentCode_str = payBox.Text; if (usernameBuyer == "") { usernameBuyer = "%"; } if (purchaseID_str == "") { purchaseID_str = "%"; } if (paymentCode_str == "") { paymentCode_str = "%"; } try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.VIEW_COMPLETED_PAYMENTS " + "WHERE Buyer_Username LIKE @Buyer " + "AND payment_code LIKE @payment_code " + "AND purchaseID LIKE @purchaseID", con); cm1.Parameters.AddWithValue("@Buyer", usernameBuyer); cm1.Parameters.AddWithValue("@purchaseID", purchaseID_str); cm1.Parameters.AddWithValue("@payment_code", paymentCode_str); SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["payment_code"].ToString()); item.SubItems.Add(rd1["purchaseID"].ToString()); item.SubItems.Add(rd1["Buyer_Username"].ToString()); item.SubItems.Add(rd1["Amount"].ToString()); item.SubItems.Add(rd1["Payment_Date"].ToString()); payCompLV.Items.Add(item); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void submitButton_Click(object sender, EventArgs e) { String delivery_company = deliveryBox.Text; String seller_address = sellerAdBox.Text; String buyer_address = buyerAdBox.Text; DateTime dispatchDate; DateTime estimatedArrivalDate; try { dispatchDate = Convert.ToDateTime(dispatch_date_dtp.Text); estimatedArrivalDate = get_Estimated_Arrival_Date(dispatchDate, hasExpressDelivery); } catch (Exception ex) { FormValidation.showError("The dispatch date has formatting issues."); return; } if (!FormValidation.validateShipping(purchaseID, delivery_company, seller_address, buyer_address, dispatchDate, estimatedArrivalDate)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand("ecommerce.sp_Create_Shipping", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@deliveryCompany", delivery_company); cmd.Parameters.AddWithValue("@dispatchDate", dispatchDate); cmd.Parameters.AddWithValue("@estimatedArrivalDate", estimatedArrivalDate); cmd.Parameters.AddWithValue("@purchaseID", purchaseID); cmd.Parameters.AddWithValue("@dispatch_address", seller_address); cmd.Parameters.AddWithValue("@delivery_address", buyer_address); cmd.ExecuteNonQuery(); MessageBox.Show("You have ordered a new shipping!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } clear_text(); this.Close(); }
private void submitButton_Click(object sender, EventArgs e) { String name = nameLabel.Text; String phone = PhoneBox.Text; String address = AddressBox.Text; Boolean hasExpressDelivery; if (YesButton.Checked) { hasExpressDelivery = true; } else if (NoButton.Checked) { hasExpressDelivery = false; } else { FormValidation.showError("You have to check if the company provides express delivery."); return; } if (!FormValidation.validateDeliveryCompany(name, phone, address, hasExpressDelivery)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Update_DeliveryCompany", con); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@name", name); cmd1.Parameters.AddWithValue("@contactNumber", phone); cmd1.Parameters.AddWithValue("@address", address); cmd1.Parameters.AddWithValue("@hasExpressDelivery", hasExpressDelivery); cmd1.ExecuteNonQuery(); MessageBox.Show("You have updated a delivery company!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } clear_text(); this.Close(); }
private void submitButton_Click(object sender, EventArgs e) { String cc_no = noBox.Text; String cc_cvc = cvcBox.Text; String billing_address = billingBox.Text; DateTime expiry_date; try { expiry_date = Convert.ToDateTime(expiry_date_dtp.Text); } catch (Exception ex) { FormValidation.showError("The expiry date has formatting issues."); return; } if (!FormValidation.validatePayment(purchaseID, amount, billing_address, cc_no, cc_cvc, expiry_date)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand("ecommerce.sp_Create_Payment", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@billingAddress", billing_address); cmd.Parameters.AddWithValue("@creditCardCVC", cc_cvc); cmd.Parameters.AddWithValue("@amount", amount); cmd.Parameters.AddWithValue("@creditCardNo", cc_no); cmd.Parameters.AddWithValue("@creditCardExpiryDate", expiry_date); cmd.Parameters.AddWithValue("@purchaseID", purchaseID); cmd.ExecuteNonQuery(); MessageBox.Show("You have made a new payment!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } clear_text(); this.Close(); }
private void submitButton_Click(object sender, EventArgs e) { decimal debitAmount; try { debitAmount = Convert.ToDecimal(debitBox.Text); } catch (Exception ex) { FormValidation.showError("The debit amount must be a number."); return; } String iban = ibanBox.Text; if (!FormValidation.validateDebit(debitAmount, iban)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); } catch (SqlException ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); return; } try { SqlCommand cmd3 = new SqlCommand("ecommerce.sp_Create_Debit", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@debitAmount", debitAmount); cmd3.Parameters.AddWithValue("@IBAN", iban); cmd3.Parameters.AddWithValue("@accountID", accountID); cmd3.ExecuteNonQuery(); MessageBox.Show("You have performed a new debit!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (SqlException ex) { MessageBox.Show("Failed to make a new debit:\r\n" + ex.Message, "Failed Operation", MessageBoxButtons.OK); } finally { con.Close(); } clear_text(); this.Close(); }
private void populateListView() { SqlConnection con = DbConnectionFactory.newConnection(); String order = getDisplayOrder(); String pcode = "%"; if (product_code > 0) { pcode = product_code.ToString(); } try { con.Open(); String query = "SELECT mvb.auctionID, Product_Code, Product_Name, " + "userName_Winner, Min_Valid_Bid, FinishDate, Seller " + "FROM ecommerce.VIEW_MIN_VALID_BID AS mvb " + "JOIN ecommerce.VIEW_AUCTION_DETAILS AS ad " + "ON mvb.auctionID = ad.auctionID " + "LEFT OUTER JOIN ecommerce.VIEW_WINNING_BID AS wb " + "ON ad.auctionID = wb.auction_ID " + "WHERE ad.Status = 1 AND Product_Code LIKE @Product_Code " + "AND BeginningDate < GETDATE() " + order; SqlCommand cm1 = new SqlCommand(query, con); cm1.Parameters.Add("@Product_Code", SqlDbType.VarChar).Value = pcode; SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { ListViewItem item = new ListViewItem(rd1["auctionID"].ToString()); item.SubItems.Add(rd1["Product_Code"].ToString()); item.SubItems.Add(rd1["Product_Name"].ToString()); item.SubItems.Add(rd1["userName_Winner"].ToString()); item.SubItems.Add(rd1["Min_Valid_Bid"].ToString()); item.SubItems.Add(rd1["FinishDate"].ToString()); item.SubItems.Add(rd1["Seller"].ToString()); OpenAuctionLV.Items.Add(item); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void submitButton_Click(object sender, EventArgs e) { decimal bidAmount; try { bidAmount = Convert.ToDecimal(bidBox.Text); } catch (Exception ex) { FormValidation.showError("The bid amount must be a number."); return; } if (!FormValidation.validateBid(bidAmount)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); // Check if bid is greater or equal to minimum valid bid if (bidAmount < getMinValidBid(auctionID)) { FormValidation.showError("The amount must equal or greater than the minimum valid bid."); return; } SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Create_Bid", con); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@auctionID", auctionID); cmd1.Parameters.AddWithValue("@Amount", bidAmount); cmd1.Parameters.AddWithValue("@userName_Buyer", username_Buyer); cmd1.ExecuteNonQuery(); MessageBox.Show("You have placed a new bid!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } bidBox.Text = ""; this.Close(); }
private void confirmButton_Click(object sender, EventArgs e) { Buyer_Name_Record = buyerNameTxtBox.Text; Seller_Name_Record = sellerNameTxtBox.Text; Buyer_TIN_Record = buyerTINTxtBox.Text; Seller_TIN_Record = sellerTINTxtBox.Text; hasExpressDelivery = expressBttn.Checked ? true : false; finalPrice = getFinalPrice(auctionID, hasExpressDelivery); if (!FormValidation.validatePurchase(finalPrice, VAT_Record, hasExpressDelivery, auctionID, Buyer_Name_Record, Buyer_TIN_Record, Seller_Name_Record, Seller_TIN_Record)) { return; } SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Create_Purchase", con); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@amount", finalPrice); cmd1.Parameters.AddWithValue("@VAT_Record", VAT_Record); cmd1.Parameters.AddWithValue("@hasExpressDelivery", hasExpressDelivery); cmd1.Parameters.AddWithValue("@auctionID", auctionID); cmd1.Parameters.AddWithValue("@Buyer_Name_Record", Buyer_Name_Record); cmd1.Parameters.AddWithValue("@Buyer_TIN_Record", Buyer_TIN_Record); cmd1.Parameters.AddWithValue("@Seller_Name_Record", Seller_Name_Record); cmd1.Parameters.AddWithValue("@Seller_TIN_Record", Seller_TIN_Record); cmd1.ExecuteNonQuery(); MessageBox.Show("You have made a new purchase!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } clearText(); this.Close(); }
private void executeCountQueries() { SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); // Average No Sells Per User SqlCommand cm1 = new SqlCommand("select count(*) / cast((select count(*) from ecommerce.regular_user) as decimal)" + " as 'avg sells per user' " + "from ecommerce.product as pr " + "where pr.[status] = 'Sold'", con); decimal avg_sells = (decimal)cm1.ExecuteScalar(); avgSells.Text = avg_sells.ToString("F"); // Average No Bids Per User SqlCommand cm2 = new SqlCommand("select count(*) / cast((select count(*) from ecommerce.regular_user) as decimal) " + " as 'avg bids per user' " + "from ecommerce.bid", con); decimal avg_bids = (decimal)cm2.ExecuteScalar(); avgBids.Text = avg_bids.ToString("F"); // Average Seller Rating SqlCommand cm3 = new SqlCommand("select avg(cast(avgRating as decimal)) " + "from ecommerce.VIEW_SELLER_RATING", con); decimal avg_rating = (decimal)cm3.ExecuteScalar(); avgRating.Text = avg_rating.ToString("F"); // % of users that made a bid SqlCommand cm4 = new SqlCommand("select count(distinct bid.userName_Buyer) / cast((select count(*) from ecommerce.regular_user) as decimal) " + "as '% of users that made a bid' from ecommerce.bid as bid", con); decimal per_bids = (decimal)cm4.ExecuteScalar(); per_bids_lbl.Text = per_bids.ToString("F"); } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }
private void Purchase_Details_Load(object sender, EventArgs e) { String delivery = ""; SqlConnection con = DbConnectionFactory.newConnection(); try { con.Open(); SqlCommand cm1 = new SqlCommand("SELECT * FROM " + "ecommerce.VIEW_COMPLETED_PURCHASES WHERE purchaseID = @purchaseID", con); cm1.Parameters.Add("@purchaseID", SqlDbType.Int).Value = purchaseID; SqlDataReader rd1 = cm1.ExecuteReader(); while (rd1.Read()) { price_lbl.Text = rd1["Purchase_Final_Price"].ToString(); date_lbl.Text = rd1["Purchase_Date"].ToString(); name_lbl.Text = rd1["Product_Name"].ToString(); code_lbl.Text = rd1["Product_Code"].ToString(); vat_lbl.Text = rd1["VAT_Record"].ToString(); bid_lbl.Text = rd1["winningAmount"].ToString(); auction_lbl.Text = rd1["auctionID"].ToString(); buyer_user_lbl.Text = rd1["Buyer_Username"].ToString(); buyer_name_lbl.Text = rd1["Buyer_Name_Record"].ToString(); buyer_tin_lbl.Text = rd1["Buyer_TIN_Record"].ToString(); seller_user_lbl.Text = rd1["Seller_Username"].ToString(); seller_name_lbl.Text = rd1["Seller_Name_Record"].ToString(); seller_tin_lbl.Text = rd1["Seller_TIN_Record"].ToString(); bool hasExpressDelivery = (bool)rd1["expressDelivery"]; delivery = hasExpressDelivery ? "Yes - 15€" : "No"; delivery_lbl.Text = delivery; purchID_lbl.Text = purchaseID.ToString(); } } catch (Exception ex) { MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK); } finally { con.Close(); } }