/// <summary>
/// Author: Caitlin Abelson
/// Created Date: 2019-02-28
///
/// This is the method for when we are inserting a new setup.
/// </summary>
/// <param name="newSetup"></param>
/// <returns></returns>
public int InsertSetup(Setup newSetup)
{
int result = 0;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_insert_setup";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EventID", newSetup.EventID);
cmd.Parameters.AddWithValue("@DateEntered", newSetup.DateEntered);
cmd.Parameters.AddWithValue("@DateRequired", newSetup.DateRequired);
cmd.Parameters.AddWithValue("@Comments", newSetup.Comments);
try
{
conn.Open();
var tempResult = cmd.ExecuteScalar();
result = Convert.ToInt32(tempResult);
newSetup.SetupID = result;
}
catch (SqlException)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Author Kevin Broskow
/// Created 4/5/201
/// <returns>List<ReceivingTicket></returns>
///
/// Returns a list of all receiving tickets
/// </summary>
public List <ReceivingTicket> selectAllReceivingTickets()
{
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_select_all_receiving";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
_tickets.Add(new ReceivingTicket()
{
ReceivingTicketID = reader.GetInt32(0),
SupplierOrderID = reader.GetInt32(1),
ReceivingTicketExceptions = reader.GetString(2),
ReceivingTicketCreationDate = reader.GetDateTime(3),
Active = reader.GetBoolean(4)
});
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_tickets);
}
/// <summary>
/// Author Kevin Broskow
/// Created 4/5/201
/// <param name="ticket"></param>
///
/// Insert a new receiving ticket
/// </summary>
public void insertReceivingTicket(ReceivingTicket ticket)
{
var cmdText = @"sp_insert_receiving";
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SupplierOrderID", ticket.SupplierOrderID);
cmd.Parameters.AddWithValue("@Description", ticket.ReceivingTicketExceptions);
cmd.Parameters.AddWithValue("@DateDelivered", ticket.ReceivingTicketCreationDate);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Jared Greenfield
/// Created: 2018/01/29
///
/// Adds an Offering record to the database.
/// </summary>
/// <param name="offering">An Offering object to be added to the database.</param>
/// <exception cref="SQLException">Insert Fails (example of exception tag)</exception>
/// <returns>Rows affected.</returns>
public int InsertOffering(Offering offering)
{
int returnedID = 0;
var conn = DBConnection.GetDbConnection();
string cmdText = @"sp_insert_offering";
try
{
SqlCommand cmd1 = new SqlCommand(cmdText, conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@OfferingTypeID", offering.OfferingTypeID);
cmd1.Parameters.AddWithValue("@EmployeeID", offering.EmployeeID);
cmd1.Parameters.AddWithValue("@Description", offering.Description);
cmd1.Parameters.AddWithValue("@Price", offering.Price);
try
{
conn.Open();
var temp = cmd1.ExecuteScalar();
returnedID = Convert.ToInt32(temp);
}
catch (Exception)
{
throw;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(returnedID);
}
/// <summary>
/// Alisa Roehr
/// Created: 2019/02/22
///
/// connect to database to check in a guest from the guest table.
/// </summary>
///
/// <remarks>
///
/// </remarks>
/// <param name="guestID">Guest unique id number</param>
/// <exception cref="SQLException">Select Fails (example of exception tag)</exception>
public void CheckInGuest(int guestID)
{
var conn = DBConnection.GetDbConnection();
var cmdText = "sp_check_in_guest_by_id";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
// parameters
cmd.Parameters.AddWithValue("@GuestID", guestID);
try
{
// open the connection
conn.Open();
// execute the command
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
public int DeactivateItemSupplier(int itemID, int supplierID)
{
int result;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_deactivate_itemsupplier_by_itemid_and_supplierid";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ItemID", itemID);
cmd.Parameters.AddWithValue("@SupplierID", supplierID);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public string Select_Pick_Sheet_Number()
{
string pickSheetNumber = null;
string cmdtext;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_select_next_picksheet_number";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmdtext = cmd.CommandText;
cmd.Parameters.Add("@PickSheetNumber", SqlDbType.NVarChar, 25);
cmd.Parameters["@PickSheetNumber"].Direction = ParameterDirection.Output;
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
pickSheetNumber = cmd.Parameters["@PickSheetNumber"].Value.ToString();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(pickSheetNumber);
}
public int SelectMemberByEmail(string email)
{
int id = 0;
var conn = DBConnection.GetDbConnection();
var procedure = @"sp_select_member_by_email";
var cmd = new SqlCommand(procedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Email", email);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
id = reader.GetInt32(0);
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(id);
}
/// <summary>
/// Author: Ramesh Adhikari
/// Created On: 02/02/2019
/// </summary>
public void DeactivateMember(Member member)
{
if (member.Active == false)
{
throw new Exception("Member is already deactivated");
}
var conn = DBConnection.GetDbConnection();
var procedure = @"sp_deactivate_member";
var cmd = new SqlCommand(procedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MemberID", member.MemberID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Richard Carroll
/// Created: 2019/01/30
///
/// This Method Requests Line data from the database by
/// it's OrderID and returns it to the Logic Layer if Possible.
/// </summary>
public List <VMInternalOrderLine> SelectOrderLinesByID(int orderID)
{
List <VMInternalOrderLine> lines = new List <VMInternalOrderLine>();
var cmdText = "sp_select_order_lines_by_id";
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@InternalOrderID", orderID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var line = new VMInternalOrderLine();
line.ItemID = reader.GetInt32(0);
line.ItemName = reader.GetString(1);
line.OrderQty = reader.GetInt32(2);
line.QtyReceived = reader.GetInt32(3);
lines.Add(line);
}
}
}
catch (Exception)
{
throw;
}
return(lines);
}
/// <summary>
/// Author: Ramesh Adhikari
/// Created On: 01/30/2019
/// </summary>
public void InsertMember(Member newMember)
{
// int rowsAffected = -1;
var conn = DBConnection.GetDbConnection();
var procedure = @"sp_insert_member";
var cmd = new SqlCommand(procedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", newMember.FirstName);
cmd.Parameters.AddWithValue("@LastName", newMember.LastName);
cmd.Parameters.AddWithValue("@PhoneNumber", newMember.PhoneNumber);
cmd.Parameters.AddWithValue("@Email", newMember.Email);
cmd.Parameters.AddWithValue("@Password", newMember.Password);
try
{
conn.Open();
Convert.ToInt32(cmd.ExecuteScalar());
// rowsAffected = Convert.ToInt32(cmd.ExecuteNonQuery());
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return;
}
/// <summary>
/// Method that creates a new department type and stores it in the table
/// </summary>
/// <param name="department">Object holding the data to add to the table</param>
/// <returns> Row Count </returns>
public int InsertDepartment(Department department)
{
int rows = 0;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_insert_department";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DepartmentID", department.DepartmentID);
cmd.Parameters.AddWithValue("@Description", department.Description);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rows);
}
public List <LuggageStatus> RetrieveAllLuggageStatus()
{
List <LuggageStatus> status = new List <LuggageStatus>();
var conn = DBConnection.GetDbConnection();
string cmdText = @"sp_select_all_luggage_status";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
status.Add(new LuggageStatus()
{
LuggageStatusID = reader.GetString(0)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(status);
}
public bool CreateLuggage(Luggage l)
{
bool result = false;
var conn = DBConnection.GetDbConnection();
string cmdText = @"sp_insert_luggage";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@GuestID", SqlDbType.Int);
cmd.Parameters.Add("@LuggageStatusID", SqlDbType.NVarChar, 50);
cmd.Parameters["@GuestID"].Value = l.GuestID;
cmd.Parameters["@LuggageStatusID"].Value = l.Status;
try
{
conn.Open();
result = cmd.ExecuteNonQuery() == 1;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// @Author Craig Barkley
/// @Created 1/23/2019
///
/// Class for the stored procedure data for Event Types
/// </summary>
/// <param name="newEventType"></param>
/// <returns></returns>
//For creating a new Event RequestC:\Users\Craig\Desktop\MillennialResortManager\MillennialResortManager\DataAccessLayer\EventTypeAccessor.cs
public int CreateEventType(EventType newEventType)
{
int rows = 0;
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand("sp_create_event_type", conn);
cmd.CommandType = CommandType.StoredProcedure;
//Parameters for new Event Request
cmd.Parameters.AddWithValue("@EventTypeID", newEventType.EventTypeID);
cmd.Parameters.AddWithValue("@Description", newEventType.Description);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rows);
}
/// <summary>
/// Author: Caitlin Abelson
/// Created Date: 1/27/19
///
/// The InsertEmployee method is for inserting a new employee into our records.
/// </summary>
/// <param name="newEmployee"></param>
/// <returns></returns>
public void InsertEmployee(Employee newEmployee)
{
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_insert_employee";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", newEmployee.FirstName);
cmd.Parameters.AddWithValue("@LastName", newEmployee.LastName);
cmd.Parameters.AddWithValue("@PhoneNumber", newEmployee.PhoneNumber);
cmd.Parameters.AddWithValue("@Email", newEmployee.Email);
cmd.Parameters.AddWithValue("@DepartmentID", newEmployee.DepartmentID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Eric Bostwick
/// Created 2/4/2019
/// Inserts records to the itemsupplier table
/// </summary>
/// <returns>
/// 1 if successful 0 if not
/// </returns>
///
public int InsertItemSupplier(ItemSupplier itemSupplier)
{
int result;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_create_itemsupplier";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ItemID", itemSupplier.ItemID);
cmd.Parameters.AddWithValue("@SupplierID", itemSupplier.SupplierID);
cmd.Parameters.AddWithValue("@PrimarySupplier", itemSupplier.PrimarySupplier);
cmd.Parameters.AddWithValue("@LeadTimeDays", itemSupplier.LeadTimeDays);
cmd.Parameters.AddWithValue("@UnitPrice", itemSupplier.UnitPrice);
cmd.Parameters.AddWithValue("@SupplierItemID", itemSupplier.ItemSupplierID);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Author: Caitlin Abelson
/// Created Date: 2/2/19
///
/// The DeleteEmployee deletes an inactive employee from the system.
/// </summary>
/// <param name="employeeID"></param>
public void DeleteEmployee(int employeeID)
{
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_delete_employee";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmployeeID", employeeID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
public int Delete_TmpPickSheet(string picksheetID)
{ //Deletes all items from a tmpPickSheet -- Used when cancelling a pick
int result;
string cmdtext;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_delete_tmp_picksheet";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmdtext = cmd.CommandText;
cmd.Parameters.Add("@PickSheetID", SqlDbType.NVarChar, 25);
cmd.Parameters["@PickSheetID"].Value = picksheetID;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result / 2);
}
/// <summary>
/// Danielle Russo
/// Created: 2019/02/21
///
/// Populates a list of building status ids
/// </summary>
/// <returns>List of building status ids</returns>
public List <string> SelectAllBuildingStatus()
{
var buildingStatus = new List <string>();
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand("sp_select_all_statusids", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
buildingStatus.Add(reader.GetString(0));
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(buildingStatus);
}
/// <summary>
/// Jared Greenfield
/// Created: 2018/02/09
///
/// Updates an Offering with a new Offering.
/// </summary>
///
/// <param name="oldOffering">The old Offering.</param>
/// <param name="newOffering">The updated Offering.</param>
/// <exception cref="SQLException">Insert Fails (example of exception tag)</exception>
/// <returns>Rows affected.</returns>
public int UpdateOffering(Offering oldOffering, Offering newOffering)
{
int result = 0;
string cmdText = @"sp_update_offering";
var conn = DBConnection.GetDbConnection();
SqlCommand cmd1 = new SqlCommand(cmdText, conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@OfferingID", oldOffering.OfferingID);
cmd1.Parameters.AddWithValue("@OldOfferingTypeID", oldOffering.OfferingTypeID);
cmd1.Parameters.AddWithValue("@OldEmployeeID", oldOffering.EmployeeID);
cmd1.Parameters.AddWithValue("@OldDescription", oldOffering.Description);
cmd1.Parameters.AddWithValue("@OldPrice", oldOffering.Price);
cmd1.Parameters.AddWithValue("@OldActive", oldOffering.Active);
cmd1.Parameters.AddWithValue("@NewOfferingTypeID", newOffering.OfferingTypeID);
cmd1.Parameters.AddWithValue("@NewEmployeeID", newOffering.EmployeeID);
cmd1.Parameters.AddWithValue("@NewDescription", newOffering.Description);
cmd1.Parameters.AddWithValue("@NewPrice", newOffering.Price);
cmd1.Parameters.AddWithValue("@NewActive", newOffering.Active);
try
{
conn.Open();
var temp = cmd1.ExecuteNonQuery();
result = Convert.ToInt32(temp);
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// Danielle Russo
/// Created: 2019/01/21
///
/// Creates a new Building
/// </summary>
///
/// <remarks>
/// Danielle Russo
/// Updated: 2019/02/13
///
/// Added additional parameters (@BuildingName and @Address).
///
/// </remarks>
/// <remarks>
/// Danielle Russo
/// Updated: 2019/02/18
///
/// Added @BuildingStatusID
///
/// </remarks>
/// <param name="newBuilding">The Building obj. to be added.</param>
/// <returns>Rows created</returns>
public int InsertBuilding(Building newBuilding)
{
int rows = 0;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_insert_building";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BuildingID", newBuilding.BuildingID);
cmd.Parameters.AddWithValue("@BuildingName", newBuilding.Name);
cmd.Parameters.AddWithValue("@Address", newBuilding.Address);
cmd.Parameters.AddWithValue("@Description", newBuilding.Description);
cmd.Parameters.AddWithValue("@BuildingStatusID", newBuilding.StatusID);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rows);
}
/// <summary>
/// Alisa Roehr
/// Created: 2019/01/24
///
/// gets all the guest types for filling out things, like the combo boxes.
/// </summary>
///
/// <remarks>
///
/// </remarks>
/// <returns>all guest types</returns>
public List <string> SelectAllGuestTypes()
{
List <string> guestTypes = new List <string>();
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand("sp_retrieve_all_guest_types", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
guestTypes.Add(r.GetString(0));
}
}
r.Close();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(guestTypes);
}
//SelectAllEventTypeID()
/// <summary>
/// Method that Retrieves All Event Types.
/// </summary>
/// <param name="">The ID of the Event Types are retrieved.</param>
/// <returns> eventTypes </returns>
public List <string> SelectAllEventTypeID()
{
var eventTypes = new List <string>();
var conn = DBConnection.GetDbConnection();
var cmd = new SqlCommand("sp_select_event_type_by_id", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var read = cmd.ExecuteReader();
if (read.HasRows)
{
while (read.Read())
{
eventTypes.Add(read.GetString(0));
}
}
read.Close();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(eventTypes);
}
/// <summary>
/// Richard Carroll
/// Created: 2/28/19
///
/// Requests a List of Guest names and Ids from the
/// Database and Returns the Result.
/// </summary>
public List <Guest> SelectGuestNamesAndIds()
{
List <Guest> guests = new List <Guest>();
var conn = DBConnection.GetDbConnection();
var cmdText = "sp_retrieve_guest_names_and_ids";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Guest guest = new Guest
{
FirstName = reader.GetString(0),
LastName = reader.GetString(1),
GuestID = reader.GetInt32(2)
};
guests.Add(guest);
}
}
}
catch (Exception)
{
throw;
}
return(guests);
}
//DeleteEventType(string eventTypeID)
/// <summary>
/// Method that deletes an Event Type and removes it from the table
/// </summary>
/// <param name="eventTypeID">The ID of the Event Type being deleted</param>
/// <returns> Row Count </returns>
public int DeleteEventType(string eventTypeID)
{
int rows = 0;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_delete_event_type_by_id";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EventTypeID", eventTypeID);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rows);
}
/// <summary>
/// Author Kevin Broskow
/// Created 4/5/201
/// <returns>ReceivingTicket</returns>
/// <paramref name="id"/>
/// Returns a specific receiving ticket.
/// </summary>
public ReceivingTicket selectReceivingTicketByID(int id)
{
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_select_receiving";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
_ticket.ReceivingTicketID = reader.GetInt32(0);
_ticket.SupplierOrderID = reader.GetInt32(1);
_ticket.ReceivingTicketExceptions = reader.GetString(2);
_ticket.ReceivingTicketCreationDate = reader.GetDateTime(3);
_ticket.Active = reader.GetBoolean(4);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_ticket);
}
/// <summary>
/// Method for retrieving all event types
/// </summary>
/// <returns></returns>
public static List <string> RetrieveAllEventTypes()
{
List <string> eventTypes = new List <string>();
var conn = DBConnection.GetDbConnection();
var cmdText = "sp_retrieve_all_event_types";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
eventTypes.Add(r.GetString(0));
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(eventTypes);
}
/// <summary>
/// Delete the shop that was passed in.
/// </summary>
/// <param name="shop"></param>
/// <returns>Rows affected</returns>
public int DeleteShop(Shop shop)
{
int result = 0;
var conn = DBConnection.GetDbConnection();
string cmdText = @"sp_delete_shop";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ShopID", shop.ShopID);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Author: Caitlin Abelson
/// Created Date: 2019-02-28
///
///
/// </summary>
/// <param name="newSetup"></param>
/// <param name="oldSetup"></param>
public void UpdateSetup(Setup newSetup, Setup oldSetup)
{
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_update_setup_by_id";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SetupID", oldSetup.SetupID);
cmd.Parameters.AddWithValue("@EventID", newSetup.EventID);
cmd.Parameters.AddWithValue("@DateRequired", newSetup.DateRequired);
cmd.Parameters.AddWithValue("@Comments", newSetup.Comments);
cmd.Parameters.AddWithValue("@OldEventID", oldSetup.EventID);
cmd.Parameters.AddWithValue("@OldDateRequired", oldSetup.DateRequired);
cmd.Parameters.AddWithValue("@OldComments", oldSetup.Comments);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}