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(); } }