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();
}
}
}
