private void FillDataGrid()
{
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'CoursePassedView'; " ;
string coursesQuery = @"
select *
from CoursePassedView " ;
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = query;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
columns.Add(reader.GetString(0));
}
connection.Close();
}
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = coursesQuery;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5), reader.GetDateTime(6), reader.GetInt32(7));
}
connection.Close();
}
}
private void button2_Click_1(object sender, EventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
int index = dataGridView1.CurrentRow.Index;
string query = @"insert Обучение(teacherId,coursId,coursePassed)
values({0},{1},0);" ;
DbWorker dbWorker = new DbWorker();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(query, _user.UserId, dataGridView1[0, index].Value.ToString());
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
dataGridView1.Rows.Clear();
FillDataGrid();
MessageBox.Show("Курс выбран");
}
else
{
MessageBox.Show("Курс не выбран");
}
}
private void RemoveUserEvent(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Пользователи
where id = {0};";
try
{
if (e.RowIndex >= 0)
{
/* queryComand = string.Format(queryComand,
* dataGridView2[0, e.RowIndex + 1].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
private void AvtorizationChangeEvent(object sender, DataGridViewCellEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" update Авторизация
set login=N'{0}', password = N'{1}'
where id = {2};";
try
{
if (e.RowIndex >= 0)
{
/* queryComand = string.Format(queryComand,
* dataGridView1[1, e.RowIndex].Value.ToString(),
* dataGridView1[2, e.RowIndex].Value.ToString(),
* dataGridView1[0, e.RowIndex].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
int index = dataGridView1.CurrentRow.Index;
bool created = MakeApproval(dataGridView1[0, index].Value.ToString());
if (created)
{
string query = @"update Обучение
set coursePassed = 1
where id = {0};" ;
DbWorker dbWorker = new DbWorker();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(query, dataGridView1[0, index].Value.ToString());
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
dataGridView1.Refresh();
this.courseNotPassedViewTableAdapter.Fill(this.coursesDataSet.CourseNotPassedView);
MessageBox.Show("Курс одобрен");
}
}
else
{
MessageBox.Show("Курс не выбран");
}
}
private void UserChangeEvent(object sender, DataGridViewCellEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" update Пользователи
set firstName= N'{0}',
midleName = N'{1}',
lastName = N'{2}',
dateOfBirth= '{3}',
authId = {4},
roleId= {5}
where id = {6};";
if (e.RowIndex >= 0)
{
queryComand = string.Format(queryComand,
dataGridView2[1, e.RowIndex].Value.ToString(),
dataGridView2[2, e.RowIndex].Value.ToString(),
dataGridView2[3, e.RowIndex].Value.ToString(),
dataGridView2[4, e.RowIndex].Value.ToString(),
dataGridView2[5, e.RowIndex].Value.ToString(),
dataGridView2[6, e.RowIndex].Value.ToString(),
dataGridView2[0, e.RowIndex].Value.ToString());
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
private void AutDataDeleted(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Авторизация
where id = {0};";
if (e.RowIndex >= 0)
{
/*queryComand = string.Format(queryComand,
* dataGridView1[0, e.RowIndex + 1].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
private void RowClickEvent(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex <= dataGridView2.RowCount && e.RowIndex >= 0)
{
firstName.Text = dataGridView2[1, e.RowIndex].Value.ToString();
midleName.Text = dataGridView2[2, e.RowIndex].Value.ToString();
lastName.Text = dataGridView2[3, e.RowIndex].Value.ToString();
dateOfBirth.Text = dataGridView2[4, e.RowIndex].Value.ToString();
login.Text = dataGridView2[5, e.RowIndex].Value.ToString();
password.Text = dataGridView2[6, e.RowIndex].Value.ToString();
//role.SelectedItem = dataGridView2[7, e.RowIndex].Value.ToString();
role.SelectedIndex = role.FindString(dataGridView2[7, e.RowIndex].Value.ToString());
}
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
List <string> roles = new List <string>();
}
public bool ExecQuery(string queryComand)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
try
{
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
private void RowSelectedEvent(object sender, EventArgs e)
{
DataGridViewCellEventArgs arg = (DataGridViewCellEventArgs)e;
firstName.Text = dataGridView2[1, arg.RowIndex].Value.ToString();
midleName.Text = dataGridView2[2, arg.RowIndex].Value.ToString();
lastName.Text = dataGridView2[3, arg.RowIndex].Value.ToString();
dateOfBirth.Text = dataGridView2[4, arg.RowIndex].Value.ToString();
login.Text = dataGridView2[5, arg.RowIndex].Value.ToString();
password.Text = dataGridView2[5, arg.RowIndex].Value.ToString();
role.SelectedItem = dataGridView2[6, arg.RowIndex].Value.ToString();
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
List <string> roles = new List <string>();
try
{
command.CommandText = "select roleName from Роли;";
connection.Open();
command.Connection = connection;
reader = command.ExecuteReaderAsync().GetAwaiter().GetResult();
while (reader.HasRows)
{
reader.Read();
roles.Add(reader.GetString(1));
}
connection.Close();
role.Items.AddRange(roles.ToArray());
}
catch (Exception ex)
{
}
}
private void AuthDataRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Авторизация
where id = {0};";
try
{
if (e.RowIndex >= 0)
{
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
private void FillDataGrid()
{
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TeacherCoursesView'; " ;
string coursesQuery = @"
select distinct k.id,k.coursName, k.courseVolume, f.educationType,v.coursName as 'courseTypeName' --,o.teacherId
from Курсы k
left join Обучение o
on o.coursId = k.id
left join ФормаОбучения f
on k.educationFormId = f.id
left join ВидКурса v
on k.coursTypeId = v.id
where (o.teacherId != {0}
or o.teacherId is null)
and k.id in (
select id from Курсы
except
select id from Курсы
where id in
(
select coursId
from Обучение
where teacherId = {0}
)
) and k.endDate > '{1}'" ;
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>()
{
"Код",
"Название курса",
"Объем курса",
"Форма обучения",
"Вид курса"
};
/*using (SqlConnection connection = dbWorker.GetConnection())
* using (SqlCommand command = new SqlCommand())
* {
* command.Connection = connection;
* command.CommandText = query;
* connection.Open();
* SqlDataReader reader = command.ExecuteReader();
* while (reader.Read())
* {
* columns.Add(reader.GetString(0));
* }
* connection.Close();
* }*/
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(coursesQuery, _user.UserId, DateTime.Now);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader["teacherId"] );
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4));
}
connection.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
int teacherIndex = dataGridView2.CurrentRow.Index;
int courseIndex = dataGridView3.CurrentRow.Index;
string query = @"Insert_Education";
/* @"insert Обучение(teacherId,coursId,coursePassed)
* values({0},{1},1);";*/
if (teacherIndex < 0)
{
MessageBox.Show("Не выбран преподаватель");
return;
}
if (courseIndex < 0)
{
MessageBox.Show("Не выбран курс");
return;
}
string id = null;
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = query;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@teacherId", dataGridView2[0, teacherIndex].Value.ToString());
command.Parameters.AddWithValue("@coursId", dataGridView3[0, courseIndex].Value.ToString());
command.Parameters.AddWithValue("@coursPassed", 1);
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
id = reader.GetValue(0).ToString();
}
connection.Close();
}
bool created = MakeTemplate(dataGridView3[0, courseIndex].Value.ToString(), dataGridView2[0, teacherIndex].Value.ToString());
if (!created)
{
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = $"delete from Обучение where id = {id}";
connection.Open();
command.ExecuteNonQuery();
}
}
}
private void FillDataGridWithFilter()
{
dataGridView1.Rows.Clear();
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'CoursePassedView'; " ;
string coursesQuery = @"select * from CoursePassedView ";
int filterParamCount = 0;
if (!string.IsNullOrEmpty(firstName.Text) &&
!string.IsNullOrWhiteSpace(firstName.Text) ||
!string.IsNullOrEmpty(midleName.Text) &&
!string.IsNullOrWhiteSpace(midleName.Text) ||
!string.IsNullOrEmpty(lastName.Text) &&
!string.IsNullOrWhiteSpace(lastName.Text) ||
!string.IsNullOrEmpty(courseVolume.Text) &&
!string.IsNullOrWhiteSpace(courseVolume.Text) ||
!string.IsNullOrEmpty(courseType.Text) &&
!string.IsNullOrWhiteSpace(courseType.Text) ||
!string.IsNullOrEmpty(courseName.Text) &&
!string.IsNullOrWhiteSpace(courseName.Text)
)
{
coursesQuery += " where ";
filterParamCount++;
}
if (!string.IsNullOrEmpty(firstName.Text) &&
!string.IsNullOrWhiteSpace(firstName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("firstName = N'{0}'", firstName.Text);
}
if (!string.IsNullOrEmpty(midleName.Text) &&
!string.IsNullOrWhiteSpace(midleName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("midleName = N'{0}'", midleName.Text);
}
if (!string.IsNullOrEmpty(lastName.Text) &&
!string.IsNullOrWhiteSpace(lastName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("lastName = N'{0}'", lastName.Text);
}
if (!string.IsNullOrEmpty(courseVolume.Text) &&
!string.IsNullOrWhiteSpace(courseVolume.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("courseVolume = '{0}'", courseVolume.Text);
}
if (!string.IsNullOrEmpty(courseName.Text) &&
!string.IsNullOrWhiteSpace(courseName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("coursName = N'{0}'", courseName.Text);
}
if (!string.IsNullOrEmpty(courseType.Text) &&
!string.IsNullOrWhiteSpace(courseType.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("coursTypeName = N'{0}'", courseType.Text);
}
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>()
{
"Код",
"Имя",
"Отчество",
"Фамилия",
"Название курса",
"Дата начала курса",
"Дата окончания курса",
"Объем курса",
"Вид курса",
"Форма обучения"
};
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = coursesQuery;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
}
connection.Close();
}
}