图书管理系统

摘要图书管理系统是典型的信息管理系统(MIS),其开发主要包括后台数据库的建立和维护以及前端应用程序的开发两个方面。

对于前者要求建立起数据一致性和完整性强、数据安全性好的库。

而对于后者则要求应用程序功能完备,易使用等特点。

因此本人结合开入式图书馆的要求，对MS SQL Server2000数据库管理系统、SQL语言原理、.NET应用程序设计进行了较深入的学习和应用，主要完成对图书管理系统的需求分析、功能模块划分、数据库需求分析，并由此设计了数据库结构和应用程序。

系统运行结果证明，本文所设计的图书管理系统可以满足借阅者、图书馆工作人员的双方面的需要。

目录一、设计的目的和内容 (1)二、系统需求分析 (1)三、系统总体设计 (1)3.1总体功能结构图 (1)3.2系统流程图 (2)四、数据库设计 (3)4.1需求分析及数据字典 (3)4.2概念设计 (4)4.3逻辑设计 (5)4.4物理设计 (6)4.5SQL语句的实现 (6)4.6创建视图 (7)4.7创建存储过程 (8)4.8创建触发器 (9)五、使用ER/STUDIO完成数据库设计 (10)六、代码实现 (11)6.1“图书管理系统”的功能模块图 (11)6.2插入功能的实现 (11)6.3修改/删除功能的实现 (11)6.4查询功能的实现 (11)七、总结 (30)图书管理系统一．系统设计目的和内容：图书管理系统主要目的是对图书馆种类繁多的书籍进行管理，并且合理管理好用户的借还信息。

提高图书馆的工作效率，降低管理成本。

其开发主要包括后台数据库的建立和维护，以及前端应用程序的开发。

前者要求建立起数据一致性各完整性强、数据安全性好的数据库。

而后者则要求应用程序具有功能完备、易用等特点。

因此本系统结合开放式图书馆的要求，采用SQL SERVER 2000数据库进行系统的开发。

二．系统需求分析:1．书目查询管理：根据一定的条件对图书进行查询，并可以查看图书的详细信息，查询范围出版社、书名、作者等查询项目进行任意条件的组合查询。

2．权限维护管理：系统管理员可以在此模块中，对已有的图书信息进行修改，并对用户信息进行管理。

3．用户信息管理：用户登录该系统后，可以进行图书的借阅和归还操作，还可修改密码、查询借阅信息等。

三．系统总体设计四．数据库设计4.1数据库的需求分析：本系统使用SQL SERVER2000作为应用程序的数据库。

考虑到系统的实际需求，系统至少需要四张数据库表，具体如下：1．图书信息表（BOOKS）：用于图书馆的所有已录入的书目信息，这是本系统最为关键的数据部分，包括图书号、图书名、图书作者、出版时间、索取号、价格和图书条码。

2．用户信息表（USES）：用户ID、用户名、密码、地址、E-MAIL、电话。

3．借阅信息表（LENDS）：用户ID、图书号、借阅时间。

4．管理员信息表（MANGER）：管理员ID、密码5．归还信息表（MANGER）：图书号、管理员ID、归还时间。

数字字典4.2概念设计4.3逻辑设计BOOKINFO USERSMANGERLENDINFO六、代码实现6.1 “图书管理系统”的功能模块图系统主界面：Imports System.Data.SqlClientImports System.IOPublic Class searchNInherits System.Web.UI.PagePrivate connectingstring As StringPrivate myConn As SqlConnectionPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterProtected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLink Protected WithEvents radiobutton1 As System.Web.UI.WebControls.RadioButton Protected WithEvents dropdownlist1 As System.Web.UI.WebControls.DropDownListProtected WithEvents button1 AsSystem.Web.UI.WebControls.ButtonPrivate myCmd As SqlCommandPublic Sub Open()myConn.Open()End SubPublic Sub Close()myConn.Close()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)If Not IsPostBack ThenIf Not Session("UserID") Is Nothing ThenIf AllowBooking(Session("UserID")) = True ThenEnd IfEnd IfEnd IfEnd Sub'private bindPublic Function GetRowsNum(ByVal sqlstr As String) As Integer If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)Close()Return ds.Tables(0).Rows.CountEnd FunctionPublic Function AllowBooking(ByVal UserID As String) As Boolean Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and AllowBooking=1 and UserID='" + UserID + "'"If GetRowsNum(sqlstr) = 0 ThenReturn FalseElseReturn TrueEnd IfEnd FunctionPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)myDBGrd.DataSource = ds.Tables(0).DefaultViewmyDBGrd.DataBind()End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)Dim dv As DataView = ds.Tables(0).DefaultViewdv.Sort = SortExpmyDBGrd.DataSource = dvmyDBGrd.DataBind()End SubPrivate Sub BindGrid()If Not Session("sqlstr") Is Nothing ThenDim sqlstr As String = CType(Session("sqlstr"), String)BindDBGrd(sqlstr, ResultGrid)Session("sqlstr") = sqlstrEnd IfEnd SubPrivate Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.ClickDim sign As String = ""If AnyChoice.Checked = True Thensign = "%"End IfDim sqlstr As String = "select * from BookInfo where 1=1 "sqlstr += " and " + dropdownlist1.SelectedValue + " like '" + sign + txtContent.Text.ToString.Trim + sign + "' "Session("sqlstr") = sqlstrBindGrid()End SubPrivate Sub ResultGrid_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles ResultGrid.PageIndexChangedResultGrid.CurrentPageIndex = e.NewPageIndexBindGrid()End SubPrivate Sub ResultGrid_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles ResultGrid.SortCommandDim sqlstr As StringIf viewstate("sortexp") Is Nothing Thenviewstate("sortexp") = e.SortExpression.ToString ElseIf viewstate("sortexp") = e.SortExpression.ToString Thenviewstate("sortexp") += " desc"Elseviewstate("sortexp") = e.SortExpression.ToString End IfIf Not Session("sqlstr") Is Nothing Thensqlstr = CType(Session("sqlstr"), String)BindDBGrd(sqlstr, ResultGrid, Viewstate("sortexp")) End IfEnd SubPublic Sub ExecNonSql(ByVal sqlstr As String) If myConn.State = ConnectionState.Closed Then myConn.Open()End IfmyCmd = New SqlCommand(sqlstr, myConn)myCmd.ExecuteNonQuery()myCmd.Dispose()Close()End SubEnd Class可以根据书名，内容进行查询，我们选取任意匹配：如上图所示最上方为自定义用户控件：bar点击权限维护：Imports System.Data.SqlClientPublic Class userInherits System.Web.UI.PagePrivate connectingstring As StringPrivate myConn As SqlConnectionPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterProtected WithEvents Button2 As System.Web.UI.WebControls.Button Protected WithEvents Label1 As belProtected WithEvents Label2 As belProtected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox Protected WithEvents Label3 As belProtected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox Protected WithEvents Login_trname AsSystem.Web.UI.HtmlControls.HtmlTableRowProtected WithEvents Login_trpassword AsSystem.Web.UI.HtmlControls.HtmlTableRowPrivate myCmd As SqlCommandPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)End SubPublic Function CheckUserberInfo(ByVal Userstr As String, ByVal Pwdstr As String) As IntegerIf myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfDim sqlstr As StringIf Pwdstr = "" Thensqlstr = "select * from manage where manageid='" + Userstr.Trim + "' and Pass is null"Elsesqlstr = "select * from manage where manageid='" + Userstr.Trim + "' and Pass='" + Pwdstr + "'"End IfFill(sqlstr)If ds.Tables(0).Rows.Count = 0 ThenClose()Return -1End Ifds.Clear()Close()Return 1End FunctionPublic Sub Open()myConn.Open()End SubPublic Sub Close()myConn.Close()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPrivate Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickIf CheckUserberInfo(TextBox1.Text, TextBox2.Text) = 1 ThenSession("UserID") = TextBox1.Text.ToStringResponse.Redirect("delete.aspx")ElseResponse.Write("<script>javascript:alert('登陆失败！！！');</script>")Response.Write("javascript:window.location='user.aspx'") End IfEnd SubEnd Class登陆成功后：点击删除后：再次查询时：Imports System.Data.SqlClientPublic Class deleteInherits System.Web.UI.PageDim myconn As SqlConnectionPrivate connectingstring As StringPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterPrivate myCmd As SqlCommandPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid) If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)myDBGrd.DataSource = ds.Tables(0).DefaultViewmyDBGrd.DataBind()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)If myconn.State = ConnectionState.Closed Thenmyconn.Open()End IfFill(sqlstr)Dim dv As DataView = ds.Tables(0).DefaultViewdv.Sort = SortExpmyDBGrd.DataSource = dvmyDBGrd.DataBind()End SubPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myconn = New SqlConnection(connectingstring)If Not IsPostBack ThenDim sqlstr As String = "select * from bookinfo "BindDBGrd(sqlstr, DataGrid1)Dim ds As DataSet = New DataSetEnd IfEnd SubPrivate Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) HandlesDataGrid1.DeleteCommandDim sql As StringDim getdata As SqlDataReaderDim mycmd As SqlCommandDim uid As String = DataGrid1.Items(e.Item.ItemIndex).Cells(0).Textsql = "delete from bookinfo where bookid='" + uid + "'"mycmd = New SqlCommand(sql, myconn)myconn.Open()mycmd.ExecuteNonQuery()myconn.Close()mycmd.Dispose()Response.Write("<script>javascript:alert('删除成功！！！');</script>")End SubEnd Class点击用户登录后：Imports System.Data.SqlClientImports System.IOPublic Class WebForm1Inherits System.Web.UI.PagePrivate connectingstring As StringPrivate myConn As SqlConnectionPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterProtected WithEvents Button2 As System.Web.UI.WebControls.ButtonPrivate myCmd As SqlCommandPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)End SubPublic Function CheckUserberInfo(ByVal Userstr As String, ByVal Pwdstr As String) As IntegerIf myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfDim sqlstr As StringIf Pwdstr = "" Thensqlstr = "select * from Users where UserID='" + Userstr.Trim + "' and Pass is null"Elsesqlstr = "select * from Users where UserID='" + Userstr.Trim + "' and Pass='" + Pwdstr + "'"End IfFill(sqlstr)If ds.Tables(0).Rows.Count = 0 ThenClose()Return -1End Ifds.Clear()Close()Return 1End FunctionPublic Sub Open()myConn.Open()End SubPublic Sub Close()myConn.Close()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPrivate Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickIf CheckUserberInfo(TextBox1.Text, TextBox2.Text) = 1 ThenSession("UserID") = TextBox1.Text.ToStringResponse.Redirect("userlist.aspx")ElseResponse.Write("<script>javascript:alert('登陆失败！！！');</script>")Response.Write("javascript:window.location='Login.aspx'") End IfEnd SubEnd Class登录成功后：Imports System.Data.SqlClientPublic Class userlistInherits System.Web.UI.PageDim myConn As SqlConnectionPrivate myCmd As SqlCommandPrivate connectingstring As StringPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)If Session("UserID") = "" ThenResponse.Redirect("Login.aspx")ElseLoadUserInfo(Session("UserID"))End IfEnd SubPublic Function ExecReaderSql(ByVal sqlstr As String) As SqlDataReaderIf myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfmyCmd = New SqlCommand(sqlstr, myConn)Dim reader As SqlDataReaderreader = myCmd.ExecuteReadermyCmd.Dispose()Return readerEnd FunctionPublic Sub LoadUserInfo(ByVal UserID As String)Dim sqlstr As String = "select * from Users where UserID='" + UserID + "'"Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)If myreader.Read ThentxtUserID.Text = myreader("UserID")txtName.Text = myreader("username")txtphone.Text = myreader("Phone")txtaddress.Text = myreader("address")txtEMail.Text = myreader("EMail")End IfmyConn.Close()End SubEnd Class点击查阅信息：Imports System.Data.SqlClientPublic Class LendInherits System.Web.UI.PageDim myConn As SqlConnectionDim ds As DataSetPrivate myAdapter As SqlDataAdapterPrivate connectingstring As StringPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)BindDBGrid()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid) If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)myDBGrd.DataSource = ds.Tables(0).DefaultViewmyDBGrd.DataBind()End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)Dim dv As DataView = ds.Tables(0).DefaultViewdv.Sort = SortExpmyDBGrd.DataSource = dvmyDBGrd.DataBind()End SubPrivate Sub BindDBGrid()Dim sqlstr As String = "select * from BookInfo,LendInfo where BookInfo.BookID=LendInfo.BookID and UserID='" + Session("UserID") + "'"BindDBGrd(sqlstr, DataGrid1)End SubEnd Class点击图书借阅登记：Imports System.Data.SqlClientPublic Class lendbookInherits System.Web.UI.PagePrivate connectingstring As StringPrivate myConn As SqlConnectionPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterPrivate myCmd As SqlCommandPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load'在此处放置初始化页的用户代码connectingstring = "datasource=(local);Database=library;uid=sa;pwd=123456;"myConn = New SqlConnection(connectingstring)If txtUserID.Text.ToString.Trim <> "" ThenEnd IfEnd SubPublic Sub ExecNonSql(ByVal sqlstr As String)If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfmyCmd = New SqlCommand(sqlstr, myConn)myCmd.ExecuteNonQuery()myCmd.Dispose()myConn.Close()End SubPublic Sub Fill(ByVal sqlstr As String)myAdapter = New SqlDataAdapter(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid) If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)myDBGrd.DataSource = ds.Tables(0).DefaultViewmyDBGrd.DataBind()End SubPublic Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)Dim dv As DataView = ds.Tables(0).DefaultViewdv.Sort = SortExpmyDBGrd.DataSource = dvmyDBGrd.DataBind()End SubPrivate Sub BindDBGrid()Dim sqlstr As String = "select * from BookInfo,LendInfo where BookInfo.BookID=LendInfo.BookID and IsBack=0 and UserID='" +txtUserID.Text.ToString + "'"BindDBGrd(sqlstr, ListDGrid)End SubPublic Function ExecReaderSql(ByVal sqlstr As String) As SqlDataReader If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfmyCmd = New SqlCommand(sqlstr, myConn)Dim reader As SqlDataReaderreader = myCmd.ExecuteReadermyCmd.Dispose()Return readerEnd FunctionPrivate Function GetBookID() As StringDim sqlstr As String = "select BookID from BookInfo where BarCode='" + txtBookID.Text.ToString.Trim + "'"Dim BookID As String = ""Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)If myreader.Read ThenBookID = myreader("BookID")End IfmyConn.Close()Return BookIDEnd FunctionPublic Function GetCurNum(ByVal UserID As String) As Integer Dim sqlstr As String = "select count(*) as Num from LendInfo where UserID='" + UserID + "' and IsBack=0 "Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)Dim num As Integer = 0If myreader.Read Thennum = myreader("Num")End IfmyConn.Close()Return numEnd FunctionPublic Function GetRowsNum(ByVal sqlstr As String) As Integer If myConn.State = ConnectionState.Closed ThenmyConn.Open()End IfFill(sqlstr)myConn.Close()Return ds.Tables(0).Rows.CountEnd FunctionPublic Function GetBookState(ByVal BookID As String) As IntegerDim sqlstr As String = "select * from BookInfo where BookState=1 and BookID=" + BookIDIf GetRowsNum(sqlstr) = 0 ThenReturn -2End Ifsqlstr = "select * from LendInfo where IsBack=0 and BookID =" + BookID If GetRowsNum(sqlstr) <> 0 ThenReturn -1End Ifsqlstr = "select * from BookingInfo where BookID=" + BookID If GetRowsNum(sqlstr) <> 0 ThenReturn 0End IfReturn 1End FunctionPublic Function GetBookDate(ByVal UserID As String) As Integer Dim sqlstr As String = "select BookDate from UserInfo where UserID='" + UserID + "'"Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)Dim num As Integer = 0If myreader.Read Thennum = myreader("BookDate")End IfmyConn.Close()Return numEnd FunctionPublic Function LendBook(ByVal UserID As String, ByVal BookID As String) As BooleanDim datenum As Integer = GetBookDate(UserID)Dim nowdate As Date = Date.NowDim ReturnDate As Date = nowdate.AddDays(datenum)Dim sqlstr As String = "insert intoLendInfo(BookID,UserID,LendDate,ReturnDate,IsBack)values( "sqlstr += "'" + BookID + "'"sqlstr += ",'" + UserID + "'"sqlstr += ",'" + nowdate.ToShortDateString + "'"sqlstr += ",'" + ReturnDate.ToShortDateString + "'"sqlstr += ",0)"ExecNonSql(sqlstr)Return TrueEnd FunctionPublic Function GetMax(ByVal UserID As String) As IntegerDim sqlstr As String = "select BookNum from Users where UserID='" + UserID + "'"Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)Dim num As Integer = 0If myreader.Read Thennum = myreader("BookNum")End IfmyConn.Close()Return numEnd FunctionPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickDim str As String = ""Dim BookID As String = GetBookID()If GetCurNum(txtUserID.Text) >= GetMax(txtUserID.Text.ToString) Then Response.Write("<script>javascript:alert('借书数已满！！！');</script>")ReturnEnd IfDim state As Integer = GetBookState(BookID)If state = 1 ThenLendBook(txtUserID.Text.ToString.Trim, BookID)ElseSelect Case stateCase -2 : Response.Write("<script>javascript:alert('此书不在流通状态或此书不存在！！！');</script>")Case -1 : Response.Write("<script>javascript:alert('此书不在书架上！！！');</script>")Case 0 : Response.Write("<script>javascript:alert('此书已经有人预约！！！');</script>")End SelectEnd IfBindDBGrid()End SubEnd Class七、总结这次课程设计用.NET来编写的，通过这次实践让我更进一步了解了.NET的基本的脚本的写法，虽然不是很熟练，但是还是知道不少，对SQL中数据库中基本操作也应用了不少，相信这次训练是受益非浅的。