// This function deletes (technically hides in database) a service that the user chooses
private void DeleteServices(AServ aServ)
{
// query to "delete"
string SQLServDel = "UPDATE SERVICE_TABLE SET SERV_ACTIVE = 0 WHERE SERV_ID = @SERV_ID";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(SQLServDel, connection);
// open new connection
connection.Open();
// gets service ID that we are setting to 0, since we aren't technically deleting the service
using (command = new SqlCommand(SQLServDel, connection))
{
command.Parameters.AddWithValue("@SERV_ID", aServ.ServID);
int rowsAffected = command.ExecuteNonQuery();
}
// close connection
connection.Close();
}
// catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// this function updates the values of a service that the user chooses
private void UpdateServices(AServ aServ)
{
// query to update a service in database
string SetServSQL = "UPDATE SERVICE_TABLE SET SERV_SENTENCE = @pSERV_SENT, "
+ "SERV_NAME = @pSERV_NAME, SERV_ACTIVE = @pSERV_ACTIVE WHERE SERV_ID = @pSERV_ID;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(SetServSQL, connection);
// open new connection
connection.Open();
// pulls the values changes from the instance of AServ and adds them to respective @p values stored in db
using (command = new SqlCommand(SetServSQL, connection))
{
command.Parameters.AddWithValue("@pSERV_ID", aServ.ServID);
command.Parameters.AddWithValue("@pSERV_NAME", aServ.ServName);
command.Parameters.AddWithValue("@pSERV_SENT", aServ.ServSent);
command.Parameters.AddWithValue("@pSERV_ACTIVE", aServ.Active);
int rowsAffected = command.ExecuteNonQuery();
}
// close connection
connection.Close();
}
//catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// Inserts a new service into the db that the user creates
private void CreateServices(AServ aServ)
{
// query to insert new service into db
string AddServSQL = "INSERT INTO SERVICE_TABLE (SERV_NAME, SERV_SENTENCE, SERV_ACTIVE) " +
" VALUES (@pSERV_NAME, @pSERV_SENT, 1) ;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(AddServSQL, connection);
//Open new connection
connection.Open();
// pulls the values changes from the instance of AServ and adds them to respective @p values stored in db
using (command = new SqlCommand(AddServSQL, connection))
{
command.Parameters.AddWithValue("@pSERV_NAME", aServ.ServName);
command.Parameters.AddWithValue("@pSERV_SENT", aServ.ServSent);
int rowsAffected = command.ExecuteNonQuery();
}
// close connection
connection.Close();
}
//catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//when the user hits save it saves if not it deactivates
private void dgvServices_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.ColumnIndex == 3)
{
if (isNew == true)
{
isSaved = true;
AServ aServ = new AServ();
//aServ.ServID = int.Parse(dgvFees.Rows[e.RowIndex].Cells[0].Value.ToString());
aServ.ServName = dgvServices.Rows[e.RowIndex].Cells[1].Value.ToString();
aServ.ServSent = dgvServices.Rows[e.RowIndex].Cells[2].Value.ToString();
CreateServices(aServ);
isNew = false;
}
else
{
// sets is saved to true so we can close out the Globals form without a prompt
isSaved = true;
AServ aServ = new AServ();
aServ.ServID = int.Parse(dgvServices.Rows[e.RowIndex].Cells[0].Value.ToString());
aServ.ServName = dgvServices.Rows[e.RowIndex].Cells[1].Value.ToString();
aServ.ServSent = dgvServices.Rows[e.RowIndex].Cells[2].Value.ToString();
aServ.Active = true;
// calls UpdateServices form with aServ instance passed in to update a specific row
UpdateServices(aServ);
}
}
// If the user clicks "Delete" on the services DGV
else if (e.ColumnIndex == 4)
{
string message = "Are you sure you want to delete this service? \nIf so it will remove access to their related characteristics on this page.";
string title = "Confirm Window";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
// Serves as a confirmation window if the user really wants to delete a service
DialogResult result = MessageBox.Show(message, title, buttons);
if (result == DialogResult.Yes)
{
AServ aServ = new AServ();
aServ.ServID = int.Parse(dgvServices.Rows[e.RowIndex].Cells[0].Value.ToString());
// calls DeleteServices function and passes in aServ instance to hide the row
DeleteServices(aServ);
}
}
}
catch (Exception ex)
{
MessageBox.Show("You tried to click the button that was not in a row with data. \n This is the error: " + ex.Message);
}
}
/// <summary>
/// creates the list of services that we interact with, filled later
/// </summary>
private void CreateServiceList()
{
string GetServicesSQL = "SELECT SERV_ID, SERV_NAME, SERV_SENTENCE, SERV_ACTIVE " + "FROM SERVICE_TABLE";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetServicesSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AServ tempService = new AServ();
if (reader["SERV_ID"] != DBNull.Value)
{
tempService.ServID = (reader["SERV_ID"] as int?) ?? 0;
}
if (reader["SERV_NAME"] != DBNull.Value)
{
tempService.ServName = reader["SERV_NAME"] as string;
}
if (reader["SERV_SENTENCE"] != DBNull.Value)
{
tempService.ServSent = reader["SERV_SENTENCE"] as string;
}
if (reader["SERV_ACTIVE"] != DBNull.Value)
{
tempService.Active = reader.GetBoolean(reader.GetOrdinal("SERV_ACTIVE"));
}
//Add the temporary plot stuff from list.
ServiceObjList.Add(tempService);
tempService = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// when the user clicks on an empty service row, this is how the new row will be formatted in
private void dgvServices_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
{
AServ aServ = new AServ();
isNew = true;
e.Row.Cells[0].Value = 0;
e.Row.Cells[1].Value = "";
e.Row.Cells[2].Value = "";
e.Row.Cells[3].Value = "Save";
e.Row.Cells[4].Value = "Delete";
}