using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace FS图书馆
{
public partial class 图书管理系统 : Form
{
string Reader = null;
int 读者级别 = 3;//默认为3级别,正常的三个级别
bool isLogined = false;
public 图书管理系统()
{
InitializeComponent();
this.dateTimePicker.MinDate = DateTime.Now;//防止用户错误的将还书的时间设置在当前日期之前。
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button_确认查询_Click(object sender, EventArgs e)
{
bool[] isChecked = new bool[3];
string[] row = new string[3]{"[作者]","[出版社]","[关键词]"};
isChecked[0] = radioButton_作者.Checked;
isChecked[1] = radioButton_出版社.Checked;
isChecked[2] = radioButton_关键词.Checked;
int flag = -1;
for(int i=0;i<isChecked.Length;i++)
{
if(isChecked[i]==true)
{
flag = i;
break;
}
}
if(flag == -1)
{
MessageBox.Show("请选一个标签再进行检索");
}
else
{
//检索数据库，并且将结果显示在DataGridView上面
dataGridView_主界面.DataSource = null;
string message = textBox_检索信息.Text;
String sql = String.Format("select * from TypeBook left join SingleBook on " +
" SingleBook.书目号 = TypeBook.书目号 where TypeBook.书目号 in " +
" (select 书目号 from TypeBook where {0} like N'%{1}%')",row[flag],message);
DataSet ds = DB.select_DataSet(sql,"SingleBook");
dataGridView_主界面.DataSource = ds.Tables["SingleBook"];
}
}
private void 图书管理系统_Load(object sender, EventArgs e)
{
}
private void button_登录_Click(object sender, EventArgs e)
{
Reader = txtbox_学工号.Text;
string password = txtbox_密码.Text;
txtbox_学工号.Text = null;
txtbox_密码.Text = null;
string sql = String.Format("select count(*) from Reader where 学工号=N'{0}' " +
" and 密码 = N'{1}'",Reader,password);
int n = (int)DB.select_count(sql);
if(n==1)
{
MessageBox.Show("登录成功");
this.isLogined = true;
string connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=C:\USERS\WANGGONGSHENG\APPDATA\LOCAL\MICROSOFT\MICROSOFT SQL SERVER LOCAL DB\INSTANCES\MSSQLLOCALDB\NEWLIBRARYKEDA.MDF;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
sql = String.Format("select 读者级别 from Reader where 学工号=N'{0}'", Reader);
try
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
读者级别 = (int)comm.ExecuteScalar();
//显示一下读者等级
DataSet ds = DB.select_DataSet(String.Format("select 读者级别 from Reader where 学工号=N'{0}'", Reader), "读者级别");
dataGridView_主界面.DataSource = ds.Tables["读者级别"];
//
conn.Close();
}
catch(Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
else
{
MessageBox.Show("账号或者密码错误，请重新输入");
txtbox_学工号.Text = null;
txtbox_密码.Text = null;
}
}
private void button_退出_Click(object sender, EventArgs e)
{
Reader = null;
isLogined = false;
读者级别 = 3;
dataGridView_主界面.DataSource = null;//清空
}
private void button_读者中心_Click(object sender, EventArgs e)
{
}
private void button_确认归还_Click(object sender, EventArgs e)
{
if(isLogined)
{
string 图书编号 = txtReturnBookId.Text;
string connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=C:\USERS\WANGGONGSHENG\APPDATA\LOCAL\MICROSOFT\MICROSOFT SQL SERVER LOCAL DB\INSTANCES\MSSQLLOCALDB\NEWLIBRARYKEDA.MDF;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
string sql1 = String.Format("select count(*) from [dbo].[SingleBook] where [图书编号]=N'{0}' and [借阅人]=N'{1}'",图书编号,Reader);
string sql2 = String.Format("select [书名] from [dbo].[TypeBook],[dbo].[SingleBook] where [dbo].[SingleBook].[图书编号]=N'{0}' and [dbo].[SingleBook].[书目号]=[dbo].[TypeBook].[书目号]",图书编号);
string sql3 = String.Format("select [书目号] from [dbo].[SingleBook] where [dbo].[SingleBook].[图书编号]=N'{0}'",图书编号);
try
{
conn.Open();
SqlCommand comm1 = new SqlCommand(sql1,conn);
SqlCommand comm2 = new SqlCommand(sql2,conn);
SqlCommand comm3 = new SqlCommand(sql3, conn);
int returnNumber = (int)comm1.ExecuteScalar();
string bookName = (string)comm2.ExecuteScalar();
string 书目号 = (string)comm3.ExecuteScalar();
conn.Close();
if (returnNumber == 0)
{
MessageBox.Show(String.Format("您在图书管理系统中没有借阅{0}的记录，[图书编号]={1}", bookName, 图书编号));
}
else
{
sql1 = String.Format("update [dbo].[TypeBook] set [可借阅数量] = [可借阅数量]+1 where [书目号]=N'{0}'",书目号);
sql2 = String.Format("update [dbo].[SingleBook] set [借阅人]='0',[借出状态]='可借阅'，[借出时间]=null,[应该归还时间]=null");
comm1 = new SqlCommand(sql1,conn);
comm2 = new SqlCommand(sql2,conn);
try
{
conn.Open();
comm1.ExecuteNonQuery();
comm2.ExecuteNonQuery();
MessageBox.Show(String.Format("成功归还图书:{0}，编号：{1}",bookName,图书编号));
conn.Close();
}catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
catch(Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
else
{
MessageBox.Show("请登录之后在进行归还操作！谢谢");
}
}
private void button_确认借出_Click(object sender, EventArgs e)
{
if(isLogined)
{
string 图书编号 = txtBorrowBookId.Text;
string connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=C:\USERS\WANGGONGSHENG\APPDATA\LOCAL\MICROSOFT\MICROSOFT SQL SERVER LOCAL DB\INSTANCES\MSSQLLOCALDB\NEWLIBRARYKEDA.MDF;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
string sql1 = String.Format("select [dbo].[TypeBook].[可借阅数量] from [dbo].[TypeBook],[dbo].[SingleBook] " +
"where [图书编号]='{0}' and [dbo].[SingleBook].[书目号]=[dbo].[TypeBook].[书目号]", 图书编号);
string sql2 = String.Format("select [dbo].[TypeBook].[书名] from [dbo].[TypeBook],[dbo].[SingleBook] " +
"where [图书编号]='{0}' and [dbo].[SingleBook].[书目号]=[dbo].[TypeBook].[书目号]", 图书编号);
string sql3 = String.Format("select [书目号] from [dbo].[SingleBook] where [图书编号]='{0}'",图书编号);
try
{
conn.Open();
SqlCommand comm1 = new SqlCommand(sql1, conn);
SqlCommand comm2 = new SqlCommand(sql2, conn);
SqlCommand comm3 = new SqlCommand(sql3, conn);
int sumBookNumber = (int)comm1.ExecuteScalar();
string bookName = (string)comm2.ExecuteScalar();
string 书目号 = (string)comm3.ExecuteScalar();
conn.Close();
if (sumBookNumber == 0)
{
MessageBox.Show(String.Format("对不起,{0}当前可借阅的数量为0.借阅操作失败！", bookName));
}
else
{
string returnBookTime = dateTimePicker.Value.Date.ToLongDateString();
string borrowBookTime = DateTime.Now.ToLongDateString();
sql1 = String.Format("update [dbo].[SingleBook] set [借阅人]=N'{0}',[借出状态]=N'{1}',[借出时间]=N'{2}',[应该归还时间]=N'{3}' where [图书编号]='{4}'", Reader, "已借出", borrowBookTime, returnBookTime,图书编号);
sql2 = String.Format("update [dbo].[TypeBook] set [可借阅数量]=[可借阅数量]-1 where [dbo].[TypeBook].[书目号]='{0}'", 图书编号);
try
{
conn.Open();
comm1 = new SqlCommand(sql1, conn);
comm2 = new SqlCommand(sql2, conn);
//comm1.ExecuteNonQuery();
comm2.ExecuteNonQuery();
MessageBox.Show(String.Format("OK,借阅操作成功！"));
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
else
{
MessageBox.Show("请登录之后在进行借阅操作！谢谢");
}
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
}
}
}
