/// <summary> /// Will attempt to retrive customers ID from all fields in customer. /// </summary> /// <param name="pCustomer"></param> /// <returns></returns> public static int SearchForCustomerID(Customer pCustomer) { SqlCommand SqlCmd2 = new SqlCommand("SELECT TOP 1 [CustomerID] FROM " + Global.DBConfig["CustomerTN"] + HelperMethods.BuildSQLConditionsWithParams(new SortedList <string, string>() { { "Name", pCustomer.Name }, { "HouseNumber", Convert.ToString(pCustomer.HouseNumber) }, { "HouseName", pCustomer.HouseName }, { "Street", pCustomer.Street }, { "Postcode", pCustomer.Postcode } })); SqlCmd2.Parameters.Add(new SqlParameter("@pName", pCustomer.Name)); SqlCmd2.Parameters.Add(new SqlParameter("@pHouseNumber", pCustomer.HouseNumber)); SqlCmd2.Parameters.Add(new SqlParameter("@pHouseName", pCustomer.HouseName)); SqlCmd2.Parameters.Add(new SqlParameter("@pStreet", pCustomer.Street)); SqlCmd2.Parameters.Add(new SqlParameter("@pPostcode", pCustomer.Postcode)); return(DataAccess.ExecuteIntegerReturnQuery("ODS", SqlCmd2)); }
private static bool SyncRoomRecord(int pDaysDuration, DateTime pDateFrom, double pPaymentAmount, int pMyRoomID, int pMyCustomerID, string pNotes) { SqlCommand SqlCmd = new SqlCommand("SELECT TOP 1 [BookingDetailID] FROM " + Global.DBConfig["BookingTN"] + HelperMethods.BuildSQLConditionsWithParams(new SortedList <string, string>() { { "Days", pDaysDuration.ToSafeString() }, { "DateStart", HelperMethods.FormatDateForSQL(pDateFrom.ToShortDateString()) }, { "PaymentAmount", pPaymentAmount.ToSafeString() }, { "myRoom", pMyRoomID.ToSafeString() }, { "myCustomer", pMyCustomerID.ToSafeString() }, { "Notes", pNotes } })); SqlCmd.Parameters.Add(new SqlParameter("@pDays", pDaysDuration)); SqlCmd.Parameters.Add(new SqlParameter("@pDateStart", pDateFrom)); SqlCmd.Parameters.Add(new SqlParameter("@pPaymentAmount", pPaymentAmount)); SqlCmd.Parameters.Add(new SqlParameter("@pmyRoom", pMyRoomID)); SqlCmd.Parameters.Add(new SqlParameter("@pmyCustomer", pMyCustomerID)); SqlCmd.Parameters.Add(new SqlParameter("@pNotes", pNotes)); int BookingID = DataAccess.ExecuteIntegerReturnQuery("ODS", SqlCmd); int RoomRowsAffected = DataAccess.ExecuteUpdateQuery("ODS", Global.DBConfig["RoomsTN"], new SortedList <string, string>() { { "BookingDetailID", BookingID.ToSafeString() } }, "WHERE RoomID = " + pMyRoomID); if (RoomRowsAffected == 1) { Logger.WriteLine("DEBUG", "Room record updated with booking detail ID succesfully."); return(true); } else if (RoomRowsAffected == 0) { Logger.WriteLine("ERROR", "Failed to update the room record with booking detail ID."); return(false); } return(false); }
public static bool CheckCustomerExists(Customer pCustomer) { string sqlCmdConditions = HelperMethods.BuildSQLConditionsWithParams (new SortedList <string, string>() { { "Name", pCustomer.Name }, { "HouseNumber", Convert.ToString(pCustomer.HouseNumber) }, { "HouseName", pCustomer.HouseName }, { "Street", pCustomer.Street }, { "Postcode", pCustomer.Postcode } }); SqlCommand SqlCmd0 = new SqlCommand("SELECT CASE WHEN EXISTS((SELECT TOP 1 * FROM " + Global.DBConfig["CustomerTN"] + sqlCmdConditions + ")) THEN 1 ELSE 0 END;"); SqlCmd0.Parameters.Add(new SqlParameter("@pName", pCustomer.Name)); SqlCmd0.Parameters.Add(new SqlParameter("@pHouseNumber", pCustomer.HouseNumber)); SqlCmd0.Parameters.Add(new SqlParameter("@pHouseName", pCustomer.HouseName)); SqlCmd0.Parameters.Add(new SqlParameter("@pStreet", pCustomer.Street)); SqlCmd0.Parameters.Add(new SqlParameter("@pPostcode", pCustomer.Postcode)); return(DataAccess.ExecuteBoolReturnQuery("ODS", SqlCmd0)); }