private void checkUserServer2ConnectionButton_Click(object sender, EventArgs e) { using (SqlConnection testConnection = new SqlConnection()) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder() { DataSource = userServer2NameTextBox.Text, InitialCatalog = userServer2DatabaseTextBox.Text, UserID = LoginForm.UserName, Password = LoginForm.Password }; testConnection.ConnectionString = sConnB.ConnectionString; testConnection.Open(); if (testConnection.State == ConnectionState.Open) { MessageBox.Show("Соединение успешно произошло"); } } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при проверке подключения к второму пользовательскому серверу в checkUserServer2ConnectionButton_Click."); MessageBox.Show("Ошибка при проверке подключения к второму пользовательскому серверу.\nВызвано исключение: " + ex.Message); } finally { testConnection.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Удаляет запись о прокате вещи private void deleteRentButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите удалить выбранную запись?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { Int32.TryParse(rentIdLabel.Text, out int rentId); if (rentId == 0) { MessageBox.Show("Выберите запись для удаления."); } else { ResidentRoomsRentThing residentRoomsRentThing = db.GetTable <ResidentRoomsRentThing>().SingleOrDefault(r => r.ResidentRoomsRentThingsId == rentId); db.GetTable <ResidentRoomsRentThing>().DeleteOnSubmit(residentRoomsRentThing); db.SubmitChanges(); LoadRentDataGridView(); } } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при удалении информации о прокате вещей в deleteRentButton_Click."); MessageBox.Show("Ошибка при удалении информации о прокате вещей.\nВызвано исключение:" + ex.Message); } } }
/// <summary> /// Добавление проката вещи /// </summary> private void RentThing() { try { ResidentRooms residentRooms = db.GetTable <ResidentRooms>() .FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null)); string selectedRentName = rentThingsComboBox.SelectedItem.ToString(); RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == selectedRentName); ResidentRoomsRentThing residentRoomsRentThing = new ResidentRoomsRentThing { ResidentRoomsId = residentRooms.ResidentRoomsId, RentThingId = rentThing.RentThingId, StartRentDate = startRentDateTimePicker.Value.Date, EndRentDate = endRentDateTimePicker.Value.Date }; db.GetTable <ResidentRoomsRentThing>().InsertOnSubmit(residentRoomsRentThing); db.SubmitChanges(); LoadRentDataGridView(); SystemSounds.Beep.Play(); MessageBox.Show("Прокат успешно добавлен!"); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при добавлении проката вещей."); MessageBox.Show("Ошибка проката. Проверьте выбранные данные.\nВызвано исключение: " + ex.Message); } }
// Создание резервной копии базы данных public static void BackupDatabase(string databaseName, string folderPath) { using (SqlConnection sqlConnection = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True")) { try { sqlConnection.Open(); sql = "BACKUP DATABASE " + databaseName + " TO DISK = '" + folderPath + "\\" + databaseName + "-" + DateTime.Now.ToString("dd-MM-yyyy-hh/mm/ss") + ".bak'"; command = new SqlCommand(sql, sqlConnection); command.ExecuteNonQuery(); sqlConnection.Close(); sqlConnection.Dispose(); SystemSounds.Exclamation.Play(); } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при создании резервной копии базы данных в BackupHelper.backupDatabase."); } finally { sqlConnection.Close(); } } }
/// <summary> /// Отвечает за выселения жителя из комнаты /// </summary> /// <param name="roomId">Идентификатор комнаты</param> /// <param name="residentId">Идентификатор жителя</param> private void EvictResident(int roomId, int residentId) { DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите выселить данного жителя?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { Resident resident = db.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId); RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == roomId)); db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents); ResidentRooms residentRooms = db.GetTable <ResidentRooms>() .FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == roomId && r.DateOfEviction == null)); residentRooms.DateOfEviction = DateTime.Now; db.SubmitChanges(); SystemSounds.Beep.Play(); MessageBox.Show("Житель успешно выселен!"); HistoryRecordsController.WriteAboutSettlement(residentRooms, false); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при выселении жителя."); MessageBox.Show("Ошибка выселения жителя. \nВызвано исключение: " + ex.Message); } } }
// Восстановление бд с резервной копии public static void RestoreDatabase(string databaseName, string folderPath) { using (SqlConnection myConn = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True")) { try { myConn.Open(); sql = "use master " + " ALTER DATABASE " + databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"; sql += "RESTORE DATABASE " + databaseName + " FROM DISK = '" + folderPath + "' WITH REPLACE;"; command = new SqlCommand(sql, myConn); command.ExecuteNonQuery(); myConn.Close(); myConn.Dispose(); SystemSounds.Exclamation.Play(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при восстановление базы данных с резервной копии в BackupHelper.restoreDatabase."); } finally { myConn.Close(); } } }
// Попытка войти в систему private void enterButton_Click(object sender, EventArgs e) { using (SqlConnection sqlConnection = new SqlConnection()) { string userName = userNameTextBox.Text; string password = passwordTextBox.Text; if (string.IsNullOrWhiteSpace(userName) || string.IsNullOrWhiteSpace(password)) { SystemSounds.Exclamation.Play(); MessageBox.Show("Введите логин и пароль!"); return; } try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServerName, InitialCatalog = Properties.Settings.Default.userServerDatabase, UserID = userName, Password = password, }; sqlConnection.ConnectionString = sConnB.ConnectionString; sqlConnection.Open(); HistoryRecordsController.ChangeUser(userName); HistoryRecordsController.WriteAboutSystemEnter(true); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; MainForm mainForm = new MainForm(this, sConnB); mainForm.Show(); this.Hide(); LoginForm.UserName = userName; LoginForm.Password = password; userNameTextBox.Clear(); passwordTextBox.Clear(); } catch (SqlException sqlEx) { HistoryRecordsController.ChangeUser(userName); HistoryRecordsController.WriteAboutSystemEnter(false); HistoryRecordsController.WriteExceptionToLogFile(sqlEx, "Не удалось выполнить вход. Введены неверный логин и(или) пароль."); SystemSounds.Exclamation.Play(); MessageBox.Show("Не удалось выполнить вход.\nУбедитесь, что введены правильные логин и(или) пароль.", "Ошибка"); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при входе."); MessageBox.Show(ex.Message); } finally { sqlConnection.Close(); } } }
// Поиск организации по названию private void searchButton_Click(object sender, EventArgs e) { try { dataTable.DefaultView.RowFilter = string.Format("[{0}] LIKE '%{1}%'", "Название", nameFilterTextBox.Text); organizationsDataGridView.ClearSelection(); } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при поиске организации в searchButton_Click."); MessageBox.Show("Ошибка при поиске организации в searchButton_Click.\nВызвано исключение:" + ex.Message); } }
// Сохраняет изменения о прокате вещи private void saveRentButton_Click(object sender, EventArgs e) { if (startRentDateTimePicker.Value.Date > endRentDateTimePicker.Value.Date) { SystemSounds.Exclamation.Play(); MessageBox.Show("Нельзя добавить данный прокат. Дата начала проката должна быть раньше даты окончания."); return; } DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { Int32.TryParse(rentIdLabel.Text, out int rentId); if (rentId == 0) { MessageBox.Show("Выберите запись для изменения."); } else { ResidentRoomsRentThing residentRoomsRentThing = db.GetTable <ResidentRoomsRentThing>().SingleOrDefault(r => r.ResidentRoomsRentThingsId == rentId); string selectedRentName = rentThingsComboBox.SelectedItem.ToString(); RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == selectedRentName); residentRoomsRentThing.RentThingId = rentThing.RentThingId; residentRoomsRentThing.StartRentDate = startRentDateTimePicker.Value.Date; residentRoomsRentThing.EndRentDate = endRentDateTimePicker.Value.Date; db.SubmitChanges(); SystemSounds.Beep.Play(); MessageBox.Show("Успешно сохранено!"); LoadRentDataGridView(); } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений о прокате вещей в saveRentButton_Click."); MessageBox.Show("Ошибка при сохранении изменений о прокате вещей.\nВызвано исключение:" + ex.Message); } } }
// Запуск выбранного sql-скрипта private void runSqlScriptButton_Click(object sender, EventArgs e) { SqlConnection myConn = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True"); string script = File.ReadAllText(sqlScriptFileTextBox.Text); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase); try { myConn.Open(); foreach (string commandString in commandStrings) { if (commandString.Trim() != "") { using (var command = new SqlCommand(commandString, myConn)) { command.ExecuteNonQuery(); } } } SystemSounds.Beep.Play(); MessageBox.Show("Успешно выполено"); SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServerName, InitialCatalog = Properties.Settings.Default.userServerDatabase, IntegratedSecurity = true }; HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString); } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при запуске sql-скрипта в runSqlScriptButton_Click."); MessageBox.Show("Ошибка при запуске sql-скрипта.\nВызвано исключение: " + ex.Message); } finally { myConn.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
// Восстановление бд с резервной копии private void restoreButton_Click(object sender, EventArgs e) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; BackupHelper.RestoreDatabase(userServerDatabaseTextBox.Text, restoreFilePathTextBox.Text); SystemSounds.Exclamation.Play(); MessageBox.Show("Успешно восстановлено!"); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при восстановление базы данных с резервной копии в restoreButton_Click."); MessageBox.Show("Ошибка при восстановление базы данных с резервной копии.\nВызвано исключение: " + ex.Message); } finally { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
/// <summary> /// Загружает информацию для вкладок элемента TabControl главного окна /// </summary> private void LoadTabs() { sectionsTabControl.TabPages.Clear(); try { // SqlConnection sqlConnection2 = new SqlConnection(sqlConnectionString); db = new DataContext(sqlConnectionString.ConnectionString); Table <Section> sections = db.GetTable <Section>(); foreach (var section in sections) { LoadSection(section); } } catch (Exception ex) { MessageBox.Show("Ошибка при загрузке секций. \nВызвано исключение: " + ex.Message); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка загрузки секций"); } }
private void updateButton_Click(object sender, EventArgs e) { try { SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = sqlConnectionString.UserID, Password = sqlConnectionString.Password, }; updateOrganizations(sConnBForOtherServer.ConnectionString); updateOrganizations(sqlConnectionString.ConnectionString); LoadDataGrid(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных организаций в updateButton_Click."); MessageBox.Show("Ошибка объединения данных организаций в updateButton_Click.\nВызвано исключение:" + ex.Message); } }
// Сохранение настроек программы private void saveSettingsButton_Click(object sender, EventArgs e) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; Properties.Settings.Default.userServerName = userServerNameTextBox.Text; Properties.Settings.Default.userServerDatabase = userServerDatabaseTextBox.Text; Properties.Settings.Default.userServer2Name = userServer2NameTextBox.Text; Properties.Settings.Default.userServer2Database = userServer2DatabaseTextBox.Text; Properties.Settings.Default.adminServerName = adminServerNameTextBox.Text; Properties.Settings.Default.adminServerDatabase = adminServerDatabaseTextBox.Text; Properties.Settings.Default.backupsFolderPath = backupsFolderPathTextBox.Text; Properties.Settings.Default.restoreFilePath = restoreFilePathTextBox.Text; Properties.Settings.Default.exceptionsLogFolderPath = exceptionsLogFolderPathTextBox.Text; Properties.Settings.Default.Save(); SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServerName, InitialCatalog = Properties.Settings.Default.userServerDatabase, IntegratedSecurity = true }; HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString); SystemSounds.Exclamation.Play(); MessageBox.Show("Настройки успешно сохранены!"); } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении настроек программы в saveSettingsButton_Click."); MessageBox.Show("Ошибка при сохранении настроек программы.\nВызвано исключение: " + ex.Message); } finally { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
// Проверка существования базы данных. Если бд не существует, открывается окно // выбора sql-скрипта для создания новой базы данных. private void LoginForm_Load(object sender, EventArgs e) { try { SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServerName, InitialCatalog = Properties.Settings.Default.userServerDatabase, IntegratedSecurity = true }; sqlConnection = new SqlConnection(sConnB.ConnectionString); sqlConnection.Open(); HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString); } catch (SqlException sqlEx) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(sqlEx, "Ошибка подключения к базе данных при запуске программы."); MessageBox.Show("Не удалось подключиться к базе данных. Ошибка:\n" + sqlEx.Message, "Ошибка"); OpenFileDialog dlg = new OpenFileDialog(); string filePath = ""; dlg.Filter = "Sql scripts(*.sql)|*.sql|All Files(*.*)|*.*"; dlg.FilterIndex = 0; if (dlg.ShowDialog() == DialogResult.OK) { filePath = dlg.FileName; } SqlConnectionStringBuilder sConnB2 = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServerName, IntegratedSecurity = true }; SqlConnection myConn = new SqlConnection(sConnB2.ConnectionString); string script = File.ReadAllText(filePath); IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase); try { myConn.Open(); foreach (string commandString in commandStrings) { if (commandString.Trim() != "") { using (var command = new SqlCommand(commandString, myConn)) { command.ExecuteNonQuery(); } } } SystemSounds.Beep.Play(); MessageBox.Show("База данных создана"); } catch (SqlException ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка создания базы данных через sql-скрипт."); MessageBox.Show("Ошибка создания базы данных:\n" + sqlEx.Message); } finally { myConn.Close(); } } finally { sqlConnection.Close(); } }
// Сохранение информации о жителе private void saveButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { if (isAllFieldsValid() == false) { return; } try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; db = new DataContext(sqlConnectionString.ConnectionString); Resident resident = db.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId); Resident oldResident = resident.ShallowCopy(); int organizationId = Int32.Parse(organizationIdLabel.Text); resident.Surname = surnameTextBox.Text; resident.Name = nameTextBox.Text; resident.Patronymic = patronymicTextBox.Text; resident.PhoneNumber = phoneNumberTextBox.Text; resident.Birthday = birthdayDateTimePicker.NullableValue(); resident.Note = noteTextBox.Text; resident.Organization = db.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId); Passport passport = db.GetTable <Passport>().SingleOrDefault(p => p.PassportId == resident.PassportId); oldResident.Passport = passport.ShallowCopy(); passport.Number = passportNumberTextBox.Text; passport.Series = passportSeriesTextBox.Text; passport.Registration = passportRegistrationTextBox.Text; passport.DateOfIssue = passportDateOfIssueDateTimePicker.Value.Date; passport.Authority = passportAuthorityTextBox.Text; SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = sqlConnectionString.UserID, Password = sqlConnectionString.Password, }; DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString); Resident resident2 = dbFromOtherServer.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId); resident2.Surname = surnameTextBox.Text; resident2.Name = nameTextBox.Text; resident2.Patronymic = patronymicTextBox.Text; resident2.PhoneNumber = phoneNumberTextBox.Text; resident2.Birthday = birthdayDateTimePicker.NullableValue(); resident2.Note = noteTextBox.Text; resident2.Organization = dbFromOtherServer.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId); Passport passport2 = dbFromOtherServer.GetTable <Passport>().SingleOrDefault(p => p.PassportId == resident2.PassportId); passport2.Number = passportNumberTextBox.Text; passport2.Series = passportSeriesTextBox.Text; passport2.Registration = passportRegistrationTextBox.Text; passport2.DateOfIssue = passportDateOfIssueDateTimePicker.Value.Date; passport2.Authority = passportAuthorityTextBox.Text; dbFromOtherServer.SubmitChanges(); db.SubmitChanges(); SystemSounds.Beep.Play(); MessageBox.Show("Успешно сохранено!"); HistoryRecordsController.WriteAboutEditResident(oldResident, resident); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений жителя."); MessageBox.Show("Ошибка при сохранении изменений. \nВызвано исключение: " + ex.Message); } finally { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Удаляет выбранного жителя private void deleteButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите удалить выбранного жителя?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; Int32.TryParse(residentIdLabel.Text, out int residentId); if (residentId == 0) { MessageBox.Show("Выберите жителя"); } else { SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = sqlConnectionString.UserID, Password = sqlConnectionString.Password, }; DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString); Resident resident2 = dbFromOtherServer.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId); if (resident2 != null) { dbFromOtherServer.GetTable <Resident>().DeleteOnSubmit(resident2); } Passport passport2 = dbFromOtherServer.GetTable <Passport>().FirstOrDefault(r => (r.PassportId == resident2.PassportId)); if (passport2 != null) { resident2.Passport = passport2; dbFromOtherServer.GetTable <Passport>().DeleteOnSubmit(passport2); } RoomResidents roomResidents2 = dbFromOtherServer.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId)); if (roomResidents2 != null) { dbFromOtherServer.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents2); } ResidentRooms residentRooms2 = dbFromOtherServer.GetTable <ResidentRooms>() .FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null)); if (residentRooms2 != null) { residentRooms2.DateOfEviction = DateTime.Now; } db = new DataContext(sqlConnectionString.ConnectionString); Resident resident = db.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId); if (resident != null) { db.GetTable <Resident>().DeleteOnSubmit(resident); } Passport passport = db.GetTable <Passport>().FirstOrDefault(r => (r.PassportId == resident.PassportId)); if (passport != null) { resident.Passport = passport; db.GetTable <Passport>().DeleteOnSubmit(passport); } RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId)); if (roomResidents != null) { db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents); } ResidentRooms residentRooms = db.GetTable <ResidentRooms>() .FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null)); if (residentRooms != null) { residentRooms.DateOfEviction = DateTime.Now; } dbFromOtherServer.SubmitChanges(); db.SubmitChanges(); LoadDataGrid(); HistoryRecordsController.WriteAboutAddDeleteResident(resident, false); } } catch (Exception ex) { HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при удалении жителя в deleteButton_Click."); MessageBox.Show("Ошибка при удалении жителя.\nВызвано исключение: " + ex.Message); } finally { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Обновить список паспортов, загрузив с другого сервера private void updatePassports(string userServerConnectionString) { using (SqlConnection userServer2Connection = new SqlConnection()) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; SqlConnectionStringBuilder userServer2ConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = LoginForm.UserName, Password = LoginForm.Password }; userServer2Connection.ConnectionString = userServer2ConnB.ConnectionString; userServer2Connection.Open(); if (userServer2Connection.State == ConnectionState.Open) { DataTable updateDataTable = new DataTable("UpdatePassports"); SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT PassportId, " + "Number, Series, Registration, DateOfIssue, Authority " + "FROM Passports", userServer2Connection); updateDataAdapter.Fill(updateDataTable); using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString)) { try { sqlConnection.Open(); string tmpTable = "create table #Passports " + "(PassportId int, Number nvarchar(20), Series nvarchar(20), " + "Registration nvarchar(100), DateOfIssue datetime, Authority nvarchar(100))"; SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection); cmd.ExecuteNonQuery(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection)) { bulkCopy.ColumnMappings.Add("PassportId", "PassportId"); bulkCopy.ColumnMappings.Add("Number", "Number"); bulkCopy.ColumnMappings.Add("Series", "Series"); bulkCopy.ColumnMappings.Add("Registration", "Registration"); bulkCopy.ColumnMappings.Add("DateOfIssue", "DateOfIssue"); bulkCopy.ColumnMappings.Add("Authority", "Authority"); bulkCopy.DestinationTableName = "#Passports"; try { bulkCopy.WriteToServer(updateDataTable); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Residents."); MessageBox.Show("Ошибка копирования данных во временную таблицу #Residents.\nВызвано исключение:" + ex.Message); } } cmd.CommandText = "SET IDENTITY_INSERT Passports ON"; cmd.ExecuteNonQuery(); string mergeSql = "merge into Passports as Target " + "using #Passports as Source " + "on " + "Target.PassportId=Source.PassportId " + "when matched then " + "update set Target.Number=Source.Number, Target.Series=Source.Series, " + "Target.Registration=Source.Registration, Target.DateOfIssue=Source.DateOfIssue, " + "Target.Authority=Source.Authority " + "when not matched then " + "insert (PassportId, Number, Series, Registration, DateOfIssue, Authority) " + "values (Source.PassportId, Source.Number, Source.Series, Source.Registration, Source.DateOfIssue, Source.Authority) " + "when not matched by Source then delete; "; cmd.CommandText = mergeSql; cmd.ExecuteNonQuery(); cmd.CommandText = "SET IDENTITY_INSERT Passports OFF;"; cmd.ExecuteNonQuery(); cmd.CommandText = "drop table #Passports"; cmd.ExecuteNonQuery(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных паспортов из двух таблиц."); MessageBox.Show("Ошибка объединения данных паспортов из двух таблиц.\nВызвано исключение: " + ex.Message); } finally { sqlConnection.Close(); } } } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных паспортов в updatePassports."); MessageBox.Show("Ошибка обновления данных паспортов в updatePassports.\nВызвано исключение:" + ex.Message); } finally { userServer2Connection.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Обновить список жителей, загрузив с другого сервера private void updateResidents(string userServerConnectionString) { using (SqlConnection userServer2Connection = new SqlConnection()) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; SqlConnectionStringBuilder userServer2ConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = LoginForm.UserName, Password = LoginForm.Password }; userServer2Connection.ConnectionString = userServer2ConnB.ConnectionString; userServer2Connection.Open(); if (userServer2Connection.State == ConnectionState.Open) { DataTable updateDataTable = new DataTable("UpdateResidents"); SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT ResidentId, " + "Surname, Name, Patronymic, PhoneNumber, Birthday, Note, PassportId, OrganizationId " + "FROM Residents", userServer2Connection); updateDataAdapter.Fill(updateDataTable); using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString)) { try { sqlConnection.Open(); string tmpTable = "create table #Residents " + "(ResidentId int, Surname nvarchar(50), Name nvarchar(50), " + "Patronymic nvarchar(50), PhoneNumber nvarchar(50), Birthday datetime, " + "Note nvarchar(100), PassportId int, OrganizationId int)"; SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection); cmd.ExecuteNonQuery(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection)) { bulkCopy.ColumnMappings.Add("ResidentId", "ResidentId"); bulkCopy.ColumnMappings.Add("Surname", "Surname"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Patronymic", "Patronymic"); bulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber"); bulkCopy.ColumnMappings.Add("Birthday", "Birthday"); bulkCopy.ColumnMappings.Add("Note", "Note"); bulkCopy.ColumnMappings.Add("PassportId", "PassportId"); bulkCopy.ColumnMappings.Add("OrganizationId", "OrganizationId"); bulkCopy.DestinationTableName = "#Residents"; try { bulkCopy.WriteToServer(updateDataTable); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Residents."); MessageBox.Show("Ошибка копирования данных во временную таблицу #Residents.\nВызвано исключение:" + ex.Message); } } cmd.CommandText = "SET IDENTITY_INSERT Residents ON"; cmd.ExecuteNonQuery(); string mergeSql = "merge into Residents as Target " + "using #Residents as Source " + "on " + "Target.ResidentId=Source.ResidentId " + "when matched then " + "update set Target.Surname=Source.Surname, Target.Name=Source.Name, " + "Target.Patronymic=Source.Patronymic, Target.PhoneNumber=Source.PhoneNumber, " + "Target.Birthday=Source.Birthday, Target.Note=Source.Note, " + "Target.PassportId=Source.PassportId, Target.OrganizationId=Source.OrganizationId " + "when not matched then " + "insert (ResidentId, Surname, Name, Patronymic, PhoneNumber, Birthday, Note, PassportId, OrganizationId) " + "values (Source.ResidentId, Source.Surname, Source.Name, Source.Patronymic, Source.PhoneNumber, Source.Birthday, Source.Note, Source.PassportId, Source.OrganizationId) " + "when not matched by Source then delete; "; cmd.CommandText = mergeSql; cmd.ExecuteNonQuery(); cmd.CommandText = "SET IDENTITY_INSERT Residents OFF"; cmd.ExecuteNonQuery(); cmd.CommandText = "drop table #Residents"; cmd.ExecuteNonQuery(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных организаций из двух таблиц."); MessageBox.Show("Ошибка объединения данных жителей из двух таблиц.\nВызвано исключение: " + ex.Message); } finally { sqlConnection.Close(); } } } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных жителей в updateResidents."); MessageBox.Show("Ошибка обновления данных жителей в updateResidents.\nВызвано исключение:" + ex.Message); } finally { userServer2Connection.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Обновить список организаций, загрузив с сервера администратора private void updateOrganizations(string userServerConnectionString) { using (SqlConnection adminConnection = new SqlConnection()) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; SqlConnectionStringBuilder adminConnB = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.adminServerName, InitialCatalog = Properties.Settings.Default.adminServerDatabase, UserID = LoginForm.UserName, Password = LoginForm.Password }; adminConnection.ConnectionString = adminConnB.ConnectionString; adminConnection.Open(); if (adminConnection.State == ConnectionState.Open) { DataTable updateDataTable = new DataTable("UpdateOrganizations"); SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT OrganizationId as [ИД], " + "Name as [Название], PhysicAddress as [Адрес] " + "FROM Organizations", adminConnection); updateDataAdapter.Fill(updateDataTable); using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString)) { try { sqlConnection.Open(); string tmpTable = "create table #Organizations " + "(OrganizationId int, Name nvarchar(100), Address nvarchar(100))"; SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection); cmd.ExecuteNonQuery(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection)) { bulkCopy.ColumnMappings.Add("[ИД]", "OrganizationId"); bulkCopy.ColumnMappings.Add("[Название]", "Name"); bulkCopy.ColumnMappings.Add("[Адрес]", "Address"); bulkCopy.DestinationTableName = "#Organizations"; try { bulkCopy.WriteToServer(updateDataTable); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Organizations."); MessageBox.Show("Ошибка копирования данных во временную таблицу #Organizations.\nВызвано исключение:" + ex.Message); } } string mergeSql = "merge into Organizations as Target " + "using #Organizations as Source " + "on " + "Target.OrganizationId=Source.OrganizationId " + "when matched then " + "update set Target.Name=Source.Name, Target.Address=Source.Address " + "when not matched then " + "insert (OrganizationId,Name,Address) values (Source.OrganizationId,Source.Name,Source.Address) " + "when not matched by Source then delete; "; cmd.CommandText = mergeSql; cmd.ExecuteNonQuery(); cmd.CommandText = "drop table #Organizations"; cmd.ExecuteNonQuery(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных организаций из двух таблиц."); MessageBox.Show("Ошибка объединения данных организаций из двух таблиц.\nВызвано исключение: " + ex.Message); } finally { sqlConnection.Close(); } } } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных организаций в updateOrganizations."); MessageBox.Show("Ошибка обновления данных организаций в updateOrganizations.\nВызвано исключение:" + ex.Message); } finally { adminConnection.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Добавление нового жителя private void addButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите добавить данного жителя?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { if (isAllFieldsValid() == false) { return; } try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; // Для сброса контекста, если была попытка ввода нового значения db = new DataContext(sqlConnectionString.ConnectionString); string surname = surnameTextBox.Text; string name = nameTextBox.Text; string patronymic = patronymicTextBox.Text; string phoneNumber = phoneNumberTextBox.Text; DateTime?birthday = birthdayDateTimePicker.NullableValue(); string passportSeries = passportSeriesTextBox.Text; string passportNumber = passportNumberTextBox.Text; string passportRegistration = passportRegistrationTextBox.Text; string note = noteTextBox.Text; DateTime passportDateOfIssue = passportDateOfIssueDateTimePicker.Value.Date; string passportAuthority = passportAuthorityTextBox.Text; int organizationId = Int32.Parse(organizationIdLabel.Text); Organization organization = db.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId); Passport passport = new Passport { Series = passportSeries, Number = passportNumber, Registration = passportRegistration, DateOfIssue = passportDateOfIssue, Authority = passportAuthority }; db.GetTable <Passport>().InsertOnSubmit(passport); Resident resident = new Resident { Surname = surname, Name = name, Patronymic = patronymic, PhoneNumber = phoneNumber, Birthday = birthday, Note = note, Passport = passport, Organization = organization, }; db.GetTable <Resident>().InsertOnSubmit(resident); SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder() { DataSource = Properties.Settings.Default.userServer2Name, InitialCatalog = Properties.Settings.Default.userServer2Database, UserID = sqlConnectionString.UserID, Password = sqlConnectionString.Password, }; DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString); Organization organization2 = dbFromOtherServer.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId); Passport passport2 = new Passport { Series = passportSeries, Number = passportNumber, Registration = passportRegistration, DateOfIssue = passportDateOfIssue, Authority = passportAuthority }; dbFromOtherServer.GetTable <Passport>().InsertOnSubmit(passport2); Resident resident2 = new Resident { Surname = surname, Name = name, Patronymic = patronymic, PhoneNumber = phoneNumber, Birthday = birthday, Note = note, Passport = passport2, Organization = organization2, }; dbFromOtherServer.GetTable <Resident>().InsertOnSubmit(resident2); dbFromOtherServer.SubmitChanges(); db.SubmitChanges(); SystemSounds.Beep.Play(); MessageBox.Show("Успешно добавлен!"); HistoryRecordsController.WriteAboutAddDeleteResident(resident, true); Close(); } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при добавлении жителя."); MessageBox.Show("Ошибка при добавлении жителя.\nВызвано исключение: " + ex.Message); } finally { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } }
// Сохраняет изменения о проживании private void saveLivingButton_Click(object sender, EventArgs e) { if (settlementDateDateTimePicker.Value.Date > dateOfEvictionDateTimePicker.NullableValue()) { SystemSounds.Exclamation.Play(); MessageBox.Show("Дата заселения должна быть раньше даты выселения."); return; } DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { Int32.TryParse(residentRoomsIdLabel.Text, out int residentRoomsId); if (residentRoomsId == 0) { MessageBox.Show("Выберите запись для изменения."); } else { int sectonNumber = Int32.Parse(sectionNumberTextBox.Text); int roomNumber = Int32.Parse(roomNumberTextBox.Text); Room room = db.GetTable <Room>().SingleOrDefault(r => r.SectionNumber == sectonNumber && r.Number == roomNumber); if (room != null) { ResidentRooms residentRooms = db.GetTable <ResidentRooms>().SingleOrDefault(r => r.ResidentRoomsId == residentRoomsId); if (residentRooms.RoomId != room.RoomId) { RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == residentRooms.RoomId)); db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents); RoomResidents newRoomResidents = new RoomResidents { RoomId = room.RoomId, ResidentId = residentRooms.ResidentId }; db.GetTable <RoomResidents>().InsertOnSubmit(newRoomResidents); } residentRooms.RoomId = room.RoomId; residentRooms.CashPayment = (bool)((ComboBoxItem)cashPaymentComboBox.SelectedItem).HiddenValue; residentRooms.BedClothes = (bool)((ComboBoxItem)bedClothesComboBox.SelectedItem).HiddenValue; residentRooms.SettlementDate = settlementDateDateTimePicker.Value; residentRooms.DateOfEviction = dateOfEvictionDateTimePicker.NullableValue(); db.SubmitChanges(); SystemSounds.Beep.Play(); MessageBox.Show("Успешно сохранено!"); LoadLivingDataGridView(); } else { MessageBox.Show("Введённой комнаты не существует."); } } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений о проживании в saveLivingButton_Click."); MessageBox.Show("Ошибка при сохранении изменений.\nВызвано исключение:" + ex.Message); } } }
// Заселяет жителя private void settleButton_Click(object sender, EventArgs e) { if (startRentDateTimePicker.Value.Date > endRentDateTimePicker.Value.Date) { SystemSounds.Exclamation.Play(); MessageBox.Show("Нельзя добавить данный прокат. Дата начала проката должна быть раньше даты окончания."); return; } DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите заселить данного жителя?\n", "Предупреждение", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { Int32.TryParse(residentIdLabel.Text, out int residentId); Resident resident = db.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId); var exist = db.GetTable <RoomResidents>().Any(r => r.ResidentId == resident.ResidentId); if (exist) { SystemSounds.Exclamation.Play(); MessageBox.Show("Данный житель уже живет в другой комнате"); } else { RoomResidents roomResidents = new RoomResidents { RoomId = roomId, ResidentId = resident.ResidentId }; db.GetTable <RoomResidents>().InsertOnSubmit(roomResidents); bool isCash = cashRadioButton.Checked ? true : false; ResidentRooms residentRooms = new ResidentRooms { ResidentId = resident.ResidentId, RoomId = roomId, CashPayment = isCash, BedClothes = bedClothesCheckBox.Checked, SettlementDate = settlementDateTimePicker.Value }; db.GetTable <ResidentRooms>().InsertOnSubmit(residentRooms); db.SubmitChanges(); if (isRentCheckBox.Checked) { RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == "Телевизор"); ResidentRoomsRentThing residentRoomsRentThing = new ResidentRoomsRentThing { ResidentRoomsId = residentRooms.ResidentRoomsId, RentThingId = rentThing.RentThingId, StartRentDate = startRentDateTimePicker.Value.Date, EndRentDate = endRentDateTimePicker.Value.Date }; db.GetTable <ResidentRoomsRentThing>().InsertOnSubmit(residentRoomsRentThing); } db.SubmitChanges(); HistoryRecordsController.WriteAboutSettlement(residentRooms, true); DialogResult = DialogResult.OK; Close(); } } catch (Exception ex) { SystemSounds.Exclamation.Play(); HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при заселении жителя в settleButton_Click."); MessageBox.Show("Ошибка при заселении жителя.\nВызвано исключение: " + ex.Message); } } }