public void CheckCorrectStudentData() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT ID FROM STUDENT WHERE ID_group = @id_group AND FIO LIKE @fio"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.Add(new SQLiteParameter("@id_group", DbType.Int64)); command.Parameters[0].Value = id_group; command.Parameters.Add(new SQLiteParameter("@fio", DbType.String)); for (int i = 0; i < dataGrid.Rows.Count; i++) { command.Parameters[1].Value = "%" + dataGrid[0, i].Value.ToString() + "%"; SQLiteDataReader reader = command.ExecuteReader(); if (reader.Read()) { dataGrid.Rows[i].Tag = Convert.ToInt64(reader[0]); } else { dataGrid.Rows[i].Tag = -1; } reader.Close(); } connection.Close(); }
private bool Save() { string[] columns; object[] paramValue; string name = textBoxName.Text.Trim(); string kod = textBoxKod.Text.Trim(); if (id_specialization == -1) { columns = new string[] { "Name", "KOD", "ID_SPECIALTY" }; paramValue = new object[] { name, kod, kodSpecialty }; } else { columns = new string[] { "Name", "KOD", "ID_SPECIALTY", "ID_SPECIALIZATION" }; paramValue = new object[] { name, kod, kodSpecialty, id_specialization }; } int rowid = SQLiteManager.InsertValue("COMPETENCE", columns, paramValue); if (rowid == -1) { return(false); } parentForm.AddCompetenceInDataGrid(name, kod, rowid, kodSpecialty, id_specialization); return(true); }
private void InitialRowsDataGridAssessment() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sql = "SELECT ID_competence, Assessment FROM Assessment_Competence WHERE ID_student = @id_stud"; SQLiteCommand command = new SQLiteCommand(sql, connection); command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64)); for (int i = 0; i < dataGridViewAssessment.Rows.Count; i++) { command.Parameters[0].Value = studentID[i]; dataGridViewAssessment.Rows[i].Tag = studentID[i]; SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { int index_column = GetIndexColumnForIdCompetence(Convert.ToInt64(reader[0])); if (index_column == -1) { continue; } dataGridViewAssessment[index_column, i].Value = Convert.ToDouble(reader[1]); } reader.Close(); } connection.Close(); }
/// <summary> /// Считывает все страницы эксель файла с данными об оценках за семестер для учебных групп. /// Возвращает массив с информацией о каждой группе. /// </summary> /// <returns></returns> public RatingGroup[] ReadAllTable() { int countSheets = getCountSheets(); if (countSheets < 1) { return(null); } RatingGroup[] result = new RatingGroup[countSheets]; for (int i = 0; i < countSheets; i++) { //String.Equals(getNameSheet().Trim(), "7133", StringComparison.OrdinalIgnoreCase) string nameGroup = getNameSheet().Trim(); if (!SQLiteManager.IsExistValue("StudyGroup", "NumGroup", nameGroup)) { // указанная группа не существует в БД, поэтому ничего считывать не будем result[i] = new RatingGroup(null, false, false, nameGroup); } else { DataTable table = ReadTable(); if (table == null) { result[i] = new RatingGroup(null, true, false, nameGroup); } else { result[i] = new RatingGroup(table, true, true, nameGroup); } } NextSheet(); } return(result); }
private void CheckCorrectData() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT ID, NAME FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.Add(new SQLiteParameter("@kod", DbType.String)); command.Parameters.AddWithValue("@id_spec", kod_specialty); SQLiteDataReader reader; for (int i = 0; i < dataGrid.Rows.Count; i++) { command.Parameters[0].Value = dataGrid.Rows[i].Cells[0].Value.ToString(); reader = command.ExecuteReader(); if (reader.Read()) { // если в БД есть такая компетенция dataGrid.Rows[i].Tag = Convert.ToInt64(reader[0]); dataGrid.Rows[i].Cells[1].Value = reader[1].ToString(); } else { // компетенция не найдена в БД dataGrid.Rows[i].Tag = -1; dataGrid.Rows[i].DefaultCellStyle.BackColor = Color.Yellow; } reader.Close(); } connection.Close(); }
/// <summary> /// Загружает данные для команды. Команда должна быть без подключения.Данные, возвращаемые /// командой, должны содержать столбцы, определённые при создании объекта. /// </summary> /// <param name="command"></param> /// <returns>true - если данные загружены успешно, иначе false</returns> public bool LoadData(SQLiteCommand command) { try { table.Rows.Clear(); SQLiteConnection connection = SQLiteManager.CreateConnection(); command.Connection = connection; SQLiteDataReader reader = command.ExecuteReader(); int countColumn = table.Columns.Count; while (reader.Read()) { DataRow row = table.NewRow(); for (int i = 0; i < countColumn; i++) { row[columns[i]] = reader[columns[i]].ToString(); } table.Rows.Add(row); } reader.Close(); connection.Close(); return(true); } catch { return(false); } }
private bool SaveData() { try { string sqlInsert = "INSERT INTO WorkProgramm(NameDiscipline, ID_specialization, Semester, FormReport) VALUES (@name, @id_spec, @sem, @report)"; SQLiteCommand command = new SQLiteCommand(sqlInsert); command.Parameters.Add("@name", DbType.String); command.Parameters.Add("@id_spec", DbType.Int64); command.Parameters["@id_spec"].Value = managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID"); command.Parameters.Add("@sem", DbType.String); command.Parameters.Add("@report", DbType.String); SQLiteConnection connection = SQLiteManager.CreateConnection(); command.Connection = connection; foreach (DataGridViewRow row in dataGridViewWorkPlan.Rows) { command.Parameters[0].Value = row.Cells[0].Value; command.Parameters[2].Value = row.Cells[1].Value; command.Parameters[3].Value = row.Cells[2].Value; command.ExecuteNonQuery(); } connection.Close(); return(true); } catch (Exception e) { MessageBox.Show(e.Message, "Ошибка"); return(false); } }
private void CheckCorrectDataForEdit(string value_kod, int index_row) { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT ID, NAME FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.AddWithValue("@kod", value_kod); command.Parameters.AddWithValue("@id_spec", kod_specialty); SQLiteDataReader reader; reader = command.ExecuteReader(); if (reader.Read()) { // если в БД есть такая компетенция dataGrid.Rows[index_row].Tag = Convert.ToInt64(reader[0]); dataGrid.Rows[index_row].Cells[1].Value = reader[1].ToString(); dataGrid.Rows[index_row].DefaultCellStyle.BackColor = Color.White; } else { // компетенция не найдена в БД dataGrid.Rows[index_row].Tag = -1; dataGrid.Rows[index_row].DefaultCellStyle.BackColor = Color.Yellow; } reader.Close(); connection.Close(); }
public void CheckCorrectDisciplineData() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT ID FROM WorkProgramm WHERE ID_specialization = @id_spec AND NameDiscipline LIKE @name"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.Add(new SQLiteParameter("@id_spec", DbType.Int64)); command.Parameters[0].Value = id_specialization; command.Parameters.Add(new SQLiteParameter("@name", DbType.String)); for (int i = 1; i < dataGrid.Columns.Count; i++) { command.Parameters[1].Value = "%" + dataGrid.Columns[i].HeaderText + "%"; SQLiteDataReader reader = command.ExecuteReader(); if (reader.Read()) { dataGrid.Columns[i].Tag = Convert.ToInt64(reader[0]); } else { dataGrid.Columns[i].Tag = -1; } reader.Close(); } connection.Close(); }
private void buttonSave_Click(object sender, EventArgs e) { if (comboBoxSpecialty.SelectedIndex == -1) { MessageBox.Show("Сначала выберите специальность группы", "Уведомление"); return; } if (comboBoxSpecialization.SelectedIndex == -1) { MessageBox.Show("Сначала выберите специализацию группы", "Уведомление"); return; } if (comboBoxNumGroup.SelectedIndex == -1) { MessageBox.Show("Сначала выберите номер группы", "Уведомление"); return; } long id_specialization = Convert.ToInt64(managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID")); string numGroup = comboBoxNumGroup.Items[comboBoxNumGroup.SelectedIndex].ToString(); int kurs = Program.GetNumKurs(numGroup); if (SQLiteManager.IsExistValue("StudyGroup", "NumGroup", numGroup)) { MessageBox.Show("В базе данных уже есть группа под таким номером.", "Ошибка"); return; } parentForm.AddNewGroup(id_specialization, numGroup, kurs); Close(); }
private void CheckCorrectData() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT EXISTS(SELECT ID FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod)"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.Add(new SQLiteParameter("@kod", DbType.String)); command.Parameters.AddWithValue("@id_spec", kod_specialty); SQLiteDataReader reader; for (int i = 0; i < dataGrid.Rows.Count; i++) { command.Parameters[0].Value = dataGrid.Rows[i].Cells[0].Value.ToString(); reader = command.ExecuteReader(); if (reader.Read() && reader[0].ToString() == "1") { // если в БД уже есть такая компетенция dataGrid.Rows[i].DefaultCellStyle.BackColor = Color.Yellow; // строка не готова к загрузке в базу dataGrid.Rows[i].Tag = false; } else { dataGrid.Rows[i].Tag = true; } reader.Close(); } connection.Close(); }
public void Save() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sql; if (IsSpecNeed) // если специализация должна быть указана { sql = "INSERT INTO COMPETENCE(NAME, KOD, ID_SPECIALTY, ID_SPECIALIZATION) VALUES(@name, @kod, @id_spec, @id_specialization)"; } else { sql = "INSERT INTO COMPETENCE(NAME, KOD, ID_SPECIALTY) VALUES(@name, @kod, @id_spec)"; } SQLiteCommand command = new SQLiteCommand(sql, connection); command.Parameters.Add(new SQLiteParameter("@name", DbType.String)); command.Parameters.Add(new SQLiteParameter("@kod", DbType.String)); command.Parameters.AddWithValue("@id_spec", kod_specialty); if (IsSpecNeed) { command.Parameters.AddWithValue("@id_specialization", Convert.ToInt64(managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID"))); } for (int i = 0; i < dataGrid.Rows.Count; i++) { command.Parameters[0].Value = dataGrid.Rows[i].Cells[1].Value.ToString(); command.Parameters[1].Value = dataGrid.Rows[i].Cells[0].Value.ToString(); command.ExecuteNonQuery(); } connection.Close(); }
/// <summary> /// Проверяет, есть ли для слушателей информация об их успеваемости за этот семестер. /// Если есть, то помечает соответствующих слушателей. /// </summary> private void CheckExistDataInDB() { IsExistDataUpdate = false; SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlExist = "SELECT EXISTS(SELECT * FROM Assessment_Discipline WHERE ID_student = @id_stud AND Semester = @sem)"; SQLiteCommand command = new SQLiteCommand(sqlExist, connection); command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@sem", DbType.Int64)); command.Parameters[1].Value = semester; SQLiteDataReader reader; for (int i = 0; i < dataGrid.Rows.Count; i++) { long id_stud = Convert.ToInt64(dataGrid.Rows[i].Tag); if (id_stud == -1) { continue; } command.Parameters[0].Value = id_stud; reader = command.ExecuteReader(); if (reader.Read() && reader[0].ToString() == "1") { // если в БД уже есть инфа о слушателе за семестер, то нельзя грузить эти данные dataGrid.Rows[i].Tag = 0; IsExistDataUpdate = true; } reader.Close(); } connection.Close(); }
/// <summary> /// Функция сохранения всей информации в БД. /// </summary> private bool SaveData() { try { // Добавляем новых пользователей в БД и записываем их ID в Таг строки for (int i = 0; i < dataGrid.Rows.Count; i++) { if (Convert.ToInt64(dataGrid.Rows[i].Tag) == -1) { int rowid = SQLiteManager.InsertValue("Student", new string[] { "FIO", "ID_group" }, new object[] { dataGrid[0, i].Value.ToString().Trim(), id_group }); dataGrid.Rows[i].Tag = rowid; } } // Проходим по каждому пользователю и добавляем в БД новую запись об оценке за семестер по каждой дисциплине SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlInsert = "INSERT INTO Assessment_Discipline(Assessment, ID_student, ID_discipline, Semester) VALUES(@ass, @id_stud, @id_disp, @sem)"; SQLiteCommand command = new SQLiteCommand(sqlInsert, connection); command.Parameters.Add(new SQLiteParameter("@ass", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64)); command.Parameters.AddWithValue("@sem", semester); for (int i = 0; i < dataGrid.Rows.Count; i++) { long id_student = Convert.ToInt64(dataGrid.Rows[i].Tag); if (id_student == -1) { continue; } for (int j = 1; j < dataGrid.Columns.Count; j++) { long id_disp = Convert.ToInt64(dataGrid.Columns[j].Tag); if (id_disp == -1) { continue; } int assessment; if (dataGrid[j, i].Value == null) { assessment = 0; } else { assessment = getValueAssessment(dataGrid[j, i].Value.ToString()); } command.Parameters[0].Value = assessment; command.Parameters[1].Value = id_student; command.Parameters[2].Value = id_disp; command.ExecuteNonQuery(); } } connection.Close(); return(true); } catch (Exception e) { MessageBox.Show("В процессе сохранения произошла ошибка.\n" + e.Message, "Ошибка"); return(false); } }
private void buttonSave_Click(object sender, EventArgs e) { if (String.IsNullOrWhiteSpace(textBox1.Text)) { MessageBox.Show("Сначала введите ФИО", "Уведомление"); return; } int rowid = SQLiteManager.InsertValue("Student", new string[] { "FIO", "ID_group" }, new object[] { textBox1.Text.Trim(), id_group }); SQLiteConnection connection = SQLiteManager.CreateConnection(); SQLiteCommand command = new SQLiteCommand("SELECT ID, Semester FROM WorkProgramm WHERE ID_specialization = @id_spec", connection); command.Parameters.AddWithValue("@id_spec", id_specialization); List <long> IDdiscipline = new List <long>(); ArrayList listSemesterDiscipline = new ArrayList(); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { string[] sem = reader[1].ToString().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries); if (sem == null || sem.Length == 0) { continue; } IDdiscipline.Add(Convert.ToInt64(reader[0])); int[] temp = new int[sem.Length]; for (int i = 0; i < sem.Length; i++) { temp[i] = Convert.ToInt32(sem[i]); } listSemesterDiscipline.Add(temp); } reader.Close(); if (listSemesterDiscipline.Count != 0) { command.Parameters.Clear(); command.CommandText = "INSERT INTO Assessment_Discipline(ID_student, ID_discipline, Semester) VALUES(@id_stud, @id_disp, @sem)"; command.Parameters.AddWithValue("@id_stud", rowid); command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@sem", DbType.Int32)); for (int i = 0; i < listSemesterDiscipline.Count; i++) { int[] sem = (int[])listSemesterDiscipline[i]; command.Parameters[1].Value = IDdiscipline[i]; for (int j = 0; j < sem.Length; j++) { if (sem[j] <= semester) { command.Parameters[2].Value = sem[j]; command.ExecuteNonQuery(); } } } } parentForm.AddNewStudent(rowid, id_group, textBox1.Text.Trim()); connection.Close(); Close(); }
/// <summary> /// Обновляет значения в ячейках таблицы оценок компетенций /// </summary> private void UpdateValueAssessmentInDataGrid() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlInsert = "INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES "; //SQLiteCommand command = new SQLiteCommand("INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES(@id_stud, @id_comp)", connection); //command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64)); //command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64)); SQLiteCommand command = new SQLiteCommand(); command.Connection = connection; for (int i = 0; i < dataGridViewAssessment.Rows.Count; i++) { double mean_value = 0; double count_notNull_value = 0; //command.Parameters[0].Value = Convert.ToInt64(dataGridViewAssessment.Rows[i].Tag); command.Parameters.Clear(); sqlInsert = "INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES "; command.Parameters.Add(new SQLiteParameter("@id_stud", Convert.ToInt64(dataGridViewAssessment.Rows[i].Tag))); int current_param = 0; for (int j = 2; j < dataGridViewAssessment.Columns.Count; j++) { if (dataGridViewAssessment[j, i].Tag == null) { dataGridViewAssessment[j, i].Tag = false; } if (dataGridViewAssessment[j, i].Value == null) { // значит в базе нет записи слушатель_компетенция, создадим её dataGridViewAssessment[j, i].Value = 0; string param_name = "@param" + (++current_param).ToString(); command.Parameters.Add(new SQLiteParameter(param_name, competenceID[j - 2])); sqlInsert += "(@id_stud, " + param_name + "),"; //command.Parameters[1].Value = competenceID[j - 2]; //command.ExecuteNonQuery(); } else if (Convert.ToDouble(dataGridViewAssessment[j, i].Value) != 0) { mean_value += Convert.ToDouble(dataGridViewAssessment[j, i].Value); count_notNull_value += 1.0d; } } if (current_param != 0) { sqlInsert = sqlInsert.Remove(sqlInsert.Length - 1); command.CommandText = sqlInsert; command.ExecuteNonQuery(); } if (count_notNull_value == 0) { dataGridViewAssessment[1, i].Value = 0; } else { dataGridViewAssessment[1, i].Value = string.Format("{0:N2}", mean_value / count_notNull_value); } } connection.Close(); }
/// <summary> /// Открывает соединение и загружает данные в дата грид. ВАЖНО - команда должна быть без соединения. /// </summary> /// <param name="command"></param> public void LoadData(SQLiteCommand command) { ClearDataGrid(); connection = SQLiteManager.CreateConnection(); command.Connection = connection; adapter = new SQLiteDataAdapter(command); adapter.Fill(dataSet, NameTable); comandBuilder = new SQLiteCommandBuilder(adapter); BindingSource bSource = new BindingSource(); bSource.DataSource = dataSet.Tables[0]; dataGridView.DataSource = bSource; }
private void InitialID() { string temp = SQLiteManager.GetValueFromDB("StudyGroup", "ID", "NumGroup", NameGroup); if (!String.IsNullOrWhiteSpace(temp)) { this.id_group = Convert.ToInt64(temp); } temp = SQLiteManager.GetValueFromDB("StudyGroup", "ID_specialization", "NumGroup", NameGroup); if (!String.IsNullOrWhiteSpace(temp)) { this.id_specialization = Convert.ToInt64(temp); } }
public void AddNewGroup(long id_specialization, string numGroup, long kurs) { int rowid = SQLiteManager.InsertValue("StudyGroup", new string[] { "ID_specialization", "NumGroup", "Kurs" }, new object[] { id_specialization, numGroup, kurs }); BindingSource bind = (BindingSource)dataGridViewGroup.DataSource; DataTable table = (DataTable)bind.DataSource; DataRow row = table.NewRow(); row["ID"] = Convert.ToInt64(rowid); row["ID_specialization"] = id_specialization; row["NumGroup"] = numGroup; row["Kurs"] = kurs; table.Rows.Add(row); row.AcceptChanges(); dataGridViewGroup.Refresh(); }
private void dataGridViewGroup_SelectionChanged_1(object sender, EventArgs e) { if (dataGridViewGroup.SelectedRows.Count == 0 || dataGridViewGroup.SelectedRows[0] == null) { return; } currentNumGroup = dataGridViewGroup.SelectedRows[0].Cells["NumGroup"].Value.ToString(); id_group_current = Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID"].Value); dataGridViewStudent.Tag = id_group_current; labelGroup.Text = "Группа " + currentNumGroup; string specName = SQLiteManager.GetValueFromDB("SPECIALIZATION", "Name", "ID", Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID_specialization"].Value)); id_specialization_current = Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID_specialization"].Value); labelSpec.Text = "Специализация: " + specName; LoadStudent(id_group_current); }
public void Save() { long id_discipline = Convert.ToInt64(managerDiscipline.GetCellValue(comboBoxDiscipline.SelectedIndex, "ID")); SQLiteConnection connection = SQLiteManager.CreateConnection(); string sql = "INSERT INTO Competence_Discipline(ID_competence, ID_discipline) VALUES(@id_comp, @id_disp)"; SQLiteCommand command = new SQLiteCommand(sql, connection); command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64)); command.Parameters.AddWithValue("@id_disp", id_discipline); for (int i = 0; i < dataGrid.Rows.Count; i++) { command.Parameters[0].Value = Convert.ToInt64(dataGrid.Rows[i].Tag); command.ExecuteNonQuery(); } connection.Close(); }
private bool Save() { string semester = ""; foreach (int index in checkedListBox1.CheckedIndices) { semester += (index + 1).ToString() + ";"; } int rowid = SQLiteManager.InsertValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization", "Semester" }, new object[] { textBoxDiscipline.Text, id_specialization, semester }); if (rowid == -1) { return(false); } parentForm.AddDisciplineInDataGrid(textBoxDiscipline.Text, rowid, id_specialization, semester); return(true); }
private void UpdateState() { bool flag = false; for (int i = 0; i < dataGridViewWorkPlan.Rows.Count; i++) { DataGridViewRow row = dataGridViewWorkPlan.Rows[i]; bool IsRowExistInDB = SQLiteManager.IsExistValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization" }, new string[] { row.Cells[0].Value.ToString(), managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID") }); if (IsRowExistInDB) { flag = true; row.DefaultCellStyle.BackColor = Color.Yellow; } } if (flag) { MessageBox.Show("Среди загружаемых дисциплин найдены дисциплины, уже загруженые в базу. Они подсвечены жёлтым.", "Уведомление"); } }
// Принимаем на вход значения айди и весов дисциплин в формировании компетенции и нормирует их, если это необходимо. // Вовзрашает обратно лист значений и айди с ЗНАЧИМЫМИ дисциплинами. private void CheckWeightCorrect(ref List <double> listWeight, ref List <long> listID, long id_competence) { double sum = 0; for (int i = 0; i < listWeight.Count; i++) { sum += listWeight[i]; } if (sum == 0) { // если сумма 0, значит нужно задать равные веса for (int k = 0; k < listWeight.Count; k++) { listWeight[k] = 1.0 / listWeight.Count; } // Сохраним эти значения в БД SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlSelectDiscipline = "UPDATE Competence_Discipline SET Weight = @w WHERE ID_competence = @id_comp AND ID_discipline = @id_disp"; SQLiteCommand command = new SQLiteCommand(sqlSelectDiscipline, connection); command.Parameters.AddWithValue("@w", listWeight[0]); command.Parameters.AddWithValue("@id_comp", id_competence); command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64)); for (int i = 0; i < listID.Count; i++) { command.Parameters[2].Value = listID[i]; command.ExecuteNonQuery(); } connection.Close(); } else { // иначе просто удалим из списков дисциплины с нулевым значением веса for (int i = listWeight.Count - 1; i >= 0; i--) { if (listWeight[i] == 0) { listWeight.RemoveAt(i); listID.RemoveAt(i); } } } }
private bool SaveSpecialization() { int rowid = SQLiteManager.InsertValue("Specialization", new string[] { "Number", "Name", "specialty_fk" }, new string[] { numericUpDown1.Value.ToString(), textBox2.Text, kodParentSpec }); if (rowid == -1) { return(false); } DataGridView d = (DataGridView)parentForm.Controls["dataGridViewSpecialization"]; BindingSource bind = (BindingSource)d.DataSource; DataTable table = (DataTable)bind.DataSource; DataRow row = table.NewRow(); row["Number"] = numericUpDown1.Value.ToString(); row["Name"] = textBox2.Text; row["ID"] = rowid; row["specialty_fk"] = kodParentSpec; table.Rows.Add(row); row.AcceptChanges(); d.Refresh(); return(true); }
private bool ValidateStateWorkPlan() { for (int i = 0; i < dataGridViewWorkPlan.Rows.Count; i++) { DataGridViewRow row = dataGridViewWorkPlan.Rows[i]; foreach (DataGridViewCell cell in row.Cells) { if (String.IsNullOrWhiteSpace(cell.Value.ToString())) { MessageBox.Show("В строке " + (i + 1) + "пустая ячейка!", "Ошибка"); return(false); } } bool IsRowExistInDB = SQLiteManager.IsExistValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization" }, new string[] { row.Cells[0].Value.ToString(), managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID") }); if (IsRowExistInDB) { MessageBox.Show("Дисциплина " + row.Cells[0].Value.ToString() + " уже существует в базе!", "Ошибка"); return(false); } } return(true); }
public void AddCompetenceChosenDiscipline(System.Collections.ArrayList ListDataGridRow) { if (ListDataGridRow == null || ListDataGridRow.Count == 0) { return; } BindingSource bind = (BindingSource)dataGridCompChosenDiscip.DataSource; DataTable table = (DataTable)bind.DataSource; for (int i = 0; i < ListDataGridRow.Count; i++) { bool flag_add = true; DataGridViewRow rowAdd = (DataGridViewRow)ListDataGridRow[i]; foreach (DataGridViewRow rowCurrent in dataGridCompChosenDiscip.Rows) { if (Convert.ToInt64(rowCurrent.Cells["ID"].Value) == Convert.ToInt64(rowAdd.Cells["ID"].Value)) { flag_add = false; break; } } if (flag_add) { // добавляем компетенцию в дата грид DataRow row = table.NewRow(); row["ID"] = rowAdd.Cells["ID"].Value; row["KOD"] = rowAdd.Cells["KOD"].Value; row["NAME"] = rowAdd.Cells["NAME"].Value; table.Rows.Add(row); row.AcceptChanges(); dataGridCompChosenDiscip.Refresh(); // теперь сохраним в базу новую связь дисциплина-компетенция SQLiteManager.InsertValue("Competence_Discipline", new string[] { "ID_competence", "ID_discipline" }, new object[] { rowAdd.Cells["ID"].Value, dataGridCompChosenDiscip.Tag }); } } }
private void LoadTree() { SQLiteConnection connection = SQLiteManager.CreateConnection(); string sqlSelect = "SELECT * FROM SPECIALTY"; SQLiteCommand command = new SQLiteCommand(sqlSelect); command.Connection = connection; SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { TreeNode node = new TreeNode(reader["Name"].ToString()); node.Tag = reader["KOD"]; treeView1.Nodes.Add(node); } reader.Close(); if (treeView1.Nodes.Count == 0) { connection.Close(); return; } // теперь загрузим все специализации command.CommandText = "SELECT * FROM SPECIALIZATION WHERE specialty_fk = @id_spec ORDER BY Number"; command.Parameters.Add(new SQLiteParameter("@id_spec")); foreach (TreeNode node in treeView1.Nodes) { command.Parameters[0].Value = node.Tag; reader = command.ExecuteReader(); while (reader.Read()) { TreeNode node_child = new TreeNode(reader["Number"].ToString() + " " + reader["Name"].ToString()); node_child.Tag = reader["ID"]; node.Nodes.Add(node_child); } reader.Close(); } }
private void buttonSave_Click(object sender, EventArgs e) { SQLiteConnection connection = SQLiteManager.CreateConnection(); SQLiteCommand command = new SQLiteCommand("UPDATE Assessment_Competence SET Assessment = @ass WHERE ID_student = @id_stud AND ID_competence = @id_comp", connection); command.Parameters.Add(new SQLiteParameter("@ass", DbType.Double)); command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64)); for (int i = 2; i < dataGridViewAssessment.Columns.Count; i++) { command.Parameters[2].Value = competenceID[i - 2]; for (int j = 0; j < dataGridViewAssessment.Rows.Count; j++) { if (Convert.ToBoolean(dataGridViewAssessment[i, j].Tag)) { // стоит флаг изменений, значит сохраняем в базу и снимает флаг dataGridViewAssessment[i, j].Tag = false; command.Parameters[0].Value = dataGridViewAssessment[i, j].Value; command.Parameters[1].Value = Convert.ToInt64(dataGridViewAssessment.Rows[j].Tag); command.ExecuteNonQuery(); } } } MessageBox.Show("Данные успешно сохранены.", "Уведомление"); }
private bool SaveSpec() { if (SQLiteManager.IsExistValue("SPECIALTY", "KOD", textBox1.Text)) { return(false); } int rowid = SQLiteManager.InsertValue("SPECIALTY", new string[] { "KOD", "Name" }, new string[] { textBox1.Text, textBox2.Text }); if (rowid == -1) { return(false); } DataGridView d = (DataGridView)parentForm.Controls["dataGridViewSpecialty"]; BindingSource bind = (BindingSource)d.DataSource; DataTable table = (DataTable)bind.DataSource; DataRow row = table.NewRow(); row[0] = textBox1.Text; row[1] = textBox2.Text; table.Rows.Add(row); row.AcceptChanges(); d.Refresh(); return(true); }