Esempi in C# (CSharp) per SearchWindows.QueryResultClasses.BookSearchWindow_BookItemsDataGrid

Esempio n. 1

File: BookWriteOffWindow.xaml.cs Progetto: ribogdanov/Library

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

Esempio n. 2

File: CheckOutBookWindow.xaml.cs Progetto: ribogdanov/Library

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