private void Button_Click(object sender, RoutedEventArgs e) { List <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid> books; var button = sender as Button; var tag = button.Tag as string; var tagInt = Convert.ToInt32(tag); switch (tagInt) { case 0: //Нажата кнопка "Искать книгу по автору" //Поиск по имени, фамилии, отчеству: if (NameTextBox.Text != "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorNameSurnamePatronymicSqlQuery const string GetBookByAuthorNameSurnamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.Authors like @line "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSurnamePatronymicSqlQuery, new SqlParameter("@line", $"%{SurnameTextBox.Text} {NameTextBox.Text} {PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по фамилии и отчеству: if (NameTextBox.Text == "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorSurnamePatronymicSqlQuery const string GetBookByAuthorSurnamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorSurnames like @surname and x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorSurnamePatronymicSqlQuery, new SqlParameter("@surname", $"%{SurnameTextBox.Text}%"), new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени и отчеству: if (NameTextBox.Text != "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorNamePatronymicSqlQuery const string GetBookByAuthorNamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Name, ', ') as AuthorNames, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name and x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNamePatronymicSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%"), new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени и фамилии: if (NameTextBox.Text != "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorNameSurnameSqlQuery const string GetBookByAuthorNameSurnameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames, string_agg (Author.Name, ', ') as AuthorNames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name and x.AuthorSurnames like @surname "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSurnameSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%"), new SqlParameter("@surname", $"%{SurnameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени: if (NameTextBox.Text != "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorNameSqlQuery const string GetBookByAuthorNameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Name, ', ') as AuthorNames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по фамилии: if (NameTextBox.Text == "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorSurnameSqlQuery const string GetBookByAuthorSurnameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorSurnames like @surname "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorSurnameSqlQuery, new SqlParameter("@surname", $"%{SurnameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по отчеству if (NameTextBox.Text == "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorPatronymicSqlQuery const string GetBookByAuthorPatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorPatronymicSqlQuery, new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Попытка поиска при отсутствии имени, фамилии, отчества if (NameTextBox.Text == "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text == "") { //Вывод окна с сообщением и вывод в DataGrid всех книг из БД MessageBoxResult result = MessageBox.Show("Введите данные для поиска."); AllBooks(); } BookItemsDataGrid.ItemsSource = null; break; case 1: //Нажата кнопка "Искать книгу по названию" if (TitleTextBox.Text != "") { #region GetBookByTitleSqlQuery const string GetBookByTitleSqlQuery = @" select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID where Title like @title group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByTitleSqlQuery, new SqlParameter("@title", $"%{TitleTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } else { //Вывод окна с сообщением и вывод в DataGrid всех книг из БД MessageBoxResult result = MessageBox.Show("Введите данные для поиска."); AllBooks(); } BookItemsDataGrid.ItemsSource = null; break; case 2: //Нажата кнопка "Списать выбранный экземпляр книги" if (BookItemsDataGrid.SelectedItem != null) { using (var db = new TheContext()) { SearchWindows.QueryResultClasses.BookSearchWindow_BookItemsDataGrid currentBookItem = (SearchWindows.QueryResultClasses.BookSearchWindow_BookItemsDataGrid)BookItemsDataGrid.SelectedItem; if (currentBookItem.Status == "Available") { const string WriteOffDocumentQuery = "update [Coursework_2018].[dbo].[DocumentItem] set Status = 'Written off', WrittenOffDate = getdate() where DocumentItemID = @id"; db.Database.ExecuteSqlCommand(WriteOffDocumentQuery, new SqlParameter("@id", currentBookItem.DocumentItemID)); //Обновление данных в BookItemsDataGrid: BookItemsByBook(); } else { if (currentBookItem.Status == "Unavailable") { MessageBoxResult result = MessageBox.Show("Книга используется читателем библиотеки, списание невозможно."); } if (currentBookItem.Status == "Written off") { MessageBoxResult result = MessageBox.Show("Книга уже списана из фонда библиотеки."); } } } } else { MessageBoxResult result = MessageBox.Show("Выберите экземпляр книги."); } break; case 3: //Нажата кнопка "Назад" Close(); break; } }
private void Button_Click(object sender, RoutedEventArgs e) { List <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid> books; var button = sender as Button; var tag = button.Tag as string; var tagInt = Convert.ToInt32(tag); switch (tagInt) { case 0: //Нажата кнопка "Искать книгу по автору" //Поиск по имени, фамилии, отчеству: if (NameTextBox.Text != "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorNameSurnamePatronymicSqlQuery const string GetBookByAuthorNameSurnamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.Authors like @line "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSurnamePatronymicSqlQuery, new SqlParameter("@line", $"%{SurnameTextBox.Text} {NameTextBox.Text} {PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по фамилии и отчеству: if (NameTextBox.Text == "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorSurnamePatronymicSqlQuery const string GetBookByAuthorSurnamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorSurnames like @surname and x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorSurnamePatronymicSqlQuery, new SqlParameter("@surname", $"%{SurnameTextBox.Text}%"), new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени и отчеству: if (NameTextBox.Text != "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorNamePatronymicSqlQuery const string GetBookByAuthorNamePatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Name, ', ') as AuthorNames, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name and x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNamePatronymicSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%"), new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени и фамилии: if (NameTextBox.Text != "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorNameSurnameSqlQuery const string GetBookByAuthorNameSurnameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames, string_agg (Author.Name, ', ') as AuthorNames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name and x.AuthorSurnames like @surname "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSurnameSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%"), new SqlParameter("@surname", $"%{SurnameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по имени: if (NameTextBox.Text != "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorNameSqlQuery const string GetBookByAuthorNameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Name, ', ') as AuthorNames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorNames like @name "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorNameSqlQuery, new SqlParameter("@name", $"%{NameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по фамилии: if (NameTextBox.Text == "" && SurnameTextBox.Text != "" && PatronymicTextBox.Text == "") { #region GetBookByAuthorSurnameSqlQuery const string GetBookByAuthorSurnameSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Surname, ', ') as AuthorSurnames from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorSurnames like @surname "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorSurnameSqlQuery, new SqlParameter("@surname", $"%{SurnameTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Поиск по отчеству if (NameTextBox.Text == "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text != "") { #region GetBookByAuthorPatronymicSqlQuery const string GetBookByAuthorPatronymicSqlQuery = @" select * from ( select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors, string_agg (Author.Patronymic, ', ') as AuthorPatronymics from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment) as x where x.AuthorPatronymics like @patronymic "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByAuthorPatronymicSqlQuery, new SqlParameter("@patronymic", $"%{PatronymicTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } //Попытка поиска при отсутствии имени, фамилии, отчества if (NameTextBox.Text == "" && SurnameTextBox.Text == "" && PatronymicTextBox.Text == "") { //Вывод окна с сообщением и вывод в DataGrid всех книг из БД MessageBoxResult result = MessageBox.Show("Введите данные для поиска."); AllBooks(); } BookItemsDataGrid.ItemsSource = null; break; case 1: //Нажата кнопка "Искать книгу по названию" if (TitleTextBox.Text != "") { #region GetBookByTitleSqlQuery const string GetBookByTitleSqlQuery = @" select Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment, string_agg (Concat(Author.Surname, ' ', Author.Name, ' ', Author.Patronymic), ', ') as Authors from [Coursework_2018].[dbo].[Book] inner join [Coursework_2018].[dbo].[Author_Book] on [Coursework_2018].[dbo].[Book].BookID=[Coursework_2018].[dbo].[Author_Book].BookID inner join [Coursework_2018].[dbo].[Author] on [Coursework_2018].[dbo].[Author_Book].AuthorID=[Coursework_2018].[dbo].[Author].AuthorID where Title like @title group by Book.BookID, Book.Title, Book.StoredItemsNumber, Book.Language, Book.Comment "; #endregion using (TheContext db = new TheContext()) { books = db.Database.SqlQuery <SearchWindows.QueryResultClasses.BookSearchWindow_BooksDataGrid>( GetBookByTitleSqlQuery, new SqlParameter("@title", $"%{TitleTextBox.Text}%") ).ToList(); BooksDataGrid.ItemsSource = books; } } else { //Вывод окна с сообщением и вывод в DataGrid всех книг из БД MessageBoxResult result = MessageBox.Show("Введите данные для поиска."); AllBooks(); } BookItemsDataGrid.ItemsSource = null; break; case 2: //Нажата кнопка "Выдать выбранный экземпляр книги читателю" if (CustomerIDTextBox.Text != "") { if (BookItemsDataGrid.SelectedItem != null) { using (var db = new TheContext()) { SearchWindows.QueryResultClasses.BookSearchWindow_BookItemsDataGrid currentBookItem = (SearchWindows.QueryResultClasses.BookSearchWindow_BookItemsDataGrid)BookItemsDataGrid.SelectedItem; if (currentBookItem.Status == "Available") { const string CreateCustomerDocumentInteractionQuery = @"insert into [Coursework_2018].[dbo].[CustomerDocumentInteraction] (CustomerID, DocumentItemID, CheckedOutDate, DueDate, IfRenewed, Status) values (@customerID, @documentItemID, getdate(), dateadd(month, 1, getdate()), 0, 'Taken')"; const string SetDocumentStatusUnavailableQuery = "update[Coursework_2018].[dbo].[DocumentItem] set Status = 'Unavailable' where DocumentItemID = @documentItemId"; bool flag = true; try { db.Database.ExecuteSqlCommand(CreateCustomerDocumentInteractionQuery, new SqlParameter("@customerID", CustomerIDTextBox.Text), new SqlParameter("@documentItemID", currentBookItem.DocumentItemID)); } catch (SqlException) { MessageBox.Show("Пользователь с указанным ID не существует"); flag = false; } if (flag == true) { db.Database.ExecuteSqlCommand(SetDocumentStatusUnavailableQuery, new SqlParameter("@documentItemID", currentBookItem.DocumentItemID)); MessageBox.Show("Экземпляр книги выдан читателю."); } //Обновление данных в BookItemsDataGrid: BookItemsByBook(); } else { if (currentBookItem.Status == "Unavailable") { MessageBoxResult result = MessageBox.Show("Книга используется читателем библиотеки, выдача невозможна."); } if (currentBookItem.Status == "Written off") { MessageBoxResult result = MessageBox.Show("Книга списана из фонда библиотеки, выдача невозможна."); } } } } else { MessageBoxResult result = MessageBox.Show("Выберите экземпляр книги."); } } else { MessageBoxResult result = MessageBox.Show("Введите ID читателя."); } break; case 3: //Нажата кнопка "Назад" Close(); break; } }