1.登陆界面 主要代码： public partial class LoginForm : Form{ public static string uacc; public static string upsw; public static string uname; public static string usex; public static string upart; public static string uright; public LoginForm() { InitializeComponent(); } private void loginbtn_Click(object sender, EventArgs e) { if (this.useracctxt.Text.Trim() == "" && this.pswtxt.Text == "") { MessageBox.Show("请输入您的用户名和密码！", "提示！"); return; } try { string sql; sql = "select * from tb_user where uacc='" + this.useracctxt.Text + "' and upsw='" + this.pswtxt.Text + "'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { uacc = this.useracctxt.Text; upsw = this.pswtxt.Text; uname = dr["uname"].ToString(); usex = dr["usex"].ToString(); upart = dr["upart"].ToString(); uright = dr["uright"].ToString(); MainForm af = new MainForm(this); this.Hide(); this.useracctxt.Clear(); this.pswtxt.Clear(); af.Show(); } else { MessageBox.Show("账号或密码错误！", "提示！"); this.useracctxt.Clear(); this.pswtxt.Clear(); this.useracctxt.Focus(); } } catch (Exception) { MessageBox.Show("数据库无法连接！", "警告！"); } } private void cancelbtn_Click(object sender, EventArgs e) { Application.Exit(); } private void LoginForm_Closing(object sender, FormClosingEventArgs e) { Application.Exit(); } } 2.主界面（图较小，自己可以拉大点） 主要代码就不写了，很简单。 3.权限设置 主要代码： public partial class RightSet : Form { public RightSet() { InitializeComponent(); } private void RightSet_Load(object sender, EventArgs e) { this.rightbox.SelectedIndex = 0; //this.treeright.ExpandAll(); Fill(); } private void okbtn_Click(object sender, EventArgs e) { if (this.txtnum.Text == string.Empty) { MessageBox.Show("请输入最大借阅图书数量！", "提示！"); return; } if (this.txtday.Text == string.Empty) { MessageBox.Show("请输入最大借阅图书时间！", "提示！"); return; } if (this.txtcost.Text == string.Empty) { MessageBox.Show("请输入借书押金金额！", "提示！"); return; } if (this.txtfine.Text == string.Empty) { MessageBox.Show("请输入超期罚款金额！", "提示！"); return; } if (this.txttim.Text == string.Empty) { MessageBox.Show("请输入图书遗失罚款倍数！", "提示！"); return; } ArrayList arr = new ArrayList(); foreach (TreeNode nodes in this.treeright.Nodes) { if (nodes.Checked) { arr.Add("1"); } else { arr.Add("0"); } foreach(TreeNode node in nodes.Nodes) { if (node.Checked) { arr.Add("1"); } else { arr.Add("0"); } } } string[] a=new string[11]; for (int i = 0; i < arr.Count; i++) { if (arr[i].ToString().Trim() == "1") { a[i] = "yes"; } else { a[i] = "no"; } } string sql = string.Empty; sql += "select * from tb_right where uright='" + this.rightbox.Text + "'"; DataTable dt = DBHelp.ExeOleCommand(sql); bool b = false; while (dt.Rows.Count != 0) { b = true; break; } string sql1; if (b) { sql1 = "update tb_right set "; sql1 += "maxbook='" + this.txtnum.Text + "',"; sql1 += "maxdate='" + this.txtday.Text + "',"; sql1 += "rcost='" + this.txtcost.Text + "',"; sql1 += "rfine='" + this.txtfine.Text + "',"; sql1 += "rtim='" + this.txttim.Text + "',"; sql1 += "rbm='" + a[0] + "',"; sql1 += "rum='" + a[1] + "',"; sql1 += "rrm='" + a[2] + "',"; sql1 += "rborm='" + a[3] + "',"; sql1 += "ris='" + a[4] + "',"; sql1 += "rbis='" + a[5] + "',"; sql1 += "ruis='" + a[6] + "',"; sql1 += "rboris='" + a[7] + "',"; sql1 += "rblp='" + a[8] + "',"; sql1 += "rbl='" + a[9] + "',"; sql1 += "rlp='" + a[10] + "' "; sql1 += "where uright='" + this.rightbox.Text + "'"; } else { sql1 = "insert into tb_right(uright,maxbook,maxdate,rcost,rfine,rtim,rbm,rum,rrm,rborm,ris,rbis,ruis,rboris,rblp,rbl,rlp)"; sql1 += " values('" + this.rightbox.Text + "','" + this.txtnum.Text + "','" + this.txtday.Text + "','" + this.txtcost.Text + "','" + this.txtfine.Text + "','" + this.txttim.Text + "','" + a[0] + "','" + a[1] + "','" + a[2] + "','" + a[3] + "','" + a[4] + "','" + a[5] + "','" + a[6] + "','" + a[7] + "','" + a[8] + "','" + a[9] + "','" + a[10] + "')"; } DataTable dt1 = DBHelp.ExeOleCommand(sql1); Fill(); } private void Fill() { string sql; sql = "select rid as ID号,uright as 用户身份, maxbook as 最大借书数量,maxdate as 最大借阅时间,rcost as 押金,rfine as 超期罚率,rtim as 遗失赔率,rbm as 图书管理,rum as 用户管理,rrm as 权限管理,rborm as 借阅管理,ris as 信息查询,rbis as 图书信息查询,ruis as 用户信息查询,rboris as 借阅历史查询,rblp as 图书挂失处理,rbl as 图书挂失,rlp as 挂失处理 from tb_right"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void cell_click(object sender, DataGridViewCellEventArgs e) { this.rightbox.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtnum.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtday.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtcost.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtfine.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txttim.Text = this.dataGridView1[6, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); ArrayList list = new ArrayList(); string sql = "select * from tb_right where uright='" + this.rightbox.Text + "'"; DataTable dt = DBHelp.ExeOleCommand(sql); if (dt.Rows.Count != 0) { for (int i = 0; i < 11; i++) { list.Add(dt.Rows[0][7+i].ToString()); } ArrayList arr = new ArrayList(); foreach (TreeNode nodes in this.treeright.Nodes) { arr.Add(nodes); foreach(TreeNode node in nodes.Nodes) { arr.Add(node); } } for (int i = 0; i < list.Count; i++) { if (list[i].ToString() == "yes") { ((TreeNode)arr[i]).Checked = true; } else { ((TreeNode)arr[i]).Checked = false; } } } } 4.权限修改 主要代码： public partial class UserRight : Form { public UserRight() { InitializeComponent(); } private void btncancel_Click(object sender, EventArgs e) { this.Close(); } private void UserRight_Load(object sender, EventArgs e) { this.checkbox.SelectedIndex = 0; this.partbox.SelectedIndex = 0; } private void Fill() { if (this.checkbox.Text == "") { MessageBox.Show("请选择要使用的查询字段！", "提示！"); return; } if (this.partbox.Text == "") { MessageBox.Show("请选择用户所在的部门！", "提示！"); return; } string sql = string.Empty; sql += "select uid as ID号,uacc as 帐号,uname as 姓名,usex as 性别,upart as 部门,utelphone as 移动电话,uphone as 固定电话,udate as 注册日期,uright as 权限 from tb_user"; if (this.checktxt.Text != "") { string c = this.checkbox.SelectedIndex.ToString(); switch (c) { case "0"://用户帐号 if (this.checktxt.Text != string.Empty) { sql += " where uacc like '%" + this.checktxt.Text + "%'"; } break; case "1"://用户姓名 if (this.checktxt.Text != string.Empty) { sql += " where uname like '%" + this.checktxt.Text + "%'"; } break; default: break; } if (this.partbox.SelectedIndex.ToString() != "0") { sql += " and upart='" + this.partbox.Text + "'"; } } else { if (this.partbox.SelectedIndex.ToString() != "0") { sql += " where upart='" + this.partbox.Text + "'"; } } sql += " order by uacc asc"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void checkbtn_Click(object sender, EventArgs e) { Fill(); } private void cell_click(object sender, DataGridViewCellEventArgs e) { this.txtuacc.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtname.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtsex.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtpart.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txttel.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtphone.Text = this.dataGridView1[6, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtdate.Text = this.dataGridView1[7, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtright.Text = this.dataGridView1[8, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtright.Enabled = true; } private void btnchange_Click(object sender, EventArgs e) { if (this.txtuacc.Text == "") { MessageBox.Show("执行操作前，请先在下表选择要修改的用户！", "提示！"); return; } DialogResult res; res=MessageBox.Show("您确定要修改该用户吗？", "提示！", MessageBoxButtons.YesNo); if (res == DialogResult.Yes) { string sql = string.Empty; sql += "update tb_user set uright='"+this.txtright.Text+"'"; sql += " where uacc='"+this.txtuacc.Text+"'"; DataTable dt = DBHelp.ExeOleCommand(sql); MessageBox.Show("修改成功！","恭喜！"); Fill(); } } } 5.添加图书信息 主要代码： public partial class NewBook : Form { public NewBook() { InitializeComponent(); } private void retbtn_Click(object sender, EventArgs e) { this.Hide(); } private void savebtn_Click(object sender, EventArgs e) { if (this.booknotxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.booknametxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.classtxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.isbntxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.bookcosttxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.bookwritertxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.bookpubtxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.numtxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } if (this.notetxt.Text == "") { MessageBox.Show("请输入图书的信息！", "提示！"); return; } //string pat3 = @"^[\u4e00-\u9fa5]+$";//全为汉字 //string pat4 = @"^([\u4e00-\u9fa5]+|[A-Za-z]+)$";//汉字或字母 //string pat5 = @"^[\u4e00-\u9fa5]{2,4}$";//两到四位汉字 string pat1 = @"^(\d[-]*){9}[\dxX]$";//图书的ISBN号格式X-XXXX-XXXX-X或X-XXX-XXXXX-X（X为数字，以图书实际ISBN号为准） string pat2 = @"^\+?[1-9][0-9]*$";//正整数 string pat3 = @"^(0|[1-9][0-9]*)(.[0-9]{2})?$";//双精度浮点数 bool m1 = Program.match(this.isbntxt.Text, pat1); bool m2 = Program.match(this.numtxt.Text, pat2); bool m3 = Program.match(this.bookcosttxt.Text, pat3); if (!m1) { MessageBox.Show("图书的ISBN号格式为X-XXXX-XXXX-X或X-XXX-XXXXX-X（X为数字，以图书实际ISBN号为准）！", "提示！"); this.isbntxt.Text = ""; return; } if (!m2) { MessageBox.Show("图书购买数量应为大于0的整数！", "提示！"); this.numtxt.Text = ""; return; } if (!m3) { MessageBox.Show("图书价格应为XX.XX元！", "提示！"); this.bookcosttxt.Text = ""; return; } int num; num = Convert.ToInt32(this.numtxt.Text); for (int i = 1, k = Convert.ToInt32(this.booknotxt.Text); i <= num; i++, k++) { string sql; sql = "insert into tb_book(bno,bname,bclass,bisbn,bcost,adder,adddate,bauthor,bpub,bstate,bnote)" + "values('" + k.ToString() + "','" + this.booknametxt.Text + "','" + this.classtxt.Text + "','" + this.isbntxt.Text + "','" + this.bookcosttxt.Text + "','" + LoginForm.uname + "','" + DateTime.Now.ToString() + "','" + this.bookwritertxt.Text + "','" + this.bookpubtxt.Text + "','在库','" + this.notetxt.Text + "')"; DataTable dt = DBHelp.ExeOleCommand(sql); } MessageBox.Show("注册成功！", "恭喜！"); this.Hide(); } private void rebtn_Click(object sender, EventArgs e) { this.isbntxt.Clear(); Clears(); } private void Clears() { this.booknametxt.Clear(); //this.isbntxt.Clear(); this.bookcosttxt.Clear(); this.bookwritertxt.Clear(); this.bookpubtxt.Clear(); this.notetxt.Clear(); this.numtxt.Clear(); } private void NewBook_Load(object sender, EventArgs e) { /* string sql; sql = "select bno from tb_book order by bno asc"; DataTable dt = DBHelp.ExeOleCommand(sql); for (int i = 0, k = 10000001; i < 1; i++, k++) { for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j][0].ToString().Trim().Equals(k.ToString())) k++; } this.booknotxt.Items.Add(k.ToString()); } this.booknotxt.SelectedIndex = 0; this.classtxt.SelectedIndex = 0; */ string sql; sql = "select top 1 bno from tb_book order by bno desc"; DataTable dt = DBHelp.ExeOleCommand(sql); int k; for (int i = 0; i < 1; i++) { if (dt.Rows[0][0].ToString() == "") { k = 10000001; } else { k = Convert.ToInt32(dt.Rows[0][0].ToString()); k++; } this.booknotxt.Items.Add(k.ToString()); } this.booknotxt.SelectedIndex = 0; this.classtxt.SelectedIndex = 0; } private void findbtn_Click(object sender, EventArgs e) { if (this.isbntxt.Text == "") { MessageBox.Show("请输入图书的ISBN号！", "提示！"); return; } string sql; sql = "select * from tb_book where bisbn='" + this.isbntxt.Text + "'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { this.booknametxt.Text = dr["bname"].ToString().Trim(); this.classtxt.Text = dr["bclass"].ToString().Trim(); this.bookcosttxt.Text = dr["bcost"].ToString().Trim(); this.bookwritertxt.Text = dr["bauthor"].ToString().Trim(); this.bookpubtxt.Text = dr["bpub"].ToString().Trim(); this.notetxt.Text = dr["bnote"].ToString().Trim(); MessageBox.Show("找到匹配图书信息，自动填充基本信息，请填充余下信息！", "提示！"); this.numtxt.Enabled = true; } else { Clears(); MessageBox.Show("未找到匹配图书信息！","提示！"); this.booknotxt.Enabled = true; this.booknametxt.Enabled = true; this.classtxt.Enabled = true; this.bookcosttxt.Enabled = true; this.bookwritertxt.Enabled = true; this.bookpubtxt.Enabled = true; this.numtxt.Enabled = true; this.notetxt.Enabled = true; } } } 6.图书信息管理 主要代码： public partial class BookManage : Form { public BookManage() { InitializeComponent(); } string bookisbn; private void BookManage_Load(object sender, EventArgs e) { this.checkbox.SelectedIndex = 0; this.classbox.SelectedIndex = 0; } private void checkbtn_Click(object sender, EventArgs e) { FillGrid(); } private void cell_click(object sender, DataGridViewCellEventArgs e) { FillInfo(); } private void nobtn_Click(object sender, EventArgs e) { this.Close(); } private void okbtn_Click(object sender, EventArgs e) { if (this.nametxt.Text == string.Empty) { MessageBox.Show("执行操作前，请先选择图书！","提示！"); return; } //修改同ISBN图书信息 string sql2= string.Empty; sql2 += "update tb_book set bisbn='" + this.isbntxt.Text + "',bname='" + this.nametxt.Text + "',bclass='" + this.classtxt.Text + "',bcost=" + this.costtxt.Text + ",bauthor='" + this.writertxt.Text + "',bpub='" + this.pubtxt.Text + "',adder='" + this.addertxt.Text + "',bnote='" + this.notetxt.Text + "'"; sql2 += " where bisbn='" + this.bookisbn + "'"; DataTable dt2 = DBHelp.ExeOleCommand(sql2); string sql5 = string.Empty; sql5 += "update tb_borrow set bname='" + this.nametxt.Text + "',bisbn ='" + this.isbntxt.Text + "'"; sql5 += " where bisbn='" + this.bookisbn + "'"; DataTable dt5 = DBHelp.ExeOleCommand(sql5); MessageBox.Show("该类图书信息修改成功！", "提示！"); this.checktxt.Text = ""; FillGrid(); FillInfo(); } private void FillGrid() { if (this.checkbox.Text == string.Empty) { MessageBox.Show("请输入你要使用的检索条件！", "提示！"); return; } if (this.classbox.Text == string.Empty) { MessageBox.Show("请输入你要查找的图书类型！", "提示！"); return; } string sql = string.Empty; sql += "select bid as ID号, bno as 图书编号, bname as 图书名称,bauthor as 作者, bclass as 类别, bisbn as ISBN号, bcost as 价格,bpub as 出版社, adder as 记录员, adddate as 入库日期,bnote as 内容简介,bstate as 状态 from tb_book"; if (this.checktxt.Text != "") { string c = this.checkbox.SelectedIndex.ToString(); switch (c) { case "0"://图书名称 if (this.checktxt.Text != string.Empty) { sql += " where bname like '%" + this.checktxt.Text + "%'"; } break; case "1"://图书编号 if (this.checktxt.Text != string.Empty) { sql += " where bno like '%" + this.checktxt.Text + "%'"; } break; case "2"://作者 if (this.checktxt.Text != string.Empty) { sql += " where bauthor like '%" + this.checktxt.Text + "%'"; } break; case "3"://isbn号 if (this.checktxt.Text != string.Empty) { sql += " where bisbn like '%" + this.checktxt.Text + "%'"; } break; case "4"://出版社 if (this.checktxt.Text != string.Empty) { sql += " where bpub like '%" + this.checktxt.Text + "%'"; } break; default: break; } if (this.classbox.SelectedIndex.ToString() != "0") { sql += " and bclass='" + this.classbox.Text + "'"; } } else { if (this.classbox.SelectedIndex.ToString() != "0") { sql += " where bclass='" + this.classbox.Text + "'"; } } sql += " order by bno asc"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void FillInfo() { this.label3.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.isbntxt.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.nametxt.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.classtxt.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.costtxt.Text = this.dataGridView1[6, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.writertxt.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.pubtxt.Text = this.dataGridView1[7, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.addertxt.Text = this.dataGridView1[8, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.notetxt.Text = this.dataGridView1[10, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.isbntxt.Enabled = true; this.nametxt.Enabled = true; this.classtxt.Enabled = true; this.costtxt.Enabled = true; this.writertxt.Enabled = true; this.pubtxt.Enabled = true; this.notetxt.Enabled = true; this.bookisbn = this.isbntxt.Text; } } 7.用户信息添加 主要代码： public partial class RegisterForm : Form { public RegisterForm() { InitializeComponent(); } private void regbtn_Click(object sender, EventArgs e) { if (this.usernametxt.Text == string.Empty) { MessageBox.Show("请输入用户姓名！", "提示！"); return; } if(this.sextxt.Text== string.Empty) { MessageBox.Show("请输入用户性别！", "提示！"); return; } if (this.partcob.Text == string.Empty) { MessageBox.Show("请输入用户所在部门！", "提示！"); return; } if (this.telphonetxt.Text == string.Empty) { MessageBox.Show("请输入用户移动电话！", "提示！"); return; } if (this.phonetxt.Text == string.Empty) { MessageBox.Show("请输入用户固定电话！", "提示！"); return; } string pat1 = @"^[0-9]{8,11}$"; bool m1 = Program.match(this.telphonetxt.Text, pat1); bool m2 = Program.match(this.phonetxt.Text, pat1); if (!m1) { MessageBox.Show("电话号码为8到11位的正整数！","提示！"); return; } if (!m2) { MessageBox.Show("电话号码为8到11位的正整数！", "提示！"); return; } string sq = string.Empty; sq += "select * from tb_user"; sq += " where uname='"+this.usernametxt.Text+"' and upart ='"+this.partcob.Text+"' and utelphone='"+this.telphonetxt.Text+"'"; DataTable d = DBHelp.ExeOleCommand(sq); bool b = false; while (d.Rows.Count == 0) { b = true; break; } if (b) { string sql = string.Empty; sql += "insert into tb_user(uacc,upsw,uname,usex,upart,utelphone,uphone,udate,uright)"; sql += " values('" + this.useracctxt.Text + "','" + this.pswtxt.Text + "','" + this.usernametxt.Text + "','" + this.sextxt.Text + "','" + this.partcob.Text + "','" + this.telphonetxt.Text + "','" + this.phonetxt.Text + "','" + DateTime.Now.ToString() + "','" + this.rightbox.Text + "')"; DataTable dt = DBHelp.ExeOleCommand(sql); MessageBox.Show("新用户添加成功！", "恭喜！"); this.Hide(); } else { MessageBox.Show("该用户已存在！","提示！"); this.usernametxt.Text = ""; this.telphonetxt.Text = ""; this.phonetxt.Text = ""; } } private void cancelbtn_Click(object sender, EventArgs e) { this.Close(); } private void RegisterForm_Load(object sender, EventArgs e) { string sql; sql = "select top 1 uacc from tb_user order by uacc desc"; DataTable dt = DBHelp.ExeOleCommand(sql); int k; for (int i = 0; i < 1; i++) { if (dt.Rows[0][0].ToString() == "") { k = 60000001; } else { k = Convert.ToInt32(dt.Rows[0][0].ToString()); k++; } this.useracctxt.Items.Add(k.ToString()); } this.useracctxt.SelectedIndex = 0; this.sextxt.SelectedIndex = 0; this.partcob.SelectedIndex = 0; this.pswtxt.Text = "123456"; } } 8.用户信息管理 主要代码： public partial class UserList : Form { public UserList() { InitializeComponent(); } private void UserList_Load(object sender, EventArgs e) { this.checkbox.SelectedIndex = 0; this.partbox.SelectedIndex = 0; this.classbox.SelectedIndex = 0; } private void Fill() { if (this.checkbox.Text == "") { MessageBox.Show("请选择要使用的查询字段！", "提示！"); return; } if (this.partbox.Text == "") { MessageBox.Show("请选择用户所在的部门！", "提示！"); return; } if (this.classbox.Text == "") { MessageBox.Show("请选择用户的类型！", "提示！"); return; } string sql = string.Empty; sql += "select uid as ID号,uacc as 帐号,uname as 姓名,usex as 性别,upart as 部门,utelphone as 移动电话,uphone as 固定电话,udate as 注册日期,uright as 权限 from tb_user"; //if (this.classbox.SelectedIndex.ToString() != "0") //{ // sql += " where uright='" + this.classbox.Text + "'"; //} if (this.checktxt.Text != "") { string c = this.checkbox.SelectedIndex.ToString(); switch (c) { case "0"://用户帐号 if (this.checktxt.Text != string.Empty) { sql += " where uacc like '%" + this.checktxt.Text + "%'"; } break; case "1"://用户姓名 if (this.checktxt.Text != string.Empty) { sql += " where uname like '%" + this.checktxt.Text + "%'"; } break; default: break; } if (this.classbox.SelectedIndex.ToString() != "0") { sql += " and uright='" + this.classbox.Text + "'"; } if (this.partbox.SelectedIndex.ToString() != "0") { sql += " and upart='" + this.partbox.Text + "'"; } } else { //sql += " where upart='" + this.partbox.Text + "'"; if (this.classbox.SelectedIndex.ToString() != "0" && this.partbox.SelectedIndex.ToString() != "0") { sql += " where uright='" + this.classbox.Text + "'"; sql += " and upart='" + this.partbox.Text + "'"; } else if (this.classbox.SelectedIndex.ToString() == "0" && this.partbox.SelectedIndex.ToString() != "0") { sql += " where upart='" + this.partbox.Text + "'"; } else if (this.classbox.SelectedIndex.ToString() != "0" && this.partbox.SelectedIndex.ToString() == "0") { sql += " where uright='" + this.classbox.Text + "'"; } } sql += " order by uacc asc"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void checkbtn_Click(object sender, EventArgs e) { Fill(); } private void cell_Click(object sender, DataGridViewCellEventArgs e) { this.txtuacc.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtname.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtsex.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtpart.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txttel.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtphone.Text = this.dataGridView1[6, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtdate.Text = this.dataGridView1[7, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtright.Text = this.dataGridView1[8, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql1 = "select maxbook,maxdate from tb_right where uright='" + this.txtright.Text + "'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); this.txtmaxbook.Text = dr1["maxbook"].ToString().Trim(); this.txtmaxdate.Text = dr1["maxdate"].ToString().Trim(); string sql2 = string.Empty;//已借图书数量 sql2 += "select count(*) as bornum from tb_borrow"; sql2 += " where uacc='" + this.txtuacc.Text + "'"; sql2 += " and borstate <> '已还' and borstate <> '丢失'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); this.txtbooknum.Text = dr2["bornum"].ToString().Trim(); int a = Convert.ToInt32(this.txtmaxdate.Text); string sql3 = string.Empty;//到期图书数量 sql3 += "select count(*) as bornum from tb_borrow"; sql3 += " where uacc='" + this.txtuacc.Text + "'"; sql3 += " and borstate <> '已还'"; sql3 += " and bordate<#" + DateTime.Now.AddDays(-a) + "#"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); this.txtbookout.Text = dr3["bornum"].ToString().Trim(); } private void cancel_Click(object sender, EventArgs e) { this.Close(); } private void btndel_Click(object sender, EventArgs e) { if (this.txtuacc.Text == "") { MessageBox.Show("执行操作前，请先在下表选择要删除的用户！", "提示！"); return; } DialogResult res; res=MessageBox.Show("您确定要删除该用户吗？", "提示！", MessageBoxButtons.YesNo); if (res == DialogResult.Yes) { string sql = string.Empty; sql += "select * from tb_borrow"; sql += " where uacc='"+this.txtuacc.Text+"'"; sql += " and borstate <> '已还'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { MessageBox.Show("该用户尚有图书未还，无法删除！", "提示！"); } else { if (this.txtuacc.Text == LoginForm.uacc) { MessageBox.Show("对不起，自己无法删除自己！", "提示！"); } else { string sql2 = string.Empty; sql2 += "select uright from tb_user"; sql2 += " where uacc='" + this.txtuacc.Text + "'"; DataTable dt2 = DBHelp.ExeOleCommand(sql2); string xxx = dt2.Rows[0][0].ToString(); if (xxx == "超级管理员") { MessageBox.Show("该用户为超级管理员，无法删除！", "提示！"); } else { string str = string.Empty; str += "delete from tb_user where uacc='" + this.txtuacc.Text + "'"; DataTable dt = DBHelp.ExeOleCommand(str); MessageBox.Show("该用户已删除！", "提示！"); Fill(); } } } } } } 9.图书挂失 主要代码： public partial class BookLost : Form { public BookLost() { InitializeComponent(); } private void FillBook() { string s = string.Empty; s += "select * from tb_book"; s += " where bno='" + this.notxt.Text + "'"; OleDbDataReader dr = DBHelp.OleReader(s); dr.Read(); if (dr.HasRows) { this.nametxt.Text = dr["bname"].ToString().Trim(); this.classtxt.Text = dr["bclass"].ToString().Trim(); this.isbntxt.Text = dr["bisbn"].ToString().Trim(); this.costtxt.Text = dr["bcost"].ToString().Trim(); this.writertxt.Text = dr["bauthor"].ToString().Trim(); this.pubtxt.Text = dr["bpub"].ToString().Trim(); this.addertxt.Text = dr["adder"].ToString().Trim(); this.adddatetxt.Text = dr["adddate"].ToString().Trim(); this.bstatetxt.Text = dr["bstate"].ToString().Trim(); this.notetxt.Text = dr["bnote"].ToString().Trim(); } else { MessageBox.Show("未找到该书！", "提示！"); Clears(); } } private void Clears() { this.notxt.Text = ""; this.nametxt.Text = ""; this.classtxt.Text = ""; this.isbntxt.Text = ""; this.costtxt.Text = ""; this.writertxt.Text = ""; this.pubtxt.Text = ""; this.addertxt.Text = ""; this.adddatetxt.Text = ""; this.bstatetxt.Text = ""; this.notetxt.Text = ""; } private void checkbookbtn_Click(object sender, EventArgs e) { if (this.notxt.Text == string.Empty) { MessageBox.Show("请输入图书的编号！", "提示！"); return; } FillBook(); } private void cancel_Click(object sender, EventArgs e) { this.Close(); } private void btnok_Click(object sender, EventArgs e) { if (this.nametxt.Text == "") { MessageBox.Show("请点击放大镜获取图书信息！", "提示！"); return; } if (this.bstatetxt.Text == "在库") { MessageBox.Show("本次挂失失败，该书并未借出！", "提示！"); Clears(); } else if (this.bstatetxt.Text == "借出") { string sql1 = string.Empty; sql1 += "update tb_book set bstate='挂失'"; sql1 += " where bno='" + this.notxt.Text + "'"; DataTable dt1 = DBHelp.ExeOleCommand(sql1); string sql2 = string.Empty; sql2 += "update tb_borrow set borstate='挂失'"; sql2 += " where bno='" + this.notxt.Text + "' and borstate='未还'"; DataTable dt2 = DBHelp.ExeOleCommand(sql2); FillBook(); MessageBox.Show("挂失操作成功", "提示！"); } else if (this.bstatetxt.Text == "挂失") { MessageBox.Show("本次挂失失败，挂失处理已经被执行过了！", "提示！"); Clears(); } else if (this.bstatetxt.Text == "丢失") { MessageBox.Show("本次挂失失败，该书已经确认丢失！", "提示！"); Clears(); } } } 10.挂失处理 主要代码： public partial class LostPro : Form { public LostPro() { InitializeComponent(); } private void Fill() { if (this.checkbox.Text == string.Empty) { MessageBox.Show("请输入你要使用的检索条件！", "提示！"); return; } if (this.classbox.Text == string.Empty) { MessageBox.Show("请输入你要查找的图书类型！", "提示！"); return; } string sql = string.Empty; sql += "select bid as ID号, bno as 图书编号, bname as 图书名称,bauthor as 作者, bclass as 类别, bisbn as ISBN号, bcost as 价格,bpub as 出版社, adder as 记录员, adddate as 入库日期,bnote as 内容简介,bstate as 状态 from tb_book"; sql += " where bstate='挂失'"; string c = this.checkbox.SelectedIndex.ToString(); string x = this.classbox.SelectedIndex.ToString(); if (this.checktxt.Text != "") { switch (c) { case "0"://图书名称 if (this.checktxt.Text != string.Empty) { sql += " and bname like '%" + this.checktxt.Text + "%'"; } break; case "1"://图书编号 if (this.checktxt.Text != string.Empty) { sql += " and bno like '%" + this.checktxt.Text + "%'"; } break; case "2"://作者 if (this.checktxt.Text != string.Empty) { sql += " and bauthor like '%" + this.checktxt.Text + "%'"; } break; case "3"://isbn号 if (this.checktxt.Text != string.Empty) { sql += " and bisbn like '%" + this.checktxt.Text + "%'"; } break; case "4"://出版社 if (this.checktxt.Text != string.Empty) { sql += " and bpub like '%" + this.checktxt.Text + "%'"; } break; default: break; } if (x != "0") { sql += " and bclass='" + this.classbox.Text + "'"; } } else { if (x != "0") { sql += " and bclass='" + this.classbox.Text + "'"; } } DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void checkbtn_Click(object sender, EventArgs e) { Fill(); } private void LostPro_Load(object sender, EventArgs e) { this.checkbox.SelectedIndex = 0; this.classbox.SelectedIndex = 0; this.label2.Text = ""; this.label3.Text = ""; } private void cell_click(object sender, DataGridViewCellEventArgs e) { this.isbntxt.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.notxt.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.nametxt.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.classtxt.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.costtxt.Text = this.dataGridView1[6, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.writertxt.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.pubtxt.Text = this.dataGridView1[7, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.addertxt.Text = this.dataGridView1[8, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.adddatetxt.Text = this.dataGridView1[9, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.bstatetxt.Text = this.dataGridView1[11, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql = string.Empty; sql += "select * from tb_borrow"; sql += " where bno='" + this.notxt.Text + "' and borstate='挂失'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { string uacc = dr["uacc"].ToString().Trim();//用户帐号 string uname = dr["uname"].ToString().Trim();//用户姓名 this.label2.Text = "帐号：" + uacc + " 姓名：" + uname; this.label3.Text = "该用户应缴罚款"; } else { this.label2.Text = ""; this.label3.Text = ""; } } private void btncancel_Click(object sender, EventArgs e) { this.Close(); } private void btnok_Click(object sender, EventArgs e) { if (this.notxt.Text == "") { MessageBox.Show("执行操作前，请先选择图书！","提示！"); return; } DialogResult result; result=MessageBox.Show("确认本书丢失？","提示！",MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { if (this.bstatetxt.Text == "丢失") { MessageBox.Show("操作失败，该图书已经是丢失状态了！", "提示！"); return; } if (this.bstatetxt.Text == "挂失") { string sql = string.Empty; sql += "select * from tb_borrow"; sql += " where bno='" + this.notxt.Text + "' and borstate='挂失'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { string uacc = dr["uacc"].ToString().Trim();//用户帐号 string uname = dr["uname"].ToString().Trim();//用户姓名 string sql1 = string.Empty; sql1 += "select * from tb_user"; sql1 += " where uacc='" + uacc + "'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); string rig = dr1["uright"].ToString().Trim();//用户权限 string sql2 = string.Empty; sql2 += "select rtim from tb_right"; sql2 += " where uright='" + rig + "'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); string tim = dr2["rtim"].ToString().Trim();//罚款倍数 int t = Convert.ToInt32(tim); string bcost = this.costtxt.Text;//图书价格 double c = Convert.ToDouble(bcost); double sum = t * c; string ss = Convert.ToString(sum); this.label2.Text = "帐号：" + uacc + " 姓名：" + uname; this.label3.Text = "该用户应缴罚款" + ss + "元"; string sql3 = string.Empty; sql3 += "select brcost from tb_borrow"; sql3 += " where bno='" + this.notxt.Text + "' and borstate='挂失'"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); string rcost = dr3["brcost"].ToString().Trim();//押金 // string str1 = string.Empty; str1 += "update tb_book set bstate='丢失'"; str1 += " where bno='" + this.notxt.Text + "'"; DataTable dts1 = DBHelp.ExeOleCommand(str1); string str2 = string.Empty; str2 += "update tb_borrow set brcost='0',borstate='丢失'"; str2 += " where bno='" + this.notxt.Text + "' and borstate='挂失'"; DataTable dts2 = DBHelp.ExeOleCommand(str2); Fill(); bClear(); MessageBox.Show("操作成功,归还押金"+rcost+"元,应缴罚款"+ss+"元！", "提示！"); } //else //{ // string str = string.Empty; // str += "update tb_book set bstate='丢失'"; // str += " where bno='" + this.notxt.Text + "'"; // DataTable dts = DBHelp.ExeOleCommand(str); // Fill(); // bClear(); // MessageBox.Show("操作成功！", "提示！"); //} } else { MessageBox.Show("操作失败，该书并未挂失！","提示！"); } } } private void bClear() { this.isbntxt.Text =""; this.notxt.Text = ""; this.nametxt.Text = ""; this.classtxt.Text = ""; this.costtxt.Text = ""; this.writertxt.Text = ""; this.pubtxt.Text = ""; this.addertxt.Text = ""; this.adddatetxt.Text = ""; this.bstatetxt.Text = ""; } } 11.借阅历史查询 主要代码： public partial class BorrowFrame : Form { public BorrowFrame() { InitializeComponent(); } private void BorrowFrame_Load(object sender, EventArgs e) { this.boxcheck.SelectedIndex = 0; this.boxstate.SelectedIndex = 0; } private void btncheck_Click(object sender, EventArgs e) { Fill(); } private void Fill() { string sql = string.Empty; sql += "select borid as ID号,uacc as 用户帐号,uname as 用户姓名,bno as 图书编号,bname as 图书名称,bisbn as ISBN号,brcost as 押金,bordate as 借书日期,retdate as 还书日期,borstate as 状态,brecorder as 借阅记录员 from tb_borrow"; string m = this.boxcheck.SelectedIndex.ToString(); string n = this.boxstate.SelectedIndex.ToString(); if (this.txtcheck.Text.Trim() == "") { switch (n) { //case "0"://全部借阅信息 case "1"://未还图书信息 sql += " where borstate <> '已还'"; break; case "2"://已还图书信息 sql += " where borstate='已还'"; break; case "3"://挂失图书信息 sql += " where borstate='挂失'"; break; case "4"://丢失图书信息 sql += " where borstate='丢失'"; break; default: break; } } else { switch (m) { case "0"://用户帐号 sql += " where uacc like '%" + this.txtcheck.Text + "%'"; break; case "1"://用户姓名 sql += " where uname like '%" + this.txtcheck.Text + "%'"; break; case "2"://图书编号 sql += " where bno like '%" + this.txtcheck.Text + "%'"; break; case "3"://图书名称 sql += " where bname like '%" + this.txtcheck.Text + "%'"; break; default: break; } switch (n) { case "1"://未还图书信息 sql += " and borstate <> '已还'"; break; case "2"://已还图书信息 sql += " and borstate='已还'"; break; case "3"://挂失图书信息 sql += " and borstate='挂失'"; break; case "4"://丢失图书信息 sql += " and borstate='丢失'"; break; default: break; } } DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void cancel_Click(object sender, EventArgs e) { this.Close(); } private void cell_click(object sender, DataGridViewCellEventArgs e) { //用户信息 this.txtuacc.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtuname.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql1 = string.Empty; sql1 += "select usex,upart,uright from tb_user"; sql1 += " where uacc='" + this.txtuacc.Text + "'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); if (dr1.HasRows) { this.txtusex.Text = dr1["usex"].ToString().Trim(); this.txtupart.Text = dr1["upart"].ToString().Trim(); this.txturight.Text = dr1["uright"].ToString().Trim(); } string sql2 = string.Empty;//未还图书 sql2 += "select count(*) as bornum from tb_borrow"; sql2 += " where uacc='" + this.txtuacc.Text + "'"; sql2 += " and borstate <> '已还' and borstate <> '丢失'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); if (dr2.HasRows) { this.txtnoret.Text = dr2["bornum"].ToString().Trim(); } string sql3 = string.Empty;//遗失图书 sql3 += "select count(*) as bornum from tb_borrow"; sql3 += " where uacc='" + this.txtuacc.Text + "'"; sql3 += " and borstate='丢失'"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); if (dr3.HasRows) { this.txtlost.Text = dr3["bornum"].ToString().Trim(); } string maxdate;//最大权限 string sql4 = string.Empty; sql4 += "select maxdate from tb_right"; sql4 += " where uright='" + this.txturight.Text + "'"; OleDbDataReader dr4 = DBHelp.OleReader(sql4); dr4.Read(); //if (dr4.HasRows) //{ maxdate = dr4["maxdate"].ToString().Trim(); //return maxdate; //} int max = Convert.ToInt32(maxdate); string sql5 = string.Empty;//到期图书 sql5 = sql2; sql5 += " and bordate<#" + DateTime.Now.AddDays(-max) + "#"; OleDbDataReader dr5 = DBHelp.OleReader(sql5); dr5.Read(); if (dr5.HasRows) { this.txtouttime.Text = dr5["bornum"].ToString().Trim(); } //图书信息 this.txtbno.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtbname.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql6 = string.Empty;//图书信息 sql6 += "select bclass,bisbn,bcost,bauthor,bpub from tb_book"; sql6 += " where bno='" + this.txtbno.Text + "'"; OleDbDataReader dr6 = DBHelp.OleReader(sql6); dr6.Read(); if (dr6.HasRows) { this.txtbclass.Text = dr6["bclass"].ToString().Trim(); this.txtbisbn.Text = dr6["bisbn"].ToString().Trim(); this.txtbcost.Text = dr6["bcost"].ToString().Trim(); this.txtbauthor.Text = dr6["bauthor"].ToString().Trim(); this.txtbpub.Text = dr6["bpub"].ToString().Trim(); //this.txtbstate.Text=dr6["bstate"].ToString().Trim(); } string sql7 = string.Empty;//图书状态 sql7 += "select borstate from tb_borrow"; sql7 += " where bno='" + this.txtbno.Text + "'"; OleDbDataReader dr7 = DBHelp.OleReader(sql7); dr7.Read(); if (dr7.HasRows) { this.txtbstate.Text = dr7["borstate"].ToString().Trim(); } } 12.借阅管理 主要代码： public partial class BookBorrow : Form { public BookBorrow() { InitializeComponent(); } private void FillUser() { string sql = string.Empty; sql += "select * from tb_user where uacc='" + this.useracctxt.Text + "'"; OleDbDataReader dr = DBHelp.OleReader(sql); dr.Read(); if (dr.HasRows) { this.usernametxt.Text = dr["uname"].ToString().Trim(); this.sextxt.Text = dr["usex"].ToString().Trim(); this.parttxt.Text = dr["upart"].ToString().Trim(); this.telphonetxt.Text = dr["utelphone"].ToString().Trim(); this.phonetxt.Text = dr["uphone"].ToString().Trim(); this.txtright.Text = dr["uright"].ToString().Trim(); string sql1 = string.Empty;//已借图书数量 sql1 += "select count(*) as bornum from tb_borrow"; sql1 += " where uacc='" + this.useracctxt.Text + "'"; sql1 += " and borstate <> '已还' and borstate <> '丢失'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); this.havnumtxt.Text = dr1["bornum"].ToString().Trim(); string sql2 = string.Empty;//最大借阅权限 sql2 += "select maxbook,maxdate,rcost from tb_right"; sql2 += " where uright='" + this.txtright.Text + "'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); if (dr2.HasRows) { this.txtmaxbook.Text = dr2["maxbook"].ToString().Trim(); this.txtmaxdate.Text = dr2["maxdate"].ToString().Trim(); this.labcost.Text = dr2["rcost"].ToString().Trim(); } //string sql3 = string.Empty;//到期图书数量 //DateTime.Now.AddDays(-30)>//当前日期减去借书期限大于借书日期，即为逾期 int a = Convert.ToInt32(this.txtmaxdate.Text); string sql3 = string.Empty;//到期图书数量 sql3 = sql1; sql3 += " and bordate<#" + DateTime.Now.AddDays(-a) + "#"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); this.canrettxt.Text = dr3["bornum"].ToString().Trim(); } else { MessageBox.Show("未找到该用户或帐号错误！", "提示！"); this.usernametxt.Text = ""; this.sextxt.Text = ""; this.parttxt.Text = ""; this.telphonetxt.Text = ""; this.phonetxt.Text = ""; this.txtright.Text = ""; this.havnumtxt.Text = ""; this.txtmaxbook.Text = ""; this.txtmaxdate.Text = ""; this.canrettxt.Text = ""; } } private void FillBook() { string sql = string.Empty; sql += "select bid as ID号, bno as 图书编号, bname as 图书名称,bauthor as 作者, bclass as 类别, bisbn as ISBN号, bcost as 价格,bpub as 出版社, adder as 记录员, adddate as 入库日期,bnote as 内容简介,bstate as 状态 from tb_book"; sql += " where bno='" + this.notxt.Text + "'"; sql += " and bstate='在库'"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; string s = string.Empty; s += "select * from tb_book"; s += " where bno='" + this.notxt.Text + "'"; s += " and bstate='在库'"; OleDbDataReader dr = DBHelp.OleReader(s); dr.Read(); if (dr.HasRows) { this.nametxt.Text = dr["bname"].ToString().Trim(); this.classtxt.Text = dr["bclass"].ToString().Trim(); this.isbntxt.Text = dr["bisbn"].ToString().Trim(); this.costtxt.Text = dr["bcost"].ToString().Trim(); this.writertxt.Text = dr["bauthor"].ToString().Trim(); this.pubtxt.Text = dr["bpub"].ToString().Trim(); this.addertxt.Text = dr["adder"].ToString().Trim(); this.adddatetxt.Text = dr["adddate"].ToString().Trim(); this.bstatetxt.Text = dr["bstate"].ToString().Trim(); this.notetxt.Text = dr["bnote"].ToString().Trim(); string sql1 = string.Empty;//库存总数 sql1 += "select count(*) as bnum from tb_book"; sql1 += " where bisbn='" + this.isbntxt.Text + "'"; sql1 += " and bstate <> '丢失'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); string sql2 = string.Empty;//可借数目 sql2 += "select count(*) as bnum from tb_book"; sql2 += " where bisbn='" + this.isbntxt.Text + "'"; sql2 += " and bstate='在库'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); this.numtxt.Text = dr1["bnum"].ToString().Trim(); this.cannumtxt.Text = dr2["bnum"].ToString().Trim(); } else { MessageBox.Show("未找到该书或该书已借出！", "提示！"); this.nametxt.Text = ""; this.classtxt.Text = ""; this.isbntxt.Text = ""; this.costtxt.Text = ""; this.writertxt.Text = ""; this.pubtxt.Text = ""; this.addertxt.Text = ""; this.adddatetxt.Text = ""; this.bstatetxt.Text = ""; this.notetxt.Text = ""; this.numtxt.Text = ""; this.cannumtxt.Text = ""; } } private void checkbtn_Click(object sender, EventArgs e) { if (this.useracctxt.Text == string.Empty) { MessageBox.Show("请输入用户的帐号！","提示！"); return; } FillUser(); } private void checkbookbtn_Click(object sender, EventArgs e) { if (this.notxt.Text == string.Empty) { MessageBox.Show("请输入图书的编号！","提示！"); return; } FillBook(); } private void cancel_Click(object sender, EventArgs e) { this.Close(); } private void borrowbtn_Click(object sender, EventArgs e) { if (this.usernametxt.Text == string.Empty || this.nametxt.Text == string.Empty) { MessageBox.Show("请选择用户或图书！","提示！"); return; } DialogResult result; result=MessageBox.Show("确认借阅本书？","借书提示！",MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { //验证用户的借阅权限 int a = Convert.ToInt32(this.havnumtxt.Text); int b = Convert.ToInt32(this.txtmaxbook.Text); int c = Convert.ToInt32(this.canrettxt.Text); if (a >= b) { MessageBox.Show("对不起！该用户最多可借" + this.txtmaxbook.Text + "本书！", "提示！"); return; } if (c > 0) { MessageBox.Show("对不起！该用户有" + this.canrettxt.Text + "本书超期未还！", "提示！"); return; } string strx = string.Empty; strx += "select count(*) as num from tb_borrow"; strx += " where uacc='"+this.useracctxt.Text+"' and borstate='挂失'"; OleDbDataReader dx = DBHelp.OleReader(strx); dx.Read(); string nx = dx["num"].ToString().Trim(); int xx = Convert.ToInt32(nx); if (xx > 0) { MessageBox.Show("对不起！该用户有" + nx + "本书挂失未还！", "提示！"); return; } // string sql = string.Empty; sql += "select * from tb_book where bno='" + this.notxt.Text + "' and bstate='在库'"; DataTable dt = DBHelp.ExeOleCommand(sql); bool x = false; while (dt.Rows.Count != 0) { x = true; break; } if (x) { string sql1 = string.Empty; sql1 += "update tb_book set bstate='借出' where bno='" + this.notxt.Text + "' and bname='" + this.nametxt.Text + "'"; string sql2 = string.Empty; sql2 += "insert into tb_borrow(uacc,uname,bno,bname,bisbn,brcost,bordate,retdate,borstate,brecorder)"; sql2 += " values('" + this.useracctxt.Text + "','" + this.usernametxt.Text + "','" + this.notxt.Text + "','" + this.nametxt.Text + "','"+this.isbntxt.Text+"','"+this.labcost.Text+"','" + DateTime.Now.ToString() + "',null,'未还','" + LoginForm.uname + "')"; DataTable dt1 = DBHelp.ExeOleCommand(sql1); DataTable dt2 = DBHelp.ExeOleCommand(sql2); MessageBox.Show("借书成功！请付押金"+this.labcost.Text+"元", "恭喜！"); FillUser(); //FillBook(); this.nametxt.Text = ""; this.classtxt.Text = ""; this.isbntxt.Text = ""; this.costtxt.Text = ""; this.writertxt.Text = ""; this.pubtxt.Text = ""; this.addertxt.Text = ""; this.adddatetxt.Text = ""; this.bstatetxt.Text = ""; this.notetxt.Text = ""; this.numtxt.Text = ""; this.cannumtxt.Text = ""; } else { MessageBox.Show("未找到该书或该书已借出！", "提示！"); this.nametxt.Text = ""; this.classtxt.Text = ""; this.isbntxt.Text = ""; this.costtxt.Text = ""; this.writertxt.Text = ""; this.pubtxt.Text = ""; this.addertxt.Text = ""; this.adddatetxt.Text = ""; this.bstatetxt.Text = ""; this.notetxt.Text = ""; this.numtxt.Text = ""; this.cannumtxt.Text = ""; } } } private void nowborrow_Click(object sender, EventArgs e) { string sql = string.Empty; sql += "select borid as 借阅编号,uacc as 用户帐号,uname as 用户姓名,bno as 图书编号,bname as 图书名称,bisbn as ISBN号,brcost as 押金,bordate as 借书日期,borstate as 状态,brecorder as 记录员 from tb_borrow"; sql += " where borstate <> '已还'"; DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } 13.还书处理 主要代码： public partial class BookReturn : Form { public BookReturn() { InitializeComponent(); } private void BookReturn_Load(object sender, EventArgs e) { this.boxcheck.SelectedIndex = 0; this.boxstate.SelectedIndex = 0; } private void btncheck_Click(object sender, EventArgs e) { Fill(); } private void cell_click(object sender, DataGridViewCellEventArgs e) { //用户信息 this.txtuacc.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtuname.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql1 = string.Empty; sql1 += "select usex,upart,uright from tb_user"; sql1 += " where uacc='"+this.txtuacc.Text+"'"; OleDbDataReader dr1 = DBHelp.OleReader(sql1); dr1.Read(); if (dr1.HasRows) { this.txtusex.Text = dr1["usex"].ToString().Trim(); this.txtupart.Text = dr1["upart"].ToString().Trim(); this.txturight.Text = dr1["uright"].ToString().Trim(); } string sql2 = string.Empty;//未还图书 sql2 += "select count(*) as bornum from tb_borrow"; sql2 += " where uacc='"+this.txtuacc.Text+"'"; sql2 += " and borstate <> '已还' and borstate <> '丢失'"; OleDbDataReader dr2 = DBHelp.OleReader(sql2); dr2.Read(); if (dr2.HasRows) { this.txtnoret.Text = dr2["bornum"].ToString().Trim(); } string sql3 = string.Empty;//遗失图书 sql3 += "select count(*) as bornum from tb_borrow"; sql3 += " where uacc='" + this.txtuacc.Text + "'"; sql3 += " and borstate='丢失'"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); if (dr3.HasRows) { this.txtlost.Text = dr3["bornum"].ToString().Trim(); } string maxdate;//最大权限 string sql4 = string.Empty; sql4 += "select maxdate from tb_right"; sql4 += " where uright='"+this.txturight.Text+"'"; OleDbDataReader dr4 = DBHelp.OleReader(sql4); dr4.Read(); //if (dr4.HasRows) //{ maxdate = dr4["maxdate"].ToString().Trim(); //return maxdate; //} int max = Convert.ToInt32(maxdate); string sql5 = string.Empty;//到期图书 sql5 = sql2; sql5 += " and bordate<#" + DateTime.Now.AddDays(-max) + "#"; OleDbDataReader dr5 = DBHelp.OleReader(sql5); dr5.Read(); if (dr5.HasRows) { this.txtouttime.Text = dr5["bornum"].ToString().Trim(); } //图书信息 this.txtbno.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); this.txtbname.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString().Trim(); string sql6 = string.Empty;//图书信息 sql6 += "select bclass,bisbn,bcost,bauthor,bpub from tb_book"; sql6 += " where bno='"+this.txtbno.Text+"'"; OleDbDataReader dr6 = DBHelp.OleReader(sql6); dr6.Read(); if (dr6.HasRows) { this.txtbclass.Text=dr6["bclass"].ToString().Trim(); this.txtbisbn.Text = dr6["bisbn"].ToString().Trim(); this.txtbcost.Text=dr6["bcost"].ToString().Trim(); this.txtbauthor.Text=dr6["bauthor"].ToString().Trim(); this.txtbpub.Text=dr6["bpub"].ToString().Trim(); //this.txtbstate.Text=dr6["bstate"].ToString().Trim(); } string sql7 = string.Empty;//图书状态 sql7 += "select borstate from tb_borrow"; sql7 += " where bno='" + this.txtbno.Text + "'"; OleDbDataReader dr7 = DBHelp.OleReader(sql7); dr7.Read(); if (dr7.HasRows) { this.txtbstate.Text=dr7["borstate"].ToString().Trim(); } } private void btnreturn_Click(object sender, EventArgs e) { if (this.txtuacc.Text == string.Empty) { MessageBox.Show("请选择用户！","提示！"); return; } if (this.txtbno.Text == string.Empty) { MessageBox.Show("请选择图书！","提示！"); return; } DialogResult result; result=MessageBox.Show("确认归还本书？", "还书提示！", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { string sql = string.Empty; sql += "select * from tb_borrow"; sql += " where bno='" + this.txtbno.Text + "' and borstate='已还'"; DataTable dt = DBHelp.ExeOleCommand(sql); bool b = false; while (dt.Rows.Count == 0) { b = true; break; } if (b) { string str1 = string.Empty;//验证是否逾期 str1 += "select maxdate,rfine from tb_right"; str1 += " where uright='"+this.txturight.Text+"'"; OleDbDataReader dr1 = DBHelp.OleReader(str1); dr1.Read(); string max = dr1["maxdate"].ToString().Trim(); int m = Convert.ToInt32(max);//最大借阅期限 string str2 = string.Empty; str2 += "select bordate from tb_borrow"; str2 += " where bno='" + this.txtbno.Text + "' and borstate <> '已还'"; OleDbDataReader dr2 = DBHelp.OleReader(str2); dr2.Read(); string date = dr2["bordate"].ToString().Trim(); DateTime n = Convert.ToDateTime(date);//借书日期 if (DateTime.Now.AddDays(-m) > n) { DateTime d1 = new DateTime(); d1 = DateTime.Now; DateTime d2 = n; TimeSpan ts = new TimeSpan(d1.Ticks-d2.Ticks); string day = Convert.ToString(ts.Days);//逾期天数 this.txtoutday.Text = day; this.txtoutcost.Text = dr1["rfine"].ToString().Trim(); double x = Convert.ToDouble(this.txtoutday.Text); double y = Convert.ToDouble(this.txtoutcost.Text); double z = x * y; this.txtpcost.Text = Convert.ToString(z); MessageBox.Show("该书已超期,请按超期信息缴费！","提示！"); ReturnBook(); } else { ReturnBook(); } } else { MessageBox.Show("还书失败，该书在库中！", "提示！"); } } } private void ReturnBook() { string str = string.Empty; str += "select brcost from tb_borrow"; str += " where bno='" + this.txtbno.Text + "' and borstate <> '已还'"; OleDbDataReader dr = DBHelp.OleReader(str); dr.Read(); string xx = dr["brcost"].ToString().Trim(); string sql1 = string.Empty; sql1 += "update tb_borrow set retdate='" + DateTime.Now.ToString() + "',borstate='已还'"; sql1 += " where bno='" + this.txtbno.Text + "' and uacc='" + this.txtuacc.Text + "'"; string sql2 = string.Empty; sql2 += "update tb_book set bstate='在库'"; sql2 += " where bno='" + this.txtbno.Text + "'"; DataTable dt1 = DBHelp.ExeOleCommand(sql1); DataTable dt2 = DBHelp.ExeOleCommand(sql2); MessageBox.Show("还书成功！归还您的押金"+xx+"元", "恭喜！"); Fill(); string sql3 = string.Empty;//未还图书 sql3 += "select count(*) as bornum from tb_borrow"; sql3 += " where uacc='" + this.txtuacc.Text + "'"; sql3 += " and borstate <> '已还'"; OleDbDataReader dr3 = DBHelp.OleReader(sql3); dr3.Read(); if (dr3.HasRows) { this.txtnoret.Text = dr3["bornum"].ToString().Trim(); } string sql7 = string.Empty;//图书状态 sql7 += "select borstate from tb_borrow"; sql7 += " where bno='" + this.txtbno.Text + "'"; OleDbDataReader dr7 = DBHelp.OleReader(sql7); dr7.Read(); if (dr7.HasRows) { this.txtbstate.Text = dr7["borstate"].ToString().Trim(); } } private void Fill() { string sql = string.Empty; sql += "select borid as ID号,uacc as 用户帐号,uname as 用户姓名,bno as 图书编号,bname as 图书名称,bisbn as ISBN号,brcost as 押金,bordate as 借书日期,retdate as 还书日期,borstate as 状态,brecorder as 借阅记录员 from tb_borrow"; string m = this.boxcheck.SelectedIndex.ToString(); string n = this.boxstate.SelectedIndex.ToString(); if (this.txtcheck.Text.Trim() == "") { switch (n) { //case "0"://全部借阅信息 case "1"://未还图书信息 sql += " where borstate <> '已还'"; break; case "2"://已还图书信息 sql += " where borstate='已还'"; break; case "3"://挂失图书信息 sql += " where borstate='挂失'"; break; case "4"://丢失图书信息 sql += " where borstate='丢失'"; break; default: break; } } else { switch (m) { case "0"://用户帐号 sql += " where uacc like '%" + this.txtcheck.Text + "%'"; break; case "1"://用户姓名 sql += " where uname like '%" + this.txtcheck.Text + "%'"; break; case "2"://图书编号 sql += " where bno like '%" + this.txtcheck.Text + "%'"; break; case "3"://图书名称 sql += " where bname like '%" + this.txtcheck.Text + "%'"; break; default: break; } switch (n) { case "1"://未还图书信息 sql += " and borstate <> '已还'"; break; case "2"://已还图书信息 sql += " and borstate='已还'"; break; case "3"://挂失图书信息 sql += " and borstate='挂失'"; break; case "4"://丢失图书信息 sql += " and borstate='丢失'"; break; default: break; } } DataTable dt = DBHelp.ExeOleCommand(sql); this.dataGridView1.DataSource = dt; } private void cancel_Click(object sender, EventArgs e) { this.boxcheck.SelectedIndex = 0; this.boxstate.SelectedIndex = 0; this.txtcheck.Text = ""; } } 14.个人借阅历史