private void All_Students_Load(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
using (connection)
{
SqlCommand command = new SqlCommand("SELECT * FROM Students;", connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Load(reader);
allStudentsTable.DataSource = dt;
}
else
{
CommonFormActions.ShowMessage("No Results", "No results to show");
}
reader.Close();
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
}
}
}
private void removeStudent_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
if (allStudentsTable.CurrentRow.Index > -1)
{
string query = "DELETE FROM Students WHERE [Id Student] = @IdStudent";
SqlParameter idStudent = new SqlParameter("@IdStudent", SqlDbType.Int);
idStudent.Value = allStudentsTable.CurrentRow.Cells[0].Value;
SqlParameter[] parameters = new SqlParameter[1] {
idStudent
};
SqlCommand command = CommonFormActions.CreateCommand(connection, query, parameters);
try
{
using (connection)
{
SqlDataReader reader = command.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "Student removed successfully");
CommonFormActions.UpdateDataGrid(connection, "Students", allStudentsTable);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
}
private void remTeacher_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
if (allTeachersTable.CurrentRow.Index > -1)
{
string query = "DELETE FROM Teachers WHERE PIN = @PIN";
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = allTeachersTable.CurrentRow.Cells[0].Value;
SqlParameter[] parameters = new SqlParameter[1] {
PIN
};
SqlCommand command = CommonFormActions.CreateCommand(connection, query, parameters);
try
{
using (connection)
{
SqlDataReader reader = command.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "Teacher removed successfully");
CommonFormActions.UpdateDataGrid(connection, "Teachers", allTeachersTable);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
}
private void assingn_Click(object sender, EventArgs e)
{
if (group.Rows.Count > 1 && teacher.Rows.Count > 1)
{
if (teacher.Rows.Count > 2)
{
CommonFormActions.ShowMessage("Notice", "PLease selcet only one teacher!");
return;
}
if (group.Rows.Count > 2)
{
CommonFormActions.ShowMessage("Notice", "Please select only one group!");
return;
}
string assignGroupQuery = "UPDATE Groups SET PIN = @PIN WHERE [Id Group] = @IdGroup";
//Parameters
SqlParameter idGroup = new SqlParameter("@IdGroup", SqlDbType.Int);
idGroup.Value = group.Rows[0].Cells[0].Value;
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = teacher.Rows[0].Cells[0].Value;
SqlParameter[] parameters = new SqlParameter[2] {
idGroup, PIN
};
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
SqlCommand assignGroupCommand = CommonFormActions.CreateCommand(connection, assignGroupQuery, parameters);
using (connection)
{
connection.Open();
SqlDataReader reader = assignGroupCommand.ExecuteReader();
reader.Close();
connection.Close();
}
CommonFormActions.ShowMessage("Success", "Group assigned successfully.");
}
else
{
CommonFormActions.ShowMessage("Notice", "Please select teacher and group!");
}
}
private void findTeacher_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
try
{
string findATeacherQuery = "SELECT * FROM Teachers WHERE PIN = @PIN OR Name = @Name OR [Family Name] = @FName OR Address = @Address";
//Parameters
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = teacherPIN.Text;
SqlParameter name = new SqlParameter("@Name", SqlDbType.NVarChar);
name.Value = teacherName.Text;
SqlParameter fName = new SqlParameter("@FName", SqlDbType.NVarChar);
fName.Value = teacherFName.Text;
SqlParameter address = new SqlParameter("@Address", SqlDbType.NVarChar);
address.Value = teacherAddress.Text;
SqlParameter[] parameters = new SqlParameter[4] {
PIN, name, fName, address
};
SqlCommand findTeacherCommand = CommonFormActions.CreateCommand(connection, findATeacherQuery, parameters);
DataTable teacherDataTable = CommonFormActions.GetDataTable(connection, findTeacherCommand);
teacher.DataSource = teacherDataTable;
if (teacher.Rows.Count == 2)
{
SqlConnection connectionShowEducation = CommonFormActions.CreateConnection("LanguageSchool");
string currentPIN = teacher.Rows[0].Cells[0].Value.ToString();
TeacherShowEducation(connectionShowEducation, currentPIN, teacherEducation);
}
else
{
CommonFormActions.ShowMessage("Notice", "Please select a teacher to show education!");
teacherEducation.DataSource = null;
}
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
private void enroll_Click(object sender, EventArgs e)
{
if (group.Rows.Count > 1 && student.Rows.Count > 1)
{
if (student.Rows.Count > 2)
{
CommonFormActions.ShowMessage("Notice", "PLease selcet only one student!");
return;
}
if (group.Rows.Count > 2)
{
CommonFormActions.ShowMessage("Notice", "Please select only one group!");
return;
}
string enrolStudentQuery = "INSERT INTO [Students-Groups] ([Id Student], [Id Group]) VALUES(@IdStudent, @IdGroup)";
//Parameters
SqlParameter idStudent = new SqlParameter("@IdStudent", SqlDbType.Int);
idStudent.Value = student.Rows[0].Cells[0].Value;
SqlParameter idGroup = new SqlParameter("@IdGroup", SqlDbType.Int);
idGroup.Value = group.Rows[0].Cells[0].Value;
SqlParameter[] parameters = new SqlParameter[2] {
idStudent, idGroup
};
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
SqlCommand enrollStudentCommand = CommonFormActions.CreateCommand(connection, enrolStudentQuery, parameters);
using (connection)
{
SqlDataReader reader = enrollStudentCommand.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "Student enrolled successfully");
}
else
{
CommonFormActions.ShowMessage("Notice", "Please select student and group!");
}
}
private void updStudent_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
if (allStudentsTable.CurrentRow.Index > -1)
{
string query = "UPDATE Students SET Name = @Name, [Family Name] = @FName, Address = @Address WHERE [Id Student] = @IdStudent";
SqlParameter studentId = new SqlParameter("@IdStudent", SqlDbType.Int);
studentId.Value = allStudentsTable.CurrentRow.Cells[0].Value;
SqlParameter studentName = new SqlParameter("@Name", SqlDbType.NVarChar);
studentName.Value = allStudentsTable.CurrentRow.Cells[1].Value;
SqlParameter studentFamilyName = new SqlParameter("@FName", SqlDbType.NVarChar);
studentFamilyName.Value = allStudentsTable.CurrentRow.Cells[2].Value;
SqlParameter studentAddress = new SqlParameter("@Address", SqlDbType.NVarChar);
studentAddress.Value = allStudentsTable.CurrentRow.Cells[3].Value;
SqlParameter[] parameters = new SqlParameter[4] {
studentId, studentName, studentFamilyName, studentAddress
};
SqlCommand command = CommonFormActions.CreateCommand(connection, query, parameters);
try
{
using (connection)
{
SqlDataReader reader = command.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "Student updated successfully.");
CommonFormActions.UpdateDataGrid(connection, "Students", allStudentsTable);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
}
private void addTeacher_Click(object sender, EventArgs e)
{
if (allTeachersTable.CurrentRow.Index > -1)
{
string query = "INSERT INTO Teachers (PIN, Name, [Family Name], Address) VALUES(@PIN, @Name, @FName, @Address)";
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = allTeachersTable.CurrentRow.Cells[0].Value;
SqlParameter teacherName = new SqlParameter("@Name", SqlDbType.NVarChar);
teacherName.Value = allTeachersTable.CurrentRow.Cells[1].Value;
SqlParameter teacherFamilyName = new SqlParameter("@FName", SqlDbType.NVarChar);
teacherFamilyName.Value = allTeachersTable.CurrentRow.Cells[2].Value;
SqlParameter teacherAddress = new SqlParameter("@Address", SqlDbType.NVarChar);
teacherAddress.Value = allTeachersTable.CurrentRow.Cells[3].Value;
SqlParameter[] parameters = new SqlParameter[4] {
PIN, teacherName, teacherFamilyName, teacherAddress
};
SqlCommand command = CommonFormActions.CreateCommand(connection, query, parameters);
try
{
using (connection)
{
SqlDataReader reader = command.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "New teacher added successfully.");
CommonFormActions.UpdateDataGrid(connection, "Teachers", allTeachersTable);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
}
private void addStudent_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
if (allStudentsTable.CurrentRow.Index > -1)
{
string query = "INSERT INTO Students (Name, [Family Name], Address) VALUES(@Name, @FName, @Address)";
SqlParameter studentName = new SqlParameter("@Name", SqlDbType.NVarChar);
studentName.Value = allStudentsTable.CurrentRow.Cells[1].Value;
SqlParameter studentFamilyName = new SqlParameter("@FName", SqlDbType.NVarChar);
studentFamilyName.Value = allStudentsTable.CurrentRow.Cells[2].Value;
SqlParameter studentAddress = new SqlParameter("@Address", SqlDbType.NVarChar);
studentAddress.Value = allStudentsTable.CurrentRow.Cells[3].Value;
SqlParameter[] parameters = new SqlParameter[3] {
studentName, studentFamilyName, studentAddress
};
SqlCommand command = CommonFormActions.CreateCommand(connection, query, parameters);
try
{
using (connection)
{
SqlDataReader reader = command.ExecuteReader();
reader.Close();
}
CommonFormActions.ShowMessage("Success", "New student added successfully.");
CommonFormActions.UpdateDataGrid(connection, "Students", allStudentsTable);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
}
private void addEducation_Click(object sender, EventArgs e)
{
if (teacher.Rows.Count > 2)
{
CommonFormActions.ShowMessage("Notice", "Please select only one teacher before add education!");
}
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
string addEducation = "INSERT INTO [Teachers-EducationFields] (PIN, Code, Certificate) VALUES (@PIN, @Code, @Certificate)";
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = teacher.Rows[0].Cells[0].Value.ToString();
SqlParameter code = new SqlParameter("@Code", SqlDbType.NVarChar);
string educationField = educationFields.Text;
if (educationField != "")
{
string codeStr = GetCode(educationField);
if (codeStr != "")
{
code.Value = codeStr;
}
else
{
CommonFormActions.ShowMessage("Notice", "Please select corect education field!");
return;
}
}
else
{
CommonFormActions.ShowMessage("Notice", "Please select education field first!");
return;
}
SqlParameter certificate = new SqlParameter("@Certificate", SqlDbType.NVarChar);
if (certificate.Value != null)
{
certificate.Value = certificate.Value;
}
else
{
certificate.Value = "";
}
SqlParameter[] parameters = new SqlParameter[3] {
PIN, code, certificate
};
SqlCommand addEducationCommand = CommonFormActions.CreateCommand(connection, addEducation, parameters);
try
{
using (connection)
{
connection.Open();
SqlDataReader reader = addEducationCommand.ExecuteReader();
reader.Close();
connection.Close();
SqlConnection connectionShowEducation = CommonFormActions.CreateConnection("LanguageSchool");
string currentPIN = teacher.Rows[0].Cells[0].Value.ToString();
TeacherShowEducation(connectionShowEducation, currentPIN, teacherEducation);
CommonFormActions.ShowMessage("Success", "Education information added successfully.");
}
}
catch (SqlException ol)
{
if (ol.Number == 2627)
{
CommonFormActions.ShowMessage("Notice", "Cannot finish because of duplicate information.");
return;
}
MessageBox.Show(ol.Number.ToString());
connection.Close();
}
}