public bool DeleteStudent(Student student)
{
if (student.HasID())
{
// We need to update because the student already has a key.
string query = String.Format("DELETE FROM {0} ", tables.Student.TableName);
query += String.Format("WHERE {0} = @{0};", tables.Student.StudentID);
OleDbCommand update = new OleDbCommand(query, dbConnection);
update.Parameters.Add(new OleDbParameter("@" + tables.Student.StudentID, OleDbType.Integer)).Value = student.StudentID;
update.ExecuteNonQuery();
return true;
}
else
{
Debug.WriteLine("A student without an ID is trying to be deleted. That's not good.");
return false;
}
}
public bool UpdateStudent(Student student)
{
if (student.HasID())
{
// AAAAAAAAAGH ACCESS AND OLEDB SUCK. You can used named parameters BUT OleDb doesn't respect them and just relies on order. SUCKS.
// We need to update because the student already has a key.
string query = String.Format("UPDATE {0} SET ", tables.Student.TableName);
query += String.Format("{0} = @{0}, ", tables.Student.FirstName);
query += String.Format("{0} = @{0}, ", tables.Student.LastName);
query += String.Format("{0} = @{0}, ", tables.Student.Username);
query += String.Format("{0} = @{0}, ", tables.Student.EmailAddress);
query += String.Format("{0} = @{0}, ", tables.Student.ClassSection);
query += String.Format("{0} = @{0} ", tables.Student.StudentSchoolID);
query += String.Format("WHERE {0} = @{0};", tables.Student.StudentID);
OleDbCommand update = new OleDbCommand(query, dbConnection);
update.Parameters.Add(new OleDbParameter("@" + tables.Student.FirstName, OleDbType.VarChar)).Value = student.FirstName;// student.FirstName;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.LastName, OleDbType.VarChar)).Value = student.LastName;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.Username, OleDbType.VarChar)).Value = student.Username;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.EmailAddress, OleDbType.VarChar)).Value = student.EmailAddress;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.ClassSection, OleDbType.Integer)).Value = student.ClassSection;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.StudentSchoolID, OleDbType.VarChar)).Value = student.StudentSchoolID;
update.Parameters.Add(new OleDbParameter("@" + tables.Student.StudentID, OleDbType.Integer)).Value = student.StudentID;
update.ExecuteNonQuery();
return true;
}
else
{
Debug.WriteLine("A student without an ID was found in a student dictionary. That's not good.");
return false;
}
}
public int AddStudent(Student student)
{
if (student.HasID())
{
Debug.WriteLine("You should not add students if they already have an ID assigned.");
return student.StudentID;
}
else
{
// We need to insert rather than update because the student has no key.
string query = String.Format("INSERT INTO {0} (", tables.Student.TableName);
query += String.Format("{0}, ", tables.Student.FirstName);
query += String.Format("{0}, ", tables.Student.LastName);
query += String.Format("{0}, ", tables.Student.Username);
query += String.Format("{0}, ", tables.Student.EmailAddress);
query += String.Format("{0}, ", tables.Student.ClassSection);
query += String.Format("{0}", tables.Student.StudentSchoolID);
query += ") VALUES (";
query += String.Format("@{0}, ", tables.Student.FirstName);
query += String.Format("@{0}, ", tables.Student.LastName);
query += String.Format("@{0}, ", tables.Student.Username);
query += String.Format("@{0}, ", tables.Student.EmailAddress);
query += String.Format("@{0}, ", tables.Student.ClassSection);
query += String.Format("@{0}", tables.Student.StudentSchoolID);
query += ");";
OleDbCommand insert = new OleDbCommand(query, dbConnection);
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.FirstName, OleDbType.VarChar)).Value = student.FirstName;// student.FirstName;
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.LastName, OleDbType.VarChar)).Value = student.LastName;
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.Username, OleDbType.VarChar)).Value = student.Username;
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.EmailAddress, OleDbType.VarChar)).Value = student.EmailAddress;
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.ClassSection, OleDbType.Integer)).Value = student.ClassSection;
insert.Parameters.Add(new OleDbParameter("@" + tables.Student.StudentSchoolID, OleDbType.VarChar)).Value = student.StudentSchoolID;
try
{
insert.ExecuteNonQuery();
}
catch
{
Debug.WriteLine("Could not insert student.");
}
// Now that we inserted the student, we need to get its ID
query = "SELECT @@IDENTITY;";
OleDbCommand getID = new OleDbCommand(query, dbConnection);
int key = student.StudentID;
try
{
key = (int)getID.ExecuteScalar();
}
catch
{
Debug.WriteLine("Could not retrieve student ID.");
}
return key;
}
}