private void findGroup_Click(object sender, EventArgs e)
{
string findGroupQuery = "SELECT * FROM Groups WHERE [Start Date] >= @StartDate AND " +
"Schedule = @Shedule AND [Start Time] >= @StartTime AND PIN IS NULL";
//Parameters
SqlParameter date = new SqlParameter("@StartDate", SqlDbType.Date);
date.Value = startDate.Value.ToShortDateString();
SqlParameter weekSchedule = new SqlParameter("@Shedule", SqlDbType.NVarChar);
weekSchedule.Value = schedule.Text;
SqlParameter time = new SqlParameter("@StartTime", SqlDbType.Time);
time.Value = startTime.Value.TimeOfDay;
SqlParameter[] parameters = new SqlParameter[3] {
date, weekSchedule, time
};
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
SqlCommand findGroupCommad = CommonFormActions.CreateCommand(connection, findGroupQuery, parameters);
DataTable groups = CommonFormActions.GetDataTable(connection, findGroupCommad);
group.DataSource = groups;
}
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;
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
private void findStudent_Click(object sender, EventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
try
{
string findStudentQuery = "SELECT * FROM Students WHERE Name=@Name OR [Family Name]=@FName OR Address=@Address";
SqlParameter name = new SqlParameter("@Name", SqlDbType.NVarChar);
name.Value = studentName.Text;
SqlParameter fName = new SqlParameter("@FName", SqlDbType.NVarChar);
fName.Value = studentFName.Text;
SqlParameter address = new SqlParameter("@Address", SqlDbType.NVarChar);
address.Value = studentAddress.Text;
SqlParameter[] parameters = new SqlParameter[3] {
name, fName, address
};
SqlCommand findStudentCommand = CommonFormActions.CreateCommand(connection, findStudentQuery, parameters);
student.DataSource = CommonFormActions.GetDataTable(connection, findStudentCommand);
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connection.Close();
}
}
private void teachersDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
int rowIndex = teachersDataGridView.CurrentRow.Index;
string currentTeacherPIN = teachersDataGridView.Rows[rowIndex].Cells[0].Value.ToString();
if (currentTeacherPIN != "")
{
SqlConnection connectionForTeacher = CommonFormActions.CreateConnection("LanguageSchool");
string selectTeacher = "SELECT * FROM Teachers WHERE PIN = @PIN";
SqlParameter selectTeacherPIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
selectTeacherPIN.Value = currentTeacherPIN;
SqlParameter[] selectTeacherParameters = new SqlParameter[1] {
selectTeacherPIN
};
SqlCommand selectStudentCommand = CommonFormActions.CreateCommand(connectionForTeacher, selectTeacher, selectTeacherParameters);
DataTable selectedTeacherDataTable = CommonFormActions.GetDataTable(connectionForTeacher, selectStudentCommand);
teachersDataGridView.DataSource = selectedTeacherDataTable;
SqlConnection connectionForEducation = CommonFormActions.CreateConnection("LanguageSchool");
string selectEducation = "SELECT PIN, Code, Certificate FROM [Teachers-EducationFields] WHERE PIN = @PIN";
SqlParameter selectEducationPIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
selectEducationPIN.Value = currentTeacherPIN;
SqlParameter[] selectEducationParameters = new SqlParameter[1] {
selectEducationPIN
};
SqlCommand selectEducationCommand = CommonFormActions.CreateCommand(connectionForEducation, selectEducation, selectEducationParameters);
DataTable selectedEducationDataTable = CommonFormActions.GetDataTable(connectionForEducation, selectEducationCommand);
teachers_EducationFieldsDataGridView.DataSource = selectedEducationDataTable;
}
}
private void searchTerm_KeyUp(object sender, KeyEventArgs e)
{
SqlConnection connectionTeachersSearch = CommonFormActions.CreateConnection("LanguageSchool");
try
{
string selectTeachers = "SELECT * FROM Teachers WHERE PIN LIKE '%' + @Term + '%' or Name LIKE '%' + @Term + '%' or [Family Name] LIKE '%' + @Term + '%' or Address LIKE '%' + @Term + '%'";
SqlParameter teachersTerm = new SqlParameter("@Term", SqlDbType.NVarChar);
teachersTerm.Value = searchTerm.Text;
SqlParameter[] parameters = new SqlParameter[1] {
teachersTerm
};
DataTable personalDataTable = new DataTable();
SqlCommand command = CommonFormActions.CreateCommand(connectionTeachersSearch, selectTeachers, parameters);
using (connectionTeachersSearch)
{
connectionTeachersSearch.Open();
command.ExecuteNonQuery();
personalDataTable = CommonFormActions.GetDataTable(connectionTeachersSearch, command);
connectionTeachersSearch.Close();
}
teachersDataGridView.DataSource = personalDataTable;
string selectTeachersEducation = "";
DataTable educationDataTable = new DataTable();
for (int row = 0; row < teachersDataGridView.Rows.Count - 1; row++)
{
SqlConnection connectionEducationSearch = CommonFormActions.CreateConnection("LanguageSchool");
string currentPIN = teachersDataGridView.Rows[row].Cells[0].Value.ToString();
selectTeachersEducation = "SELECT tef.PIN, tef.Code, tef.Certificate FROM [Teachers-EducationFields] as tef WHERE tef.PIN = @PIN";
SqlParameter teachersEducParam = new SqlParameter("@PIN", SqlDbType.NVarChar);
teachersEducParam.Value = currentPIN;
SqlParameter[] parametersEducation = new SqlParameter[1] {
teachersEducParam
};
SqlCommand commandEduc = CommonFormActions.CreateCommand(connectionEducationSearch, selectTeachersEducation, parametersEducation);
using (connectionEducationSearch)
{
DataTable currentResults = CommonFormActions.GetDataTable(connectionEducationSearch, commandEduc);
educationDataTable.Merge(currentResults, true);
}
}
teachers_EducationFieldsDataGridView.DataSource = educationDataTable;
}
catch (SqlException ol)
{
MessageBox.Show(ol.Message.ToString());
connectionTeachersSearch.Close();
}
}
private void TeacherShowEducation(SqlConnection connection, string PIN, DataGridView table)
{
string showEducationQuery = "SELECT ef.Name, tef.Certificate FROM " +
"EducationFields as ef INNER JOIN [Teachers-EducationFields] as tef ON " +
"ef.Code = tef.Code WHERE tef.PIN = @PIN";
SqlParameter PINParam = new SqlParameter("@PIN", SqlDbType.NVarChar);
PINParam.Value = PIN;
SqlParameter[] parameters = new SqlParameter[1] {
PINParam
};
SqlCommand showEducationCommand = CommonFormActions.CreateCommand(connection, showEducationQuery, parameters);
DataTable showEducationDataTable = CommonFormActions.GetDataTable(connection, showEducationCommand);
table.DataSource = showEducationDataTable;
}
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 teacher_CellClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
int rowIndex = teacher.CurrentRow.Index;
string currentTeacherPIN = teacher.Rows[rowIndex].Cells[0].Value.ToString();
if (currentTeacherPIN != "")
{
string selectStudentQuery = "SELECT * FROM Teachers WHERE [PIN]=@PIN";
SqlParameter PIN = new SqlParameter("@PIN", SqlDbType.NVarChar);
PIN.Value = currentTeacherPIN;
SqlParameter[] parameters = new SqlParameter[1] {
PIN
};
SqlCommand selectTeacherCommand = CommonFormActions.CreateCommand(connection, selectStudentQuery, parameters);
DataTable selectedStudent = CommonFormActions.GetDataTable(connection, selectTeacherCommand);
teacher.DataSource = selectedStudent;
}
}
private void group_CellClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
int rowIndex = group.CurrentRow.Index;
int currentGroupId = 0;
bool isId = int.TryParse(group.Rows[rowIndex].Cells[0].Value.ToString(), out currentGroupId);
if (isId)
{
string selectGroupQuery = "SELECT * FROM Groups WHERE [Id Group]=@IdGroup";
SqlParameter groupId = new SqlParameter("@IdGroup", SqlDbType.Int);
groupId.Value = currentGroupId;
SqlParameter[] parameters = new SqlParameter[1] {
groupId
};
SqlCommand selectGroupCommand = CommonFormActions.CreateCommand(connection, selectGroupQuery, parameters);
DataTable selectedGroup = CommonFormActions.GetDataTable(connection, selectGroupCommand);
group.DataSource = selectedGroup;
}
}
private void student_CellClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection connection = CommonFormActions.CreateConnection("LanguageSchool");
int rowIndex = student.CurrentRow.Index;
string currentStudentId = student.Rows[rowIndex].Cells[0].Value.ToString();
if (currentStudentId != "")
{
string selectStudentQuery = "SELECT * FROM Students WHERE [Id Student]=@IdStudent";
SqlParameter studentId = new SqlParameter("@IdStudent", SqlDbType.NVarChar);
studentId.Value = currentStudentId;
SqlParameter[] parameters = new SqlParameter[1] {
studentId
};
SqlCommand selectStudentCommand = CommonFormActions.CreateCommand(connection, selectStudentQuery, parameters);
using (connection)
{
DataTable selectedStudent = CommonFormActions.GetDataTable(connection, selectStudentCommand);
student.DataSource = selectedStudent;
}
}
}