【C#项目】图书馆管理系统-WinForm+MySQL
作者:mmseoamin日期:2023-12-21

文章目录

  • 前言
  • 一、业务梳理与需求分析
      • 1.功能描述
      • 2.实现步骤
      • 3.功能逻辑图
      • 二、数据库设计
          • 1.实体-关系(E-R图)概念模型设计
          • 2.数据表设计
          • 三、WinForm界面交互设计
              • 1、界面交互逻辑
              • 2、项目树
              • 3、主界面+登录界面
              • 4、 图书查询界面
              • 5、图书借阅界面
              • 6、图书插入界面
              • 7、图书更新/删除界面
              • 四、通用类设计
                  • 1、与MySQL通讯模块(整个类模块中删除命名空间等,值保留这个类)
                  • 2、数据库操作模块:
                  • 五、系统代码实现与分析
                      • 1、主界面代码:
                      • 2、登录界面代码
                      • 3、图书查询模块代码
                      • 4、图书借阅代码
                      • 5、图书插入代码
                      • 6、图书更新/删除代码
                      • 总结

                        前言

                        图书馆管理系统主要功能有普通用户(学生)借书、管理员管理图书。


                        一、业务梳理与需求分析

                        1.功能描述

                        • 系统中的普通用户模块有以下功能:

                          图书查询:根据输入的搜索条件进行查询,可以查找多项,也可以查找所有图书。

                          图书借阅:提供图书借阅证号,可以进行图书借阅。

                        • 系统中的管理员用户模块有以下功能:

                          图书查询:查询图书信息。

                          图书借阅:提供图书借阅证号,可以进行图书借阅。

                          增加图书:增添新图书。

                          修改图书:对图书信息进行修改。

                          删除图书:删除过时的、不能借阅的图书。

                          2.实现步骤

                          • 一个完整系统的数据库设计。
                          • 图书馆管理系统的界面设计。
                          • 项目的通用类。
                          • 图书馆管理系统代码的实现和分析。

                            3.功能逻辑图

                            【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第1张

                            二、数据库设计

                            1.实体-关系(E-R图)概念模型设计

                            【C#项目】图书馆管理系统-WinForm+MySQL,代码如下(示例):,第2张

                            2.数据表设计

                            根据需求分析进行数据库设计,数据库名称为BookManage,根据E-R图,有学生表、图书信息表、管理员表、借阅表。学生和管理员合并为一张用户表,增加一个字段用户权限进行区分。

                            • 用户表结构

                              【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第3张

                              【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第4张

                              • 图书信息表结构

                                【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第5张

                                【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第6张

                                • 图书借阅信息表结构

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第7张

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第8张

                                  三、WinForm界面交互设计

                                  1、界面交互逻辑

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第9张

                                  2、项目树

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第10张

                                  3、主界面+登录界面

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第11张

                                  4、 图书查询界面

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第12张

                                  5、图书借阅界面

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第13张

                                  6、图书插入界面

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第14张

                                  7、图书更新/删除界面

                                  【C#项目】图书馆管理系统-WinForm+MySQL,在这里插入图片描述,第15张

                                  四、通用类设计

                                  1、与MySQL通讯模块(整个类模块中删除命名空间等,值保留这个类)

                                  public static class DBModule
                                  {
                                      public static string ServerIP = "**.**.**.**";
                                      public static string ServerPort = "****";
                                      public static string ServerUser = "****";
                                      public static string ServerPassword = "****";
                                      public static string ServerDBName = "bookmanage";
                                  }
                                  

                                  2、数据库操作模块:

                                  using System;
                                  using System.Collections.Generic;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using MySql.Data.MySqlClient;
                                  using System.Data;
                                  public class MySQLHelper
                                  {
                                      private MySqlConnection myConnection;
                                      private string mErrorString;
                                      //构造函数
                                      public MySQLHelper(string strServerIP, string strServerPort, string strServerUser, string strPassword, string strDBName)
                                      {
                                          string strConnectionString = String.Format("server={0}; port={1}; user id={2}; password={3}; database={4}", 
                                              strServerIP, strServerPort, strServerUser, strPassword, strDBName);
                                          myConnection = new MySqlConnection(strConnectionString);
                                      }
                                      //测试连接
                                      public bool ConnectionTest()
                                      {
                                          bool result = false;
                                          try
                                          {
                                              if (myConnection.State == System.Data.ConnectionState.Closed)
                                              {
                                                  myConnection.Close();
                                                  result = true;
                                              }
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = ex.ToString();
                                          }
                                          return result;
                                      }
                                      /// 
                                      /// 执行查询语句,用DataTable返回结果,调用前要传入DataTable的实例化对象作为参数
                                      /// 
                                      /// 查询命令
                                      /// 返回数据表
                                      /// 
                                      public bool ExcuteQuerySql(string strQuery, ref DataTable dt)
                                      {
                                          if (dt == null)
                                          {
                                              mErrorString = "传入的DataTable为null";
                                              return false;
                                          }
                                          bool result = false;
                                          try
                                          {
                                              MySqlCommand myCommand = new MySqlCommand(strQuery);
                                              myCommand.Connection = myConnection;
                                              if (myConnection.State == ConnectionState.Closed)
                                              {
                                                  myConnection.Open();
                                              }
                                              dt.Load(myCommand.ExecuteReader());
                                              result = true;
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = String.Format("ExcuteQuery {0} failed.", ex.ToString());
                                              return false;
                                          }
                                          finally
                                          {
                                              myConnection.Close();
                                          }
                                          return result;
                                      }
                                      /// 
                                      /// 执行带参数的查询语句,使用前传入参数、DataTable实例化对象
                                      /// 
                                      /// 查询语句
                                      /// 
                                      /// 
                                      /// 
                                      public bool ExcuteQuerySql(string strQuery, MySqlParameter[] param, ref DataTable dt)
                                      {
                                          if (dt == null)
                                          {
                                              mErrorString = "传入的DataTable为null.";
                                              return false;
                                          }
                                          bool result = false;
                                          try
                                          {
                                              MySqlCommand myCommand = new MySqlCommand(strQuery);
                                              myCommand.Connection = myConnection;
                                              if (myConnection.State == ConnectionState.Closed)
                                              {
                                                  myConnection.Open();
                                              }
                                              for (int i = 0; i < param.Length; i++)
                                              {
                                                  myCommand.Parameters.Add(param[i]);
                                              }
                                              dt.Load(myCommand.ExecuteReader());
                                              result = true;
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = String.Format("ExcuteQuery {0} failed", strQuery) + ex.Message;
                                              return false;
                                          }
                                          finally
                                          {
                                              myConnection.Close();
                                          }
                                          return result;
                                      }
                                      //执行非查询语句
                                      public int ExcuteSql(string SqlCmdText)
                                      {
                                          int row = -1;
                                          try
                                          {
                                              MySqlCommand myCommand = new MySqlCommand(SqlCmdText);
                                              myCommand.CommandText = SqlCmdText;
                                              myCommand.Connection = myConnection;
                                              if (myConnection.State == ConnectionState.Closed)
                                              {
                                                  myConnection.Open();
                                              }
                                              row = myCommand.ExecuteNonQuery();
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = String.Format("ExcuteNonQuery {0} failed", SqlCmdText);
                                          }
                                          return row;
                                      }
                                      //执行非查询语句
                                      public int ExcuteSql(string[] SqlCmdText)
                                      {
                                          try
                                          {
                                              if (myConnection.State == ConnectionState.Closed)
                                              {
                                                  myConnection.Open();
                                              }
                                              for (int i = 0; i < SqlCmdText.Length; i++)
                                              {
                                                  MySqlCommand myCommand = new MySqlCommand(SqlCmdText[i]);
                                                  myCommand.CommandText = SqlCmdText[i];
                                                  myCommand.Connection = myConnection;
                                                  myCommand.ExecuteNonQuery();
                                              }
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = String.Format("ExcuteNonQuery {0} failed", SqlCmdText) + ex.Message;
                                              return -1;
                                          }
                                          return -1;
                                      }
                                      //执行带参数的非查询语句
                                      public int ExcuteSql(string SqlCmdText, MySqlParameter[] param)
                                      {
                                          int row = -1;
                                          try
                                          {
                                              MySqlCommand myCommand = new MySqlCommand(SqlCmdText);
                                              myCommand.CommandText = SqlCmdText;
                                              myCommand.Connection = myConnection;
                                              if (myConnection.State == ConnectionState.Closed)
                                              {
                                                  myConnection.Open();
                                              }
                                              for (int i = 0; i < param.Length; i++)
                                              {
                                                  myCommand.Parameters.Add(param[i]);
                                              }
                                              row = myCommand.ExecuteNonQuery();
                                          }
                                          catch (MySqlException ex)
                                          {
                                              mErrorString = String.Format("ExecuteNonQuery {0} failed", SqlCmdText) + ex.Message;
                                              return row = -1;
                                          }
                                          return row;
                                      }
                                      public string GetErrInfo()
                                      {
                                          return mErrorString;
                                      }
                                  }
                                  

                                  五、系统代码实现与分析

                                  1、主界面代码:

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmMain : Form
                                      {
                                          public static DialogResult result;//声明对话框返回对象
                                          public FrmMain()
                                          {
                                              InitializeComponent();
                                          }
                                          /// 
                                          /// 查询子窗体是否存在
                                          /// 
                                          /// 
                                          /// 
                                          public bool CheckChildFrm(string childfrmname)
                                          {
                                              foreach (Form childFrm in this.MdiChildren)//遍历子窗体
                                              {
                                                  if (childFrm.Name == childfrmname)//如果子窗体存在
                                                  {
                                                      if (childFrm.WindowState == FormWindowState.Minimized)
                                                      {
                                                          childFrm.WindowState = FormWindowState.Normal;
                                                      }
                                                      childFrm.Activate();//激活该窗体
                                                      return true;        //存在返回true
                                                  }
                                              }
                                              return false;//不存在返回false
                                          }
                                          /// 
                                          /// 用户登录菜单的事件处理
                                          /// 
                                          /// 
                                          /// 
                                          private void 用户登录ToolStripMenuItem_Click(object sender, EventArgs e)
                                          {
                                              //检测登录窗口是否打开
                                              if (this.CheckChildFrm("FrmLogin") == true)
                                              {
                                                  return;//窗口已经打开,返回
                                              }
                                              FrmLogin user = new FrmLogin();//实例化登录窗体
                                              user.ShowDialog();//登录窗体以模式对话框的方式打开
                                              
                                              //判断是否登录成功,登录成功则启用相应的菜单和按钮
                                              if (result == System.Windows.Forms.DialogResult.OK && FrmLogin.ustate == 0)//普通用户
                                              {
                                                  toolSBSearch.Enabled = true;
                                                  toolSBBookBorown.Enabled = true;
                                              }
                                              else if (result == System.Windows.Forms.DialogResult.OK && FrmLogin.ustate == 1)//管理员
                                              {
                                                  toolSBSearch.Enabled = true;
                                                  toolSBBookBorown.Enabled = true;
                                                  toolMBookManage.Enabled = true;
                                              }
                                          }
                                          /// 
                                          /// 用户登录按钮事件
                                          /// 
                                          /// 
                                          /// 
                                          private void toolSBLogin_Click(object sender, EventArgs e)
                                          {
                                              //检测登录窗口是否打开
                                              if (this.CheckChildFrm("FrmLogin") == true)
                                              {
                                                  return;//窗口已经打开,返回
                                              }
                                              FrmLogin user = new FrmLogin();//实例化登录窗体
                                              user.ShowDialog();//登录窗体以模式对话框的方式打开
                                              //判断是否登录成功,登录成功则启用相应的菜单和按钮
                                              if (result == System.Windows.Forms.DialogResult.OK && FrmLogin.ustate == 0)//普通用户
                                              {
                                                  //普通用户登录后使能按钮
                                                  toolSBSearch.Enabled = true;
                                                  toolSBBookBorown.Enabled = true;
                                              }
                                              else if (result == System.Windows.Forms.DialogResult.OK && FrmLogin.ustate == 1)//管理员
                                              {
                                                  //管理员登录后使能按钮
                                                  toolSBSearch.Enabled = true;
                                                  toolSBBookBorown.Enabled = true;
                                                  toolMBookManage.Enabled = true;
                                              }
                                          }
                                          /// 
                                          /// 图书查询按钮,转到图书查询界面
                                          /// 
                                          /// 
                                          /// 
                                          private void toolSBSearch_Click(object sender, EventArgs e)
                                          {
                                              if (this.CheckChildFrm("FrmBookSearch") == true)
                                              {
                                                  return;
                                              }
                                              FrmBookSearch frmBookSearch = new FrmBookSearch();
                                              frmBookSearch.MdiParent = this;//设置当前窗体的子窗体为frmBookSearch
                                              frmBookSearch.Show();
                                          }
                                          /// 
                                          /// 图书借阅按钮,转到图书借阅界面
                                          /// 
                                          /// 
                                          /// 
                                          private void toolSBBookBorown_Click(object sender, EventArgs e)
                                          {
                                              if (this.CheckChildFrm("FrmBookBrown") == true)
                                              {
                                                  return;
                                              }
                                              FrmBookBrown frmBookBrown = new FrmBookBrown();
                                              frmBookBrown.MdiParent = this;//设置当前窗体的子窗体为frmBookBrown
                                              frmBookBrown.Show();
                                          }
                                          /// 
                                          /// 图书插入按钮,转到图书插入界面
                                          /// 
                                          /// 
                                          /// 
                                          private void toolMBookIn_Click(object sender, EventArgs e)
                                          {
                                              if (this.CheckChildFrm("FrmBookIn") == true)
                                              {
                                                  return;
                                              }
                                              FrmBookIn frmBookIn = new FrmBookIn();
                                              frmBookIn.MdiParent = this;//设置当前窗体的子窗体为frmBookIn
                                              frmBookIn.Show();
                                          }
                                          /// 
                                          /// 图书更新/删除按钮,转到图书更新与删除界面
                                          /// 
                                          /// 
                                          /// 
                                          private void toolMBookUpdate_Click(object sender, EventArgs e)
                                          {
                                              if (this.CheckChildFrm("FrmUpdateBook") == true)
                                              {
                                                  return;
                                              }
                                              FrmUpdateBook frmUpdateBook = new FrmUpdateBook();
                                              frmUpdateBook.MdiParent = this;//设置当前窗体的子窗体为frmUpdateBook
                                              frmUpdateBook.Show();
                                          }
                                          private void toolSBExit_Click(object sender, EventArgs e)
                                          {
                                              if (MessageBox.Show("确认退出程序?", "确认信息", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
                                              {
                                                  System.Environment.Exit(0);
                                              }
                                          }
                                      }
                                  }
                                  

                                  2、登录界面代码

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmLogin : Form
                                      {
                                          public FrmLogin()
                                          {
                                              InitializeComponent();
                                          }
                                          public static int ustate;//普通用户or管理员标志
                                          FrmMain femmain = new FrmMain();
                                          private void btnLogin_Click(object sender, EventArgs e)
                                          {
                                              //输入合法性判断
                                              if (Volidity())//合法性通过
                                              {
                                                  string state = this.cmbUserType.Text;
                                                  int num;
                                                  if (state.Equals("管理员"))//判断用户角色
                                                  {
                                                      num = 1;
                                                  }
                                                  else
                                                  {
                                                      num = 2;
                                                  }
                                                  //定义查询语句
                                                  string sql = String.Format("select * from user where uname='{0}' and upwd='{1}' and ustate='{2}'", 
                                                  this.txtUserName.Text.Trim(), this.txtPwd.Text.Trim(), num);
                                                  MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                               
                                                  DataTable dt = new DataTable();
                                                  
                                                  bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dt);
                                                  if (queryFlag == false)
                                                  {
                                                      MessageBox.Show("登录失败,没有找到该用户");
                                                  }
                                                  if (dt.Rows.Count > 0)
                                                  {
                                                      MessageBox.Show("登录成功!");
                                                      FrmMain.result = DialogResult.OK;//为变量result赋值
                                                      DBModule.UserName = txtUserName.Text;
                                                      this.Close();
                                                  }
                                                  else
                                                  {
                                                      MessageBox.Show("用户名或密码错误,请重新输入");
                                                  }
                                              }
                                          }
                                          /// 
                                          /// 验证合法性
                                          /// 
                                          private bool Volidity()
                                          {
                                              if (this.txtPwd.Text != string.Empty && this.txtPwd.Text != string.Empty)
                                              {
                                                  return true;
                                              }
                                              else
                                              {
                                                  MessageBox.Show("用户名或密码不能为空");
                                              }
                                              return false;
                                          }
                                          private void btnCancel_Click(object sender, EventArgs e)
                                          {
                                              this.Close();
                                          }
                                          private void cmbUserType_SelectedIndexChanged(object sender, EventArgs e)
                                          {
                                              if (cmbUserType.SelectedIndex == 0)
                                              {
                                                  cmbUserType.Text = "普通用户";
                                                  ustate = 0;
                                              }
                                              else
                                              {
                                                  cmbUserType.Text = "管理员";
                                                  ustate = 1;
                                              }
                                          }
                                          private void FromLogin_Load(object sender, EventArgs e)
                                          {
                                              this.cmbUserType.SelectedIndex = 0;
                                              ustate = 0;
                                          }
                                      }
                                  }
                                  

                                  3、图书查询模块代码

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmBookSearch : Form
                                      {
                                          public FrmBookSearch()
                                          {
                                              InitializeComponent();
                                          }
                                          private void btnSearch_Click(object sender, EventArgs e)
                                          {
                                              string strBookType = cmbBokTyp.Text;
                                              string strBookTypeOr = cmbBokTypOr.Text;
                                              string strBookName = txtBokNm.Text;
                                              string strBookNameOr = txtBokNmOr.Text;
                                              string strBookConment = txtBokCotnt.Text;
                                              //定义sql语句
                                              string sql = "SELECT * FROM book WHERE booktype='" + strBookType + "'" + "or booktype='" + strBookTypeOr + "'" + 
                                                  " and bookname='" + strBookName + "';";
                                              MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                              DataTable dt = new DataTable();
                                              bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dt);
                                              if (queryFlag == false)
                                              {
                                                  MessageBox.Show("查询失败,没有该图书信息");
                                              }
                                              if (dt.Rows.Count > 0)
                                              {
                                                  dgvBookSearch.DataSource = dt;
                                              }
                                              //设置列名标题
                                              string[] strColumnName = { "图书ID", "图书类别", "书名", "作者", "价格", "封面", "内容简介", "指定访问码"};
                                              for (int i = 0; i < dgvBookSearch.Columns.Count; i++)
                                  			{
                                                  dgvBookSearch.Columns[i].HeaderText = strColumnName[i];
                                  			}
                                          }
                                          private void btnClose_Click(object sender, EventArgs e)
                                          {
                                              this.Close();
                                          }
                                          private void FrmBookSearch_Load(object sender, EventArgs e)
                                          {
                                              //图书类别的初始化
                                              string sql = "SELECT DISTINCT booktype FROM book;";
                                              MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                              DataTable dtTtpe = new DataTable();
                                              try
                                              {
                                                  bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dtTtpe);
                                                  if (queryFlag == false)
                                                  {
                                                      MessageBox.Show("查询图书类型失败");
                                                  }
                                              }
                                              catch (Exception ex)
                                              {
                                                  MessageBox.Show("查询图书类型失败" + ex.ToString());
                                              }
                                              if (dtTtpe.Rows.Count > 0)
                                              {
                                                  for (int i = 0; i < dtTtpe.Rows.Count; i++)
                                                  {
                                                      this.cmbBokTyp.Items.Add(dtTtpe.Rows[i][0].ToString());
                                                      this.cmbBokTypOr.Items.Add(dtTtpe.Rows[i][0].ToString());
                                                  }
                                              }
                                              this.cmbBokTyp.SelectedIndex = 0;
                                              this.cmbBokTypOr.SelectedIndex = 0;
                                          }
                                      }
                                  }
                                  

                                  4、图书借阅代码

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmBookBrown : Form
                                      {
                                          public FrmBookBrown()
                                          {
                                              InitializeComponent();
                                          }
                                          DataTable dt = new DataTable();
                                          MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                          /// 
                                          /// 图书借阅按钮
                                          /// 
                                          /// 
                                          /// 
                                          private void btnBrown_Click(object sender, EventArgs e)
                                          {
                                              //获取下拉框中选中图书的ID
                                              int bookid = Convert.ToInt32(this.cmbBookName.SelectedIndex + 1);
                                              //获取用户的借阅证
                                              int issid = Convert.ToInt32(this.txtIssueID.Text);
                                              //获取借阅时间
                                              DateTime date = Convert.ToDateTime(this.IssDatetime.Text);
                                              //更新借阅信息
                                              string sql = "insert into bookbrown (bookid, issbookid, issdatetime) values ('" + bookid + "', '" + issid + "', '" + date + "');";
                                              //借阅信息中没有这本书才可借阅
                                              DataTable dtC = new DataTable();
                                              sqlHelper.ExcuteQuerySql("select bookid from bookbrown;", ref dtC);
                                              int flagTmp = 0;
                                              for (int i = 0; i < dtC.Rows.Count; i++)
                                              {
                                                  if (Convert.ToInt32(dtC.Rows[i][0].ToString()) == bookid)
                                                  {
                                                      flagTmp = 1;
                                                  }
                                              }
                                              if (flagTmp == 1)
                                              {
                                                  MessageBox.Show("这本书你已经借阅过,借阅不能超过1本", "提示信息", MessageBoxButtons.OK);
                                              }
                                              else
                                              {
                                                  if (sqlHelper.ExcuteSql(sql) == 1)
                                                  {
                                                      MessageBox.Show("借阅成功");
                                                  }
                                                  else
                                                  {
                                                      MessageBox.Show("借阅失败");
                                                  }
                                              }
                                              
                                              //刷新借阅证中的图书信息
                                              string sqlQ = "select bookinfo.bookid,bookname,issbookid,issdatetime from bookbrown,bookinfo where bookinfo.bookid=bookbrown.bookid;";
                                             
                                              bool queryFlagQ = sqlHelper.ExcuteQuerySql(sqlQ, ref dt);
                                              if (queryFlagQ == true)
                                              {
                                                  this.dataGridView1.DataSource = dt;
                                              }
                                          }
                                          private void btnCancel_Click(object sender, EventArgs e)
                                          {
                                              this.Close();
                                          }
                                          private void FrmBookBrown_Load(object sender, EventArgs e)
                                          {
                                              string sql = "select * from bookinfo;";
                                              //联合图书信息表、借阅信息表查询
                                              string sqlBro = "select bookinfo.bookid,bookname,issbookid,issdatetime from bookbrown,bookinfo where bookinfo.bookid=bookbrown.bookid;"; //主键与外键的关联
                                              try
                                              {
                                                  bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dt);//查询图书信息
                                                  if (queryFlag == false)
                                                  {
                                                      MessageBox.Show("查询失败");
                                                  }
                                                  else
                                                  {
                                                      //自动加载图书名到下拉框
                                                      for (int i = 0; i < dt.Rows.Count; i++)
                                                      {
                                                          this.cmbBookName.Items.Add(dt.Rows[i][1].ToString());
                                                      }
                                                  }
                                                 //联合查询查询本人借阅信息
                                                  DataTable dtBro=new DataTable();
                                                  bool queryFlagBro = sqlHelper.ExcuteQuerySql(sqlBro, ref dtBro);
                                                  if (queryFlagBro == true)
                                                  {
                                                      this.dataGridView1.DataSource = dtBro;
                                                  }
                                                  else
                                                  {
                                                      MessageBox.Show("查询借阅信息失败");
                                                  }
                                                  //查询用户信息 
                                                  string sqlU = "select * from user where uname= '" + DBModule.UserName + "';";
                                                  DataTable dtU = new DataTable();
                                                  bool queryFlagU = sqlHelper.ExcuteQuerySql(sqlU, ref dtU);
                                                  if (queryFlagU == true)
                                                  {
                                                      this.txtUserName.Text = dtU.Rows[0]["uname"].ToString();
                                                      this.txtIssueID.Text = dtU.Rows[0]["upwd"].ToString();
                                                  }
                                                  
                                                  //初始化列名
                                                  string[] strColumnName = { "图书编号", "图书名称", "指定访问码", "借阅时间"};
                                                  for (int i = 0; i < dt.Columns.Count; i++)
                                                  {
                                                      dataGridView1.Columns[i].HeaderText=strColumnName[i];
                                                  }
                                              }
                                              catch (Exception ex)
                                              {
                                                  MessageBox.Show(""); 
                                              }
                                              
                                          }
                                          /// 
                                          /// 图书名称选项变化的事件
                                          /// 
                                          /// 
                                          /// 
                                          private void cmbBookName_SelectedIndexChanged(object sender, EventArgs e)
                                          {
                                              //遍历图书信息表,查询和选中书名相同的信息,加载到界面
                                              foreach (DataRow row in dt.Rows)
                                              {
                                                  if (cmbBookName.Text == row["bookname"].ToString())
                                                  {
                                                      this.txtBookIssue.Text = row["bookissue"].ToString();
                                                      this.txtBookAuthor.Text = row["bookauthor"].ToString();
                                                  }
                                              }
                                          }
                                      }
                                  }
                                  

                                  5、图书插入代码

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmBookIn : Form
                                      {
                                          public FrmBookIn()
                                          {
                                              InitializeComponent();
                                          }
                                          /// 
                                          /// 图书插入按钮
                                          /// 
                                          /// 
                                          /// 
                                          private void btnInsert_Click(object sender, EventArgs e)
                                          {
                                              //定义变量接收控件的值
                                              string strBookType = txtBookType.Text;
                                              string strBookName = txtBookName.Text;
                                              string strAuthor = txtAuthor.Text;
                                              string strPrice = txtPrice.Text;
                                              string strPic = txtPic.Text;
                                              string strContent = txtContent.Text;
                                              string strIssue = txtIssue.Text;
                                              if (Vaildate())
                                              {
                                                  //sql语句
                                                  string sql = "insert into bookinfo (bookname, booktype, bookauthor, bookprice, bookpic, bookcontent, bookissue)"
                                                      + " values ('" + strBookName + "', '" + strBookType + "', '" + strAuthor + "', '" + strPrice + "', '" + 
                                                      strPic + "', '" + strContent + "', '" + strIssue + "');";
                                                  MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                                  
                                                  int result=sqlHelper.ExcuteSql(sql);
                                                  if (result == 1)
                                                  {
                                                      MessageBox.Show("添加成功", "提示", MessageBoxButtons.OK);
                                                  }
                                                  else
                                                  {
                                                      MessageBox.Show("添加失败", "提示", MessageBoxButtons.OK);
                                                  }
                                                  DataTable dt = new DataTable();
                                                  sqlHelper.ExcuteQuerySql("select * from bookinfo;", ref dt);
                                                  this.dataGridView1.DataSource = dt;
                                                  //设置列名标题
                                                  string[] strColumnName = { "图书ID", "图书类别", "书名", "作者", "价格", "封面", "内容简介", "指定访问码" };
                                                  for (int i = 0; i < dataGridView1.Columns.Count; i++)
                                                  {
                                                      dataGridView1.Columns[i].HeaderText = strColumnName[i];
                                                  }
                                              }
                                          }
                                          /// 
                                          /// 退出图书界面按钮
                                          /// 
                                          /// 
                                          /// 
                                          private void btnExit_Click(object sender, EventArgs e)
                                          {
                                              this.Close();
                                          }
                                          /// 
                                          /// 输入合法性验证
                                          /// 
                                          /// 
                                          private bool Vaildate()
                                          {
                                              if (txtBookType.Text != string.Empty && txtBookName.Text != string.Empty && txtAuthor.Text != string.Empty
                                                  && txtPrice.Text != string.Empty && txtPic.Text != string.Empty && txtContent.Text != string.Empty && txtIssue.Text != string.Empty)
                                              {
                                                  return true;
                                              }
                                              else
                                              {
                                                  MessageBox.Show("请出入完整信息");
                                              }
                                              return false;
                                          }
                                      }
                                  }
                                  

                                  6、图书更新/删除代码

                                  using System;
                                  using System.Collections.Generic;
                                  using System.ComponentModel;
                                  using System.Data;
                                  using System.Drawing;
                                  using System.Linq;
                                  using System.Text;
                                  using System.Threading.Tasks;
                                  using System.Windows.Forms;
                                  namespace BookManage
                                  {
                                      public partial class FrmUpdateBook : Form
                                      {
                                          public FrmUpdateBook()
                                          {
                                              InitializeComponent();
                                          }
                                          MySQLHelper sqlHelper = new MySQLHelper(DBModule.ServerIP, DBModule.ServerPort, DBModule.ServerUser, DBModule.ServerPassword, DBModule.ServerDBName);
                                          /// 
                                          /// 保存信息
                                          /// 
                                          /// 
                                          /// 
                                          private void btnSave_Click(object sender, EventArgs e)
                                          {
                                              string sql = "select * from bookinfo;";
                                              DataTable dt = new DataTable();
                                              if (MessageBox.Show("确实要将修改保存到数据库?", "提示信息", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
                                              {
                                                  bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dt);
                                                  if (queryFlag == true)
                                                  {
                                                      MessageBox.Show("保存成功");
                                                  }
                                                  this.dataGridView1.DataSource = dt;
                                              }
                                              //设置列名标题
                                              string[] strColumnName = { "图书ID", "图书类别", "书名", "作者", "价格", "封面", "内容简介", "指定访问码" };
                                              for (int i = 0; i < dataGridView1.Columns.Count; i++)
                                              {
                                                  dataGridView1.Columns[i].HeaderText = strColumnName[i];
                                              }
                                          }
                                          /// 
                                          /// 更新信息
                                          /// 
                                          /// 
                                          /// 
                                          private void btnUpdate_Click(object sender, EventArgs e)
                                          {
                                              string strBookType = txtBookType.Text;
                                              string strBookName = txtBookName.Text;
                                              string strBookAuthor = txtBookAuthor.Text;
                                              string strBookPrice = txtBookPrice.Text;
                                              string strBookPic = txtBookPic.Text;
                                              string strBookContent = txtBookContent.Text;
                                              string strBookIssue = txtBookIssue.Text;
                                              if (true)
                                              {
                                                  string sql = string.Format("update bookinfo set booktype='{0}', bookname='{1}', bookauthor='{2}', bookprice='{3}', bookpic='{4}', bookcontent='{5}',bookissue='{6}' where bookid={7};",
                                                   strBookType, strBookName, strBookAuthor, strBookPrice, strBookPic, strBookContent, strBookIssue, Convert.ToInt32(txtBookID.Text));
                                                  if (sqlHelper.ExcuteSql(sql) == 1)
                                                  {
                                                      MessageBox.Show("更新成功", "提示信息", MessageBoxButtons.OK);
                                                  }
                                                  else
                                                  {
                                                      MessageBox.Show("更新失败", "提示信息", MessageBoxButtons.OK);
                                                  }
                                              }
                                          }
                                          /// 
                                          /// 删除信息
                                          /// 
                                          /// 
                                          /// 
                                          private void btnDelete_Click(object sender, EventArgs e)
                                          {
                                              if (this.txtBookID.Text != string.Empty)
                                              {
                                                  string sql = "select * from bookbrown where bookid=" + Convert.ToInt32(txtBookID.Text) + "";
                                                  DataTable dt = new DataTable();
                                                  bool queryFlag = sqlHelper.ExcuteQuerySql(sql, ref dt);
                                                  if (queryFlag == true && dt.Rows.Count > 0)
                                                  {
                                                      MessageBox.Show("此书有借阅,不能删除");
                                                  }
                                                  else
                                                  {
                                                      string sqlDe = "delete from bookinfo where bookid=" + this.txtBookID.Text + "";
                                                      if (sqlHelper.ExcuteSql(sqlDe) == 1)
                                                      {
                                                          MessageBox.Show("删除成功", "提示信息", MessageBoxButtons.OK);
                                                      }
                                                      else
                                                      {
                                                          MessageBox.Show("删除失败", "提示信息", MessageBoxButtons.OK);
                                                      }
                                                  }
                                              }
                                          }
                                          /// 
                                          /// 取消
                                          /// 
                                          /// 
                                          /// 
                                          private void btnCancel_Click(object sender, EventArgs e)
                                          {
                                          }
                                          /// 
                                          /// 验证输入合法性
                                          /// 
                                          /// 
                                          private bool Vaildate()
                                          {
                                              if (txtBookType.Text != string.Empty && txtBookName.Text != string.Empty && txtBookAuthor.Text != string.Empty
                                                  && txtBookPrice.Text != string.Empty && txtBookPic.Text != string.Empty
                                                  && txtBookContent.Text != string.Empty && txtBookIssue.Text != string.Empty && txtBookID.Text != string.Empty)
                                              {
                                                  return true;
                                              }
                                              else
                                              {
                                                  MessageBox.Show("请出入完整信息");
                                              }
                                              return false;
                                          }
                                      }
                                  }
                                      
                                  

                                  总结

                                  本项目实现一个简单的图书馆管理系统,是一个完整的图书馆管理系统的简化版。通过这个项目,了解整个桌面应用软件的规范化开发流程,业务梳理、需求分析、概要设计、详细设计、数据库设计、界面交互设计、详细代码设计等步骤。同时将代码开发规范融入其中,尽量使用较为规范的代码。加深了对WinForm界面控件的认识,对MySQL更加熟悉。数据库SQL语言操作还需要多联系,纸上得来终觉浅,觉知此事要躬行。SQL语句看似简单,实际操作起来是出问题最多的地方。