自己用C#做了一个简单的图书管理系统,发博客记录一下好心情,课程设计作业。
一.数据准备
(1)创建数据库
利用SQL Sever2008创建一个MBOOK的数据库
(2)创建了四张基础表:TReader(读者信息表),TBook(图书信息表),TLend(借阅表),TUser(用户数据表)
(3)借书的存储过程
CREATE PROCEDURE Book_Borrow @in_ReaderID char(8), @in_ISBN char(18), @in_BookID char(10),
@out_str char(30) OUTPUT
AS
BEGIN
IF NOT EXISTS(SELECT * FROM TReader WHERE ReaderID=@in_ReaderID)
BEGIN
SET @out_str= ‘该读者不存在’
RETURN 0
END
IF NOT EXISTS(SELECT * FROM TBook WHERE ISBN=@in_ISBN)
BEGIN
SET @out_str= ‘该图书不存在’
RETURN 0
END
IF (SELECT Num FROM TReader WHERE ReaderID=@in_ReaderID)=5
BEGIN
SET @out_str= ‘读者借书量不能大于5’
RETURN 0
END
IF (SELECT SNum FROM TBook WHERE ISBN=@in_ISBN)=0
BEGIN
SET @out_str= ‘图书库存量为0’
RETURN 0
END
IF @in_ISBN IN (SELECT ISBN FROM TLend WHERE ReaderID=@in_ReaderID)
BEGIN
SET @out_str= ‘读者已经借过该书’
RETURN 0
END
IF EXISTS(SELECT * FROM TLend WHERE BookID=@in_BookID AND ReaderID=@in_ReaderID)
BEGIN
SET @out_str= ‘该图书ID已存在’
RETURN 0
END
BEGIN TRAN /开始一个事务/
INSERT INTO TLend VALUES(@in_BookID, @in_ReaderID, @in_ISBN, GETDATE())
IF @@ERROR>0 /如果前面一条SQL语句出错则回滚事务并返回/
BEGIN
ROLLBACK TRAN
SET @out_str= ‘执行过程中遇到错误’
RETURN 0
END
UPDATE TReader SET Num=Num+1 WHERE ReaderID=@in_ReaderID
IF @@ERROR>0 /如果前面一条SQL语句出错则回滚事务并返回/
BEGIN
ROLLBACK TRAN
SET @out_str= ‘执行过程中遇到错误’
RETURN 0
END
UPDATE TBook SET SNum=SNum1 WHERE ISBN=@in_ISBN
IF @@ERROR=0 /如果所有语句都不出错则结束事务并返回/
BEGIN
COMMIT TRAN
SET @out_str= ‘借书成功’
RETURN 1
END
ELSE /如果执行出错则回滚所有操作并返回/
BEGIN
ROLLBACK TRAN
SET @out_str= ‘执行过程中遇到错误’
RETURN 0
END
END
(4)还书的时候的触发器
USE MBOOK
GO
CREATE TRIGGER TLend_delete ON TLend
AFTER DELETE
AS
BEGIN
UPDATE TReader SET Num=Num-1 WHERE ReaderID=(SELECT ReaderID FROM deleted)
UPDATE TBook SET SNum=SNum+1 WHERE ISBN=(SELECT ISBN FROM deleted)
END
(5)还书时候的存储过程
USE [MBook]
GO
/****** Object: StoredProcedure [dbo].[Book_Return] Script Date: 06/03/2020 16:59:15 *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Book_Return](@in_ReaderID nchar(10),@in_BookID nchar(10),@out_str char(30) OUTPUT)
AS
BEGIN
IF NOT EXISTS(SELECT * FROM TReader WHERE ReaderID=@in_ReaderID)
BEGIN
SET @out_str= ‘该读者不存在’
RETURN 0
END
IF NOT EXISTS(SELECTFROM TLend WHERE ReaderID=@in_ReaderID AND BookID=@in_BookID)
BEGIN
SET @out_str=‘该读者并未借过此书’
RETURN 0
END
BEGIN TRAN
DELETE FROM TLend WHERE ReaderID=@in_ReaderID AND BookID=@in_BookID
IF @@ERROR>0 /*如果前面一条SQL语句出错则回滚事务并返回*/BEGINROLLBACK TRANSET @out_str= \'执行过程中遇到错误\'RETURN 0ENDIF @@ERROR=0BEGINCOMMIT TRANSET @out_str= \'还书成功\'RETURN 1ENDELSE /*如果执行出错则回滚所有操作并返回*/BEGINROLLBACK TRANSET @out_str= \'执行过程中遇到错误\'RETURN 0END
END
二.各窗体实现
登录窗体
注意事项:匹配用户账号,用户密码,用于实现登录,忘记密码的时候可以进行找回密码,可以修改密码
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BOOKSY{public partial class LoginForm : Form{public LoginForm(){InitializeComponent();this.BackgroundImageLayout = ImageLayout.Stretch;}private void toolStripStatusLabel1_Click(object sender, EventArgs e)//忘记密码{}private void button1_Click(object sender, EventArgs e)//登录{string id = textBox1.Text.Trim().ToString();string pwd = textBox2.Text.Trim().ToString();if(textBox1 .Text .Trim ()==\"\"||textBox2 .Text .Trim() == \"\"){MessageBox.Show(\"请输入密码和账号!\");return;}else{string strcon = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strcon);string strselect = \"select Name from TUser where id=\'\" + id + \"\' and pwd=\'\" + pwd + \"\'\";SqlCommand cmd = new SqlCommand(strselect, conn);conn.Open();string name1 = cmd.ExecuteScalar().ToString();if(name1 != \"\"){this.Hide();BooKManage bookmanage = new BOOKSY.BooKManage();bookmanage.name = name1;bookmanage.ShowDialog();}else{MessageBox.Show(\"登录失败,密码或账号错误!\");}}}private void button2_Click(object sender, EventArgs e)//取消{this.Close();}}}
主界面窗体
1.菜单栏以及工具栏的使用,作为父窗体,,IsMdiContainer属性值设置为“True”,Text属性值设置为“图书管理系统”,Icon属性选择准备好的图标。BackgroundImage属性选择准备好的图片。
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 BOOKSY{public partial class BooKManage : Form{public BooKManage(){InitializeComponent();this.BackgroundImageLayout = ImageLayout.Stretch;//设置背景图片自动适应}private string _name;public string name{get{return _name;}set{_name = value;}}private void 借书BToolStripMenuItem_Click(object sender, EventArgs e)//借书窗体{BorrowBook form2 = new BorrowBook();form2.MdiParent = this;form2.Show();}private void 还书RToolStripMenuItem_Click(object sender, EventArgs e)//还书窗体{ReturnBook form3 = new ReturnBook();form3.MdiParent = this;form3.Show();}private void 读者管理PToolStripMenuItem_Click(object sender, EventArgs e)//读者管理窗体{ReaderInfo form4 = new ReaderInfo();form4.MdiParent = this;form4.Show();}private void 图书管理MToolStripMenuItem_Click(object sender, EventArgs e)//图书管理窗体{BookInfo form5 = new BookInfo();form5.MdiParent = this;form5.Show();}private void BooKManage_Load(object sender, EventArgs e)//主页显示登录状态{tssl1.Text = name+\"同学,你好,欢迎你使用图书管理系统\";}private void timer1_Tick(object sender, EventArgs e)//主页显示时间{tssl2.Text = DateTime.Now.ToString();}}}
借书窗体
1.点击查询按钮,利用借书证号进行匹配数据库,查询借阅表,并将借阅信息展现在下方DataGridView控件中
2.利用借书证号,图书ID,ISBN,利用ADO.NET技术操作数据库MBOOK,调用存储过程borrow_book,点击借书按钮,完成借书过程,并将更新后的借阅信息表更新在下方DataGridView控件中
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;using System.Data.SqlClient;namespace BOOKSY{public partial class BorrowBook : Form{public BorrowBook(){InitializeComponent();this.BackgroundImageLayout = ImageLayout.Stretch;//设置背景图片自动适应dataGridView1.Columns[0].Width = 180;}string strcon = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";private void button1_Click(object sender, EventArgs e)//查询书籍{SqlConnection conn = new SqlConnection(strcon);//实例化连接对象string sqlstrselect = \"select[BookID],[ISBN],[BookName],[Publisher],[Price],[LTime]from [RBL] where [ReaderID]=\'\" + textBox1.Text.Trim() + \"\'\";//操作命令try{SqlDataAdapter adapter = new SqlDataAdapter(sqlstrselect, conn);//创建新的适配器DataSet distable = new DataSet();adapter.Fill(distable, \"testTable\");dataGridView1.DataSource = distable.Tables[\"testTable\"];//将数据显示在dataGridView中dataGridView1.Show();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();//关闭连接}}private void button2_Click(object sender, EventArgs e)//借书{if (textBox1.Text.Trim() == \"\" || textBox2.Text.Trim() == \"\" || textBox3.Text.Trim() == \"\"){MessageBox.Show(\"借书证号,ISBN,图书ID输入完整!\");return;}SqlConnection conn = new SqlConnection(strcon);//实例化连接对象SqlCommand cmd = new SqlCommand(\"Book_Borrow\", conn);cmd.CommandType = CommandType.StoredProcedure;//设置命令类型为存储过程SqlParameter inReaderID = new SqlParameter(\"@in_ReaderID\", SqlDbType.NChar);inReaderID.Direction = ParameterDirection.Input;inReaderID.Value = textBox1.Text.Trim();cmd.Parameters.Add(inReaderID);SqlParameter inISBN = new SqlParameter(\"@in_ISBN\", SqlDbType.Char);inISBN.Direction = ParameterDirection.Input;inISBN.Value = textBox2.Text.Trim();cmd.Parameters.Add(inISBN);SqlParameter inBookID = new SqlParameter(\"@in_BookID\", SqlDbType.NChar);inBookID.Direction = ParameterDirection.Input;//参数类型为输入参数inBookID.Value = textBox3.Text.Trim();cmd.Parameters.Add(inBookID);SqlParameter outReturn = new SqlParameter(\"@out_str\", SqlDbType.Char, 30);outReturn.Direction = ParameterDirection.Output; //参数类型为输出参数cmd.Parameters.Add(outReturn);try{conn.Open();//打开数据连接cmd.ExecuteNonQuery();//执行存储过程MessageBox.Show(outReturn.Value.ToString());}catch{MessageBox.Show(\"借书出错\");}finally{conn.Close();//关闭连接button1_Click(null, null);//回调方法}}private void button3_Click(object sender, EventArgs e)//帮助{BorrowBook_help help = new BOOKSY.BorrowBook_help();help.ShowDialog();help.Dispose();}}}
还书窗体
注意事项:
1.点击查询按钮,利用借书证号进行匹配数据库,查询借阅表,并将借阅信息展现在下方DataGridView控件中
2.点击还书按钮,利用借书证号,图书ID,利用ADO.NET技术操作数据库MBOOK,利用触发器实现功能:当用户“归还”图书时,即删除借阅表(TLend)中的一条借书记录 时,读者信息表(TReader)该读者的借书量减“1”;图书信息表(TBook)该图书 记录的库存量加“1”。 通过创建借阅表(TLend)的DELETE触发器实现。
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BOOKSY{public partial class ReturnBook : Form{public ReturnBook(){InitializeComponent();this.BackgroundImageLayout = ImageLayout.Stretch;//设置背景图片自动适应this.dataGridView1.Columns[0].Width = 160;this.dataGridView1.Columns[1].Width = 135;}string strcon = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";private void button1_Click(object sender, EventArgs e)//还书{if(textBox1 .Text .Trim ()==\"\" ||textBox2 .Text .Trim() == \"\"){MessageBox.Show(\"请输入正确的借书证号或图书ID\");}SqlConnection conn = new SqlConnection(strcon);//实例化连接对象SqlCommand cmd = new SqlCommand(\"Book_Return\", conn);//cmd.CommandType = CommandType.StoredProcedure;//设置命令类型为存储过程SqlParameter inReaderID = new SqlParameter(\"@in_ReaderID\", SqlDbType.NChar);inReaderID.Direction = ParameterDirection.Input;inReaderID.Value = textBox2.Text.Trim();cmd.Parameters.Add(inReaderID);SqlParameter inBookID=new SqlParameter(\"@in_BookID\", SqlDbType.NChar);inBookID.Direction = ParameterDirection.Input;inBookID.Value = textBox1.Text.Trim();cmd.Parameters.Add(inBookID);SqlParameter outReturn = new SqlParameter(\"@out_str\", SqlDbType.Char, 30);outReturn.Direction = ParameterDirection.Output; //参数类型为输出参数cmd.Parameters.Add(outReturn);try{conn.Open();//打开数据连接cmd.ExecuteNonQuery();//开始执行存储过程MessageBox.Show(outReturn.Value.ToString());}catch{MessageBox.Show(\"还书出错\");}finally{conn.Close();button2_Click(null, null);}}private void button2_Click(object sender, EventArgs e)//查阅自己借阅的所有书籍{SqlConnection conn = new SqlConnection(strcon);string strselect = \"select[BookID],[ISBN],[BookName],[Publisher],[Price],[LTime]from [RBL] where [ReaderID]=\'\" + textBox2.Text.Trim() + \"\'\";try{SqlDataAdapter adapter = new SqlDataAdapter(strselect, conn);//创建新的适配器DataSet distable = new DataSet();adapter.Fill(distable, \"testTable\");dataGridView1.DataSource = distable.Tables[\"testTable\"];//将数据显示在dataGridView中dataGridView1.Show();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}}private void button3_Click(object sender, EventArgs e){ReturnBook_help help = new BOOKSY.ReturnBook_help();help.ShowDialog();help.Dispose();}}}
读者信息窗体
注意事项:
1.查询,点击查询按钮,利用借书证号进行匹配数据库,查询读者信息表,并将借阅信息展现在下方DataGridView控件中,注意利用SqlDataReader读取数据库数据,在读的过程中,我们得先判定照片框中是否预先有照片,如果有进行销毁,SQL数据库中的图片是以二进制信息进行保存的,我么要利用字节数组,以字节的形式输出,并且将字节流转换成内存流,最后利用Image.FromStream(),生成图片;
2.添加读者,利用控件输入框填写信息,点击保存按钮进行保存,记得注意的是我们得判断是否选择了照片,如果要,点击选择照片按钮,利用openFileDialog1.FileName获得文件路径,利用FileStream读取,用字节数组写入数据库中
3.修改读者信息,将要修改的信息填写在输入框中,要注意的是得判断输入框是不是为空,不为空代表这个字段要修改,书写SQL语句的时候得加上这个字段。
4.删除读者,利用借书证号直接删除
5.帮助按钮,点击打开一个对话框,里面书写着使用手册
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BOOKSY{public partial class ReaderInfo : Form{public ReaderInfo(){InitializeComponent();dataGridView1.Columns[0].Width = 170;//改动dataGridView的列间距离dataGridView1.Columns[3].Width = 155;dataGridView1.Columns[4].Width = 155;}private void label1_Click(object sender, EventArgs e)//将读者的照片先一步传到数据库中{string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接conn.Open();SqlCommand cmd = new SqlCommand();//实例化命令cmd.Connection = conn;cmd.CommandText = \"update[TReader] set [Photo]=@photo where ReaderID=\'2017211720\'\";//设置命令类型byte[] buffer = new byte[1024*1024*10];//创建字节数组@\"C:\\Users\\14463\\Pictures\\image.jpg\"FileStream fs = new FileStream(@\"C:\\Users\\14463\\Pictures\\image.jpg\", FileMode.Open, FileAccess.Read);//从指定文件中读取图片fs.Read(buffer, 0, 1024 * 1024 * 10);cmd.Parameters.Add(\"@photo\", SqlDbType.Image , (int)(fs.Length));cmd.Parameters[\"@photo\"].Value = buffer;//将字节赋值给Photocmd.ExecuteNonQuery();//执行命令conn.Close();}private void button1_Click(object sender, EventArgs e)//查阅读者{if(textBox1 .Text .Trim() == \"\"){MessageBox.Show(\"请输入借书证号!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strselect=\"select[ReaderID],[Name],[Sex],[Born],[Spec],[Num],[Photo],[Detail] from [TReader] where ReaderID=\'\"+textBox1 .Text .Trim ()+\"\'\";SqlCommand cmd = new SqlCommand(strselect, conn);//实例化命令try{conn.Open();SqlDataReader dr = cmd.ExecuteReader();MemoryStream memorystream=null ;if(dr.HasRows){dr.Read();//读取第一行记录textBox2.Text = dr[\"Name\"].ToString();textBox3.Text = dr[\"Born\"].ToString();comboBox1.Text = dr[\"Spec\"].ToString();textBox5.Text = dr[\"Detail\"].ToString();bool sex = Convert.ToBoolean(dr[\"Sex\"]);if (sex==true){radioButton1.Checked = true;}else{radioButton2.Checked = true;}label6.Text = dr[\"Num\"].ToString();if (dr[\"Photo\"] != System.DBNull.Value)//判定数据库有没有照片{if (this.pictureBox4.Image != null)//判定图片框事先是否存在照片{this.pictureBox4.Image.Dispose();//如果存在,销毁}byte []buffer = (byte[])dr[\"Photo\"];//注意细节,是以字节数组的形式输出memorystream = new MemoryStream(buffer);//将字节流转换成内存流pictureBox4.Image = Image.FromStream(memorystream);}}else{MessageBox.Show(\"没有此读者\");}if(memorystream != null){memorystream.Close();//关闭内存流}if(dr.IsClosed ==false){dr.Close();}}catch(Exception ex){MessageBox.Show(ex.Message);}finally{if (conn.State == ConnectionState.Open)//如果连接还没有关闭,关闭连接{conn.Close();}}}private void button2_Click(object sender, EventArgs e)//添加读者{string strinsert;if (textBox1 .Text .Trim ()==\"\"||textBox2 .Text .Trim ()==\"\"||textBox3 .Text .Trim ()==\"\"||textBox5 .Text .Trim() == \"\"){MessageBox.Show(\"请填写完整信息!\");return;}if(textBox4 .Text .Trim() != \"\")//选择了照片{strinsert = \"insert into[TReader]([ReaderID],[Name],[Sex],[Born],[Spec],[Num],[Photo],[Detail] )values(@ReaderID,@Name,@Sex,@Born,@Spec,@Num,@Photo,@Detail)\";}else{strinsert = \"insert into[TReader]([ReaderID],[Name],[Sex],[Born],[Spec],[Num],[Detail] )values(@ReaderID,@Name,@Sex,@Born,@Spec,@Num,@Detail)\";}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);SqlCommand cmd = new SqlCommand(strinsert, conn);cmd.Parameters.Add(\"@ReaderID\", SqlDbType.NChar).Value = textBox1.Text.Trim();cmd.Parameters.Add(\"@Name\", SqlDbType.Char).Value = textBox2.Text.Trim();if(radioButton1 .Checked ==true&&radioButton2 .Checked==false ){cmd.Parameters.Add(\"@Sex\", SqlDbType.Bit).Value = true;}if(radioButton1 .Checked ==false &&radioButton2 .Checked ==true){cmd.Parameters.Add(\"@Sex\", SqlDbType.Bit).Value = false;}cmd.Parameters.Add(\"@Born\", SqlDbType.Date).Value = textBox3.Text.Trim();cmd.Parameters.Add(\"@Spec\", SqlDbType.Char).Value = comboBox1.SelectedItem;cmd.Parameters.Add(\"Num\", SqlDbType.Int).Value = 0;cmd.Parameters.Add(\"@Detail\", SqlDbType.NText).Value = textBox5.Text.Trim();if(textBox4.Text.Trim() != \"\"){FileStream fs = new FileStream(textBox4.Text.Trim(), FileMode.Open, FileAccess.Read);MemoryStream mem = new MemoryStream();byte[] buffer = new byte[fs.Length];fs.Read(buffer, 0, (int)(fs.Length));cmd.Parameters.Add(\"@Photo\", SqlDbType.Image);cmd.Parameters[\"@Photo\"].Value = buffer;}try{conn.Open();cmd.ExecuteNonQuery();MessageBox.Show(\"保存成功!\");string strselect = \"select* from TReader where ReaderID=\'\" + textBox1.Text.Trim() + \"\'\";SqlDataAdapter adapter = new SqlDataAdapter(strselect ,conn);DataSet distable = new DataSet();adapter.Fill(distable, \"testTable\");dataGridView1.DataSource = distable.Tables[\"testTable\"];//将数据显示在dataGridView中dataGridView1.Show();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();textBox4.Text = \"\";}}private void button5_Click(object sender, EventArgs e)//选择照片{if(openFileDialog1.ShowDialog()==DialogResult.OK){textBox4.Text = openFileDialog1.FileName ;}}private void button3_Click(object sender, EventArgs e)//读者删除{if (textBox1.Text.Trim() == \"\"){MessageBox.Show(\"请输入借书证号!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strdelete = \"delete from [TReader] where [ReaderID]=@ReaderID\";//删除指定读者信息SqlCommand cmd = new SqlCommand (strdelete, conn);cmd.Parameters.Add(\"@ReaderID\", SqlDbType.NChar).Value = textBox1.Text.Trim();try{conn.Open();cmd.ExecuteNonQuery();MessageBox.Show(\"删除成功!\");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}}private void button4_Click(object sender, EventArgs e)//读者修改{if (textBox1.Text.Trim() == \"\"){MessageBox.Show(\"请输入借书证号!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strupdate = \"update [TReader] set\";if(textBox2 .Text .Trim() != \"\"){strupdate += \"[Name]=\'\" + textBox2.Text.Trim() + \"\',\";}if(textBox3.Text.Trim() != \"\"){strupdate += \"[Born]=\'\" + textBox3.Text.Trim() + \"\',\";}if(textBox4 .Text.Trim() != \"\"){strupdate += \"[Photo]=@Photo,\";}strupdate += \"[Spec]=\'\" + comboBox1.Text + \"\',\" + \"[Sex]=@Sex \";strupdate += \"where ReaderID=\'\" + textBox1.Text.Trim() + \"\'\";SqlCommand cmd = new SqlCommand(strupdate, conn);if (radioButton1.Checked == true) //如果性别是男{ cmd.Parameters.Add(\"@Sex\", SqlDbType.Bit).Value = true; }else if (radioButton2.Checked == true) //如果性别是女{ cmd.Parameters.Add(\"@Sex\", SqlDbType.Bit).Value = false; }else{MessageBox.Show(\"请选择性别\");return;}if (textBox4.Text.Trim() != \"\"){FileStream fs = new FileStream(textBox4.Text.Trim(), FileMode.Open, FileAccess.Read);MemoryStream mem = new MemoryStream();byte[] buffer = new byte[fs.Length];fs.Read(buffer, 0, (int)(fs.Length));cmd.Parameters.Add(\"@Photo\", SqlDbType.Image);cmd.Parameters[\"@Photo\"].Value = buffer;}try{conn.Open();int x=cmd.ExecuteNonQuery();if (x == 1){MessageBox.Show(\"修改成功!\");}else{MessageBox.Show(\"没有找到该读者!\");}string strselect = \"select* from TReader where ReaderID=\'\" + textBox1.Text.Trim() + \"\'\";SqlDataAdapter adapter = new SqlDataAdapter(strselect, conn);DataSet distable = new DataSet();adapter.Fill(distable, \"testTable\");dataGridView1.DataSource = distable.Tables[\"testTable\"];//将数据显示在dataGridView中dataGridView1.Show();}catch (Exception ex){MessageBox.Show(\"修改失败!\"+ex.Message);}finally{conn.Close();textBox4.Text = \"\";}}private void button6_Click(object sender, EventArgs e)//帮助按钮{ReaderInfo_help help = new BOOKSY.ReaderInfo_help();help.ShowDialog();help.Dispose();}}}
图书信息窗体
注意事项:
1.查询,点击查询按钮,利用图书ID进行匹配数据库,查询图书信息表,并将借阅信息展现在下方DataGridView控件中,注意利用SqlDataReader读取数据库数据
2.修改,将要修改的信息填写在输入框中
3.添加,将要添加的信息填写在输入框中
4.帮助按钮,点击打开一个对话框,里面书写着使用手册
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BOOKSY{public partial class BookInfo : Form{public BookInfo(){InitializeComponent();}private void button1_Click(object sender, EventArgs e)//查询{if (textBox1.Text.Trim() == \"\"){MessageBox.Show(\"请输入图书书名!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strselect = \"select [ISBN],[BookName],[Author],[Publisher],[Price],[CNum],[SNum],[Summary] from [TBook] where BookName=\'\" + textBox1.Text.Trim() + \"\'\";SqlCommand cmd = new SqlCommand(strselect, conn);try{conn.Open();SqlDataReader dr = cmd.ExecuteReader();if(dr.HasRows)//判断是否读成功{dr.Read();//开始读第一行textBox2.Text = dr[\"BooKName\"].ToString();textBox3.Text = dr[\"Author\"].ToString();textBox4.Text = dr[\"Publisher\"].ToString();textBox5.Text = dr[\"Price\"].ToString();textBox6.Text = dr[\"ISBN\"].ToString();numericUpDown1.Value = Convert.ToDecimal(dr[\"CNum\"]);numericUpDown2.Value = Convert.ToDecimal(dr[\"SNum\"]);textBox7.Text = dr[\"Summary\"].ToString();}else{MessageBox.Show(\"没有找到这本书!\");}if (!dr.IsClosed)//关闭查询{dr.Close();}SqlDataAdapter adapter = new SqlDataAdapter(strselect, conn);DataSet ditable = new DataSet();adapter.Fill(ditable, \"textTable\");dataGridView1.DataSource = ditable.Tables[\"textTable\"];//绑定datagridview,并且显示出来dataGridView1.Show();}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}}private void button3_Click(object sender, EventArgs e)//修改{if (textBox1.Text.Trim() == \"\"){MessageBox.Show(\"请输入图书书名!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strupdate = \"update [TBook] set\";//设置SQL语句if(textBox2 .Text .Trim() != \"\"){strupdate += \"[BookName]=\'\" + textBox2.Text.Trim() + \"\',\";}if(textBox3.Text.Trim() != \"\"){strupdate += \"[Author]=\'\" + textBox3.Text.Trim() + \"\',\";}if(textBox4 .Text.Trim() != \"\"){strupdate += \"[Publish]=\'\" + textBox4.Text.Trim() + \"\',\";}if(textBox5 .Text.Trim() != \"\"){strupdate += \"[Price]=\'\"+ int.Parse(textBox5.Text.Trim()) + \"\',\";}if(textBox6 .Text.Trim() != \"\"){strupdate += \"[ISBN]=\'\" + textBox6.Text.Trim() + \"\',\";}if(textBox7 .Text.Trim() != \"\"){strupdate += \"[Summary]=\'\" + textBox7.Text.Trim() + \"\',\";}if(numericUpDown1 .Value != 0){strupdate += \"[CNum]=\'\" + numericUpDown1.Value + \"\',\";}if(numericUpDown2 .Value != 0){strupdate += \"[SNum]=\'\" + numericUpDown2.Value + \"\',\";}strupdate = DelLastCommo(strupdate);//去除语句结尾的逗号,以便下面连接wherestrupdate += \" where BookName=\'\" + textBox1.Text.Trim() + \"\'\";SqlCommand cmd = new SqlCommand(strupdate, conn);try{conn.Open();cmd.ExecuteNonQuery();MessageBox.Show(\"修改成功\");}catch (Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();button1_Click(null, null);//重新绑定dategridview,显示修改之后的数据}}private void button4_Click(object sender, EventArgs e)//添加{//判断填写信息是否完整if (textBox1.Text.Trim() == \"\" || textBox2.Text.Trim() == \"\" || textBox3.Text.Trim() == \"\" || textBox4.Text.Trim() == \"\" || textBox5.Text.Trim() == \"\" || textBox6.Text.Trim() == \"\" || textBox7.Text.Trim() == \"\"){MessageBox.Show(\"请把信息补充完整!\");return;}string strconn = @\"Data Source=(local);Initial Catalog=MBook;Persist Security Info=True;User ID=sa;Password=\'zdllxf211215\'\";SqlConnection conn = new SqlConnection(strconn);//实例化连接string strinsert = \"insert into [TBook]([ISBN],[BookName],[Author],[Publisher],[Price],[CNum],[SNum],[Summary]) values(@ISBN,@BookName,@Author,@Publisher,@Price,@CNum,@SNum,@Summary)\";SqlCommand cmd = new SqlCommand(strinsert, conn);cmd.Parameters.Add(\"@ISBN\", SqlDbType.Char).Value = textBox6.Text.Trim().ToString();cmd.Parameters.Add(\"@BookName\", SqlDbType.Char).Value = textBox1.Text.Trim().ToString();cmd.Parameters.Add(\"@Author\", SqlDbType.Char).Value = textBox3.Text.Trim().ToString();cmd.Parameters.Add(\"@Publisher\", SqlDbType.Char).Value = textBox4.Text.Trim().ToString();cmd.Parameters.Add(\"@Price\", SqlDbType.Float).Value = int.Parse(textBox5.Text.Trim());cmd.Parameters.Add(\"@CNum\", SqlDbType.Int).Value = numericUpDown1.Value;cmd.Parameters.Add(\"@SNum\", SqlDbType.Int).Value = numericUpDown2.Value;cmd.Parameters.Add(\"@Summary\", SqlDbType.VarChar).Value = textBox7.Text.Trim().ToString();try{conn.Open();cmd.ExecuteNonQuery();MessageBox.Show(\"添加成功!\");}catch (Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();button1_Click(null, null);//重新绑定dategridview,显示修改之后的数据}}private void button5_Click(object sender, EventArgs e)//帮助{BookInfo_help help = new BookInfo_help();help.ShowDialog();help.Dispose();}public static string DelLastCommo(string str)//去除字符串最后结尾逗号的方法{return str.Substring(0, str.LastIndexOf(\',\'));}}}
以上就是整个图书管理系统了,时间紧,做的有点粗糙,欢迎指出我的错误……