C# + MySQL 图书管理系统.doc

_C#面向对象程序设计课程设计图书借阅管理系统源码下载地址：http:/pan.baidu.com/s/1eRCzK2m院 系：计算机学院专 业：计算机科学与技术年 级：2014级学生姓名：/ 学 号：/教 师：/日 期： 2017年6月6日开发和运行环境- 开发环境：Windows 10-1703；- 开发工具：Visual Studio 013 Update5；- 数据库工具：Mysql 5.7 + NaviCat；- 运行环境：Windows 10-1703；需求分析1.【角色】用户通过【注册】成为本系统的可访问成员，通过【登陆】进入系统。用户可以操作的功能有：-查看图书馆的所有图书信息，图书的是否可借阅信息；-搜索功能：通过输入关键字，来查找是否有自己需要的图书；-借阅功能：未被其他用户借阅的书籍可以直接借阅；-还书功能：若存在之前借阅的书籍，在未归还的情况下，可以进行还书操作，若未存在需要还的书籍，提示：未有需要归还的书籍；-个人信息：修改除了【用户名】以外之前注册的全部信息；-借阅信息：可以查看借阅历史，曾经的借书日期和还书日期，是否有未归还的书等等；2.【角色】管理员通过【登陆】进入用户进入不了的管理系统。管理员可以操作的功能有：-图书信息修改：修改除了图书名的现有的所有图书信息；-添加图书：添加新的图书；-查看用户信息：可以查看用户表的部分用户信息；3数据保存：需要长期保存在数据库的数据有：-图书信息：图书的基本信息；-读者信息：读者的基本信息；-借阅信息：图书的借阅信息；-帐号信息：图书管理员和读者的登录帐号； 系统流程图（不包含注册功能）数据库设计说明- 管理员表：adminInfo；- 图书信息表：bookInfo；- 借阅信息表：borrowed；- 用户信息表：userInfo；adminInfo表列名数据类型可否为空长度说明adminIdint否11管理员IDadminNamevarchar否255管理员用户名adminPasswordvarchar否255管理员密码bookInfo表列名数据类型可否为空长度说明bookIdint否11图书编号IDbookNamevarchar否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操作时间bookNamevarchar是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#librarycenter; /根据自己的设置 MySqlConnection conn = new MySqlConnection(sql); conn.Open();/打开数据库连接 MySqlDataAdapter data1 = new MySqlDataAdapter();/实例化sqldataadpter MySqlCommand cmd1 = new MySqlCommand(SELECT bookName as 图书名,bookAuthor as 作者,bookPrice as 图书价格,bookPress 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);/把数据填充到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; 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 bookName 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 +% , 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; 借阅功能：主要实现代码 - borrow.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 userId = ; 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 = select 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 updateBorrow2 = update bookInfo set isBorrowed = 1, borrowId = + userId + ,borrowedTime = + borrowedTime + where bookId = + h + ; / MySqlCommand mysqlcommand2 = new MySqlCommand(updateBorrow2, conn); int i = mysqlcommand2.ExecuteNonQuery(); if (i != -1) MessageBox.Show(login.username + rn恭喜你借 + rn这本书 + 成功); else MessageBox.Show(系统出了点问题，请联系管理员！); /更新借阅表 String updateBorrow3 = insert into borrowed(userId,userName,bookId,editTime,bookName,edit) values( + userId + , + login.username + , + h + , + borrowedTime + , + s + ,借书); MySqlCommand mysqlcommand3 = new MySqlCommand(updateBorrow3, conn); mysqlcommand3.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.dataGridView1.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 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(); if (i != -1) MessageBox.Show(login.username + rn rn这本书，还书成功); else MessageBox.Show(系统出了点问题，请联系管理员！); /更新借阅表 String updateBorrow = select userId from userInfo where userName = + login.username + ; MySqlCommand 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( + userId + , + login.username + , + h + , + returnedTime + , + s + ,还书); MySqlCommand mysqlcommand3 = new MySqlCommand(updateBorrow2, conn); mysqlcommand3.ExecuteNonQuery(); /刷新dataGridView1，重新绑定数据源 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 = 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 where userName = +login.username+; MySqlCommand yz = new MySqlCommand(yanzheng,conn); MySqlDataReader mdr2 = yz.ExecuteReader(); if (mdr2.Read() userPassword = mdr2.GetString(0); mdr2.Close(); if (textBox2.Text.Trim() != ) if (pw = userPassword) / String editsql1 = select userId 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 = + 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请牢记您现在的密码); else MessageBox.Show(修改失败，请联系管理员); else MessageBox.Show(你的原密码输入错误，请重新输入啊啊啊); else MessageBox.Show(新的密码请不要填空！); 查询借阅信息：主要实现代码 borrowedinfo.cs：public borrowedinfo() InitializeComponent(); label1.Text = login.username + ,以下是您的借阅记录：; String sql = server=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 +,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 操作时间,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 dataGridView1.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, 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.Controls.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 = 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 static 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.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.ToString(); 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; 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 = textBox3.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 + , bookPrice = + bp1 + , bookPress = + bp2 + ,bookType = + bt + where bookName = + bn + ; MySqlCommand cmd = new MySqlCommand(sql2, conn); int i = cmd.ExecuteNonQuery(); if (i = 1) MessageBox.Show(图书修改成功！); 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.Open(); String sql2 = insert into bookInfo(bookName,bookAuthor,bookPrice,bookPress,bookType,isBorrowed) values( + bn + , + ba + , + bp1 + , + bp2 + , + bt + ,0) ; MySqlCommand cmd = new MySqlCommand(sql2, conn); int i = cmd.ExecuteNonQuery(); if (i = 1) MessageBox.Show(图书添加成功！); else MessageBox.Show(系统出错！); this.Hide(); 用户管理：主要实现代码 edit3.cs：public edit3() InitializeComponent(); 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 userId as 用户ID,userName as 用户名,(CASE userPassword WHEN null THEN * ELSE * END) as 用户密码,userSex as 用户性别 FROM c#librarycenter.userInfo, conn);/sql语句 data1.SelectCommand = cmd1;/设置为已实例化SqlDataAdapter的查询命令 DataTable dt = new DataTable(); dt.Clear(); data1.Fill(dt);/把数据填充到datatable dataGridView1.DataSource = dt;/将数据集绑定datagridview,完成显示 dataGridView1.AllowUserToAddRows = false; 总结这次的图书管理系统，并未用到MVC设计模式，只是简单的面向对象设计，选的题目也是比较熟悉的，本系统提供两种权限：用户和管理员，其实应该把【管理员】分成【图书管理员】和【系统管理员】，提供不同的权限，【系统管理员】具有对所有用户的操作权限，【图书管理员】具有对所有图书的操作权限。本次课题当然还存在着许多不足，以后有机会的话，会加以改进。姓名：/学号：/-可编辑修改-