private void btnAdd_Click(object sender, EventArgs e) { add = !add; if (add) { WriteMode(); maskBreak.Text = maskInit.Text = maskEnd.Text = "0000"; btnEdit.Enabled = false; } else { bool valid = false; ReadMode(); DB database = new DB(); database.SetPreferences(filepath); MySqlCommand command = new MySqlCommand("select * from schedules where Name = @nam", database.getConnection()); command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text; try { DataTable checktable = new DataTable(); MySqlDataAdapter adapter = new MySqlDataAdapter(command); database.openConnection(); adapter.Fill(checktable); if (checktable.Rows.Count > 0) { valid = false; MessageBox.Show("Такая запись уже существует", "Ошибка"); return; } else { valid = true; } } catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); } finally { database.closeConnection(); } if (valid) { command = new MySqlCommand("insert into schedules(Name,TimeInit,TimeEnd,Lunch) values (@nam,@tii,@tie,@lun);", database.getConnection()); command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text; command.Parameters.Add("@tii", MySqlDbType.Time).Value = TimeSpan.Parse(maskInit.Text + ":00"); command.Parameters.Add("@tie", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00"); command.Parameters.Add("@lun", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00"); try { database.openConnection(); if (command.ExecuteNonQuery() == 1) { MessageBox.Show("Запись создана"); } else { MessageBox.Show("Ошибка создания записи"); } } catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); } finally { database.closeConnection(); } UpdateData(); } btnEdit.Enabled = true; } }
private void UpdateData() { table.Clear(); DB database = new DB(); database.SetPreferences(filepath); MySqlDataAdapter adapter = new MySqlDataAdapter(); MySqlCommand command = new MySqlCommand("select * from schedules order by TimeInit", database.getConnection()); adapter.SelectCommand = command; try { database.openConnection(); adapter.Fill(table); } catch { MessageBox.Show("Ошибка базы данных", "Ошибка"); } finally { database.closeConnection(); } listschedules.Items.Clear(); for (int i = 0; i < table.Rows.Count; i++) { listschedules.Items.Add(table.Rows[i].Field <String>("Name")); } btnEdit.Enabled = btnDelete.Enabled = false; maskInit.Text = maskEnd.Text = maskBreak.Text = "0000"; }
private void btnEdit_Click(object sender, EventArgs e) { edit = !edit; if (edit) { WriteMode(); btnAdd.Enabled = false; } else { btnAdd.Enabled = true; ReadMode(); DB database = new DB(); database.SetPreferences(filepath); string name = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text; MySqlCommand command = new MySqlCommand("update schedules set Name = @nam , TimeInit = @tii , TimeEnd = @tie , Lunch = @lun where Id = @id;", database.getConnection()); command.Parameters.Add("@id", MySqlDbType.UInt32).Value = table.Rows[listschedules.SelectedIndex].Field <UInt32>("Id"); command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = name; command.Parameters.Add("@tii", MySqlDbType.Time).Value = TimeSpan.Parse(maskInit.Text + ":00"); command.Parameters.Add("@tie", MySqlDbType.Time).Value = TimeSpan.Parse(maskEnd.Text + ":00"); command.Parameters.Add("@lun", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00"); try { database.openConnection(); if (command.ExecuteNonQuery() == 1) { MessageBox.Show("Запись изменена"); } else { MessageBox.Show("Ошибка изменения записи"); } } catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); } finally { database.closeConnection(); } UpdateData(); btnAdd.Enabled = true; } }
private void btnDelete_Click(object sender, EventArgs e) { if (edit) { edit = false; ReadMode(); btnEdit.Enabled = false; btnAdd.Enabled = true; } else if (add) { add = false; ReadMode(); maskBreak.Text = maskEnd.Text = maskInit.Text = "00:00"; } else if (MessageBox.Show("Подтвердите удаление записи", "Подтвердите действие", MessageBoxButtons.YesNo) == DialogResult.Yes) { DB database = new DB(); database.SetPreferences(filepath); MySqlCommand command = new MySqlCommand("delete from schedules where Id = @id;", database.getConnection()); command.Parameters.Add("@id", MySqlDbType.UInt32).Value = table.Rows[listschedules.SelectedIndex].Field <UInt32>("Id"); try { database.openConnection(); if (command.ExecuteNonQuery() == 1) { MessageBox.Show("Запись удалена"); } else { MessageBox.Show("Запись не удалена"); } } catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); } finally { database.closeConnection(); } } UpdateData(); }
private void panelbtncheck_Click(object sender, EventArgs e) { bool connection = false; if (paneltextadr.TextLength > 0 && paneltextport.TextLength > 0 && paneltextname.TextLength > 0 && paneltextpass.TextLength > 0 && paneltextdbname.TextLength > 0) { DisableContents(); string cmd = "server=" + paneltextadr.Text + ";port=" + paneltextport.Text + ";username="******";password="******";database=" + paneltextdbname.Text; File.WriteAllText(filepath, cmd); DB database = new DB(); database.SetPreferences(filepath); if (database.checkConnection()) { DisableContents(); connection = true; } else { MessageBox.Show("Проверка не выполена"); EnableContents(); connection = false; } if (connection == true && MessageBox.Show("Соединение с базой данных установлено.\nСоздать структуру базы данных?\n(Неоходимо при первом запуске системы)", "Уведомление", MessageBoxButtons.YesNo) == DialogResult.Yes) { MySqlCommand command = new MySqlCommand( "CREATE TABLE IF NOT EXISTS `schedules` " + "(`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," + " `Name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " `TimeInit` TIME NOT NULL," + " `TimeEnd` TIME NOT NULL," + " `Lunch` TIME NULL DEFAULT NULL," + " PRIMARY KEY(`Id`))" + " ENGINE = InnoDB" + " AUTO_INCREMENT = 14" + " DEFAULT CHARACTER SET = utf8;" + " CREATE UNIQUE INDEX `Id_UNIQUE` ON `schedules` (`Id` ASC) VISIBLE;" + " CREATE UNIQUE INDEX `Name_UNIQUE` ON `schedules` (`Name` ASC) VISIBLE;" + " CREATE TABLE IF NOT EXISTS `userstatus` (" + " `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," + " `Status` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " PRIMARY KEY(`Id`))" + " ENGINE = InnoDB" + " AUTO_INCREMENT = 3" + " DEFAULT CHARACTER SET = utf8;" + " CREATE UNIQUE INDEX `Id_UNIQUE` ON `userstatus` (`Id` ASC) VISIBLE;" + " CREATE TABLE IF NOT EXISTS `user` (" + " `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," + " `Name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " `Surname` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " `Lastname` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL," + " `Login` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " `Password` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," + " `Access` INT UNSIGNED NOT NULL," + " `StatusId` INT UNSIGNED NOT NULL," + " `ScheduleId` INT UNSIGNED NOT NULL DEFAULT '0'," + " PRIMARY KEY(`Id`, `ScheduleId`, `StatusId`)," + " CONSTRAINT `ScheduleId`" + " FOREIGN KEY(`ScheduleId`)" + " REFERENCES `schedules` (`Id`)" + " ON DELETE RESTRICT" + " ON UPDATE CASCADE," + " CONSTRAINT `StatusId`" + " FOREIGN KEY(`StatusId`)" + " REFERENCES `userstatus` (`Id`)" + " ON DELETE NO ACTION" + " ON UPDATE NO ACTION)" + " ENGINE = InnoDB" + " AUTO_INCREMENT = 19" + " DEFAULT CHARACTER SET = utf8;" + " CREATE UNIQUE INDEX `Id_UNIQUE` ON `user` (`Id` ASC) VISIBLE;" + " CREATE UNIQUE INDEX `Login_UNIQUE` ON `user` (`Login` ASC) VISIBLE;" + " CREATE INDEX `ScheduleId_idx` ON `user` (`StatusId` ASC) VISIBLE;" + " CREATE INDEX `ScheduleId_idx1` ON `user` (`ScheduleId` ASC) VISIBLE;" + " CREATE TABLE IF NOT EXISTS `activities` (" + " `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," + " `UserId` INT UNSIGNED NOT NULL," + " `ActivityDate` DATE NOT NULL," + " `ActivityTimeInit` TIME NOT NULL," + " `ActivityTimeEnd` TIME NOT NULL DEFAULT '00:00:00'," + " `ActivityHours` TIME NOT NULL DEFAULT '00:00:00'," + " `Lunch` INT UNSIGNED NOT NULL DEFAULT '1'," + " PRIMARY KEY(`Id`, `UserId`)," + " CONSTRAINT `UserId`" + " FOREIGN KEY(`UserId`)" + " REFERENCES `user` (`Id`)" + " ON DELETE RESTRICT" + " ON UPDATE CASCADE)" + " ENGINE = InnoDB" + " AUTO_INCREMENT = 66" + " DEFAULT CHARACTER SET = utf8;" + " CREATE UNIQUE INDEX `Id_UNIQUE` ON `activities` (`Id` ASC) VISIBLE;" + " CREATE INDEX `UserId_idx` ON `activities` (`UserId` ASC) VISIBLE;" + " INSERT INTO schedules(Name, TimeInit, TimeEnd, Lunch) VALUES" + " (\"DEFAULT\", \"00:00:00\", \"00:00:00\", \"00:00:00\");" + " INSERT INTO `userstatus` (`Id`, `Status`) VALUES(1, 'Неактивен');" + " INSERT INTO `userstatus` (`Id`, `Status`) VALUES(2, 'Активен');" + " INSERT INTO `user` (`Name`, `Surname`, `Lastname`, `Login`, `Password`, `Access`, `StatusId`, `ScheduleId`) VALUES('SERVER', 'ACCOUNT', NULL, 'admin', 'admin', 2, 2, (select Id from schedules where Name = \"DEFAULT\"));" + " COMMIT;", database.getConnection()); try { database.openConnection(); if (command.ExecuteNonQuery() == 1) { } ; } catch { MessageBox.Show("Ошибка создания объектов БД", "Ошибка"); } finally { database.closeConnection(); MessageBox.Show("Элементы БД созданы", "Уведомление"); } database.End(); database = null; } } else { MessageBox.Show("Введите данные БД"); } }