public List <SubjectEntry> GetAllSubjects()
{
command = new SqlCommand();
command.CommandText = "SELECT * FROM tbl_subjects ORDER BY order_by ASC";
command.Connection = connection;
command.Connection.Open();
reader = command.ExecuteReader();
List <SubjectEntry> allSubjects = new List <SubjectEntry>();
while (reader.Read())
{
SubjectEntry aSubjectEntry = new SubjectEntry();
aSubjectEntry.Id = Convert.ToInt32(reader["id"]);
aSubjectEntry.Name = reader["name"].ToString();
aSubjectEntry.OrderBy = Convert.ToInt32(reader["order_by"]);
allSubjects.Add(aSubjectEntry);
}
reader.Close();
connection.Close();
return(allSubjects);
}
private void deleteButton_Click(object sender, EventArgs e)
{
SubjectEntry aSubjectEntry = new SubjectEntry();
aSubjectEntry.Id = Convert.ToInt32(subjectIdTextBox.Text);
string message = aSubjectEntryManager.DeleteSubject(aSubjectEntry.Id);
MessageBox.Show(message);
LoadSubjectEntryListView();
}
public int UpdateSubject(SubjectEntry aSubjectEntry)
{
query = "UPDATE tbl_subjects SET name = '" + aSubjectEntry.Name + "', order_by = '" + aSubjectEntry.OrderBy + "' WHERE id = '" + aSubjectEntry.Id + "'";
connection.Open();
command = new SqlCommand(query, connection);
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return(rowAffected);
}
public int SaveSubject(SubjectEntry aSubjectEntry)
{
string query = "INSERT INTO tbl_subjects (name, order_by) VALUES ('" + aSubjectEntry.Name + "','" + aSubjectEntry.OrderBy + "')";
connection.Open();
command = new SqlCommand(query, connection);
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return(rowAffected);
}
public bool IsSubjectNameExist(SubjectEntry aSubjectEntry)
{
query = "SELECT * FROM tbl_subjects WHERE name = '" + aSubjectEntry.Name + "' AND id <> '" + aSubjectEntry.Id + "'";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
bool isExist = reader.HasRows;
reader.Close();
connection.Close();
return(isExist);
}
public string UpdateSubject(SubjectEntry aSubjectEntry)
{
if (aSubjectEntryGateway.IsSubjectNameExist(aSubjectEntry))
{
return("This Subject Already Exists");
}
else
{
int rowAffected = aSubjectEntryGateway.UpdateSubject(aSubjectEntry);
if (rowAffected > 0)
{
return("Update Subject Successfully");
}
return("Update Failed");
}
}
private void subjectEntryListView_DoubleClick(object sender, EventArgs e)
{
ListViewItem selecteditem = subjectEntryListView.SelectedItems[0];
SubjectEntry aSubjectEntry = (SubjectEntry)selecteditem.Tag;
deleteButton.Enabled = true;
if (aSubjectEntry != null)
{
nameTextBox.Text = aSubjectEntry.Name;
orderByTextBox.Text = aSubjectEntry.OrderBy.ToString();
subjectIdTextBox.Text = aSubjectEntry.Id.ToString();
saveButton.Text = "Update";
}
}
private void saveButton_Click(object sender, EventArgs e)
{
SubjectEntry aSubjectEntry = new SubjectEntry();
aSubjectEntry.Name = nameTextBox.Text;
aSubjectEntry.OrderBy = Convert.ToInt32(orderByTextBox.Text);
if (saveButton.Text == "Save")
{
string message = aSubjectEntryManager.SaveSubject(aSubjectEntry);
MessageBox.Show(message);
}
else
{
aSubjectEntry.Id = Convert.ToInt32(subjectIdTextBox.Text);
string message = aSubjectEntryManager.UpdateSubject(aSubjectEntry);
MessageBox.Show(message);
}
LoadSubjectEntryListView();
}
