/// <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();
            }
        }
Beispiel #4
0
        /// <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);
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        /// <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);
        }
Beispiel #11
0
        /// <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;
        }
Beispiel #12
0
        /// <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);
        }
Beispiel #15
0
        /// <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);
        }
Beispiel #16
0
        /// <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);
        }
Beispiel #18
0
        /// <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);
        }
Beispiel #20
0
        /// <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);
        }
Beispiel #21
0
        /// <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);
        }
Beispiel #22
0
        /// <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);
        }
Beispiel #24
0
        //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);
        }
Beispiel #26
0
        //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);
        }
Beispiel #28
0
        /// <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();
            }
        }