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