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