private string BookServices(DataTable dtTblServicesToBeBooked) { string appointmentDateSelected = ""; using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { //Insert services data in Database var command = new SqlCommand("StoredPro_InsertAppointmentsBatch", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@AppointmentDetails", SqlDbType.Structured).Value = dtTblServicesToBeBooked; command.Parameters.Add("@OperationStatus", SqlDbType.Bit).Direction = ParameterDirection.Output; appointmentDateSelected = dtTblServicesToBeBooked.Rows[0].Field <DateTime>("AppointmentDate").ToShortDateString(); connection.Open(); command.ExecuteNonQuery(); if (!(bool)command.Parameters["@OperationStatus"].Value) { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, "SP Error booking appointment"); } AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.SuccessForecolor, AppConstants.LblMessage.SuccessBackgroundColor, "Appointment Booked!"); command.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } return(appointmentDateSelected); }
private DataTable LoadCustomerNamesIntoDataTable() { var dtTableComboBoxCustomerNames = new DataTable(); using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { //Load Services and styliyst data from DB var command = new SqlCommand("StoredPro_PopulateCustomerNamesComboBox", connection); command.CommandType = CommandType.StoredProcedure; var adapterComboBoxCustomerNames = new SqlDataAdapter(); adapterComboBoxCustomerNames.SelectCommand = command; adapterComboBoxCustomerNames.Fill(dtTableComboBoxCustomerNames); command.Dispose(); adapterComboBoxCustomerNames.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } return(dtTableComboBoxCustomerNames); }
private DataSet LoadAppointmentInformation(string dateSelected) { var dtSetAppointmentsPerSpecificDate = new DataSet(); using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { //StoredPro_RetrieveAppointmentDetails var command = new SqlCommand("StoredPro_RetrieveAppointmentDetails", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@AppointmentDate", SqlDbType.NVarChar, 10).Value = dateSelected; //Create adapter to pull appointment data from DB var adapterAppointments = new SqlDataAdapter(); adapterAppointments.SelectCommand = command; adapterAppointments.Fill(dtSetAppointmentsPerSpecificDate); adapterAppointments.Dispose(); command.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } return(dtSetAppointmentsPerSpecificDate); }
private DataTable RetrieveStylistSchedule(AppointmentDetails customerAppointment) { var dtTableStylistAppointments = new DataTable(); /*Placed connection string in a class in App_Code to improve DB connection callings*/ using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { var command = new SqlCommand("StoredPro_RetrieveStylistSchedule", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@AppointmentDate", SqlDbType.NVarChar, 10).Value = customerAppointment.DesiredDate; command.Parameters.Add("@StylistId", SqlDbType.TinyInt).Value = customerAppointment.HairStylist; var adapterStylistAppointments = new SqlDataAdapter(); adapterStylistAppointments.SelectCommand = command; adapterStylistAppointments.Fill(dtTableStylistAppointments); command.Dispose(); adapterStylistAppointments.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } return(dtTableStylistAppointments); }
private DataTable RetrieveServicesDurationAndProcessingTime(AppointmentDetails customerAppointment) { var dtTableServicesDetails = new DataTable(); /*Placed connection string in a class in App_Code to improve DB connection callings*/ using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { var command = new SqlCommand("StoredPro_RetrieveServiceDurationAndProcessingTime", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@HairLenght", SqlDbType.TinyInt).Value = (int)customerAppointment.CustomerHairLength; var adapterServicesDetails = new SqlDataAdapter(); adapterServicesDetails.SelectCommand = command; adapterServicesDetails.Fill(dtTableServicesDetails); dtTableServicesDetails.PrimaryKey = new DataColumn[] { dtTableServicesDetails.Columns["Id"] }; command.Dispose(); adapterServicesDetails.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } return(dtTableServicesDetails); }
//Save customer data into DB public void RegisterCustomer(ClientHair clientToRegister) { using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { var command = ClientHair.InsertNewCustomerDataIntoDB(clientToRegister, connection); int insertResult = ClientHair.DetermineWhetherCustomerAlreadyExistInDB(command); string message = ClientHair.GetMessageResultFromInsertingCustomer(command); DisplayInsertResultToUser(message, insertResult); LblMessageToUser.Visible = true; command.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } } }
//Load Services and Stylists from DB into group checkbox and dropdownbox private void LoadServicesAndStylistForAppointmentBooking() { var PopulateDropDownListForBookingAppointments = new DataSet(); var adapterDropDownListForBookingAppointments = new SqlDataAdapter(); /*Placed connection string in a class in App_Code to improve DB connection callings*/ using (SqlConnection connection = HairStylistConnectionString.Connection()) { try { //Load Services and styliyst data from DB var command = new SqlCommand("StoredPro_PopulateDropDownListForBookingAppointments", connection); command.CommandType = CommandType.StoredProcedure; adapterDropDownListForBookingAppointments.SelectCommand = command; adapterDropDownListForBookingAppointments.Fill(PopulateDropDownListForBookingAppointments); if (PopulateDropDownListForBookingAppointments.Tables[0].Rows.Count != 0) { DDLHairLength.DataSource = PopulateDropDownListForBookingAppointments.Tables[0]; DDLHairLength.DataTextField = "HairLength"; DDLHairLength.DataValueField = "Id"; DDLHairLength.DataBind(); } else { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.WarningForecolor, AppConstants.LblMessage.WarningBackgroundColor, "There are no hair lenght options in Database"); } if (PopulateDropDownListForBookingAppointments.Tables[1].Rows.Count != 0) { ChkBxListServices.DataSource = PopulateDropDownListForBookingAppointments.Tables[1]; ChkBxListServices.DataTextField = "Service"; ChkBxListServices.DataValueField = "Id"; ChkBxListServices.DataBind(); } else { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.WarningForecolor, AppConstants.LblMessage.WarningBackgroundColor, "There are no services in Database"); } if (PopulateDropDownListForBookingAppointments.Tables[2].Rows.Count != 0) { DDLStylist.DataSource = PopulateDropDownListForBookingAppointments.Tables[2]; DDLStylist.DataTextField = "Stylist"; DDLStylist.DataValueField = "Id"; DDLStylist.DataBind(); } else { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.WarningForecolor, AppConstants.LblMessage.WarningBackgroundColor, "There are no stylist in Database"); } if (PopulateDropDownListForBookingAppointments.Tables[3].Rows.Count != 0) { DDLBeginTime.DataSource = PopulateDropDownListForBookingAppointments.Tables[3]; DDLBeginTime.DataTextField = "StartTime"; DDLBeginTime.DataValueField = "Id"; DDLBeginTime.DataBind(); } else { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.WarningForecolor, AppConstants.LblMessage.WarningBackgroundColor, "There are no service start times in Database"); } if (PopulateDropDownListForBookingAppointments.Tables[4].Rows.Count != 0) { CmbBxName.DataSource = PopulateDropDownListForBookingAppointments.Tables[4]; CmbBxName.DataTextField = "FullName"; CmbBxName.DataValueField = "Id"; CmbBxName.DataBind(); CmbBxName.Items.Insert(0, new ListItem("Please Select")); CmbBxName.SelectedIndex = 0; } else { AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.WarningForecolor, AppConstants.LblMessage.WarningBackgroundColor, "There are no Customer Names in Database"); } command.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += "\n" + "Message:" + ex.Message; AssignMessageToLabelWithResultFromOperation(AppConstants.LblMessage.ErrorForecolor, AppConstants.LblMessage.ErrorBackgroundColor, str); } finally { adapterDropDownListForBookingAppointments.Dispose(); } } }