//加载窗口
private void A_returnBook_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlCommand selectCmd = new SqlCommand("select * from returntable", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string RID, BID, Bname;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
RID = datareader["RID"].ToString();
Bname = datareader["Bname"].ToString();
BID = datareader["BID"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = RID.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = BID.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bname.Trim();
}
}
catch
{
MessageBox.Show("操作错误！");
}
}
//添加数据的命令操作
public static void insert(string insertStr)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = SQLbase.getConn();
conn.Open();
cmd = new SqlCommand(insertStr, conn);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("数据添加成功！", "消息");
}
else
{
MessageBox.Show("数据添加失败！", "消息");
}
}
catch (SqlException e)
{
MessageBox.Show("数据库异常" + e.Message, "消息");
}
finally
{
SQLbase.close(cmd);
SQLbase.close(conn);
}
}
//加载初始信息
private void A_updateInfo_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string AID = LogWindow.logname;
SqlCommand selectCmd = new SqlCommand("select * from Administrators where AID='" + AID + "'", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
if (datareader.Read())
{
label_ID.Text = AID;
txt_name.Text = datareader["Aname"].ToString().Trim();
txt_gender.Text = datareader["Agender"].ToString().Trim();
txt_address.Text = datareader["Aaddress"].ToString().Trim();
txt_number.Text = datareader["Anumber"].ToString().Trim();
txt_phone.Text = datareader["Atel"].ToString().Trim();
txt_pwd.Text = datareader["Apassword"].ToString().Trim();
}
else
{
MessageBox.Show("查询出错");
}
}
catch
{
MessageBox.Show("操作有误！", "错误提示");
}
SQLbase.close(conn);
}
private void button1_Click(object sender, EventArgs e)
{
string id, name, gender, address, age, unit, pwd, type;
id = textBox1.Text.ToString();
name = textBox2.Text.ToString();
gender = textBox3.Text.ToString();
address = textBox4.Text.ToString();
age = textBox5.Text.ToString();
unit = textBox6.Text.ToString();
pwd = textBox7.Text.ToString();
type = textBox8.Text.ToString();
//确认必填项
if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(name) || string.IsNullOrEmpty(pwd))
{
MessageBox.Show("请把必填项填写完整！");
}
else
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string insertStr = "insert into Readers values('" + id
+ "','" + name + "','" + gender + "','" + address
+ "','" + age + "','" + unit + "','" + type + "','" + pwd + "',0)";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);
//对于Update、Insert和Delete语句，返回值为该命令所影响的行数。
//对于所有其他类型的语句，返回值为 - 1。
int RecordsAffected = insertCmd.ExecuteNonQuery();
if (RecordsAffected == 1)
{
MessageBox.Show("添加成功！重新查询即可查看记录");
}
else
{
MessageBox.Show("添加失败！");
}
SQLbase.close(insertCmd);
}
catch (SqlException)
{
MessageBox.Show(" 添加失败！\n 请重新填写信息！");
}
SQLbase.close(conn);
this.Close();
}
}
//确认图书入库
private void button1_Click(object sender, EventArgs e)
{
//
string id, name, author, publisher, type;
id = textBox1.Text.ToString();
name = textBox2.Text.ToString();
author = textBox3.Text.ToString();
publisher = textBox4.Text.ToString();
type = textBox5.Text.ToString();
//确认必填项
if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(name))
{
MessageBox.Show("请把必填项填写完整！");
}
else
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string insertStr = "insert into Books values('" + id
+ "','" + name + "','" + author + "','" + publisher
+ "','" + type + "',1)";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);
//对于Update、Insert和Delete语句，返回值为该命令所影响的行数。
//对于所有其他类型的语句，返回值为 - 1。
int RecordsAffected = insertCmd.ExecuteNonQuery();
if (RecordsAffected == 1)
{
MessageBox.Show("入库成功！重新查询即可查看");
}
else
{
MessageBox.Show("入库失败！");
}
SQLbase.close(insertCmd);
}
catch (SqlException)
{
MessageBox.Show(" 入库失败！\n 请重新填写信息！");
}
SQLbase.close(conn);
this.Close();
}
}
//借阅按钮的响应事件
private void button2_Click(object sender, EventArgs e)
{
if (this.datagridview.SelectedCells[0] != null)
{
string Bid, Bstate;
Bid = this.datagridview.SelectedCells[0].Value.ToString();
Bstate = this.datagridview.SelectedCells[5].Value.ToString();
if (Bstate == "0")
{
MessageBox.Show("该书本已借出，此时不可借阅！");
}
else
{
try
{
SqlConnection conn = SQLbase.getConn();
conn.Open();
//借阅图书之后，该图书数量减一，在借阅表格中添加一条记录
//图书数量-1
int amount = Convert.ToInt32(Bstate) - 1;
string updateStr = "update Books set Bstate=" + amount + " where BID ='" + Bid + "'";
SqlCommand updateCmd = new SqlCommand(updateStr, conn);
int result_update = updateCmd.ExecuteNonQuery();
string RID = LogWindow.logname;
string date1 = DateTime.Now.ToShortDateString().ToString();
string date2 = DateTime.Now.AddMonths(1).ToShortDateString().ToString();
//借阅表中添加一条记录
string insertStr = "insert into Borrow values('" + Bid + "','" + RID + "','" + date1 + "','" + date2 + "',null," + "'借阅中')";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);
int result_insert = insertCmd.ExecuteNonQuery();
if (result_insert == 1 && result_update == 1)
{
MessageBox.Show("借阅成功！");
}
else
{
MessageBox.Show("借阅失败！");
}
}
catch
{
MessageBox.Show("操作异常", "错误提示");
}
}
}
else
{
MessageBox.Show("未选中任何记录！");
}
}
//加载窗口函数
private void R_historyRecord_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string RID = LogWindow.logname;
SqlCommand selectCmd = new SqlCommand("select BID,BorrowDate,ReturnDate,RealDate,Note from Borrow where RID='" + RID + "'", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string Bid, Bname, BorrowDate, ReturnDate, RealDate, Note;
int rowindex = 0;
this.dataGridView1.Rows.Clear();
while (datareader.Read())
{
Bid = datareader["BID"].ToString();
BorrowDate = datareader["BorrowDate"].ToString();
ReturnDate = datareader["ReturnDate"].ToString();
RealDate = datareader["RealDate"].ToString();
Note = datareader["Note"].ToString();
rowindex = this.dataGridView1.Rows.Add();
//trim函数同于去除字符串两头的空格
this.dataGridView1.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.dataGridView1.Rows[rowindex].Cells[2].Value = BorrowDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[3].Value = ReturnDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[4].Value = RealDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[5].Value = Note.Trim();
}
datareader.Close();
SQLbase.close(selectCmd);
int n = this.dataGridView1.RowCount;
for (int i = 0; i < n; i++)
{
Bid = this.dataGridView1.Rows[i].Cells[0].Value.ToString();
Bname = select(conn, Bid);
this.dataGridView1.Rows[i].Cells[1].Value = Bname.Trim();
}
}
catch
{
MessageBox.Show("操作错误！");
}
SQLbase.close(conn);
}
private void btn_register_Click(object sender, EventArgs e)
{
string uid = txt_ID.Text.ToString();
string uname = txt_name.Text.ToString();
string pwd = txt_pwd.Text.ToString();
string age = txt_age.Text.ToString();
string address = txt_address.Text.ToString();
string unit = txt_unit.Text.ToString();
string gender = comboBox_gender.SelectedItem.ToString();
string type = comboBox_type.SelectedItem.ToString();
if (string.IsNullOrEmpty(uid) || string.IsNullOrEmpty(uname) || string.IsNullOrEmpty(pwd))
{
MessageBox.Show("请将必填项填写完整！");
}
else
{
SqlConnection conn = SQLbase.getConn();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string insertStr = "insert into Readers values('" + uid + "','" + uname + "','" + gender + "','" + address + "'," + age + ",'" + unit + "','" + type + "','" + pwd + "'," + 0 + ")";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);
//对于Update、Insert和Delete语句，返回值为该命令所影响的行数。
//对于所有其他类型的语句，返回值为 - 1。
try
{
int RecordsAffected = insertCmd.ExecuteNonQuery();
if (RecordsAffected == 1)
{
MessageBox.Show("注册成功！");
}
else
{
MessageBox.Show("注册失败！");
}
}
catch (SqlException)
{
MessageBox.Show(" 注册失败！\n 请重新注册！");
}
SQLbase.close(insertCmd);
SQLbase.close(conn);
this.Close();
}
}
private void btn_continue_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
conn.Open();
string RID = LogWindow.logname;
string Bid = this.datagridview.SelectedCells[0].Value.ToString();
//判断是否超期和是否续借
int result;
string datestr = this.datagridview.SelectedCells[5].Value.ToString().Trim();
string notice = this.datagridview.SelectedCells[6].Value.ToString().Trim();
DateTime shoulddate = Convert.ToDateTime(datestr);
DateTime realdate = DateTime.Now;
if (shoulddate.CompareTo(realdate) >= 0 && notice.Equals("借阅中"))//图书未超期且未续借
{
//应还日期后推一个月
string finaldate = shoulddate.AddMonths(1).ToShortDateString().ToString();
string updateStr = "update Borrow set ReturnDate='" + finaldate + "',Note='已续借' where RID='" + RID + "'and BID='" + Bid + "'";
SqlCommand updateCmd = new SqlCommand(updateStr, conn);
result = updateCmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("图书续借成功！");
this.datagridview.SelectedCells[5].Value = finaldate;
}
else
{
MessageBox.Show("图书续借失败！");
}
}
else if (notice.Equals("已续借"))
{
MessageBox.Show("图书只能续借一次！");
}
else
{
MessageBox.Show("图书已超期，不能续借!");
}
}
catch
{
MessageBox.Show("操作有误！", "错误提示");
}
SQLbase.close(conn);
}
private void button1_Click(object sender, EventArgs e)
{
string RID = LogWindow.logname;
string name, gender, age, address, unit, type, pwd;
name = txt_name.Text.ToString();
gender = txt_gender.Text.ToString();
age = txt_age.Text.ToString();
address = txt_address.Text.ToString();
unit = txt_unit.Text.ToString();
type = txt_type.Text.ToString();
pwd = txt_pwd.Text.ToString();
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlCommand upCmd = conn.CreateCommand();
string updatestr = "update Readers set Rname='" + name
+ "',Rgender='" + gender + "',Raddress='" + address + "',Rage='" +
age + "',Runit='" + unit + "',Rtype='" + type + "',Rpassword='******' where RID='" + RID + "'";
upCmd.CommandText = updatestr;
int result = upCmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("信息修改成功！");
}
else
{
MessageBox.Show("信息修改失败！");
}
SQLbase.close(upCmd);
}
catch
{
MessageBox.Show("操作有误！");
}
SQLbase.close(conn);
this.Close();
}
//确认更改信息
private void button1_Click(object sender, EventArgs e)
{
string AID = LogWindow.logname;
string name, gender, number, address, phone, pwd;
name = txt_name.Text.ToString();
gender = txt_gender.Text.ToString();
address = txt_address.Text.ToString();
phone = txt_phone.Text.ToString();
number = txt_number.Text.ToString();
pwd = txt_pwd.Text.ToString();
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlCommand upCmd = conn.CreateCommand();
string updatestr = "update Administrators set Anumber='" + number + "',Aname='" + name
+ "',Agender='" + gender + "',Aaddress='" + address + "',Atel='" +
phone + "',Apassword='******' where AID='" + AID + "'";
upCmd.CommandText = updatestr;
int result = upCmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("信息修改成功！");
}
else
{
MessageBox.Show("信息修改失败！");
}
SQLbase.close(upCmd);
}
catch
{
MessageBox.Show("操作有误！");
}
SQLbase.close(conn);
this.Close();
}
//根据书号查询书名
public string selectBook(SqlConnection con, string Bid)
{
//查询书名
string Bname = " ";
string selectStr = "select Bname from Books where BID='" + Bid + "'";
SqlCommand selectCmd = con.CreateCommand();
selectCmd.CommandText = selectStr;
SqlDataReader data = selectCmd.ExecuteReader();
if (data.Read())
{
Bname = data["Bname"].ToString();
}
data.Close();
SQLbase.close(selectCmd);
return(Bname);
}
//根据读者号查询读者名
public string selectReader(SqlConnection con, string Rid)
{
//查询读者名
string Rname = " ";
string selectStr = "select Rname from Readers where RID='" + Rid + "'";
SqlCommand selectCmd = con.CreateCommand();
selectCmd.CommandText = selectStr;
SqlDataReader data = selectCmd.ExecuteReader();
if (data.Read())
{
Rname = data["Rname"].ToString();
}
data.Close();
SQLbase.close(selectCmd);
return(Rname);
}
private void A_FineReader_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string selectStr = "select * from readers where Rstate > 0";
SqlCommand SelectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = SelectCmd.ExecuteReader();
string Rid, Rname, Rgender, Runit, Rtype, Rstate;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
Rid = datareader["RID"].ToString();
Rname = datareader["Rname"].ToString();
Rgender = datareader["Rgender"].ToString();
Runit = datareader["Runit"].ToString();
Rtype = datareader["Rtype"].ToString();
Rstate = datareader["Rstate"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Rid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Rname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Rgender.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Runit.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Rtype.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = Rstate.Trim();
}
}
catch
{
MessageBox.Show("操作错误！");
}
}
private void 注销身份ToolStripMenuItem_Click(object sender, EventArgs e)
{
DialogResult dr = MessageBox.Show("确定要注销读者身份吗？", "警告", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
//确认注销就删除掉读者表中的信息。
string Rid = LogWindow.logname;
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string deletestr = "delete from Readers where RID='" + Rid + "'";
SqlCommand deletecmd = new SqlCommand(deletestr, conn);
int result = deletecmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("注销成功！");
this.Close();
}
else
{
MessageBox.Show("删除失败！");
}
SQLbase.close(deletecmd);
}
catch
{
MessageBox.Show("操作有误！");
}
SQLbase.close(conn);
}
}
//删除读者
private void btn_delete_Click(object sender, EventArgs e)
{
if (this.datagridview.SelectedCells[0] != null)
{
string Rid = this.datagridview.SelectedCells[0].Value.ToString();
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string deletestr = "delete from Readers where RID='" + Rid + "'";
SqlCommand deletecmd = new SqlCommand(deletestr, conn);
int result = deletecmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("删除成功！重新查询即可查看记录");
}
else
{
MessageBox.Show("删除失败！");
}
SQLbase.close(deletecmd);
}
catch
{
MessageBox.Show("操作有误！");
}
SQLbase.close(conn);
}
else
{
MessageBox.Show("未选中任何记录！");
}
}
//加载窗口
private void R_updateInfo_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string RID = LogWindow.logname;
SqlCommand selectCmd = new SqlCommand("select * from readers where RID='" + RID + "'", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
if (datareader.Read())
{
label_ID.Text = RID;
txt_name.Text = datareader["Rname"].ToString().Trim();
txt_gender.Text = datareader["Rgender"].ToString().Trim();
txt_address.Text = datareader["Raddress"].ToString().Trim();
txt_age.Text = datareader["Rage"].ToString().Trim();
txt_unit.Text = datareader["Runit"].ToString().Trim();
txt_type.Text = datareader["Rtype"].ToString().Trim();
txt_pwd.Text = datareader["Rpassword"].ToString().Trim();
}
else
{
MessageBox.Show("查询出错");
}
}
catch
{
MessageBox.Show("操作有误！", "错误提示");
}
SQLbase.close(conn);
}
//确认罚款
//如果读者状态为0，弹出提示，不需罚款。否则把状态改为0
private void button1_Click(object sender, EventArgs e)
{
if (this.datagridview.SelectedCells != null || Convert.ToInt32(this.datagridview.SelectedCells[5].Value) == 0)
{
string Rid = this.datagridview.SelectedCells[0].Value.ToString();
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string updatestr = "update Readers set Rstate = 0 where RID='" + Rid + "'";
SqlCommand updatecmd = new SqlCommand(updatestr, conn);
int result = updatecmd.ExecuteNonQuery();
SQLbase.close(updatecmd);
if (result == 1)
{
MessageBox.Show("操作成功！");
}
else
{
MessageBox.Show("操作失败！");
}
}
catch
{
MessageBox.Show("操作有误！");
}
}
else
{
MessageBox.Show("未选中任何记录！");
}
}
//同意归还，将这条记录从这个表删除，并且将该图书数量+1.
private void button1_Click(object sender, EventArgs e)
{
if (this.datagridview.SelectedCells != null)
{
string Bid = this.datagridview.SelectedCells[1].Value.ToString();
string Rid = this.datagridview.SelectedCells[0].Value.ToString();
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string deletestr = "delete from returntable where BID='" + Bid + "'";
SqlCommand deletecmd = new SqlCommand(deletestr, conn);
int result = deletecmd.ExecuteNonQuery();
SQLbase.close(deletecmd);
if (result == 1)//删除成功了
{
//更新图书表的数量信息
SqlCommand upcmd = new SqlCommand("update Books set Bstate=Bstate+1 where BID='" + Bid + "'", conn);
int result1 = upcmd.ExecuteNonQuery();
if (result1 == 1)
{
MessageBox.Show("操作成功！");
}
else
{
MessageBox.Show("操作失败！");
}
//更新借阅表的信息
string date = DateTime.Now.ToShortDateString().ToString();
string updateStr = "update Borrow set Note= '已归还',realDate='" + date + "' where BID='" + Bid + "'and RID='" + Rid + "'";
SqlCommand updateCmd = new SqlCommand(updateStr, conn);
result1 = updateCmd.ExecuteNonQuery();
if (result1 == 1)
{
MessageBox.Show("图书归还成功！", "消息");
}
else
{
MessageBox.Show("图书归还失败！", "消息");
}
}
else
{
MessageBox.Show("操作失败！");
}
}
catch
{
MessageBox.Show("操作有误！");
}
}
else
{
MessageBox.Show("未选中任何记录！");
}
}
//窗口加载函数
private void R_BooksReturn_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
String RID = LogWindow.logname;
//查询书籍信息
string selectStr = "select BID,Bname,Bauthor,Bpublisher,Btype from Books where BID IN(select BID from Borrow where RID='" + RID + "'and Note in('借阅中','已续借','待审核')) ORDER BY BID ASC";
SqlCommand selectCmd1 = conn.CreateCommand();
selectCmd1.CommandText = selectStr;
SqlDataReader datareader1 = selectCmd1.ExecuteReader();
string Bid, Bname, Bauthor, Bpublisher, Btype;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader1.Read())
{
Bid = datareader1["BID"].ToString();
Bname = datareader1["Bname"].ToString();
Bauthor = datareader1["Bauthor"].ToString();
Bpublisher = datareader1["Bpublisher"].ToString();
Btype = datareader1["Btype"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Bname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bauthor.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Bpublisher.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Btype.Trim();
}
SQLbase.close(selectCmd1);
//必须关闭当前的dataReader才能继续
datareader1.Close();
//查询应还日期和借阅状态
string selectStr2 = "select BID,ReturnDate,Note from Borrow where RID='" + RID + "'order by BID ASC";
SqlCommand selectCmd2 = conn.CreateCommand();
selectCmd2.CommandText = selectStr2;
SqlDataReader datareader2 = selectCmd2.ExecuteReader();
string return_date;
string note;
int index = 0;
while (datareader2.Read())
{
if (index < this.datagridview.RowCount)
{
return_date = datareader2["ReturnDate"].ToString();
note = datareader2["Note"].ToString();
this.datagridview.Rows[index].Cells[5].Value = return_date.Trim();
this.datagridview.Rows[index].Cells[6].Value = note.Trim();
index++;
}
}
datareader2.Close();
SQLbase.close(selectCmd2);
}
catch
{
MessageBox.Show("操作异常", "错误提示");
}
SQLbase.close(conn);
}
//读者查询
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
this.datagridview.Rows.Clear();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string selectStr = "select * from readers";
//按读者号查询
if (comboBox_QueryStyle.SelectedIndex == 0)
{
string id = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from readers where RID=" + id;
}
//按读者名查询
else if (comboBox_QueryStyle.SelectedIndex == 1)
{
string name = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from readers where Rname= '" + name + "'";
}
//按单位查询
else if (comboBox_QueryStyle.SelectedIndex == 2)
{
string unit = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from readers where Runit= '" + unit + "'";
}
//按类型查询
else if (comboBox_QueryStyle.SelectedIndex == 3)
{
string type = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from readers where Rtype= '" + type + "'";
}
//没有选择下拉框，则查询全部
SqlCommand SelectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = SelectCmd.ExecuteReader();
string Rid, Rname, Rgender, Runit, Rtype, Rstate;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
Rid = datareader["RID"].ToString();
Rname = datareader["Rname"].ToString();
Rgender = datareader["Rgender"].ToString();
Runit = datareader["Runit"].ToString();
Rtype = datareader["Rtype"].ToString();
Rstate = datareader["Rstate"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Rid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Rname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Rgender.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Runit.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Rtype.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = Rstate.Trim();
}
}
catch
{
MessageBox.Show("查无此人！");
}
SQLbase.close(conn);
}
//归还图书响应事件
private void btn_return_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
conn.Open();
//判断是否超期
int result, result1;
string datestr = this.datagridview.SelectedCells[5].Value.ToString().Trim();
DateTime shoulddate = Convert.ToDateTime(datestr);
DateTime realdate = DateTime.Now;
if (shoulddate.CompareTo(realdate) >= 0)
{
MessageBox.Show("图书未超期，请放心归还！");
}
else
{
MessageBox.Show("图书已超期，请自觉缴纳罚款!");
//跳转至罚款页面？？？？？？？？？？？？
string updateStr = "update Readers set Rstate=1";
SqlCommand updateCmd = new SqlCommand(updateStr, conn);
result = updateCmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("欠款状态修改成功！");
}
else
{
MessageBox.Show("欠款状态修改失败！");
}
SQLbase.close(updateCmd);
}
//往图书归还表中添加记录
string Bid, Bname = "";
Bid = this.datagridview.SelectedCells[0].Value.ToString();
SqlCommand selectCmd1 = new SqlCommand("select Bname from books where BID='" + Bid + "'", conn);
SqlDataReader datareader = selectCmd1.ExecuteReader();
if (datareader.Read())
{
Bname = datareader["Bname"].ToString().Trim();
}
else
{
MessageBox.Show("查询出错");
}
datareader.Close();
SQLbase.close(selectCmd1);
string RID = LogWindow.logname;
string insertstr = "insert into Returntable values('" + RID + "','" + Bid + "','" + Bname + "')";
SqlCommand insertCmd = new SqlCommand(insertstr, conn);
result = insertCmd.ExecuteNonQuery();
if (result == 1)
{
//如果归还成功，直接在借阅表中把备注改为“待审核”即可。不用从表中删除
string updateStr = "update Borrow set Note= '待审核' where BID='" + Bid + "'and RID='" + RID + "'";
SqlCommand deleteCmd = new SqlCommand(updateStr, conn);
result1 = deleteCmd.ExecuteNonQuery();
if (result1 == 1)
{
MessageBox.Show("图书归还成功！请耐心等待审核", "消息");
}
else
{
MessageBox.Show("图书归还失败！", "消息");
}
}
else
{
MessageBox.Show("图书归还失败！", "消息");
}
}
catch
{
MessageBox.Show("操作有误！", "错误提示");
}
SQLbase.close(conn);
}
//查询按钮
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
this.datagridview.Rows.Clear(); //清空表格
//打开连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string selectStr = "select * from Borrow";
//按读者号查询
if (comboBox_QueryStyle.SelectedIndex == 0)
{
string rid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from Borrow where RID=" + rid;
}
//按书号查询
else if (comboBox_QueryStyle.SelectedIndex == 1)
{
string bid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from Borrow where BID=" + bid;
}
//没有选择下拉框，则查询全部
SqlCommand selectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string BID, Bname, RID, Rname, BorrowDate, RealDate, Note;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
RID = datareader["RID"].ToString();
BID = datareader["BID"].ToString();
BorrowDate = datareader["BorrowDate"].ToString();
RealDate = datareader["RealDate"].ToString();
Note = datareader["Note"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = BID.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = RID.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = BorrowDate.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = RealDate.Trim();
this.datagridview.Rows[rowindex].Cells[6].Value = Note.Trim();
}
datareader.Close();
SQLbase.close(selectCmd);
int n = this.datagridview.RowCount;
for (int i = 0; i < n; i++)
{
//填充书名
BID = this.datagridview.Rows[i].Cells[0].Value.ToString();
Bname = selectBook(conn, BID);
this.datagridview.Rows[i].Cells[1].Value = Bname.Trim();
//填充作者名
RID = this.datagridview.Rows[i].Cells[2].Value.ToString();
Rname = selectReader(conn, RID);
this.datagridview.Rows[i].Cells[3].Value = Rname.Trim();
}
}
catch
{
MessageBox.Show("查无此记录！");
}
SQLbase.close(conn);
}
private void Btn_exit_Click(object sender, EventArgs e)
{
SQLbase.close(conn);
Application.Exit();
}
//图书查询
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
this.datagridview.Rows.Clear();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
//打开连接
conn.Open();
string selectStr = "select * from books";
//按书号查询
if (comboBox_QueryStyle.SelectedIndex == 0)
{
string bid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where BID=" + bid;
}
//按书名查询
else if (comboBox_QueryStyle.SelectedIndex == 1)
{
string bname = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bname= '" + bname + "'";
}
//按作者查询
else if (comboBox_QueryStyle.SelectedIndex == 2)
{
string bauthor = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bauthor= '" + bauthor + "'";
}
//按出版社查询
else if (comboBox_QueryStyle.SelectedIndex == 3)
{
string bpublisher = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bpublisher= '" + bpublisher + "'";
}
//没有选择下拉框，则查询全部
SqlCommand SelectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = SelectCmd.ExecuteReader();
string Bid, Bname, Bauthor, Bpublisher, Btype, Bstate;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
Bid = datareader["BID"].ToString();
Bname = datareader["Bname"].ToString();
Bauthor = datareader["Bauthor"].ToString();
Bpublisher = datareader["Bpublisher"].ToString();
Btype = datareader["Btype"].ToString();
Bstate = datareader["Bstate"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Bname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bauthor.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Bpublisher.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Btype.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = Bstate.Trim();
}
}
catch
{
MessageBox.Show("查无此书！");
}
SQLbase.close(conn);
}
