// This function deletes (technically hides in database) a characteristic that the user chooses
private void DeleteFees(AFee aFee)
{
// query to "delete"
string SQLFeeDel = "UPDATE CHARACTERISTIC_TABLE SET CHAR_ACTIVE = 0 WHERE CHAR_ID = @CHAR_ID";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(SQLFeeDel, connection);
// open a new connection
connection.Open();
// gets characteristic ID that we are setting to 0, since we aren't technically deleting the characteristic
using (command = new SqlCommand(SQLFeeDel, connection))
{
command.Parameters.AddWithValue("@CHAR_ID", aFee.FeeID);
int rowsAffected = command.ExecuteNonQuery();
}
// close connection
connection.Close();
}
// catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// This function updates the values of a characteristic that the user chooses
private void UpdateFees(AFee aFee)
{
// query to update a characteristic in database
string SetFeeSQL = "UPDATE CHARACTERISTIC_TABLE SET CHAR_COST = @pCHAR_COST, "
+ "CHAR_MIN = @pCHAR_MIN, CHAR_NAME = @pCHAR_NAME, SERV_ID = @pSERV_ID WHERE CHAR_ID = @pCHAR_ID;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(SetFeeSQL, connection);
// open new connection
connection.Open();
using (command = new SqlCommand(SetFeeSQL, connection))
{
command.Parameters.AddWithValue("@pCHAR_ID", aFee.FeeID);
command.Parameters.AddWithValue("@pCHAR_NAME", aFee.FeeName);
command.Parameters.AddWithValue("@pCHAR_COST", aFee.FeeCost);
command.Parameters.AddWithValue("@pCHAR_MIN", aFee.FeeMin);
command.Parameters.AddWithValue("@pSERV_ID", aFee.ServID);
int rowsAffected = command.ExecuteNonQuery();
}
// close connection
connection.Close();
}
//catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// Inserts a new characteristic into the db that the user creates
private void CreateFees(AFee aFee)
{
// query to insert characteristic into db
string AddFeesSQL = "INSERT INTO CHARACTERISTIC_TABLE (CHAR_NAME, CHAR_COST, CHAR_MIN, SERV_ID, CHAR_ACTIVE) " +
" VALUES (@pCHAR_NAME, @pCHAR_COST, @pCHAR_MIN, @pSERV_ID, 1) ;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(AddFeesSQL, connection);
// open new connection
connection.Open();
// pulls the values changes from the instance of AFee and adds them to respective @p values stored in db
using (command = new SqlCommand(AddFeesSQL, connection))
{
command.Parameters.AddWithValue("@pCHAR_NAME", aFee.FeeName);
command.Parameters.AddWithValue("@pCHAR_COST", aFee.FeeCost);
command.Parameters.AddWithValue("@pCHAR_MIN", aFee.FeeMin);
command.Parameters.AddWithValue("@pSERV_ID", aFee.ServID);
int rowsAffected = command.ExecuteNonQuery();
}
//close connection
connection.Close();
}
//catch
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//these are some defalt values that are passed into fees
private void dgvFees_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
{
AFee aFee = new AFee();
isNew = true;
e.Row.Cells[0].Value = 0;
e.Row.Cells[1].Value = "";
e.Row.Cells[2].Value = 0.00m;
e.Row.Cells[3].Value = 0.00m;
e.Row.Cells[4].Value = 1;
e.Row.Cells[5].Value = "";
e.Row.Cells[6].Value = "Save";
e.Row.Cells[7].Value = "Delete";
}
/// <summary>
/// this is an eventhandler for a click. if it is for the save it saves and if not it is deactivated after a prompt
/// </summary>
private void dgvFees_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
// if the user clicks "Save" on the Characteristics DGV
if (e.ColumnIndex == 6)
{
if (isNew == true)
{
// this is set to true so there won't be a prompt when the user clicks the Done button
isSaved = true;
// new instance of AFee
AFee aFee = new AFee();
aFee.FeeID = int.Parse(dgvFees.Rows[e.RowIndex].Cells[0].Value.ToString());
aFee.FeeName = dgvFees.Rows[e.RowIndex].Cells[1].Value.ToString();
aFee.FeeCost = decimal.Parse(dgvFees.Rows[e.RowIndex].Cells[2].Value.ToString());
aFee.FeeMin = decimal.Parse(dgvFees.Rows[e.RowIndex].Cells[3].Value.ToString());
aFee.ServID = (int)dgvFees.Rows[e.RowIndex].Cells[4].Value;
// calls CreateFees with instance of aFee
CreateFees(aFee);
// this will keep us from inserting every existing characteristic that the user wants to edit into the database as a duplicate
isNew = false;
}
else
{
// sets isSaved to true so there won't be a prompt when the user clicks the Done button
isSaved = true;
// new instance of AFee
AFee aFee = new AFee();
aFee.FeeID = int.Parse(dgvFees.Rows[e.RowIndex].Cells[0].Value.ToString());
aFee.FeeName = dgvFees.Rows[e.RowIndex].Cells[1].Value.ToString();
aFee.FeeCost = decimal.Parse(dgvFees.Rows[e.RowIndex].Cells[2].Value.ToString());
aFee.FeeMin = decimal.Parse(dgvFees.Rows[e.RowIndex].Cells[3].Value.ToString());
// to read in associated services
foreach (AServ aServ in ServiceObjList)
{
if (aServ.ServName == dgvFees.Rows[e.RowIndex].Cells[5].Value.ToString())
{
aFee.ServID = aServ.ServID;
}
}
// calls UpdateFees with instance of aFee passed in to update a characteristic
UpdateFees(aFee);
}
}
// If the user clicks on "Delete" column in a characteristic
else if (e.ColumnIndex == 7)
{
string message = "Are you sure you want to delete this characteristic?";
string title = "Confirm Window";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
// prompt to confirm the user wants to delete the characteristic
DialogResult result = MessageBox.Show(message, title, buttons);
if (result == DialogResult.Yes)
{
AFee aFee = new AFee();
aFee.FeeID = int.Parse(dgvFees.Rows[e.RowIndex].Cells[0].Value.ToString());
// calls delete function with aFee instance passed in to hide to column
DeleteFees(aFee);
}
}
}
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 a list of characteristics that we interact with on this page, filled later
/// </summary>
private void CreateFeeList()
{
string GetFeesSQL = "SELECT CHAR_ID, CHAR_NAME, CHAR_COST, CHAR_MIN, ct.SERV_ID, st.SERV_NAME, " +
"CHAR_ACTIVE FROM CHARACTERISTIC_TABLE ct INNER " +
"JOIN SERVICE_TABLE st on ct.SERV_ID = st.SERV_ID WHERE SERV_ACTIVE = 1";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetFeesSQL, 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())
{
AFee tempFee = new AFee();
if (reader["CHAR_ID"] != DBNull.Value)
{
tempFee.FeeID = (reader["CHAR_ID"] as int?) ?? 0;
}
if (reader["CHAR_NAME"] != DBNull.Value)
{
tempFee.FeeName = reader["CHAR_NAME"] as string;
}
if (reader["CHAR_COST"] != DBNull.Value)
{
tempFee.FeeCost = (reader["CHAR_COST"] as decimal?) ?? 0.00m;
}
if (reader["CHAR_MIN"] != DBNull.Value)
{
tempFee.FeeMin = (reader["CHAR_MIN"] as decimal?) ?? 0.00m;
}
if (reader["SERV_ID"] != DBNull.Value)
{
tempFee.ServID = (reader["SERV_ID"] as int?) ?? 0;
}
if (reader["SERV_NAME"] != DBNull.Value)
{
tempFee.ServName = reader["SERV_NAME"] as string;
}
if (reader["CHAR_ACTIVE"] != DBNull.Value)
{
tempFee.Active = reader.GetBoolean(reader.GetOrdinal("CHAR_ACTIVE"));
}
else
{
tempFee.Active = true; //if it is null we treat it as true
}
//Add the temporary plot stuff from list.
FeeObjList.Add(tempFee);
tempFee = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
