图书案例sql.docx

1、图书案例sql图书管理系统一系统设计目的和内容：图书管理系统主要目的是对图书馆种类繁多的书籍进行管理，并且合理管理好用户的借还信息。提高图书馆的工作效率，降低管理成本。其开发主要包括后台数据库的建立和维护，以及前端应用程序的开发。前者要求建立起数据一致性各完整性强、数据安全性好的数据库。而后者则要求应用程序具有功能完备、易用等特点。因此本系统结合开放式图书馆的要求，采用.NET技术各SQL SERVER 2000数据库进行系统的开发。二系统需求分析:1书目查询管理：根据一定的条件对图书进行查询，并可以查看图书的详细信息，查询范围出版社、书名、作者等查询项目进行任意条件的组合查询。2权限维护管理

2、：系统管理员可以在此模块中，对已有的图书信息进行修改，并对用户信息进行管理。3、用户信息管理：用户登录该系统后，可以进行图书的借阅和归还操作，还可修改密码、查询借阅信息等。 三系统总体设计四数据库设计4.1数据库的需求分析：本系统使用SQL SERVER2000作为应用程序的数据库。考虑到系统的实际需求，系统至少需要四张数据库表，具体如下：1图书信息数据表（BOOKINFO）：用于图书馆的所有已录入的书目信息，这是本系统最为关键的数据部分，包括图书号、图书名、图书作者、出版时间、索取号、价格和图书条码。2名字:密码别名:描述:用户和管理员进入管理系统的条件.定义:1数字或字符8.位置: 管理员

3、信息用户信息 名字:管理员号别名:描述:管理员在图书馆中的惟一的标识.定义:1数字5.位置: 管理员信息 名字:借阅号别名:描述:用户在图书馆中借还书的惟一的标识.定义:1数字8.位置: 借阅信息 名字:用户号别名:描述: 此用户在图书馆中的惟一的标识.定义:1数字8.位置: 用户信息借阅信息 名字:图书号别名:描述: 此书在图书馆中的惟一的标识.定义:1数字8.位置: 图书信息借阅信息用户信息表（USER）：用户ID、用户名、密码、地址、E-MAIL、电话。3借阅信息表（LENDINFO）：ID、用户ID、图书ID、借阅时间、归还时间、归还与否。4管理员信息表（MANGER）：管理员ID、密

4、码。 数字字典4.4物理设计对数据库建立索引，索引语句在SQL语句中。Create unique index book_id ON bookinfo(bookid)Create unique index user_id ON user(userid)Create unique index lend_id ON lendinfo(lendid)4.5 SQL语句的实现创建数据库名为library CREATE DATABASE LIBRARY创建员工基本信息 CREATE TABLE BookInfo( bookid int(4) NOT NULL UNIQUE ,bookname varcha

5、r(100) ,pubname varchar(100) ,bookauthor varchar(50) ,series varchar(50) ,ISBN varchar(50) ,SearchNO varchar(50) ,PubDate smalldatetime(4) ,Price float(8) ,Barcode varchar(50); CREATE TABLE LendInfo(LendID int(4) NOT NULL UNIQUE ,BookID int(4) NOT NULL UNIQUE ,UserID varchar(50) NOT NULL UNIQUE ,Len

6、dDate smalldatetime(4) ,ReturnDate smalldatetime(4) ,IsBack int(4); CREATE TABLE manage(manageid int(4) NOT NULL UNIQUE,Pass char(10) ; CREATE TABLE users(userid int(4) NOT NULL UNIQUE ,username char(10) ,pass char(10) ,email char(50) ,phone char(10) ,address varchar(50) ,BookNum int(4); 4.6 创建视图创建关

7、于书名的视图，因为查询时需要绑定。Create view book_nameAsSelect bookid,pubname,bookauthor,bookname,searchNOFROM BOOKINFOWHERE bookname=ASP程序设计； 创建关于用户名的视图，因为查询时需要绑定。Create view user_nameAsSelect userid,username,phone,address,emailFROM usersWHERE username=1； 4.7 创建存储过程 创建建表的存储过程USE LIBRARYGOCREATE PROCEDURE createtab

8、lesASCREATE TABLE BookInfo( bookid int(4) NOT NULL UNIQUE ,bookname varchar(100) ,pubname varchar(100) ,bookauthor varchar(50) ,series varchar(50) ,ISBN varchar(50) ,SearchNO varchar(50) ,PubDate smalldatetime(4) ,Price float(8) ,Barcode varchar(50); CREATE TABLE LendInfo(LendID int(4) NOT NULL UNIQ

9、UE ,BookID int(4) NOT NULL UNIQUE ,UserID varchar(50) NOT NULL UNIQUE ,LendDate smalldatetime(4) ,ReturnDate smalldatetime(4) ,IsBack int(4);CREATE TABLE manage(manageid int(4) NOT NULL UNIQUE,Pass char(10) ; CREATE TABLE users(userid int(4) NOT NULL UNIQUE ,username char(10) ,pass char(10) ,email c

10、har(50) ,phone char(10) ,address varchar(50) ,BookNum int(4); GO 4.8 创建触发器创建关于书号和用户号的触发器，分别在BOOKINFO .USERS中修改bookid和userid时，在LENDINFO中也会有相应的修改。CREATE TRIGGER BookidChangeON BOOKINFOAFTER UPDATEASIF UPDATE(bookid)BEGINDECLARE book_id as intDECLARE old_book_id as intSELECT book_id=bookid insertedSELE

11、CT old_book_id =bookid deletedUpdate LENDINFOSET LENDINFO.bookid= book_idWHERE LENDINFO. bookid = old_book_idEND CREATE TRIGGER UserIdChangesON USERSAFTER UPDATEASIF UPDATE(userid)BEGINDECLARE user_id as intDECLARE old_user_id as intSELECT user_id =userid insertedSELECT old_user_id = userid deletedU

12、pdate LENDINFOSET LENDINFO. userid = user_idWHERE LENDINFO. userid = old_user_idEND 五、 使用ER/STUDIO完成数据库设计 六、 代码实现6.1 “图书管理系统”的功能模块图 系统主界面： Imports System.Data.SqlClientImports System.IOPublic Class searchNInherits System.Web.UI.PagePrivate connectingstring As StringPrivate myConn As SqlConnectionPri

13、vate ds As DataSetPrivate myAdapter As SqlDataAdapterProtected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLinkProtected WithEvents radiobutton1 As System.Web.UI.WebControls.RadioButtonProtected WithEvents dropdownlist1 As System.Web.UI.WebControls.DropDownListProtected WithEvents button

14、1 As System.Web.UI.WebControls.ButtonPrivate myCmd As SqlCommand Public 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 sende

15、r As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load在此处放置初始化页的用户代码connectingstring = data source=(local);Database=library;uid=sa;pwd=123456;myConn = New SqlConnection(connectingstring)If Not IsPostBack Then If Not Session(UserID) Is Nothing ThenIf AllowBooking(Session(UserID) = True

16、Then End IfEnd IfEnd IfEnd Subprivate bindPublic Function GetRowsNum(ByVal sqlstr As String) As IntegerIf myConn.State = ConnectionState.Closed ThenmyConn.Open()End If Fill(sqlstr)Close()Return ds.Tables(0).Rows.CountEnd Function Public Function AllowBooking(ByVal UserID As String) As BooleanDim sql

17、str 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 = Connecti

18、onState.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

19、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 Sub Private Sub but

20、ton1_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.

21、Trim + sign + Session(sqlstr) = sqlstrBindGrid()End Sub Private Sub ResultGrid_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles ResultGrid.PageIndexChangedResultGrid.CurrentPageIndex = e.NewPageIndexBindGrid()End Sub Private Sub Resu

22、ltGrid_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.ToStringElseIf viewstate(sortexp) = e.SortExpression.ToString The

23、nviewstate(sortexp) += descElseviewstate(sortexp) = e.SortExpression.ToStringEnd IfIf Not Session(sqlstr) Is Nothing Thensqlstr = CType(Session(sqlstr), String) BindDBGrd(sqlstr, ResultGrid, Viewstate(sortexp)End IfEnd Sub Public Sub ExecNonSql(ByVal sqlstr As String)If myConn.State = ConnectionStat

24、e.Closed ThenmyConn.Open()End IfmyCmd = New SqlCommand(sqlstr, myConn)myCmd.ExecuteNonQuery()myCmd.Dispose()Close()End Sub End Class 可以根据书名，内容进行查询，我们选取任意匹配：如上图所示最上方为自定义用户控件：bar 点击权限维护： Imports System.Data.SqlClientPublic Class userInherits System.Web.UI.PagePrivate connectingstring As StringPrivate

25、myConn As SqlConnectionPrivate ds As DataSetPrivate myAdapter As SqlDataAdapterProtected WithEvents Button2 As System.Web.UI.WebControls.ButtonProtected WithEvents Label1 As System.Web.UI.WebControls.LabelProtected WithEvents Label2 As System.Web.UI.WebControls.LabelProtected WithEvents TextBox1 As

26、System.Web.UI.WebControls.TextBoxProtected WithEvents Label3 As System.Web.UI.WebControls.LabelProtected WithEvents TextBox2 As System.Web.UI.WebControls.TextBoxProtected WithEvents Login_trname As System.Web.UI.HtmlControls.HtmlTableRowProtected WithEvents Login_trpassword As System.Web.UI.HtmlCont

27、rols.HtmlTableRowPrivate myCmd As SqlCommand Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load在此处放置初始化页的用户代码connectingstring = data source=(local);Database=library;uid=sa;pwd=123456;myConn = New SqlConnection(connectingstring)End SubPublic Function

28、 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 nullElsesqlstr = select * from manage where mana

29、geid= + Userstr.Trim + and Pass= + Pwdstr + End If Fill(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 SqlDataAdapt

30、er(sqlstr, myConn)ds = New DataSetmyAdapter.Fill(ds)End Sub Private 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