/// <summary> /// CREATOR: Ryan Morganti /// CREATED: 2020/04/16 /// APPROVER: Derek Taylor /// /// Access layer method to insert new recurring donation records into the database. /// This method will create a new donorID before linking it to the RecurringDonationInfo table. /// Then will make the first donation, tying it to the RecurringDonationInfo in the RecurringDonation table. /// </summary> /// <remarks> /// UPDATER: NA /// UPDATED: NA /// UPDATE: NA /// /// </remarks> public int InsertNewRecurringDonation(RecurringDonationVM donation) { int donorID; int result; int recurringDonationID; int donationID; string firstName = ""; string lastName = ""; var conn = DBConnection.GetConnection(); var cmd = new SqlCommand("sp_select_name_by_username", conn); var cmd2 = new SqlCommand("sp_insert_new_donor", conn); var cmd3 = new SqlCommand("sp_insert_new_recurring_donation_info", conn); var cmd4 = new SqlCommand("sp_insert_new_monetary_donation", conn); var cmd5 = new SqlCommand("sp_insert_new_recurring_donation", conn); cmd.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; cmd3.CommandType = CommandType.StoredProcedure; cmd4.CommandType = CommandType.StoredProcedure; cmd5.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserName", donation.UserName); try { conn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { firstName = reader.GetString(0); lastName = reader.GetString(1); } } reader.Close(); cmd2.Parameters.AddWithValue("@FirstName", firstName); cmd2.Parameters.AddWithValue("@LastName", lastName); donorID = Convert.ToInt32(cmd2.ExecuteScalar()); cmd3.Parameters.AddWithValue("@UserName", donation.UserName); cmd3.Parameters.AddWithValue("@DonorID", donorID); cmd3.Parameters.AddWithValue("@DonationAmount", donation.DonationAmount); cmd3.Parameters.AddWithValue("@Interval", donation.Interval); recurringDonationID = Convert.ToInt32(cmd3.ExecuteScalar()); cmd4.Parameters.AddWithValue("@DonorID", donorID); cmd4.Parameters.AddWithValue("@TypeOfDonation", "Monetary - Recurring"); cmd4.Parameters.AddWithValue("@DonationAmount", donation.DonationAmount); donationID = Convert.ToInt32(cmd4.ExecuteScalar()); cmd5.Parameters.AddWithValue("@RecurringDonationID", recurringDonationID); cmd5.Parameters.AddWithValue("@DonationID", donationID); result = cmd5.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public User SelectUserByEmail(string email) { User user = null; // get SqlConnection object var conn = DBConnection.GetConnection(); // commands var cmd1 = new SqlCommand("sp_select_user_by_email", conn); var cmd2 = new SqlCommand("sp_select_roles_by_employeeid", conn); // command types cmd1.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; // parameters cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 250); cmd2.Parameters.Add("@EmployeeID", SqlDbType.Int); // values for first command, second requires first results cmd1.Parameters["@Email"].Value = email; try { // open the connection conn.Open(); var reader1 = cmd1.ExecuteReader(); user = new User(); user.Email = email; if (reader1.Read()) { //Read gets one row with multiple columns user.EmployeeID = reader1.GetInt32(0); // 0 represents table column user.FirstName = reader1.GetString(1); user.LastName = reader1.GetString(2); user.PhoneNumber = reader1.GetString(3); user.Active = reader1.GetBoolean(4); } else { throw new ApplicationException("User not found."); } reader1.Close(); // now cmd2 needs a parameter value cmd2.Parameters["@EmployeeID"].Value = user.EmployeeID; var reader2 = cmd2.ExecuteReader(); List <string> roles = new List <string>(); while (reader2.Read()) { roles.Add(reader2.GetString(0)); } reader2.Close(); user.Roles = roles; } catch (Exception up) { throw up; } return(user); }
/// <summary> /// Creator: Jaeho Kim /// Created: 03/19/2020 /// Approver: Rasha Mohammed /// /// Implementation for inserting a transaction. /// </summary> /// <remarks> /// Updater: Robert Holmes /// Updated: 04/21/2020 /// Update: Added CustomerEmail and StripeChargeID. /// </remarks> /// <param name="transaction">The transaction that is inserted to the database.</param> /// <returns>rows effected</returns> public int InsertTransaction(Transaction transaction) { var conn = DBConnection.GetConnection(); var cmd = new SqlCommand("sp_insert_transaction", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@TransactionDateTime", transaction.TransactionDateTime); cmd.Parameters.AddWithValue("@TaxRate", transaction.TaxRate); cmd.Parameters.AddWithValue("@SubTotalTaxable", transaction.SubTotalTaxable); cmd.Parameters.AddWithValue("@SubTotal", transaction.SubTotal); cmd.Parameters.AddWithValue("@Total", transaction.Total); cmd.Parameters.AddWithValue("@TransactionTypeID", transaction.TransactionTypeID); cmd.Parameters.AddWithValue("@EmployeeID", transaction.EmployeeID); cmd.Parameters.AddWithValue("@TransactionStatusID", transaction.TransactionStatusID); if (transaction.CustomerEmail != null) { cmd.Parameters.AddWithValue("@CustomerEmail", transaction.CustomerEmail); } else { cmd.Parameters.AddWithValue("@CustomerEmail", DBNull.Value); } if (transaction.StripeChargeID != null) { cmd.Parameters.AddWithValue("@StripeChargeID", transaction.StripeChargeID); } else { cmd.Parameters.AddWithValue("@StripeChargeID", DBNull.Value); } // Tax Exemption if (!String.IsNullOrWhiteSpace(transaction.TaxExemptNumber)) { cmd.Parameters.AddWithValue("@TaxExemptNumber", transaction.TaxExemptNumber); } else { cmd.Parameters.AddWithValue("@TaxExemptNumber", DBNull.Value); } cmd.Parameters.AddWithValue("@ReturnTransactionId", 0); try { conn.Open(); TransactionID = Convert.ToInt32(cmd.ExecuteScalar()); transaction.TransactionID = TransactionID; } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(TransactionID); }
public static Employee GetEmployeeByEmail(string email) { Employee employee = null; var conn = DBConnection.GetDBConnection(); string cmdText1 = @"sp_get_employee_info_by_email"; string cmdText2 = @"sp_get_employee_roles"; var cmd1 = new SqlCommand(cmdText1, conn); var cmd2 = new SqlCommand(cmdText2, conn); // command types cmd1.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; // parameters cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 255); cmd2.Parameters.Add("@Email", SqlDbType.NVarChar, 255); // parameter values cmd1.Parameters["@Email"].Value = email; cmd2.Parameters["@Email"].Value = email; try { int employeeID = 0; string firstName = null; string lastName = null; List <string> roles = new List <string>(); conn.Open(); SqlDataReader theReader = cmd1.ExecuteReader(); if (theReader.HasRows) { theReader.Read(); employeeID = theReader.GetInt32(0); firstName = theReader.GetString(1); lastName = theReader.GetString(2); } else { throw new ApplicationException("Employee not found."); } //close this reader so that other readers can run theReader.Close(); //here is the second reader for the second command execute reader.////////// //run the second reader SqlDataReader theSecondReader = cmd2.ExecuteReader(); if (theSecondReader.HasRows) { while (theSecondReader.Read()) { string role = theSecondReader.GetString(0); //add roles to employee role roles.Add(role); } } theSecondReader.Close(); //group employee attributes into the employee objects //Build the second employee object based on email employee = new Employee(employeeID, firstName, lastName, roles); } catch (Exception) { throw; } finally { conn.Close(); } return(employee); }
public int UpdateProduct(Product oldProduct, Product newProduct) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_update_product", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ProductID", SqlDbType.Int); cmd.Parameters.Add("@NewProductName", SqlDbType.NVarChar, 100); cmd.Parameters.Add("@NewVendor", SqlDbType.NVarChar, 250); cmd.Parameters.Add("@NewProductType", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@NewBuyPrice", SqlDbType.Decimal, 19); cmd.Parameters["@NewBuyPrice"].Precision = 19; cmd.Parameters["@NewBuyPrice"].Scale = 4; cmd.Parameters.Add("@NewSalePrice", SqlDbType.Decimal, 19); cmd.Parameters["@NewSalePrice"].Precision = 19; cmd.Parameters["@NewSalePrice"].Scale = 4; cmd.Parameters.Add("@NewQuantity", SqlDbType.Int); cmd.Parameters.Add("@OldProductName", SqlDbType.NVarChar, 100); cmd.Parameters.Add("@OldVendor", SqlDbType.NVarChar, 250); cmd.Parameters.Add("@OldProductType", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@OldBuyPrice", SqlDbType.Decimal, 19); cmd.Parameters["@OldBuyPrice"].Precision = 19; cmd.Parameters["@OldBuyPrice"].Scale = 4; cmd.Parameters.Add("@OldSalePrice", SqlDbType.Decimal, 19); cmd.Parameters["@OldSalePrice"].Precision = 19; cmd.Parameters["@OldSalePrice"].Scale = 4; cmd.Parameters.Add("@OldQuantity", SqlDbType.Int); cmd.Parameters["@ProductID"].Value = oldProduct.ProductID; cmd.Parameters["@NewProductName"].Value = newProduct.ProductName; cmd.Parameters["@NewVendor"].Value = newProduct.Vendor; cmd.Parameters["@NewProductType"].Value = newProduct.ProductType; cmd.Parameters["@NewBuyPrice"].Value = newProduct.BuyPrice; cmd.Parameters["@NewSalePrice"].Value = newProduct.SalePrice; cmd.Parameters["@NewQuantity"].Value = newProduct.Quantity; cmd.Parameters["@OldProductName"].Value = oldProduct.ProductName; cmd.Parameters["@OldVendor"].Value = oldProduct.Vendor; cmd.Parameters["@OldProductType"].Value = oldProduct.ProductType; cmd.Parameters["@OldBuyPrice"].Value = oldProduct.BuyPrice; cmd.Parameters["@OldSalePrice"].Value = oldProduct.SalePrice; cmd.Parameters["@OldQuantity"].Value = oldProduct.Quantity; try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
/// <summary> /// James Heim /// Created 2019-04-25 /// /// Select a TabLine by the specified ID. /// </summary> /// <param name="memberTabLineID"></param> /// <returns></returns> public MemberTabLine SelectMemberTabLineByID(int memberTabLineID) { MemberTabLine tabLine = null; var conn = DBConnection.GetDbConnection(); var cmdText = @"sp_select_membertabline"; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@MemberTabLineID", memberTabLineID); try { conn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); tabLine = new MemberTabLine() { MemberTabLineID = reader.GetInt32(0), MemberTabID = reader.GetInt32(1), OfferingID = reader.GetInt32(2), Quantity = reader.GetInt32(3), Price = (decimal)reader.GetSqlMoney(4), DatePurchased = reader.GetDateTime(8) }; // Employee ID is nullable. if (reader.IsDBNull(5)) { tabLine.EmployeeID = null; } else { tabLine.EmployeeID = reader.GetInt32(5); } // Discount is nullable. if (reader.IsDBNull(6)) { tabLine.Discount = 0; } else { tabLine.Discount = reader.GetDecimal(6); } // GuestID is nullable. if (reader.IsDBNull(7)) { tabLine.GuestID = null; } else { tabLine.GuestID = reader.GetInt32(7); } } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(tabLine); }
/// <summary> /// Creator: Thomas Dupuy /// Created: 2020/04/12 /// Approver: Michael Thompson /// This method selects an appointment by its id /// </summary> /// <remarks> /// Updater: Mohamed Elamin /// Updated: 2020/04/20 /// Update: I Added param, returns tags for the comments. /// And updated the date format. /// </remarks> /// <param name="id"></param> /// <returns> An appointment by appointment ID</returns> public AppointmentLocationVM SelectAppointmentByID(int id) { AppointmentLocationVM appointment = new AppointmentLocationVM(); var conn = DBConnection.GetConnection(); var cmd1 = new SqlCommand("sp_select_appointment_by_appointment_id"); var cmd2 = new SqlCommand("sp_select_location_by_location_id"); cmd1.Connection = conn; cmd2.Connection = conn; cmd1.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@AppointmentID", id); cmd2.Parameters.Add("@LocationID", SqlDbType.Int); try { conn.Open(); var reader1 = cmd1.ExecuteReader(); if (reader1.Read()) { appointment.AppointmentID = id; appointment.AdoptionApplicationID = reader1.GetInt32(1); appointment.AppointmentTypeID = reader1.GetString(2); appointment.DateTime = reader1.GetDateTime(3); appointment.Notes = reader1.GetString(4); appointment.Decicion = reader1.GetString(5); appointment.LocationID = reader1.GetInt32(6); appointment.Active = reader1.GetBoolean(7); } else { throw new ApplicationException("User not found."); } cmd2.Parameters["@LocationID"].Value = appointment.LocationID; var reader2 = cmd2.ExecuteReader(); if (reader2.Read()) { appointment.LocationName = reader2.GetString(1); appointment.LocationAddress1 = reader2.GetString(2); appointment.LocationAddress2 = reader2.GetString(3); appointment.LocationCity = reader2.GetString(4); appointment.LocationState = reader2.GetString(5); appointment.LocationZip = reader2.GetString(6); } else { throw new ApplicationException("Location not found."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(appointment); }
public Collector RetrieveCollectorByUsername(string username) { Collector collector = null; // connection var conn = DBConnection.GetDBConnection(); // command text var cmdText = @"sp_retrieve_collector_by_email"; // command var cmd = new SqlCommand(cmdText, conn); // Command type cmd.CommandType = CommandType.StoredProcedure; // parameters cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100); // parameter value cmd.Parameters["@Email"].Value = username; // try-catch for command execution try { // open connection conn.Open(); // execute the command var reader = cmd.ExecuteReader(); // Process the results (only one result, so no while loop here) if (reader.HasRows) // HasRows is boolean. set to true if reader has rows... { reader.Read(); // read the next line in the result // create a new user object collector = new Collector() { CollectorID = reader.GetString(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), PhoneNumber = reader.GetString(3), Email = reader.GetString(4), Active = reader.GetBoolean(5) }; if (collector.Active != true) { throw new ApplicationException("Not an active collector."); } } else { throw new ApplicationException("Collector record not found."); } } catch (Exception) { throw; } finally { conn.Close(); } return(collector); }