CMySQL图书管理系统

1、C#面向对象程序设计课程设计图书借阅管理系统源码下载地址：院 系：计算机学院专 业：计算机科学与技术年 级：2014级学生姓名：/ 学 号：/教 师：/日 期： 2017年6月6日开发和运行环境- 开发环境：Windows 10-1703；- 开发工具：Visual Studio 013 Update5；- 数据库工具：Mysql 5.7 + NaviCat；- 运行环境：Windows 10-1703；需求分析1.【角色】用户通过【注册】成为本系统的可访问成员，通过【登陆】进入系统。用户可以操作的功能有：-查看图书馆的所有图书信息，图书的是否可借阅信息；-搜索功能：通过输入关键字，来查找是否

2、有自己需要的图书；-借阅功能：未被其他用户借阅的书籍可以直接借阅；-还书功能：若存在之前借阅的书籍，在未归还的情况下，可以进行还书操作，若未存在需要还的书籍，提示：未有需要归还的书籍；-个人信息：修改除了【用户名】以外之前注册的全部信息；-借阅信息：可以查看借阅历史，曾经的借书日期和还书日期，是否有未归还的书等等；2.【角色】管理员通过【登陆】进入用户进入不了的管理系统。管理员可以操作的功能有：-图书信息修改：修改除了图书名的现有的所有图书信息；-添加图书：添加新的图书；-查看用户信息：可以查看用户表的部分用户信息；3数据保存：需要长期保存在数据库的数据有：-图书信息：图书的基本信息；-读者信

3、息：读者的基本信息；-借阅信息：图书的借阅信息；-帐号信息：图书管理员和读者的登录帐号；   系统流程图（不包含注册功能）数据库设计说明- 管理员表：adminInfo；- 图书信息表：bookInfo；- 借阅信息表：borrowed；- 用户信息表：userInfo；adminInfo表列名数据类型可否为空长度说明adminIdint否11管理员IDadminNamevarchar否255管理员用户名adminPasswordvarchar否255管理员密码bookInfo表列名数据类型可否为空长度说明bookIdint否11图书编号IDbookNamevarchar否

4、255图书名bookAuthorvarchar否255图书作者bookPriceint否11图书价格bookPressvarchar否255图书出版社bookTypevarchar是255图书类型isBorrowedtinyint是11是否被借阅borrowIdint是11借阅人的IDBorrowedTimedatetime是0被借阅的时间borrowed表列名数据类型可否为空长度说明borrowedIdint否11借阅信息IDuserIdint是11操作人IDuserNamevarchar是255操作人用户名bookIdint是11图书IDeditTimedatatime是0操作时间book

5、Namevarchar是255图书名editvarchar是255操作名UserInfo表列名数据类型可否为空长度说明userIdint否11用户IDuserNamevarchar否255用户名userPasswordvarchar否255用户密码userSexvarchar是255用户性别模块分析cs文件：主界面：主要显示代码 mainCenter.cs ：public mainCenter() InitializeComponent(); string sql = "server=localhost;user id=root;password=123456;database=C#

6、librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open();/打开数据库连接 MySqlDataAdapter data1 = new MySqlDataAdapter();/实例化sqldataadpter MySqlCommand cmd1 = new MySqlCommand("SELECT bookName as '图书名',bookAuthor as '作者',bookPrice as '图书价格',book

7、Press as '出版社',bookType as '图书类型',(CASE isBorrowed WHEN 0 THEN '未借阅' ELSE '已借阅' END) as'借阅情况' FROM c#librarycenter.bookinfo", conn);/sql语句 data1.SelectCommand = cmd1;/设置为已实例化SqlDataAdapter的查询命令 DataTable dt = new DataTable(); dt.Clear(); data1.Fill(dt);/把

8、数据填充到datatable dataGridView1.DataSource = dt;/将数据集绑定datagridview,完成显示 dataGridView1.AllowUserToAddRows = false; / login login = new login(); label1.Text = "这里是SMART-LIBRARY！用户：" + login.username; / 查找功能：主要实现代码：private void button1_Click(object sender, EventArgs e) String so = textBox1.Text

9、; String sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open();/打开数据库连接 MySqlDataAdapter data1 = new MySqlDataAdapter();/实例化sqldataadpter MySqlCommand cmd1 = new MySqlCommand("SELECT bookNam

10、e as '图书名',bookAuthor as '作者',bookPrice as '图书价格',bookPress as '出版社',bookType as '图书类型',(CASE isBorrowed WHEN 0 THEN '未借阅' ELSE '已借阅' END) as'借阅情况' FROM c#librarycenter.bookinfo where bookName like '%"+ so +"%' ",

11、 conn);/sql语句 data1.SelectCommand = cmd1;/设置为已实例化SqlDataAdapter的查询命令 /DataSet ds1 = new DataSet();/实例化dataset DataTable dt = new DataTable(); dt.Clear(); data1.Fill(dt);/把数据填充到datatable dataGridView1.DataSource = dt;/将数据集绑定datagridview,完成显示 dataGridView1.AllowUserToAddRows = false; 借阅功能：主要实现代码 - bor

12、row.cs：private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) int r = this.dataGridView1.CurrentRow.Index; string h = this.dataGridView1.Rowsr.Cells1.Value.ToString(); string s = this.dataGridView1.Rowsr.Cells2.Value.ToString(); / login login = new login(); String us

13、erId = "" DateTime dt = DateTime.Now; String borrowedTime = dt.ToString("yyyy-MM-dd HH:mm:ss"); string sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); String updateBorrow1 = "s

14、elect userId from userInfo where userName = '"+login.username+"'" MySqlCommand mysqlcommand = new MySqlCommand(updateBorrow1,conn); conn.Open(); MySqlDataReader mdr = mysqlcommand.ExecuteReader(); if (mdr.Read() userId = mdr.GetString(0).ToString(); mdr.Close(); String updateB

15、orrow2 = "update bookInfo set isBorrowed = '1', borrowId = '" + userId + "',borrowedTime ='" + borrowedTime + "' where bookId = '" + h + "'" / MySqlCommand mysqlcommand2 = new MySqlCommand(updateBorrow2, conn); int i = mysqlcomm

16、and2.ExecuteNonQuery(); if (i != -1) MessageBox.Show(login.username + " rn恭喜你借" + " << " + s + " >> rn这本书" + "成功"); else MessageBox.Show("系统出了点问题，请联系管理员！"); /更新借阅表 String updateBorrow3 = "insert into borrowed(userId,userName,bookId,e

17、ditTime,bookName,edit) values('" + userId + "','" + login.username + "','" + h + "','" + borrowedTime + "','" + s + "','借书')" MySqlCommand mysqlcommand3 = new MySqlCommand(updateBorrow3, conn); mys

18、qlcommand3.ExecuteNonQuery(); /刷新dataGridView1，重新绑定数据源 f5(); 归还图书功能：主要实现代码 returnbook.cs：private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) int r = this.dataGridView1.CurrentRow.Index; string h = this.dataGridView1.Rowsr.Cells1.Value.ToString(); string s = this.d

19、ataGridView1.Rowsr.Cells2.Value.ToString(); DateTime dt = DateTime.Now; String returnedTime = dt.ToString("yyyy-MM-dd HH:mm:ss"); String userId = "" string sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new

20、 MySqlConnection(sql); conn.Open(); / String updateBorrow1 = "update bookInfo set isBorrowed = '0',borrowId = null,borrowedTime = null where bookId = '"+h+"'" MySqlCommand mysqlcommand2 = new MySqlCommand(updateBorrow1, conn); int i = mysqlcommand2.ExecuteNonQuery

21、(); if (i != -1) MessageBox.Show(login.username + " rn<< " + s + " >> rn这本书，还书成功"); else MessageBox.Show("系统出了点问题，请联系管理员！"); /更新借阅表 String updateBorrow = "select userId from userInfo where userName = '" + login.username + "'" MySq

22、lCommand mysqlcommand = new MySqlCommand(updateBorrow, conn); MySqlDataReader mdr = mysqlcommand.ExecuteReader(); if (mdr.Read() userId = mdr.GetString(0).ToString(); mdr.Close(); String updateBorrow2 = "insert into borrowed(userId,userName,bookId,editTime,bookName,edit) values('" + us

23、erId + "','" + login.username + "','" + h + "','" + returnedTime + "','" + s + "','还书')" MySqlCommand mysqlcommand3 = new MySqlCommand(updateBorrow2, conn); mysqlcommand3.ExecuteNonQuery(); /刷新dataGridView1

24、，重新绑定数据源 f5(); 修改个人信息：主要实现代码 editUser.cs：private void button1_Click(object sender, EventArgs e) login login = new login(); String pw = textBox1.Text; String npw = textBox2.Text; String un = login.username; String userPassword = "" String userId = "" if (male.Checked = true) ans =

25、 "male" if (female.Checked = true) ans = "female" String sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open(); / String yanzheng = "SELECT userPassword FROM userinfo whe

26、re userName = '"+login.username+"'" MySqlCommand yz = new MySqlCommand(yanzheng,conn); MySqlDataReader mdr2 = yz.ExecuteReader(); if (mdr2.Read() userPassword = mdr2.GetString(0); mdr2.Close(); if () != "") if (pw = userPassword) / String editsql1 = "select user

27、Id from userInfo where userName = '" + login.username + "' " MySqlCommand cmd1 = new MySqlCommand(editsql1, conn); MySqlDataReader mdr = cmd1.ExecuteReader(); if (mdr.Read() userId = mdr.GetString(0); else mdr.Close(); / String editsql2 = "update userInfo set userPassword

28、 = '" + npw + "', userSex = '" + ans + "'where userId = '" + userId + "'" MySqlCommand cmd2 = new MySqlCommand(editsql2, conn); int i = cmd2.ExecuteNonQuery(); if (i = 1) MessageBox.Show(login.username + "rn您的信息修改成功！rn请牢记您现在的密码");

29、else MessageBox.Show("修改失败，请联系管理员"); else MessageBox.Show("你的原密码输入错误，请重新输入啊啊啊"); else MessageBox.Show("新的密码请不要填空！"); 查询借阅信息：主要实现代码 borrowedinfo.cs：public borrowedinfo() InitializeComponent(); label1.Text = login.username + " ,以下是您的借阅记录：" String sql = "ser

30、ver=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open();/打开数据库连接 String userId = "" MySqlCommand cmd = new MySqlCommand("select userId from borrowed where userName = '"+ login.username +&q

31、uot;'",conn); MySqlDataReader mdr = cmd.ExecuteReader(); if (mdr.Read() userId = mdr.GetString(0); mdr.Close(); MySqlDataAdapter data1 = new MySqlDataAdapter();/实例化sqldataadpter MySqlCommand cmd1 = new MySqlCommand("select bookId as '图书编号',bookName as '图书名',editTime as

32、'操作时间',edit as '操作' from borrowed where userId = '"+ userId +"'", conn);/sql语句 data1.SelectCommand = cmd1;/设置为已实例化SqlDataAdapter的查询命令 /DataSet ds1 = new DataSet();/实例化dataset DataTable dt = new DataTable(); dt.Clear(); data1.Fill(dt);/把数据填充到datatable dataGridVi

33、ew1.DataSource = dt;/将数据集绑定datagridview,完成显示 dataGridView1.AllowUserToAddRows = false; 后台管理系统：主要实现代码 adminCenter.cs：private void 关于ToolStripMenuItem_Click(object sender, EventArgs e) MessageBox.Show("制作者：Matthew Hanrn完成时间：2017-6-4"); private void 退出系统ToolStripMenuItem_Click(object sender,

34、EventArgs e) System.Environment.Exit(0); private void 图书管理ToolStripMenuItem_Click(object sender, EventArgs e) editMain editMain = new editMain(); editMain.FormBorderStyle = FormBorderStyle.None; editMain.Dock = DockStyle.Fill; editMain.TopLevel = false; this.panel1.Controls.Clear(); this.panel1.Cont

35、rols.Add(editMain); editMain.Show(); private void 添加图书ToolStripMenuItem_Click(object sender, EventArgs e) edit2 edit2 = new edit2(); edit2.Show(); private void 用户管理ToolStripMenuItem_Click(object sender, EventArgs e) edit3 edit3 = new edit3(); edit3.FormBorderStyle = FormBorderStyle.None; edit3.Dock

36、= DockStyle.Fill; edit3.TopLevel = false; this.panel1.Controls.Clear(); this.panel1.Controls.Add(edit3); edit3.Show(); 修改图书信息：主要实现代码 editMain.cs & edit1.cs：public static string bookname; public static string bookauthor; public static string bookprice; public static string bookpress; public stati

37、c string booktype;/说明：这里用的是全局静态变量，把表格获取到的数据保存起来，进行数据传输。private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) int r = this.dataGridView1.CurrentRow.Index; string bn = this.dataGridView1.Rowsr.Cells1.Value.ToString(); string ba = this.dataGridView1.Rowsr.Cells2.Value.

38、ToString(); string bp1 = this.dataGridView1.Rowsr.Cells3.Value.ToString(); string bp2 = this.dataGridView1.Rowsr.Cells4.Value.ToString(); string bt = this.dataGridView1.Rowsr.Cells5.Value.ToString(); / editMain editMain = new editMain(); editMain.bookname = bn.ToString(); editMain.bookauthor = ba.To

39、String(); editMain.bookprice = bp1.ToString(); editMain.bookpress = bp2.ToString(); editMain.booktype = bt.ToString(); / edit1 edit1 = new edit1(); edit1.Show(); /接下来是edit1.cs中的提交新的数据，update数据库。public edit1() InitializeComponent(); editMain editMain = new editMain(); label6.Text = editMain.bookname;

40、 textBox1.Text = editMain.bookauthor; textBox2.Text = editMain.bookprice; textBox3.Text = editMain.bookpress; textBox4.Text = editMain.booktype; private void button1_Click(object sender, EventArgs e) String bn = editMain.bookname; String ba = textBox1.Text; String bp1 = textBox2.Text; String bp2 = t

41、extBox3.Text; String bt = textBox4.Text; / String sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open(); / String sql2 = "update bookInfo set bookAuthor = '" + ba + "', boo

42、kPrice = '" + bp1 + "', bookPress = '" + bp2 + "',bookType = '"+ bt +"' where bookName = '"+ bn +"' " MySqlCommand cmd = new MySqlCommand(sql2, conn); int i = cmd.ExecuteNonQuery(); if (i = 1) MessageBox.Show("图书修改成功！&

43、quot;); this.Hide(); 添加图书：主要实现代码 edit2.cs：private void button1_Click(object sender, EventArgs e) String bn = textBox5.Text; String ba = textBox1.Text; String bp1 = textBox2.Text; String bp2 = textBox3.Text; String bt = textBox4.Text; / String sql = "server=localhost;user id=root;password=123456;database=C#librarycenter" /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Op