//Create public static bool AddIndividual(Individual objIndividual) { //these are our variables to be used int rowsAffected = 0; NpgsqlConnection conn = null; NpgsqlCommand command = null; string sqlQuery; try { //using allows the program to shut the connection so that it's not constantly attatched to the database using (conn = AccessData.GetConnection()) { //open the connection to the database conn.Open(); //this is where the SQL query to the database will be put in. the parameters ie '@ind_num' must match // the table's column names to work properly sqlQuery = "INSERT into individuals values (@ind_num, @ind_fname, @ind_lname)"; using (command = new NpgsqlCommand(sqlQuery, conn)) { //this command will replace the parameter in the query string with the individual object's corresponding // property command.Parameters.AddWithValue("@ind_num", objIndividual.ID); command.Parameters.AddWithValue("@ind_fname", objIndividual.FirstName); command.Parameters.AddWithValue("@ind_lname", objIndividual.LastName); //this is essentially our "did it work" kind of check rowsAffected = command.ExecuteNonQuery(); } //if rows affected is greater than 0, then we know data was put into the database if (rowsAffected > 0) { return(true); } else { return(false); } } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw ex; } finally { //Failsafe to make sure that the Database Connection is closed if (conn != null) { conn.Close(); } } }
//Update public static bool UpdateIndividual(Individual objIndividual) { int rowsAffected = 0; NpgsqlConnection conn = null; NpgsqlCommand command = null; string sqlQuery; try { using (conn = AccessData.GetConnection()) { conn.Open(); //the UPDATE syntax will be used in this case, and the format will match SQL query format so each // column on a new line for clarity sqlQuery = "UPDATE individual" + Environment.NewLine + "set firstname = @ind_fname" + Environment.NewLine + "lastname = @ind_lname" + Environment.NewLine + "WHERE id = @ind_num"; using (command = new NpgsqlCommand(sqlQuery, conn)) { command.Parameters.AddWithValue("@ind_num", objIndividual.ID); command.Parameters.AddWithValue("@ind_fname", objIndividual.FirstName); command.Parameters.AddWithValue("@ind_lname", objIndividual.LastName); rowsAffected = command.ExecuteNonQuery(); } if (rowsAffected > 0) { return(true); } else { return(false); } } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw ex; } finally { if (conn != null) { conn.Close(); } } }
//Read public static Individual GetIndividual(int ind_Num) { Individual objIndividual = null; NpgsqlConnection conn = null; NpgsqlCommand command = null; NpgsqlDataReader reader = null; string sqlQuery = "SELECT * FROM individual WHERE id = @ind_num"; try { using (conn = AccessData.GetConnection()) { conn.Open(); using (command = new NpgsqlCommand(sqlQuery, conn)) { command.Parameters.AddWithValue("@ind_num", ind_Num); using (reader = command.ExecuteReader()) { while (reader.Read()) { //inside the square brackets, the field must match the database perfectly for the reader to work objIndividual = new Individual(); objIndividual.ID = reader["id"].ToString(); objIndividual.FirstName = reader["first_name"].ToString(); objIndividual.LastName = reader["last_name"].ToString(); } } } } return(objIndividual); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw; } finally { if (conn != null) { conn.Close(); } } }
//Read public static Organization GetOrganization(int org_Num) { Organization objOrganization = null; NpgsqlConnection conn = null; NpgsqlCommand command = null; NpgsqlDataReader reader = null; string sqlQuery = "SELECT * FROM organization WHERE id = @org_num"; try { using (conn = AccessData.GetConnection()) { conn.Open(); using (command = new NpgsqlCommand(sqlQuery, conn)) { command.Parameters.AddWithValue("@org_num", org_Num); using (reader = command.ExecuteReader()) { while (reader.Read()) { //inside the square brackets, the field must match the database perfectly for the reader to work objOrganization = new Organization(); objOrganization.ID = reader["id"].ToString(); objOrganization.FirstName = reader["name"].ToString(); objOrganization.LastName = reader["nickname"].ToString(); } } } } return(objOrganization); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw; } finally { if (conn != null) { conn.Close(); } } }
//Delete public static bool DeleteIndividual(Individual objIndividual) { int rowsAffected = 0; NpgsqlConnection conn = null; NpgsqlCommand command = null; string sqlQuery; try { using (conn = AccessData.GetConnection()) { conn.Open(); //DELETE syntax is much simpler, we only need to find the selected ID and delete it sqlQuery = "DELETE individual WHERE id = @ind_num"; using (command = new NpgsqlCommand(sqlQuery, conn)) { command.Parameters.AddWithValue("@ind_num", objIndividual.ID); command.Parameters.AddWithValue("@ind_fname", objIndividual.FirstName); command.Parameters.AddWithValue("@ind_lname", objIndividual.LastName); rowsAffected = command.ExecuteNonQuery(); } if (rowsAffected > 0) { return(true); } else { return(false); } } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw ex; } finally { if (conn != null) { conn.Close(); } } }
//start here public static List <Individual> GetIndividuals() { //this allows us to populate a list of all individuals List <Individual> list = new List <Individual>(); NpgsqlConnection conn = null; NpgsqlCommand command = null; NpgsqlDataReader reader = null; string sqlQuery = "SELECT * FROM individual ORDER BY id"; try { using (conn = AccessData.GetConnection()) { conn.Open(); using (command = new NpgsqlCommand(sqlQuery, conn)) { using (reader = command.ExecuteReader()) { while (reader.Read()) { //We want to use the constructor from the business object version here, reading each // of the parameters from the database by putting the column's name in the square brackets Individual objIndividual = new Individual(reader["id"].ToString(), reader["first_name"].ToString(), reader["last_name"].ToString()); list.Add(objIndividual); } } } } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); throw; } finally { } return(list); }