c# 数据库系统设计综合实验-图书管理系统

本是古典何须时尚 2023-10-03 13:54 35阅读 0赞

实验3  
数据库系统设计综合实验  
预备知识  
本实验的任务是设计并实现一个数据库系统。数据库设计的一般步骤包括：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。  
（1）概念结构设计  
了解概念结构设计的基本方法，根据需求分析的结果或实验题目给出的要求，能够准确地用实体联系图来描述实体和实体之间的联系。  
（2）逻辑结构设计  
理解逻辑结构设计的基本方法，根据实体联系图的设计，转换成合理的关系模式，每个关系模式至少应该满足第三范式的要求。  
（3）物理结构设计  
理解物理结构设计的基本方法，选择合理的索引结构和存储结构，优化数据库的存取。  
（4）数据库实施  
选择一门熟悉的面向对象程序设计语言，完成应用程序的开发。  
实验环境  
win10 、visual studio 2019、SQL Server  
实验目的  
通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。  
实验要求  
熟悉实验室实验环境，掌握实验预备知识，了解实验中故障排除的基本方法。实验中根据实验内容和步骤完成相应的任务，并独立完成实验报告。  
实验素材可选择数据库原理实验七素材，也可以自行选题，选题要求有一定的难度，设计的数据库系统至少要有5张表。  
实验内容和步骤  
假设有“读者”、“管理员”、“图书”“图书商店”三个实体，读者的基本信息包括：读者id、姓名、性别，管理员的基本信息包括：管理员id、姓名，图书的基本信息包括：书号、书名、作者、出版社、数量；图书商店的基本信息包括：书号、书名、作者、出版社、价格。系统必须满足以下要求：  
（1）同类图书读者只能借阅一本，读者可以借阅多种不同类型的图书；  
（2）管理员可以购买商店中任意的图书；  
（3）读者只能进行借阅和归还图书功能；  
（4）管理员具有修改图书、添加图书、删除图书、按书号寻找图书、按书名寻找图书、购买图书等功能；  
（5）能够统计剩余图书的数量，借出即减少，归还或者购买则增加。  
根据上述描述，解答下列问题：  
（1）设计并画出 E-R图，要求标注连通词（即联系类型）；  
各实体属性：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 3]

实体联系图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 4]

完整的E-R图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 5]

（2）将E-R图转化为关系模型，并指出各关系的主码和外码；  
t\_user(uid,uname,usex,psw)其中uid为主码；  
t\_admin(adid,adname,psw)其中adid为主码；  
t\_book(bid,bname,author,press,number)其中bid为主码；  
t\_buy(bid,bname,author,press,price)其中bid为主码；  
t\_lend(uid,bid,datetime)其中(uid,bid)为主码，参照t\_user(uid)为外码，参照t\_admin(adid)为外码。  
（3）在MySQL、SQL Developer、Oracle中选择一个数据库管理系统，并完成数据库的逻辑设计；

实验过程原始记录：  
首先在VS2019自带的SQL Server中创建t\_user、t\_admin、t\_book、t\_buy、t\_lend表并插入数据：

CREATE TABLE [dbo].[t_user] (
        [uid]   VARCHAR (20)  NOT NULL,
        [uname] NVARCHAR (50) NOT NULL,
        [usex]  NVARCHAR (2)  NOT NULL,
        [psw]   INT           NOT NULL,
        PRIMARY KEY CLUSTERED ([uid] ASC)
    );
    
    CREATE TABLE [dbo].[t_admin] (
        [adid]   VARCHAR (50)  NOT NULL,
        [adname] NVARCHAR (50) NOT NULL,
        [psw]    INT           NOT NULL,
        PRIMARY KEY CLUSTERED ([adid] ASC)
    );
    
    CREATE TABLE [dbo].[t_book] (
        [bid]    VARCHAR (50)  NOT NULL,
        [bname]  NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [author] NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [press]  NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [number] INT           NULL,
        PRIMARY KEY CLUSTERED ([bid] ASC)
    );
    
    CREATE TABLE [dbo].[t_buy] (
        [bid]    VARCHAR (50)  NOT NULL,
        [bname]  NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [author] NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [press]  NVARCHAR (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [price]  SMALLINT      NOT NULL
    );
    
    CREATE TABLE [dbo].[t_lend] (
        [uid]      VARCHAR (20) NOT NULL,
        [bid]      VARCHAR (50) NOT NULL,
        [datetime] DATETIME     NULL,
        PRIMARY KEY CLUSTERED ([uid] ASC, [bid] ASC),
        FOREIGN KEY ([uid]) REFERENCES [dbo].[t_user] ([uid]),
        FOREIGN KEY ([bid]) REFERENCES [dbo].[t_book] ([bid])
    );

利用c\#语言开发：

Program.cs //应用程序主入口点
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace BookMS
    {
        
        static class Program
        {
        
            /// <summary>
            /// 应用程序的主入口点。
            /// </summary>
            [STAThread]
            static void Main()
            {
        
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new login());
            }
        }
    }
    
    Dao.cs //连接数据库
    using System.Data.SqlClient;
    
    namespace BookMS
    {
        
        class Dao
        {
        
            SqlConnection sc;
            public SqlConnection connect()
            {
        
                string str = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=BookDB;Integrated Security=True";//数据库连接字符串
                sc = new SqlConnection(str);//创建数据库连接对象
                sc.Open();//打开数据库
                return sc;//返回数据库连接对象
            }
            public SqlCommand command(string sql)
            {
        
                SqlCommand cmd = new SqlCommand(sql, connect());
                return cmd;
            }
            public int Execute(string sql)//更新操作
            {
        
                return command(sql).ExecuteNonQuery();
            }
            public SqlDataReader read(string sql)//读取操作
            {
        
                return command(sql).ExecuteReader();
            }
            public void DaoClose()
            {
        
                sc.Close();//关闭数据库连接
            }
        }
    }
    
    Data.cs
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace BookMS
    {
        
        class Data
        {
        
            public static string UID = "", UName = "";//登录用户的ID和姓名
        }
    }
    
    
    login.cs //登录程序
    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 BookMS
    {
        
        public partial class login : Form
        {
        
            public login()
            {
        
                InitializeComponent();
            }
    
            private void label1_Click(object sender, EventArgs e)
            {
        
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
        
                if(textBox1.Text!=""&&textBox2.Text!="")
                {
        
                    Login();
                }
                else
                {
        
                    MessageBox.Show("输入为空，请重新输入");
                }
            }
            //登录是否被允许，允许登录返回真
            public void Login()
            {
        
                //用户
                if (radioButtonUser.Checked == true)
                {
        
                    Dao dao = new Dao();
                    string sql= string.Format("select * from t_user where uid='{0}'and psw='{1}'", textBox1.Text, textBox2.Text);
                    IDataReader dc = dao.read(sql);
                    if (dc.Read())
                    {
        
                        Data.UID = dc["uid"].ToString();
                        Data.UName = dc["uname"].ToString();
                        MessageBox.Show("登录成功");
                        user1 user = new user1();
                        this.Hide();
                        user.ShowDialog();
                        this.Show();
                    }
                    else
                    {
        
                        MessageBox.Show("账号或密码错误，登录失败!");
                    }
                    dao.DaoClose();
                }
                
                //管理员
                if(radioButtonAdmin.Checked==true)
                {
        
                    Dao dao = new Dao();
                    string sql = string.Format("select * from t_admin where adid='{0}'and psw='{1}'", textBox1.Text, textBox2.Text);
                    IDataReader dc = dao.read(sql);
                    if (dc.Read())
                    {
        
                        MessageBox.Show("登录成功");
                        admin1 a= new admin1();
                        this.Hide();
                        a.ShowDialog();
                        this.Show();
                    }
                    else
                    {
        
                        MessageBox.Show("账号或密码错误，登录失败!");
                    }
                    dao.DaoClose();
                }
            }
    
            private void login_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    admin1.cs //管理员登录页面
    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 BookMS
    {
        
        public partial class admin1 : Form
        {
        
            public admin1()
            {
        
                InitializeComponent();
            }
    
            private void 图书管理ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                admin2 admin=new admin2();
                admin.ShowDialog();
            }
    
            private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                this.Close();
            }
    
            private void admin1_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    admin2.cs //管理员管理页面
    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 BookMS
    {
        
        public partial class admin2 : Form
        {
        
            public admin2()
            {
        
                InitializeComponent();
                
            }
    
            private void admin2_Load(object sender, EventArgs e)
            {
        
                Table();
                label2.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString()+dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号
    
            }
            //从数据库读取数据显示在表格控件中
            public void Table()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select * from t_book";
                IDataReader dc = dao.read(sql);
                while (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString());
                }
                dc.Close();
                dao.DaoClose();
            }
    
            //根据书号显示数据
            public void TableID()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select * from t_book where bid='{
          textBox1.Text}'";
                IDataReader dc = dao.read(sql);
                if (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString());
                }
                else
                {
        
                    MessageBox.Show("没有该图书信息！");
                }
                dc.Close();
                dao.DaoClose();
            }
            //根据书名显示数据
            public void TableName()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select * from t_book where bname like'%{
          textBox2.Text}%'";
                IDataReader dc = dao.read(sql);
                if (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString());
                }
                else
                {
        
                    MessageBox.Show("没有该图书信息！");
                }
                dc.Close();
                dao.DaoClose();
            }
            private void button1_Click(object sender, EventArgs e)
            {
        
                admin21 a = new admin21();
                a.ShowDialog();
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
        
                try
                {
        
                    string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
                    label2.Text = id + dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                    DialogResult dr = MessageBox.Show("确认删除吗？", "信息提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
        
                        string sql = $"delete from t_book where bid='{
          id}'";
                        Dao dao = new Dao();
                        if (dao.Execute(sql) > 0)
                        {
        
                            MessageBox.Show("删除成功");
                            Table();
                        }
                        else
                        {
        
                            MessageBox.Show("删除失败" + sql);
                        }
                        dao.DaoClose();
                    }
                }
                catch
                {
        
                    MessageBox.Show("请先在表格选中要删除的图书记录！","信息提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
                
            }
    
            private void dataGridView1_Click(object sender, EventArgs e)
            {
        
                label2.Text= dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
        
                try
                {
        
                    string bid=dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                    string bname = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                    string author = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                    string press = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                    string number = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                    admin22 admin = new admin22(bid,bname,author,press,number);
                    admin.ShowDialog();
                    Table();//刷新数据
                }
                catch
                {
        
                    MessageBox.Show("ERROR");
                }
                
            }
    
            private void button4_Click(object sender, EventArgs e)
            {
        
                Table();
                textBox1.Text = "";
                textBox2.Text = "";
            }
    
            private void button5_Click(object sender, EventArgs e)
            {
        
                TableID();
            }
    
            private void button6_Click(object sender, EventArgs e)
            {
        
                TableName();
            }
    
            private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
            {
        
    
            }
    
            private void button7_Click(object sender, EventArgs e)
            {
        
                admin23 admin = new admin23();
                admin.ShowDialog();
            }
        }
    }
    
    admin21.cs //添加图书
    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 BookMS
    {
        
        public partial class admin21 : Form
        {
        
            public admin21()
            {
        
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
        
                if(textBox1.Text!=""&& textBox2.Text != ""&& textBox3.Text != ""&& textBox4.Text != ""&& textBox5.Text != "")
                {
        
                    Dao dao = new Dao();
                    string sql = $"insert into t_book(bid,bname,author,press,number)values('{
          textBox1.Text}','{
          textBox2.Text}','{
          textBox3.Text}','{
          textBox4.Text}',{
          textBox5.Text})";
                    int n = dao.Execute(sql);
                    if (n > 0)
                    {
        
                        MessageBox.Show("添加成功");
                        
                    }
                    else
                    {
        
                        MessageBox.Show("添加失败");
                    }
                    textBox1.Text = "";
                    textBox2.Text = "";
                    textBox3.Text = "";
                    textBox4.Text = "";
                    textBox5.Text = "";
                }
                else
                {
        
                    MessageBox.Show("输入不允许有空");
                }
                
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
        
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
            }
    
            private void admin21_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    admin22.cs //修改图书
    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 BookMS
    {
        
        public partial class admin22 : Form
        {
        
            string ID = "";
            public admin22()
            {
        
                InitializeComponent();
            }
            public admin22(string bid,string bname,string author,string press,string number)
            {
        
                InitializeComponent();
                ID=textBox1.Text = bid;
                textBox2.Text = bname;
                textBox3.Text = author;
                textBox4.Text = press;
                textBox5.Text = number;
            }
            private void button1_Click(object sender, EventArgs e)
            {
        
                string sql = $"update t_book set bid='{
          textBox1.Text}',bname='{
          textBox2.Text}',author='{
          textBox3.Text}',press='{
          textBox4.Text}',number={
          textBox5.Text} where bid='{
          ID}'";
                Dao dao = new Dao();
                if (dao.Execute(sql) > 0)
                {
        
                    MessageBox.Show("修改成功");
                    this.Close();
                }
            }
    
            private void admin22_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    
    admin23.cs //购买图书
    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 BookMS
    {
        
        public partial class admin23 : Form
        {
        
            public admin23()
            {
        
                InitializeComponent();
                Table();
            }
            public void Table()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select * from t_buy";
                IDataReader dc = dao.read(sql);
                while (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString());
                }
                dc.Close();
                dao.DaoClose();
            }
            private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
            {
        
                
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
        
                this.Close();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
        
                string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
                string name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                string author = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                string press = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                Dao dao = new Dao();
                string a = $"select bid from t_book where '{
          id}'=bid";
                IDataReader dc = dao.read(a);
                string aa = null;
                while (dc.Read())
                {
        
                    aa=dc[0].ToString();
                }
                if (id != aa)
                {
        
                    string sql = $"insert into t_book(bid,bname,author,press,number)values('{
          id}','{
          name}','{
          author}','{
          press}',1)";
                    int b=dao.Execute(sql);
                    if (b > 0)
                    {
        
                        MessageBox.Show("购买成功");
                    }
                    else
                    {
        
                        MessageBox.Show("购买失败！");
                    }
                }
                else
                {
        
                    string c= $"update t_book set number=number+1 where bid='{
          id}'";
                    int d=dao.Execute(c);
                    if (d > 0)
                    {
        
                        MessageBox.Show("购买成功");
                    }
                    else
                    {
        
                        MessageBox.Show("购买失败！");
                    }
                }
                
    
            }
    
            private void admin23_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    
    user1.cs //读者功能入口
    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 BookMS
    {
        
        public partial class user1 : Form
        {
        
            public user1()
            {
        
                InitializeComponent();            
            }
    
            private void 图书查看和借阅ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                user2 user = new user2();
                user.ShowDialog();
            }
    
            private void 已借图书和归还状态ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                user3 user = new user3();
                user.ShowDialog();
            }
    
            private void 帮助ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                MessageBox.Show("如遇到问题可访问网站www.baidu.com获取帮助");
            }
    
            private void 联系管理员ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                MessageBox.Show("管理员电话：15603053353");
            }
    
            private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
            {
        
                this.Close();
            }
    
            private void user1_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }
    
    user2.cs //查看图书和借阅
    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 BookMS
    {
        
        public partial class user2 : Form
        {
        
            public user2()
            {
        
                InitializeComponent();
                Table();
            }
    
            private void user2_Load(object sender, EventArgs e)
            {
        
                
            }
            //从数据库读取数据显示在表格控件中
            public void Table()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select * from t_book";
                IDataReader dc = dao.read(sql);
                while (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString());
                }
                dc.Close();
                dao.DaoClose();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
        
                string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
                int number = int.Parse(dataGridView1.SelectedRows[0].Cells[4].Value.ToString());//库存
                if (number < 1)
                {
        
                    MessageBox.Show("库存不足，如有需要可联系管理员");
                }
                else
                {
        
                    string sql = $"insert into t_lend(uid,bid,datetime)values('{
          Data.UID}','{
          id}',getdate());update t_book set number=number-1 where bid='{
          id}'";
                    Dao dao = new Dao();
                    if (dao.Execute(sql) > 1)
                    {
        
                        MessageBox.Show($"用户{
          Data.UID}借出了图书{
          id}");
                        Table();
                    }
                }
            }
    
            private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
            {
        
                
            }
        }
    }
    
    user3.cs //归还功能
    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 BookMS
    {
        
        public partial class user3 : Form
        {
        
            public user3()
            {
        
                InitializeComponent();
                Table();
            }
            public void Table()
            {
        
                dataGridView1.Rows.Clear();//清空旧数据
                Dao dao = new Dao();
                string sql = $"select bid,datetime from t_lend where uid='{
          Data.UID}'";
                IDataReader dc = dao.read(sql);
                while (dc.Read())
                {
        
                    dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString());
                }
                dc.Close();
                dao.DaoClose();
            }
            private void button1_Click(object sender, EventArgs e)
            {
        
                string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
                string sql = $"delete from t_lend where bid='{
          id}';update t_book set number=number+1 where bid='{
          id}'";
                Dao dao = new Dao();
                if (dao.Execute(sql) > 1)
                {
        
                    MessageBox.Show("归还成功");
                    Table();
                }
            }
    
            private void user23_Load(object sender, EventArgs e)
            {
        
    
            }
        }
    }

功能测试过程：  
读者登录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 6]

借书功能，在这里u001借阅了一本数据结构和一本数据库系统概论，可以看到库存对应减少了1：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 7]

还书功能：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 8]

所有图书均归还，库存相应增加

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 9]

管理员登录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 10]

图书管理页面，可以看到库存图书的信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 11]

添加图书：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 12]

可以看到图书成功添加了进来：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 13]

修改书号为b006的图书信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 15]

可以看到该图书信息被成功修改。

删除图书，这里选择删除书号为b007的书籍：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 16]

可以看到成功删除该图书

书号查询功能：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 17]

书名查询功能：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 18]

购买图书：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 19]

可以看到成功购买了C Primer Plus:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 20]

假如够买原有的图书，则该数的数量增加，现购买一本数据结构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 21]

可以看到数据结构数量变为21本。

实验总结  
本来想用SQL Developer进行数据库设计的，但是连接过程中c\#连接不上Oracle数据库，而在SQL Developer中却是可以连接成功的。后来看到VS2019中自带有SQL Server，而且经过简单配置即可使用，想着也可以趁着这个了解一下该平台，就用SQL Server进行数据库设计。在设计数据库过程中，我发现SQL Server的操作更简单，创建表格只需要在它给出的原始表格中填写id和数据段类型即可自动生成SQL语句，而且数据的插入也很简单。总体来说SQL Server的语句和SQL Developer的差不多，但是还是有一些不同之处，比如属性名要加中括号“\[\]”，而且，uid和number这两个词在SQL Developer中是关键字，而在SQL Server中不是关键字；在SQL Server中数据段类型为varchar插入中文会导致乱码，必须使用nvarchar类型。在c\#语言上，可以很容易就学会该语言，因为设计过程都是很明确的，创建一个窗体添加相应的控件后双击对应的控件在该函数下进行相应的编程即可，各代码块之间的关联性不是很强，而且每个控件的功能也都很明确，所以实现起来不会很困难。  
一开始也有想过用MFC进行开发，但是MFC消息机制太复杂，中间环节太多，而且又都很重要。c\# 体系虽然大覆盖面虽广，但编程模型简单，而且很多地方有java的影子 ，快捷简单而且不用指针已经垃圾回收机制，对于这个程序来说用c\#更合适，实现起来会更容易。

//源代码已经放在我的github账号里面，仅供参考，建议自己动手做一遍。  
[项目文件][Link 1]

2022.6.21更新：  
由于Github经常打不开，在这里我将项目放到gitee上面供大家参考，并且加上了数据库文件，只需要直接在vs中将该数据库文件配置上去即可，假如你是自己创建数据库，如果需要获取连接字符串的话可以看我这篇文章[vs中获取数据库连接字符串][vs]  
[项目文件][Link 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/89b62795a6d34b14a8209a70853cf77c.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/de671c5438de4c2e90fc2edcd1a0fdf8.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/5fe6aa10ae8b4e97aed891a0c3d8bba6.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/673a2fae03bc4befba5b29fe8e49c81d.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/4581c5fa76bd4400ad9fa59df5243c6d.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/743f0abf5f5342c598cad856f4e1c22f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2855eeea4dbc4f0e924d93f28ef48ef2.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/7ffbfcc8cbda4f52875af84a691025c3.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/855275356afd423c999129b2ab525ffb.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/015114f69fdf45169fe069663fdaa995.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/b5bb54bd7418404bb7c6b76a6c4ca34a.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/ba56f3b3bc3c45babf6b018483fa6020.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/3d4fe8c42d944d10a3104c22c2eafd1e.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/bf66b6e0ea3f437b831c528a188e5b6b.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/35dc198eacb8428394e0973e48cc6b75.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/d41af37cdb2541029392571de17ecf53.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/0720722408254b30a1fbf0312078802e.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/a045a75e73e54840b5836c54c841439d.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/e21234e235624c53953a9f747f5f4b2c.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/c7cfb9c8f2aa4971b80693d901b24cf0.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 20]: https://img-blog.csdnimg.cn/3f858ae392154dc2a7928f9ef60ba2b0.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg_size_16_color_FFFFFF_t_70 21]: https://img-blog.csdnimg.cn/23ea8de0fdd54b928a80b4b9f5158e59.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTc1MDU3Mg==,size_16,color_FFFFFF,t_70
[Link 1]: https://github.com/TBug001/TBug-
[vs]: https://blog.csdn.net/weixin_45750572/article/details/117717520
[Link 2]: https://gitee.com/t-bug/book-ms