//Заполнение поля поиска
public static void FillComboBoxSearch(ComboBox cbCategorySearch, ComboBox cbSearch)
{
switch (cbCategorySearch.SelectedIndex)
{
case 0:
{
foreach (var item in ClassGetContext.context.Books.Select(x => x.name).Distinct())
{
cbSearch.Items.Add(item);
}
}; break;
case 1:
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT DISTINCT idAuthor, shortName FROM [Author]",
ClassConnection.SqlConnection());
adapter.Fill(dataSet);
cbSearch.DataSource = dataSet.Tables[0];
cbSearch.DisplayMember = "shortName";
cbSearch.ValueMember = "idAuthor";
}; break;
case 2:
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT DISTINCT idGenre, genre FROM [Genre]",
ClassConnection.SqlConnection());
adapter.Fill(dataSet);
cbSearch.DataSource = dataSet.Tables[0];
cbSearch.DisplayMember = "genre";
cbSearch.ValueMember = "idGenre";
}; break;
}
}
//Добавление новой книги
private void btnAddBook_Click(object sender, EventArgs e)
{
if (!string.IsNullOrWhiteSpace(tbNameBook.Text) && !string.IsNullOrWhiteSpace(tbYearOfPublishing.Text) &&
!string.IsNullOrWhiteSpace(tbISBN.Text) && !string.IsNullOrWhiteSpace(cbAgeLimit.Text) &&
!string.IsNullOrWhiteSpace(cbCoverType.Text) && !string.IsNullOrWhiteSpace(richTextBoxDescription.Text) &&
!string.IsNullOrWhiteSpace(cbPublishing.Text) && numericUpDownPageCount.Value > 0 &&
tableAuthor.RowCount > 0 && tableGenre.RowCount > 0)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
var commInsBook = new SqlCommand(@"INSERT INTO [Book]
(name, yearOfPublishing, pageCount, ageLimit, ISBN, cover, coverType, description, idPublishing)
VALUES (@nameBook, @yearOfPublishing, @pageCount, @ageLimit, @ISBN, @cover, @coverType,
@description, @idPublishing)", connection);
commInsBook.Parameters.AddWithValue("@nameBook", tbNameBook.Text);
commInsBook.Parameters.AddWithValue("@yearOfPublishing", Convert.ToInt32(tbYearOfPublishing.Text));
commInsBook.Parameters.AddWithValue("@pageCount", Convert.ToInt32(numericUpDownPageCount.Value));
commInsBook.Parameters.AddWithValue("@ageLimit", cbAgeLimit.Text);
commInsBook.Parameters.AddWithValue("@ISBN", tbISBN.Text);
commInsBook.Parameters.AddWithValue("@cover", newPath);
commInsBook.Parameters.AddWithValue("@coverType", cbCoverType.Text);
commInsBook.Parameters.AddWithValue("@description", richTextBoxDescription.Text);
commInsBook.Parameters.AddWithValue("@idPublishing", cbPublishing.SelectedValue);
commInsBook.ExecuteNonQuery();
var commSelMaxId = new SqlCommand(@"SELECT MAX(idBook) FROM [Book]", connection);
SqlDataReader readerSelMaxId = commSelMaxId.ExecuteReader();
readerSelMaxId.Read();
int maxId = Convert.ToInt32(readerSelMaxId[0]);
readerSelMaxId.Close();
foreach (DataGridViewRow row in tableGenre.Rows)
{
var commInsGenre = new SqlCommand(@"INSERT INTO [BookGenre]
(idBook, idGenre) VALUES (" + maxId + ", " + Convert.ToInt32(row.Cells[0].Value) + ")", connection);
commInsGenre.ExecuteNonQuery();
}
foreach (DataGridViewRow row in tableAuthor.Rows)
{
var commInsAuthor = new SqlCommand(@"INSERT INTO [BookAuthor]
(idBook, idAuthor) VALUES (" + maxId + ", " + Convert.ToInt32(row.Cells[0].Value) + ")", connection);
commInsAuthor.ExecuteNonQuery();
}
var commInsWarehouse = new SqlCommand(@"INSERT INTO [Warehouse] (idBook, quantity, price)
VALUES (" + maxId + ", 0, 0)", connection);
commInsWarehouse.ExecuteNonQuery();
MessageBox.Show("Книга успешно добавлена!");
Hide();
}
}
else
{
MessageBox.Show("Не все поля заполены!");
}
}
//Заполнение списка издательств
private void FillPublishing()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idPublishing, namePublishing FROM
[Publishing]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
cbPublishing.DataSource = dataSet.Tables[0];
cbPublishing.DisplayMember = "namePublishing";
cbPublishing.ValueMember = "idPublishing";
}
//Заполнение списка авторов
private void FillAuthors()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idAuthor, shortName FROM
[Author]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
cbAuthor.DataSource = dataSet.Tables[0];
cbAuthor.DisplayMember = "shortName";
cbAuthor.ValueMember = "idAuthor";
}
//Заполнение списка жанров
private void FillGenres()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idGenre, genre FROM
[Genre]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
cbGenre.DataSource = dataSet.Tables[0];
cbGenre.DisplayMember = "genre";
cbGenre.ValueMember = "idGenre";
}
//Поиск
public static void Search(int selectedIndex, ComboBox cbSearch, DataGridView tableBooks)
{
DataSet dataSet = new DataSet();
switch (selectedIndex)
{
case 0:
{
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT Book.idBook as [Номер],
Book.name as [Название],
Warehouse.quantity as [Количество],
Warehouse.price as [Цена]
FROM [Book], [Warehouse]
WHERE Book.idBook = Warehouse.idBook AND
Book.name LIKE '%" + cbSearch.Text + "%'", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
}; break;
case 1:
{
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT Book.idBook as [Номер],
Book.name as [Название],
Warehouse.quantity as [Количество],
Warehouse.price as [Цена]
FROM [Book], [Warehouse], [Author], [BookAuthor]
WHERE Book.idBook = Warehouse.idBook AND
BookAuthor.idBook = Book.idBook AND
BookAuthor.idAuthor = Author.idAuthor AND
Author.shortName LIKE '%" + cbSearch.Text + "%'", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
}; break;
case 2:
{
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT Book.idBook as [Номер],
Book.name as [Название],
Warehouse.quantity as [Количество],
Warehouse.price as [Цена]
FROM [Book], [Warehouse], [Genre], [BookGenre]
WHERE Book.idBook = Warehouse.idBook AND
BookGenre.idBook = Book.idBook AND
BookGenre.idGenre = Genre.idGenre AND
Genre.genre LIKE '%" + cbSearch.Text + "%'", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
}; break;
}
tableBooks.DataSource = dataSet.Tables[0];
tableBooks.Columns[0].Width = 70;
tableBooks.Columns[1].Width = 200;
tableBooks.Columns[2].Width = 80;
tableBooks.Columns[3].Width = 80;
}
//Заполнение таблицы жанров
private void FillGenres()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idGenre as [Номер], genre as [Жанр] FROM
[Genre]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
tableInfo.DataSource = dataSet.Tables[0];
tableInfo.Columns[0].Width = 80;
lblInfo1.Text = "Жанр";
}
//Заполнение таблицы изданий
private void FillPublishing()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idPublishing as [Номер],
namePublishing as [Издательство]
FROM [Publishing]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
tableInfo.DataSource = dataSet.Tables[0];
tableInfo.Columns[0].Width = 80;
lblInfo1.Text = "Издательство";
}
//Удаление издательста, жанров или авторов
public static void DeleteInfo(string sqlQueryDel, DataGridView tableInfo)
{
try
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand commDel = new SqlCommand(sqlQueryDel, connection);
commDel.Parameters.AddWithValue("@Param", Convert.ToInt32(tableInfo.CurrentRow.Cells[0].Value));
commDel.ExecuteNonQuery();
}
}
catch { MessageBox.Show("Невозможно удалить!"); }
}
//Оформление поступления
private void buttonArrival_Click(object sender, EventArgs e)
{
if (tableArrival.RowCount > 0)
{
using (var connection = ClassConnection.SqlConnection())
{
connection.Open();
var commAddToArrival = new SqlCommand(@"INSERT INTO [Arrival] (idWorker, dateOfArrival, sum)
VALUES (@idWorker, @dateOfArrival, @sum)", connection);
commAddToArrival.Parameters.AddWithValue("@idWorker", ClassActiveUser.WorkerId);
commAddToArrival.Parameters.AddWithValue("@dateOfArrival", DateTime.Now);
commAddToArrival.Parameters.AddWithValue("@sum", Convert.ToDecimal(lblParamSum.Text));
commAddToArrival.ExecuteNonQuery();
var commSelMaxId = new SqlCommand(@"SELECT MAX(idArrival) FROM [Arrival]", connection);
SqlDataReader readerSelMaxId = commSelMaxId.ExecuteReader();
readerSelMaxId.Read();
int maxId = Convert.ToInt32(readerSelMaxId[0]);
readerSelMaxId.Close();
foreach (DataGridViewRow row in tableArrival.Rows)
{
var commAddToSellBook = new SqlCommand(@"INSERT INTO [ArrivalBook]
(idBook, idArrival, quantity, price1pc)
VALUES (@idBook, @idArrival, @quantity, @price1pc)", connection);
commAddToSellBook.Parameters.AddWithValue(@"idBook", Convert.ToInt32(row.Cells[0].Value));
commAddToSellBook.Parameters.AddWithValue(@"idArrival", maxId);
commAddToSellBook.Parameters.AddWithValue(@"quantity", Convert.ToInt32(row.Cells[2].Value));
commAddToSellBook.Parameters.AddWithValue(@"price1pc", Convert.ToDecimal(row.Cells[3].Value) / Convert.ToInt32(row.Cells[2].Value));
commAddToSellBook.ExecuteNonQuery();
var commUpdWarehouse = new SqlCommand(@"UPDATE [Warehouse]
SET quantity = (quantity + " + Convert.ToInt32(row.Cells[2].Value) + "), price = @Price WHERE Warehouse.idBook = " + Convert.ToInt32(row.Cells[0].Value) + "", connection);
commUpdWarehouse.Parameters.AddWithValue("@Price", Convert.ToDecimal(row.Cells[3].Value) / Convert.ToDecimal(row.Cells[2].Value));
commUpdWarehouse.ExecuteNonQuery();
}
}
ClassFillTable.FillTable(tableBooks);
tableArrival.Rows.Clear();
lblParamSum.Text = "0";
MessageBox.Show("Поступление оформлено!");
}
else
{
MessageBox.Show("Список поступления пуст!");
}
}
//Заполнение таблицы авторов
private void FillAuthors()
{
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT idAuthor as [Номер],
shortName as [Сокращенное имя],
fullName as [Полное имя]
FROM [Author]", ClassConnection.SqlConnection());
adapter.Fill(dataSet);
tableInfo.DataSource = dataSet.Tables[0];
tableInfo.Columns[0].Width = 80;
panelInfo2.Visible = true;
lblInfo1.Text = "Сокращенное имя автора";
lblInfo2.Text = "Полное имя автора";
}
//Заполнение информации о книге
private void FillPopularBookInfo()
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(sqlReportBook, connection);
adapter.SelectCommand.Parameters.AddWithValue("@idBook", Convert.ToInt32(tableReport.CurrentRow.Cells[0].Value));
adapter.Fill(dataSet);
tableReportBook.DataSource = dataSet.Tables[0];
tableReportBook.Columns[0].Width = 250;
tableReportBook.Columns[1].Width = 80;
tableReportBook.Columns[2].Width = 150;
}
}
//Вывод в таблицу отчета
private void FillTable()
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
var dataSetArrival = new DataSet();
var adapterArrival = new SqlDataAdapter(sqlReport, connection);
adapterArrival.SelectCommand.Parameters.AddWithValue("@dateOfBegin", dateTimePickerBegin.Value);
adapterArrival.SelectCommand.Parameters.AddWithValue("@dateOfEnd", dateTimePickerEnd.Value);
adapterArrival.Fill(dataSetArrival);
tableReport.DataSource = dataSetArrival.Tables[0];
tableReport.Columns[0].Width = 80;
tableReport.Columns[1].Width = 250;
tableReport.Columns[2].Width = 110;
tableReport.Columns[3].Width = 150;
}
}
public static void AddInfo(string type, string sqlQuery, ComboBox cbType)
{
using (var connection = ClassConnection.SqlConnection())
{
var items = from DataRowView i in cbType.Items
where i.Row.ItemArray[1].ToString() == type
select i;
if (!items.Any() && !string.IsNullOrWhiteSpace(type))
{
connection.Open();
SqlCommand commandAddType = new SqlCommand(sqlQuery, connection);
commandAddType.ExecuteNonQuery();
}
else
{
MessageBox.Show("Не удалось добавить!");
}
}
}
//Добавление новой информации
private void btnAdd_Click(object sender, EventArgs e)
{
switch (hat)
{
case "genres":
{
if (!string.IsNullOrWhiteSpace(tbInfo1.Text))
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand check = new SqlCommand(@"SELECT genre FROM [Genre] WHERE genre = '" + tbInfo1.Text + "'", connection);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такой жанр уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"INSERT INTO [Genre] (genre)
VALUES (@Genre)", connection);
command.Parameters.AddWithValue("@Genre", tbInfo1.Text);
command.ExecuteNonQuery();
FillGenres();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
case "publishing":
{
if (!string.IsNullOrWhiteSpace(tbInfo1.Text))
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand check = new SqlCommand(@"SELECT namePublishing FROM [Genre]
WHERE namePublishing = '" + tbInfo1.Text + "'", connection);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такое издательство уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"INSERT INTO [Publishing] (namePublishing)
VALUES (@NamePublishing)", connection);
command.Parameters.AddWithValue("@NamePublishing", tbInfo1.Text);
command.ExecuteNonQuery();
FillPublishing();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
case "authors":
{
if (!string.IsNullOrWhiteSpace(tbInfo1.Text) && !string.IsNullOrWhiteSpace(tbInfo2.Text))
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand check = new SqlCommand(@"SELECT shortName FROM [Author]
WHERE shortName ='" + tbInfo1.Text + "'", connection);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такой автор уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"INSERT INTO [Author] (shortName, fullName)
VALUES (@ShortName, @FullName)", connection);
command.Parameters.AddWithValue("@ShortName", tbInfo1.Text);
command.Parameters.AddWithValue("@FullName", tbInfo2.Text);
command.ExecuteNonQuery();
FillAuthors();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
}
}
//Продажа
private void buttonSell_Click(object sender, EventArgs e)
{
if (tableSell.RowCount > 0)
{
using (var connection = ClassConnection.SqlConnection())
{
connection.Open();
var commandAddToSell = new SqlCommand(@"INSERT INTO [Sell] (idWorker, dateOfSale, sum)
VALUES (@idWorker, @dateOfSale, @sum)", connection);
commandAddToSell.Parameters.AddWithValue("@idWorker", ClassActiveUser.WorkerId);
commandAddToSell.Parameters.AddWithValue("@dateOfSale", DateTime.Now);
commandAddToSell.Parameters.AddWithValue("@sum", Convert.ToDecimal(lblParamSum.Text));
commandAddToSell.ExecuteNonQuery();
var commandSelectMaxId = new SqlCommand(@"SELECT MAX(idSell) FROM [Sell]", connection);
SqlDataReader readerSelectMaxId = commandSelectMaxId.ExecuteReader();
readerSelectMaxId.Read();
int maxId = Convert.ToInt32(readerSelectMaxId[0]);
readerSelectMaxId.Close();
foreach (DataGridViewRow row in tableSell.Rows)
{
var commandAddToSellBook = new SqlCommand(@"INSERT INTO [SellBook]
(idBook, idSell, quantity, price1pc)
VALUES (@idBook, @idSell, @quantity, @price1pc)", connection);
commandAddToSellBook.Parameters.AddWithValue(@"idBook", Convert.ToInt32(row.Cells[0].Value));
commandAddToSellBook.Parameters.AddWithValue(@"idSell", maxId);
commandAddToSellBook.Parameters.AddWithValue(@"quantity", Convert.ToInt32(row.Cells[2].Value));
commandAddToSellBook.Parameters.AddWithValue(@"price1pc", Convert.ToInt32(row.Cells[3].Value) / Convert.ToInt32(row.Cells[2].Value));
commandAddToSellBook.ExecuteNonQuery();
var commandUpdateWarehouse = new SqlCommand(@"UPDATE [Warehouse]
SET quantity = (quantity - " + Convert.ToInt32(row.Cells[2].Value) + ") WHERE Warehouse.idBook = " + Convert.ToInt32(row.Cells[0].Value) + "", connection);
commandUpdateWarehouse.ExecuteNonQuery();
}
}
MessageBox.Show("Продажа произведена успешно!");
//Печать чека
Word.Application wordapp = new Word.Application();
wordapp.Visible = true;
Object template = Environment.CurrentDirectory + "\\TovarCheck.dotx";
Object newTemplate = false;
Object documentType = Word.WdNewDocumentType.wdNewBlankDocument;
Object visible = true;
Word._Document worddoc;
//Создаем документ 1
worddoc = wordapp.Documents.Add(ref template, ref newTemplate, ref documentType, ref
visible);
Object bookmarkNameObj = "Date";
Word.Range bookmarkRange = null;
bookmarkRange = worddoc.Bookmarks.get_Item(ref bookmarkNameObj).Range;
bookmarkRange.Text = Convert.ToDateTime(DateTime.Now).ToString();
bookmarkNameObj = "Number";
bookmarkRange = null;
bookmarkRange = worddoc.Bookmarks.get_Item(ref bookmarkNameObj).Range;
using (var connection = ClassConnection.SqlConnection())
{
connection.Open();
var comSelNumber = new SqlCommand(@"SELECT MAX(idSell) FROM [Sell]", connection);
SqlDataReader readerNumber = comSelNumber.ExecuteReader();
readerNumber.Read();
string sellNumber = readerNumber[0].ToString();
readerNumber.Close();
bookmarkRange.Text = sellNumber;
}
bookmarkNameObj = "FIO";
bookmarkRange = null;
bookmarkRange = worddoc.Bookmarks.get_Item(ref bookmarkNameObj).Range;
using (var connection = ClassConnection.SqlConnection())
{
connection.Open();
var comSelWorker = new SqlCommand(@"SELECT fullName FROM [Worker]
WHERE idWorker = " + ClassActiveUser.WorkerId + "", connection);
SqlDataReader readerWorker = comSelWorker.ExecuteReader();
readerWorker.Read();
string workerFullName = readerWorker[0].ToString();
readerWorker.Close();
bookmarkRange.Text = workerFullName;
}
bookmarkNameObj = "Total";
bookmarkRange = null;
bookmarkRange = worddoc.Bookmarks.get_Item(ref bookmarkNameObj).Range;
bookmarkRange.Text = lblParamSum.Text;
bookmarkNameObj = "Table";
bookmarkRange = null;
bookmarkRange = worddoc.Bookmarks.get_Item(ref bookmarkNameObj).Range;
Object behiavor = Word.WdDefaultTableBehavior.wdWord9TableBehavior;
Object autoFitBehiavor = Word.WdAutoFitBehavior.wdAutoFitFixed;
int rows = tableSell.RowCount + 1;
int columns = 4;
worddoc.Tables.Add(bookmarkRange, rows, columns, ref behiavor, ref autoFitBehiavor);
worddoc.Tables[1].Cell(1, 1).Range.Text = "№";
worddoc.Tables[1].Cell(1, 2).Range.Text = "Наименование товара";
worddoc.Tables[1].Cell(1, 3).Range.Text = "Количество";
worddoc.Tables[1].Cell(1, 4).Range.Text = "Цена";
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
if (tableSell.Rows.Count > i)
{
worddoc.Tables[1].Cell(i + 2, j + 1).Range.Text = tableSell.Rows[i].Cells[j].Value.ToString();
}
}
}
ClassFillTable.FillTable(tableBooks);
tableSell.Rows.Clear();
lblParamSum.Text = "0";
}
else
{
MessageBox.Show("Нечего продавать!");
}
}
public static void FillBookInfo(DataGridView tableBooks, PictureBox pictureBoxCover,
Label lblParamGenre, Label lblParamAuthor, Label lblParamNameBook, Label lblParamYearPublishing,
Label lblParamCoverType, Label lblParamPublishing, Label lblParamPageCount, Label lblParamAgeLimit, Label lblParamISBN,
RichTextBox richTextBoxDescription)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
lblParamGenre.Text = "";
lblParamAuthor.Text = "";
lblParamNameBook.Text = tableBooks.CurrentRow.Cells[1].Value.ToString();
var commandAuthor = new SqlCommand(@"SELECT Author.idAuthor, Author.shortName
FROM [Author], [Book], [BookAuthor]
WHERE Book.idBook = '" + tableBooks.CurrentRow.Cells[0].Value.ToString() + "' AND Author.idAuthor = BookAuthor.idAuthor AND BookAuthor.idBook = Book.idBook", connection);
var readerAuthor = commandAuthor.ExecuteReader();
if (readerAuthor.HasRows)
{
while (readerAuthor.Read())
{
lblParamAuthor.Text += readerAuthor[1].ToString() + " ";
}
readerAuthor.Close();
}
readerAuthor.Close();
var commandGenre = new SqlCommand(@"SELECT Genre.idGenre, Genre.genre
FROM [Genre], [Book], [BookGenre]
WHERE Book.idBook = '" + tableBooks.CurrentRow.Cells[0].Value.ToString() + "' AND Genre.idGenre = BookGenre.idGenre AND Book.idBook = BookGenre.idBook", connection);
var readerGenre = commandGenre.ExecuteReader();
if (readerGenre.HasRows)
{
while (readerGenre.Read())
{
lblParamGenre.Text += readerGenre[1].ToString() + " ";
}
readerGenre.Close();
}
readerGenre.Close();
SqlCommand commandBookInfo = new SqlCommand(@"SELECT
Book.yearOfPublishing, Book.CoverType, Publishing.namePublishing, Book.pageCount,
Book.ageLimit, Book.ISBN, Book.description, Book.cover
FROM [Book], [Author], [BookAuthor], [Genre], [BookGenre], [Publishing]
WHERE Book.idBook = '" + tableBooks.CurrentRow.Cells[0].Value.ToString() + "' AND " +
"Author.idAuthor = BookAuthor.idAuthor AND " +
"BookAuthor.idBook = Book.idBook AND " +
"Genre.idGenre = BookGenre.idGenre AND " +
"Book.idBook = BookGenre.idBook AND " +
"Book.idPublishing = Publishing.idPublishing", connection);
SqlDataReader reader = commandBookInfo.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
lblParamYearPublishing.Text = reader[0].ToString();
lblParamCoverType.Text = reader[1].ToString();
lblParamPublishing.Text = reader[2].ToString();
lblParamPageCount.Text = reader[3].ToString();
lblParamAgeLimit.Text = reader[4].ToString();
lblParamISBN.Text = reader[5].ToString();
richTextBoxDescription.Text = reader[6].ToString();
string path = reader[7].ToString();
if (path != "")
{
Bitmap image = new Bitmap(path);
pictureBoxCover.Image = image;
}
else
{
pictureBoxCover.Image = pictureBoxCover.ErrorImage;
}
}
reader.Close();
}
}
}
//Изменение информации о книге
private void btnEditBook_Click(object sender, EventArgs e)
{
if (!string.IsNullOrWhiteSpace(tbNameBook.Text) && !string.IsNullOrWhiteSpace(tbYearOfPublishing.Text) &&
!string.IsNullOrWhiteSpace(tbISBN.Text) && !string.IsNullOrWhiteSpace(cbAgeLimit.Text) &&
!string.IsNullOrWhiteSpace(cbCoverType.Text) && !string.IsNullOrWhiteSpace(richTextBoxDescription.Text) &&
!string.IsNullOrWhiteSpace(cbPublishing.Text) && numericUpDownPageCount.Value > 0 &&
tableAuthor.RowCount > 0 && tableGenre.RowCount > 0)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand commandUpdate = new SqlCommand(@"UPDATE [Book]
SET Book.name = @nameBook, Book.yearOfPublishing = @yearOfPublishing,
Book.pageCount = @pageCount, Book.ageLimit = @ageLimit,
Book.ISBN = @ISBN, Book.cover = @cover, Book.coverType = @coverType,
Book.description = @description, Book.idPublishing = @idPublishing
WHERE Book.idBook = '" + idBook + "'", connection);
commandUpdate.Parameters.AddWithValue("@nameBook", tbNameBook.Text);
commandUpdate.Parameters.AddWithValue("@yearOfPublishing", Convert.ToInt32(tbYearOfPublishing.Text));
commandUpdate.Parameters.AddWithValue("@pageCount", Convert.ToInt32(numericUpDownPageCount.Value));
commandUpdate.Parameters.AddWithValue("@ageLimit", cbAgeLimit.Text);
commandUpdate.Parameters.AddWithValue("@ISBN", tbISBN.Text);
commandUpdate.Parameters.AddWithValue("@cover", newPath);
commandUpdate.Parameters.AddWithValue("@coverType", cbCoverType.Text);
commandUpdate.Parameters.AddWithValue("@description", richTextBoxDescription.Text);
commandUpdate.Parameters.AddWithValue("@idPublishing", cbPublishing.SelectedValue);
commandUpdate.ExecuteNonQuery();
SqlCommand commDeleteGenre = new SqlCommand(@"DELETE FROM [BookGenre] WHERE idBook = " + idBook + "", connection);
commDeleteGenre.ExecuteNonQuery();
SqlCommand commDeleteAuthor = new SqlCommand(@"DELETE FROM [BookAuthor] WHERE idBook = " + idBook + "", connection);
commDeleteAuthor.ExecuteNonQuery();
foreach (DataGridViewRow row in tableGenre.Rows)
{
SqlCommand commInsertGenre = new SqlCommand(@"INSERT INTO [BookGenre]
(idBook, idGenre) VALUES (" + idBook + ", " + Convert.ToInt32(row.Cells[0].Value) + ")", connection);
commInsertGenre.ExecuteNonQuery();
}
foreach (DataGridViewRow row in tableAuthor.Rows)
{
SqlCommand commInsertAuthor = new SqlCommand(@"INSERT INTO [BookAuthor]
(idBook, idAuthor) VALUES (" + idBook + ", " + Convert.ToInt32(row.Cells[0].Value) + ")", connection);
commInsertAuthor.ExecuteNonQuery();
}
MessageBox.Show("Информация о книге успешно изменена!");
var fWarehouseman = new FormWarehouseman();
fWarehouseman.Show();
Hide();
}
}
else
{
MessageBox.Show("Не все поля заполены!");
}
}
//Заполнение информации для изменения
private void FillInformationForEdit()
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
var commandAuthor = new SqlCommand(@"SELECT Author.idAuthor, Author.shortName
FROM [Author], [Book], [BookAuthor]
WHERE Book.idBook = " + idBook + " AND Author.idAuthor = BookAuthor.idAuthor AND BookAuthor.idBook = Book.idBook", connection);
var readerAuthor = commandAuthor.ExecuteReader();
if (readerAuthor.HasRows)
{
while (readerAuthor.Read())
{
tableAuthor.Rows.Add(readerAuthor[0], readerAuthor[1]);
}
readerAuthor.Close();
}
readerAuthor.Close();
var commandGenre = new SqlCommand(@"SELECT Genre.idGenre, Genre.genre
FROM [Genre], [Book], [BookGenre]
WHERE Book.idBook = '" + idBook + "' AND Genre.idGenre = BookGenre.idGenre AND Book.idBook = BookGenre.idBook", connection);
var readerGenre = commandGenre.ExecuteReader();
if (readerGenre.HasRows)
{
while (readerGenre.Read())
{
tableGenre.Rows.Add(readerGenre[0], readerGenre[1]);
}
readerGenre.Close();
}
readerGenre.Close();
var commandBookInfo = new SqlCommand(@"SELECT Book.name,
Book.yearOfPublishing, Book.CoverType, Publishing.namePublishing, Book.pageCount,
Book.ageLimit, Book.ISBN, Book.description, Book.cover
FROM [Book], [Author], [BookAuthor], [Genre], [BookGenre], [Publishing]
WHERE Book.idBook = '" + idBook + "' AND " +
"Author.idAuthor = BookAuthor.idAuthor AND " +
"BookAuthor.idBook = Book.idBook AND " +
"Genre.idGenre = BookGenre.idGenre AND " +
"Book.idBook = BookGenre.idBook AND " +
"Book.idPublishing = Publishing.idPublishing", connection);
SqlDataReader reader = commandBookInfo.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
tbNameBook.Text = reader[0].ToString();
tbYearOfPublishing.Text = reader[1].ToString();
cbCoverType.Text = reader[2].ToString();
cbPublishing.Text = reader[3].ToString();
numericUpDownPageCount.Text = reader[4].ToString();
cbAgeLimit.Text = reader[5].ToString();
tbISBN.Text = reader[6].ToString();
richTextBoxDescription.Text = reader[7].ToString();
string path = reader[8].ToString();
newPath = reader[8].ToString();
if (path != "")
{
Bitmap image = new Bitmap(path);
pictureBoxCover.Image = image;
}
else
{
pictureBoxCover.Image = pictureBoxCover.ErrorImage;
}
}
reader.Close();
}
}
}
//Изменение существующей информации
private void btnEdit_Click(object sender, EventArgs e)
{
switch (hat)
{
case "genres":
{
if (tbInfo1.TextLength != 0)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand check = new SqlCommand(@"SELECT genre FROM [Genre] WHERE genre = @Genre", connection);
check.Parameters.AddWithValue("@Genre", tbInfo1.Text);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такой жанр уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"UPDATE [Genre] SET genre = @Genre
WHERE idGenre = @TableGenre", connection);
command.Parameters.AddWithValue("@Genre", tbInfo1.Text);
command.Parameters.AddWithValue("@TableGenre", tableInfo.CurrentRow.Cells[0].Value.ToString());
command.ExecuteNonQuery();
FillGenres();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
case "publishing":
{
if (tbInfo1.TextLength != 0)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
string checkPublishing = "SELECT namePublishing FROM [Publishing] WHERE namePublishing = " + tbInfo1.Text + "";
SqlCommand check = new SqlCommand(checkPublishing, connection);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такое издательство уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"UPDATE [Publishing] SET namePublishing = @NamePublishing
WHERE idPublishing = @IdPublishing", connection);
command.Parameters.AddWithValue("@NamePublishing", tbInfo1.Text);
command.Parameters.AddWithValue("@IdPublishing", tableInfo.CurrentRow.Cells[0].Value.ToString());
command.ExecuteNonQuery();
FillPublishing();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
case "authors":
{
if (tbInfo1.TextLength != 0 && tbInfo2.TextLength != 0)
{
using (SqlConnection connection = ClassConnection.SqlConnection())
{
connection.Open();
SqlCommand check = new SqlCommand(@"SELECT fullName FROM [Author]
WHERE fullName = @FullName", connection);
check.Parameters.AddWithValue("@FullName", tbInfo2.Text);
SqlDataReader reader = check.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show("Такой автор уже существует");
reader.Close();
}
else
{
reader.Close();
SqlCommand command = new SqlCommand(@"UPDATE [Author] SET
shortName = @ShortName, fullName = @FullName
WHERE idAuthor = @IdAuthor", connection);
command.Parameters.AddWithValue("@ShortName", tbInfo1.Text);
command.Parameters.AddWithValue("@FullName", tbInfo2.Text);
command.Parameters.AddWithValue("@IdAuthor", tableInfo.CurrentRow.Cells[0].Value.ToString());
command.ExecuteNonQuery();
FillAuthors();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены");
}
}; break;
}
}