myCommand.Parameters.Clear();

myCommand.Parameters.AddWithValue("kodcard", kodcard);

conn.Open();

myReader = myCommand.ExecuteReader();

string kodekz = "";

while (myReader.Read())

{

kodekz = myReader["id_ekz"].ToString();

string connectionString1 = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True";

SqlConnection conn1 = new SqlConnection(connectionString1);

using (var cmd1 = conn1.CreateCommand())

{

//Текст SQL запроса на обновление данных

cmd1.CommandText = "UPDATE Ekz SET exist=@ex WHERE id_ekz=@kodekz";

cmd1.Parameters.Clear();

cmd1.Parameters.AddWithValue("ex", true);

cmd1.Parameters.AddWithValue("kodekz", kodekz);

conn1.Open();

cmd1.ExecuteNonQuery();

conn1.Close();

}

}

myReader.Close();

conn.Close();

//Текст SQL запроса на удаление данных

cmd.CommandText = "DELETE FROM CardTmp WHERE kodcard=@id";

cmd.Parameters.Clear();

cmd.Parameters.AddWithValue("id", kodcard);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

cmd.CommandText = "DELETE FROM Card WHERE id_card=@id";

cmd.Parameters.Clear();

cmd.Parameters.AddWithValue("id", kodcard);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

kodcard = Guid.NewGuid().ToString();

cmd.CommandText = "INSERT INTO Card(id_card) VALUES(@id)";

cmd.Parameters.Clear();

cmd.Parameters.AddWithValue("id", kodcard);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

cmb();

private void button32_Click(object sender, EventArgs e)

//поиск данных о читателе

{

button30.Enabled = true;

persTableAdapter.Connection.Close();

persTableAdapter.Connection.Open();

persTableAdapter.Adapter.SelectCommand.CommandText = "SELECT id_pers, fio, paspdate FROM dbo.Pers WHERE fio LIKE '" + textBox14.Text + "%'";

persTableAdapter.Adapter.SelectCommand.Parameters.Clear();

persTableAdapter.Adapter.SelectCommand.ExecuteReader();

persTableAdapter.Connection.Close();

database1DataSet.Pers.Clear();

persTableAdapter.Adapter.Fill(database1DataSet.Pers);

if (database1DataSet.Pers.Count == 0)

{

ShowPers();

button30.Enabled = false;

//выдача сообщения о результатах поиска

MessageBox.Show("Ничего не найдено", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

}

private void button33_Click(object sender, EventArgs e)

{

if (radioButton25.Checked && comboBox9.SelectedIndex == 0 && comboBox10.SelectedIndex == 0)

{

DataTable12TableAdapter.Connection.Close();

DataTable12TableAdapter.Connection.Open();

DataTable12TableAdapter.Adapter.SelectCommand.CommandText = "SELECT Author.id_author, Author.fio, Book.id_izd, Book.kodauthor, Book.kodpubl, Book.kodcateg, Book.name, Book.year, " +

"Book.datereg, Book.kol, Categ.id_categ, Categ.name AS Expr1, Publ.id_publ, Publ.name AS Expr2 FROM Author, Book, Categ, Publ" +

" Where Author.id_author = Book.kodauthor AND Book.kodcateg = Categ.id_categ AND Book.kodpubl = Publ.id_publ AND Author.fio=@fio";

DataTable12TableAdapter.Adapter.SelectCommand.Parameters.Clear();

DataTable12TableAdapter.Adapter.SelectCommand.Parameters.AddWithValue("fio", comboBox8.Text);

DataTable12TableAdapter.Adapter.SelectCommand.ExecuteReader();

DataTable12TableAdapter.Connection.Close();

database1DataSet.DataTable12.Clear();

DataTable12TableAdapter.Adapter.Fill(database1DataSet.DataTable12);

this.DataTable12TableAdapter.Fill(this.database1DataSet.DataTable12);

if (database1DataSet.DataTable12.Count > 0)

{

//создание отчета по параметрам пользователя

reportViewer1.LocalReport.ReportEmbeddedResource = "Biblio.Report1.rdlc";

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter1", comboBox8.Text));

//считывание и установка параметров для построения отчетов

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter2", DateTime.Today.ToString("dd.MM.yyyy")));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter3", " "));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter4", " "));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter5", " "));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter6", " "));

reportViewer1.Clear();

this.reportViewer1.RefreshReport();

}

else

{

reportViewer1.Clear();

MessageBox.Show("Ничего не найдено", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

} //задание параметров для построения отчета

if (radioButton25.Checked && comboBox9.SelectedIndex == 0 && comboBox10.SelectedIndex == 1)

{

DataTable12TableAdapter.Connection.Close();

DataTable12TableAdapter.Connection.Open();

DataTable12TableAdapter.Adapter.SelectCommand.CommandText = "SELECT Author.id_author, Author.fio, Book.id_izd, Book.kodauthor, Book.kodpubl, Book.kodcateg, Book.name, Book.year, " +

"Book.datereg, Book.kol, Categ.id_categ, Categ.name AS Expr1, Publ.id_publ, Publ.name AS Expr2 FROM Author, Book, Categ, Publ" +

" Where Author.id_author = Book.kodauthor AND Book.kodcateg = Categ.id_categ AND Book.kodpubl = Publ.id_publ AND Publ.name=@nm";

DataTable12TableAdapter.Adapter.SelectCommand.Parameters.Clear();

DataTable12TableAdapter.Adapter.SelectCommand.Parameters.AddWithValue("nm", comboBox8.Text);

DataTable12TableAdapter.Adapter.SelectCommand.ExecuteReader();

DataTable12TableAdapter.Connection.Close();

database1DataSet.DataTable12.Clear();

DataTable12TableAdapter.Adapter.Fill(database1DataSet.DataTable12);

this.DataTable12TableAdapter.Fill(this.database1DataSet.DataTable12);

if (database1DataSet.DataTable12.Count > 0)

{ //создание отчета по параметрам пользователя

reportViewer1.LocalReport.ReportEmbeddedResource = "Biblio.Report2.rdlc";

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter1", comboBox8.Text));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter2", DateTime.Today.ToString("dd.MM.yyyy")));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter3", " "));

reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter("ReportParameter4", " "));

}}}

//Вкладка «Отчетность». Выбор раздела для построения отчета

private void radioButton25_CheckedChanged(object sender, EventArgs e)

{

if (radioButton25.Checked)

{

label63.Visible = true;

label63.Text = "Каталоги:";

dateTimePicker8.Visible = false;

comboBox9.Items.Clear();

comboBox9.Items.Add("Каталог книг");

comboBox9.Items.Add("Формуляр читателя");

comboBox9.SelectedIndex = 0;

}

}

//выбор критериев для построения отчета

private void comboBox9_SelectedIndexChanged(object sender, EventArgs e)

{

if (radioButton25.Checked)

{

switch (comboBox9.SelectedIndex)

{

case 0:

comboBox10.Items.Clear();

comboBox10.Items.Add("по автору");

comboBox10.Items.Add("по издательству");

comboBox10.Items.Add("по категории");

comboBox10.SelectedIndex = 0;

comboBox10.Visible = true;

comboBox8.Visible = true;

label65.Visible = true;

label65.Text = "Выберите критерий:";

break;

case 1:

comboBox8.Visible = false;

comboBox10.Visible = true;

label64.Visible = false;

label65.Visible = true;

label65.Text = "Выберите издание:";

bookTableAdapter.Connection.Close();

bookTableAdapter.Connection.Open();

//текст SQL запроса для выборки изданий из базы по заданному критерию

bookTableAdapter.Adapter.SelectCommand.CommandText = "SELECT id_izd, kodauthor, kodpubl, kodcateg, name, year, datereg, kol, foto FROM dbo.Book";

bookTableAdapter.Adapter.SelectCommand.ExecuteReader();

bookTableAdapter.Connection.Close();

database1DataSet.Book.Clear();

bookTableAdapter.Adapter.Fill(database1DataSet.Book);

comboBox10.Items.Clear();

int n = database1DataSet.Book.Count;

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox10.Items.Add(database1DataSet.Book.Rows[i]["name"]);

}

comboBox10.SelectedIndex = 0;

}

break;

case 2:

label64.Visible = false;

label65.Visible = false;

comboBox8.Visible = false;

comboBox10.Visible = false;

break;

}

}

if (radioButton21.Checked)

{

int n;

switch (comboBox9.SelectedIndex) //выбор даты для построения отчета

{

case 0:

comboBox8.Visible = false;

comboBox10.Visible = false;

label64.Visible = false;

label65.Text = "Выберите дату:";

label65.Visible = true;

dateTimePicker8.Value = DateTime.Today;

dateTimePicker8.Visible = true;

break;

case 1:

comboBox8.Visible = false;

comboBox10.Visible = true;

label64.Visible = false;

label65.Text = "Выберите читателя:";

label65.Visible = true;

dateTimePicker8.Visible = false;

n = database1DataSet.Pers.Count;

comboBox10.Items.Clear();

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox10.Items.Add(database1DataSet.Pers.Rows[i]["fio"]);

}

comboBox10.SelectedIndex = 0;

}

break;

case 2:

comboBox8.Visible = true;

comboBox10.Visible = true;

label64.Visible = true;

label64.Text = "Выберите название:";

label65.Text = "Выберите автора:";

label65.Visible = true;

dateTimePicker8.Visible = false;

n = database1DataSet.Author.Count;

comboBox10.Items.Clear();

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox10.Items.Add(database1DataSet.Author.Rows[i]["fio"]);

}

comboBox10.SelectedIndex = 0;

}

break;

}

}

//отчет по всем читателям

if (radioButton20.Checked && comboBox9.SelectedIndex==0)

{

int n = database1DataSet.Pers.Count;

comboBox10.Items.Clear();

comboBox10.Items.Add("Все читатели");

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox10.Items.Add(database1DataSet.Pers.Rows[i]["fio"]);

}

comboBox10.SelectedIndex = 0;

}

}

if (radioButton20.Checked && comboBox9.SelectedIndex == 1)

{

int n = database1DataSet.Pers.Count;

comboBox10.Items.Clear();

comboBox10.Items.Add("Все сотрудники");

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox10.Items.Add(database1DataSet.Pers.Rows[i]["fio"]);

}

comboBox10.SelectedIndex = 0;

}

}

}

//нет наличия в библиотеке

private void comboBox10_SelectedIndexChanged(object sender, EventArgs e)

{

int n;

if (radioButton25.Checked && comboBox9.SelectedIndex == 0)

{

label65.Visible = true;

label64.Visible = true;

switch (comboBox10.SelectedIndex)

{

case 0:

label64.Text = "Выберите автора:";

n = database1DataSet.Author.Count;

comboBox8.Items.Clear();

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox8.Items.Add(database1DataSet.Author.Rows[i]["fio"]);

}

comboBox8.SelectedIndex = 0;

}

break;

case 1:

label64.Text = "Выберите издательство:";

n = database1DataSet.Publ.Count;

comboBox8.Items.Clear();

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox8.Items.Add(database1DataSet.Publ.Rows[i]["name"]);

}

comboBox8.SelectedIndex = 0;

}

break;

case 2:

label64.Text = "Выберите жанр:";

n = database1DataSet.Categ.Count;

comboBox8.Items.Clear();

if (n > 0)

{

for (int i = 0; i < n; i++)

{

comboBox8.Items.Add(database1DataSet.Categ.Rows[i]["name"]);

}

comboBox8.SelectedIndex = 0;

}

break;

}

}

if (radioButton21.Checked && comboBox9.SelectedIndex == 2)

{//SQL запрос на выборку данных о книгах выбранного автора

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True";

SqlConnection conn = new SqlConnection(connectionString);

using (var cmd = conn.CreateCommand())

{

SqlDataReader myReader = null;

SqlCommand myCommand = new SqlCommand("SELECT Author.id_author, Author.fio, Book.kodauthor, Book.name FROM Author, Book WHERE Author.id_author=Book.kodauthor AND Author.fio=@fio", conn);

myCommand.Parameters.Clear();

myCommand.Parameters.AddWithValue("fio", comboBox10.Text);

conn.Open();

myReader = myCommand.ExecuteReader();

reestr = false;

comboBox8.Items.Clear();

while (myReader.Read())

{

comboBox8.Items.Add(myReader["name"].ToString());

}

if (comboBox8.Items.Count > 0)

{

comboBox8.SelectedIndex = 0;

}

else

{

comboBox8.Text = "";

}

myReader.Close();

conn.Close();

}

}

}

//метод выбора критериев для поиска

private void radioButton21_CheckedChanged(object sender, EventArgs e)

{

if (radioButton21.Checked)

{

comboBox8.Visible = false;

comboBox10.Visible = false;

label64.Visible = false;

label65.Text = "Выберите дату:";

label65.Visible = true;

label63.Text="Выбрать";

dateTimePicker8.Value = DateTime.Today;

dateTimePicker8.Visible = true;

comboBox9.Items.Clear();

comboBox9.Items.Add("по дате выдачи");

comboBox9.Items.Add("по сотруднику");

comboBox9.SelectedIndex = 0;

}

}

private void radioButton20_CheckedChanged(object sender, EventArgs e)

{

if (radioButton20.Checked)

{

comboBox8.Visible = false;

comboBox10.Visible = true;

label64.Visible = false;

label65.Text = "Выберите читателя:";

label65.Visible = true;

label63.Text = "Выбрать";

dateTimePicker8.Visible = false;

comboBox9.Items.Clear();

comboBox9.Items.Add("Возврат книг");

comboBox9.SelectedIndex = 0;

}

}

private void button34_Click(object sender, EventArgs e)

{

label67.Text = dataGridView14.CurrentRow.Cells[2].Value.ToString();

label69.Text = dataGridView14.CurrentRow.Cells[4].Value.ToString();

label69.Text = label69.Text.Substring(0, 10);

label71.Text = dataGridView14.CurrentRow.Cells[5].Value.ToString();

label71.Text = label71.Text.Substring(0, 10);

dataTable61TableAdapter.Connection.Close();

dataTable61TableAdapter.Connection.Open();

//SQL-запрос на выборку всех записей из истории выдачи книг читателю

dataTable61TableAdapter.Adapter.SelectCommand.CommandText = "SELECT CardTmp.kodekz, CardTmp.kodcard, Ekz.id_ekz, CardTmp.id, Ekz.kodizd, Ekz.nreestr, Book.kodauthor, " +

"Book.name, Book.id_izd, Author.id_author, Author.fio, Card.kodpers, Pers.id_pers, Pers.fio AS Expr1, " +

"Card.id_card, Card.datevyd, Card.datevozv, CardTmp.datefactvozv FROM CardTmp, Ekz, Book, Author, Card, Pers " +

"WHERE Pers.id_pers = Card.kodpers AND Card.id_card = CardTmp.kodcard AND CardTmp.kodekz = Ekz.id_ekz AND " +

"Author.id_author = Book.kodauthor AND Ekz.kodizd = Book.id_izd AND Pers.fio=@fio AND Card.id_card=@id";

dataTable61TableAdapter.Adapter.SelectCommand.Parameters.Clear();

dataTable61TableAdapter.Adapter.SelectCommand.Parameters.AddWithValue("fio", dataGridView14.CurrentRow.Cells[2].Value.ToString());

dataTable61TableAdapter.Adapter.SelectCommand.Parameters.AddWithValue("id", dataGridView14.CurrentRow.Cells[17].Value.ToString());

dataTable61TableAdapter.Adapter.SelectCommand.ExecuteReader();

dataTable61TableAdapter.Connection.Close();

database1DataSet.DataTable61.Clear();

dataTable61TableAdapter.Adapter.Fill(database1DataSet.DataTable61);

groupBox25.Visible = true;

}

//подключение справки в формате CHM

private void label1_Click(object sender, EventArgs e)

{

Process proc = new Process();

proc.StartInfo.FileName = @"help.chm";

proc.StartInfo.Arguments = "";

try

{

proc.Start();

}

//обработка исключительной ситуации

catch

{

MessageBox.Show("файл помощи help.chm не найден", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

}

}

}

Приложение Б

(справочное)

Формы выходных документов

Рисунок Б.1

Рисунок Б.2

Размещено на Allbest.ru