private void 我的借阅情况ToolStripMenuItem_Click(object sender, EventArgs e)
{
panel4.Visible = true;
panel5.Visible = true;
panel1.Visible = false;
panel2.Visible = true;
panel3.Visible = false;
textBox6.Text = DateTime.Now.ToShortDateString().ToString();
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
string strQuery1 = "select Borrow.Bid as 书籍编号, Borrow.Sid as 读者编号, Borrowtime as 借出时间 From Borrow,Reback Where Returntime='未还 ' and Borrow.Bid=Reback.Bid and Borrow.Sid=Reback.Sid and Borrow.Sid='" + str0 + "'";
string strQuery2 = "select Bid as 书籍编号, Sid as 读者编号, Returntime as 归还时间 from Reback Where Sid='" + str0 + "' and Returntime!='未还 '";
try
{
DataSet ds1 = sqlHelper.GetDataSet(strQuery1);
DataTable dt1 = ds1.Tables[0];
dataGridView2.DataSource = dt1;
DataSet ds2 = sqlHelper.GetDataSet(strQuery2);
DataTable dt2 = ds2.Tables[0];
dataGridView3.DataSource = dt2;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void 借阅书籍ToolStripMenuItem_Click(object sender, EventArgs e)
{
panel3.Visible = true;
panel2.Visible = true;
panel1.Visible = false;
panel4.Visible = false;
panel5.Visible = false;
textBox4.Text = DateTime.Now.ToShortDateString().ToString();
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
string strQuery = "select Bid as 书籍编号, Bname as 书名, Bpress as 出版社, Bstate as 状态, Bwriter as 作者, Bdate as 出版时间 from Books";
try
{
DataSet ds = sqlHelper.GetDataSet(strQuery);
DataTable dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void reg_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(str0))
{
}
else
{
string strQuery1 = "Select Sname as 用户姓名 From Students Where Sid='" + str0 + "' ";
string strQuery2 = "Select Spwd as 密码 From Students Where Sid='" + str0 + "' ";
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand cmd1 = new SqlCommand(strQuery1, con);
SqlCommand cmd2 = new SqlCommand(strQuery2, con);
con.Open();
id1 = (string)cmd1.ExecuteScalar();
id2 = (string)cmd2.ExecuteScalar();
textBox1.Text = str0;
textBox2.Text = id1;
textBox3.Text = id2;
}
}
private void button3_Click(object sender, EventArgs e)
{
//刷新显示
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
string strQuery = "select Bid as 书籍编号, Bname as 书名, Bpress as 出版社, Bstate as 状态, Bwriter as 作者, Bdate as 出版时间 from Books";
try
{
DataSet ds = sqlHelper.GetDataSet(strQuery);
DataTable dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
textBox1.Text = null;
textBox2.Text = null;
textBox3.Text = null;
comboBox1.Text = null;
textBox5.Text = null;
dateTimePicker1.Value = DateTime.Now;
}
private void 用户管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
K = 1;
panel1.Visible = false;
panel4.Visible = true;
panel6.Visible = false;
panel5.Visible = false;
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
string strQuery = "select Sid as 学号, Sname as 姓名 from Students";
try
{
DataSet ds = sqlHelper.GetDataSet(strQuery);
DataTable dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void 书籍借阅管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
K = 2;
panel1.Visible = false;
panel4.Visible = false;
panel6.Visible = false;
panel5.Visible = false;
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
string strQuery = "select Borrow.Bid as 书籍编号, Borrow.Sid as 读者编号, Borrowtime as 借出时间, Returntime as 归还时间 From Borrow, Reback Where Borrow.Bid=Reback.Bid";
try
{
DataSet ds = sqlHelper.GetDataSet(strQuery);
DataTable dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/*
* private void button4_Click(object sender, EventArgs e)
* {
* //textBox3.Text = dataGridView2.SelectedRows[0].Cells["书籍编号"].Value.ToString();
* string str1 = textBox3.Text.Trim();
* string str2 = textBox5.Text.Trim();
*
* DateTime dtDate;
*
* if (DateTime.TryParse(str2, out dtDate))
* {
* string strQuery1 = "Select Bnum From Borrow Where Bnum='" + str1 + "'AND Btime='" + str2 + "' AND Unum='" + str0 + "'";
* string strUpdata = "Update Borrow Set cost=0 Where Bnum='" + str1 + "'AND Btime='" + str2 + "' AND Unum='" + str0 + "'";
*
* Sql1 sqlHelper = new Sql1();
* SqlConnection con = sqlHelper.GetConnection();
* SqlCommand cmd1 = new SqlCommand(strQuery1, con);
* con.Open();
* string id1 = (string)cmd1.ExecuteScalar();
* if (string.IsNullOrEmpty(id1))
* {
* MessageBox.Show("未查到该记录！请检查输入是否正确");
* return;
* }
*
* try
* {
* sqlHelper.OperateTb(strUpdata);
* MessageBox.Show("缴纳成功");
* 我的借阅情况ToolStripMenuItem_Click(null, null);//刷新页面
* }
* catch (Exception ex)
* {
* MessageBox.Show(ex.Message);
* }
* }
* else
* {
* MessageBox.Show("不是正确的日期格式类型！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
* }
* }*/
private void button3_Click(object sender, EventArgs e)
{
if (textBox2.Text == "" || textBox2.Text.Length > 10)
{
MessageBox.Show("请输入正确的书籍编号！");
}
else
{
string str1 = textBox3.Text.Trim();
string str3 = textBox6.Text.Trim();
//更改书籍状态为可借
string strUpdata1 = "Update Books Set Bstate='可借' Where Bid='" + str1 + "'";
//删除借阅记录
string strDelete = "Delete From Borrow Where Sid='" + str0 + "' AND Bid='" + str1 + "'";
//增加归还记录
string strInsert2 = "Update Reback Set Returntime='" + str3 + "' Where Bid='" + str1 + "'And Sid='" + str0 + "'";
string strQuery = "Select Bid From Borrow Where Sid='" + str0 + "' and Bid='" + str1 + "'";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand cmd = new SqlCommand(strQuery, con);
con.Open();
string id2 = (string)cmd.ExecuteScalar();
if (string.IsNullOrEmpty(id2))
{
MessageBox.Show("未借阅该书！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
sqlHelper.OperateTb(strUpdata1);
sqlHelper.OperateTb(strInsert2);
sqlHelper.OperateTb(strDelete);
MessageBox.Show("还书成功");
我的借阅情况ToolStripMenuItem_Click(null, null);//刷新页面
textBox3.Text = null;
textBox6.Text = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox1.Text.Length > 10)
{
MessageBox.Show("请输入小于11位的学号！");
}
else if (textBox2.Text == "")
{
MessageBox.Show("请输入姓名！");
}
else if (textBox3.Text == "" || textBox3.Text.Length > 10)
{
MessageBox.Show("请输入小于11位的密码！");
}
else
{
string Unumstr = textBox1.Text.Trim();
string Unamestr = textBox2.Text.Trim();
string Upwdstr = textBox3.Text.Trim();
string strInsert = "Insert Into Students(Sid,Sname,Spwd) Values('"
+ Unumstr + "','" + Unamestr + "','" + Upwdstr + "')";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
SqlCommand com = new SqlCommand("select Sid from Students where Sid='" + Unumstr + "'", con);
SqlDataReader sdr = com.ExecuteReader();
if (sdr.HasRows)
{
MessageBox.Show("用户已存在！");
}
else
{
int intResult = sqlHelper.OperateTb(strInsert);
MessageBox.Show("添加成功！");
}
this.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox2.Text.Trim() == "")
{
MessageBox.Show("请输入姓名！");
}
else if (textBox3.Text == "" || textBox3.Text.Length > 10)
{
MessageBox.Show("请输入小于11位的密码！");
}
else
{
string Unumstr = str0;
string Unamestr = textBox2.Text.Trim();
string Upwdstr = textBox3.Text.Trim();
string strUpdata = "Update Students Set Sname='" + Unamestr
+ "',Spwd='" + Upwdstr + "' Where Sid='" + Unumstr + "'";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
SqlCommand com = new SqlCommand("select Sid from Students where Sid='" + Unumstr + "'", con);
SqlDataReader sdr = com.ExecuteReader();
if (sdr.HasRows)
{
sqlHelper.OperateTb(strUpdata);
MessageBox.Show("修改成功！");
this.Close();
con.Close();
}
else
{
MessageBox.Show("修改失败！不存在该用户");
textBox2.Text = null;
textBox3.Text = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox1.Text.Length > 10)
{
MessageBox.Show("请输入小于11位的工号！");
}
else if (textBox3.Text == "" || textBox3.Text.Length > 10)
{
MessageBox.Show("请输入小于11位的密码！");
}
else
{
string Adnumstr = textBox1.Text.Trim();
string Admimastr = textBox3.Text.Trim();
string strInsert = "Insert Into Administrators(Aid,Apwd) Values('"
+ Adnumstr + "','" + Admimastr + "')";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
SqlCommand com = new SqlCommand("select Aid from Administrators where Aid='" + Adnumstr + "'", con);
SqlDataReader sdr = com.ExecuteReader();
if (sdr.HasRows)
{
MessageBox.Show("管理员已存在！");
}
else
{
int intResult = sqlHelper.OperateTb(strInsert);
MessageBox.Show("添加成功！");
}
this.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
}
private void memad_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(str0))
{
}
else
{
string strQuery1 = "Select Aid as 工号 From Administrators Where Aid='" + str0 + "' ";
string strQuery2 = "Select Apwd as 密码 From Administrators Where Aid='" + str0 + "' ";
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand cmd1 = new SqlCommand(strQuery1, con);
SqlCommand cmd2 = new SqlCommand(strQuery2, con);
con.Open();
id1 = (string)cmd1.ExecuteScalar();
id2 = (string)cmd2.ExecuteScalar();
textBox3.Text = id2;
}
}
private void button4_Click(object sender, EventArgs e)
{
//删除书籍
if (textBox1.Text.Trim() == "")
{
MessageBox.Show("请输入书籍编号！");
}
else
{
string Bid = textBox1.Text.Trim();
DialogResult dialogResult = MessageBox.Show("你确定要删除编号为：" + Bid + "的这本书籍的信息吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.No)
{
return;
}
string strSelect = "Select Bid From Books Where Bid='" + Bid + "'";
string strDelete = "Delete From Books Where Bid='" + Bid + "'";
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand cmd = new SqlCommand(strSelect, con);
con.Open();
if (cmd.ExecuteScalar() == null)
{
MessageBox.Show("不存在该书籍编号！");
}
else
{
try
{
sqlHelper.OperateTb(strDelete);
MessageBox.Show("删除成功！");
书籍管理ToolStripMenuItem_Click(null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox2.Text == "" || textBox2.Text.Length > 10)
{
MessageBox.Show("请输入正确的书籍编号！");
}
else
{
//借阅书籍
string str1 = textBox2.Text.Trim(); // 编号
string str2 = textBox4.Text.Trim(); // 时间
//检查是否可借
string strQuery = "Select Bstate From Books Where Bid='" + str1 + "' ";
//检查该用户是否已借过该书
string strQuery0 = "Select Sid From Borrow Where Bid='" + str1 + "' AND Sid='" + str0 + "'";
//更新状态
string strUpdata = "Update Books Set Bstate='借出' Where Bid='" + str1 + "'AND Bid='" + str1 + "'";
//增加借阅记录
string strInsert2 = "Insert Into Borrow(Bid,Sid,Borrowtime) Values('"
+ str1 + "','" + str0 + "','" + str2 + "')";
//增加归还记录
string strInsert3 = "Insert Into Reback(Bid,Sid,Returntime) Values('"
+ str1 + "','" + str0 + "','未还')";
string strDelete = "Delete From Reback Where Sid='" + str0 + "' AND Bid='" + str1 + "'";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand cmd = new SqlCommand(strQuery, con);
SqlCommand cmd2 = new SqlCommand(strQuery0, con);
con.Open();
string id1 = (string)cmd.ExecuteScalar();
id1 = id1.Trim();
string id2 = (string)cmd2.ExecuteScalar();
if ((id1 == "可借") && (string.IsNullOrEmpty(id2)))
{
sqlHelper.OperateTb(strUpdata);
sqlHelper.OperateTb(strInsert2);
sqlHelper.OperateTb(strDelete);
sqlHelper.OperateTb(strInsert3);
MessageBox.Show("借阅成功");
借阅书籍ToolStripMenuItem_Click(null, null);//刷新页面
textBox2.Text = null;
textBox1.Text = null;
}
else if (id1 == "借出") // &&(string.IsNullOrEmpty(id2))
{
MessageBox.Show("书已借出，借书失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else // if (!string.IsNullOrEmpty(id2))
{
MessageBox.Show("已借阅过该书（不允许重复借阅）", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("该书籍编号不存在！");
}
}
}
private void button1_Click(object sender, EventArgs e) // 登录
{
string userid = textBox1.Text.Trim();
string userpw = textBox2.Text.Trim();
if (radioButton2.Checked == true)
{
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand com = new SqlCommand("select Aid,Apwd from Administrators where Aid='" + userid + "' and Apwd='" + userpw + "'", con);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
int n = da.Fill(ds, "register");
if (n != 0)
{
Admin f = new Admin();
f.ShowDialog();
textBox1.Text = null;
textBox2.Text = null;
}
else
{
MessageBox.Show("请输入正确的账号和密码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox2.Text = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
SqlCommand com = new SqlCommand("select Sid,Spwd from Students where Sid='" + userid + "' and Spwd='" + userpw + "'", con);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
int n = da.Fill(ds, "register");
if (n != 0)
{
Puser f = new Puser();
f.str0 = userid;
f.ShowDialog();
textBox1.Text = null;
textBox2.Text = null;
}
else
{
MessageBox.Show("请输入正确的账号和密码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox2.Text = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void button2_Click(object sender, EventArgs e)
{
//修改书籍
DateTime dt = new DateTime();
dt = DateTime.Now;
if (textBox1.Text.Trim() == "")
{
MessageBox.Show("请输入书籍编号！");
}
else if (textBox2.Text.Trim() == "")
{
MessageBox.Show("请输入书名！");
}
else if (textBox3.Text.Trim() == "")
{
MessageBox.Show("请输入出版社！");
}
else if (comboBox1.Text.Trim() == "")
{
MessageBox.Show("请输入状态！");
}
else if (textBox5.Text.Trim() == "")
{
MessageBox.Show("请输入作者！");
}
else if (DateTime.Compare(dateTimePicker1.Value.Date, dt) > 0)
{
MessageBox.Show("日期超前，请选择正确日期！");
}
else
{
string Bid = textBox1.Text.Trim();
string Bname = textBox2.Text.Trim();
string Bpress = textBox3.Text.Trim();
string Bstate = comboBox1.Text.Trim();
string Bwriter = textBox5.Text.Trim();
string Bdate = dateTimePicker1.Value.Date.ToShortDateString();
string strUpdata = "Update Books Set Bid='" + Bid + "',Bname='" + Bname + "',Bpress='" + Bpress + "',Bstate='" + Bstate + "',Bwriter='" + Bwriter + "',Bdate='" + Bdate + "' Where Bid='" + Bid + "'";
try
{
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
con.Open();
SqlCommand com = new SqlCommand("select Bid from Books where Bid='" + Bid + "'", con);
SqlDataReader sdr = com.ExecuteReader();
if (sdr.HasRows)
{
sqlHelper.OperateTb(strUpdata);
MessageBox.Show("修改成功！");
书籍管理ToolStripMenuItem_Click(null, null);
}
else
{
MessageBox.Show("修改失败！不存在该书籍");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//添加书籍
DateTime dt = new DateTime();
dt = DateTime.Now;
if (textBox1.Text.Trim() == "")
{
MessageBox.Show("请输入书籍编号！");
}
else if (textBox2.Text.Trim() == "")
{
MessageBox.Show("请输入书名！");
}
else if (textBox3.Text.Trim() == "")
{
MessageBox.Show("请输入出版社！");
}
else if (comboBox1.Text.Trim() == "")
{
MessageBox.Show("请输入状态！");
}
else if (textBox5.Text.Trim() == "")
{
MessageBox.Show("请输入作者！");
}
else if (DateTime.Compare(dateTimePicker1.Value.Date, dt) > 0)
{
MessageBox.Show("日期超前，请选择正确日期！");
}
else
{
string Bid = textBox1.Text.Trim();
string Bname = textBox2.Text.Trim();
string Bpress = textBox3.Text.Trim();
string Bstate = comboBox1.Text.Trim();
string Bwriter = textBox5.Text.Trim();
string Bdate = dateTimePicker1.Value.Date.ToShortDateString();
string strInsert = "Insert Into Books(Bid,Bname,Bpress,Bstate,Bwriter,Bdate) Values('"
+ Bid + "','" + Bname + "','" + Bpress + "','" + Bstate + "','" + Bwriter + "','" + Bdate + "')";
//string strQuery1 = "Select Bstate as 状态 From Books Where Bstate='" + comboBox1.Text + "' ";
Sql1 sqlHelper = new Sql1();
SqlConnection con = sqlHelper.GetConnection();
// SqlCommand cmd1 = new SqlCommand(strQuery1, con);
con.Open();
// string id1 = (string)cmd1.ExecuteScalar();
try
{
//con.Open();
SqlCommand com = new SqlCommand("select Bid from Books where Bid='" + Bid + "'", con);
SqlDataReader sdr = com.ExecuteReader();
if (sdr.HasRows)
{
MessageBox.Show("该书籍已存在！");
}
else
{
int intResult = sqlHelper.OperateTb(strInsert);
MessageBox.Show("成功添加" + intResult + "记录！");
书籍管理ToolStripMenuItem_Click(null, null);
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
