摘 要
图书管理系统是典型的信息管理系统(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.5 SQL语句的实现.................................... 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
**理工大学________学院《数据库系统概论》设计
设 计 评 分 表
学生姓名： 设计题目：
评价
项目
序号
评 定 标 准
评价
比例
实际得分
数
据
库
系
统
课
程
设
计
1
设计的目的和内容，需求分析
5分
2
总体功能结构图
5分
3
系统流程图
5分
4
需求分析及数据字典
5分
5
概念设计，E-R图
5分
6
逻辑设计
5分
7
物理设计，要求建立索引
5分
8
SQL语句的实现
5分
9
创建视图
5分
10
创建存储过程，能够使用事务
5分
11
创建触发器，能够进行级联修改和受限修改
5分
12
使用ER/STUDIO完成数据库设计
5分
13
代码实现
35分
14
总结
5分
总 分
100分
备注：此表由计算机教研室汇总存档备查
图书管理系统
一．系统设计目的和内容：
图书管理系统主要目的是对图书馆种类繁多的书籍进行管理，并且合理管理好用户的借还信息。提高图书馆的工作效率，降低管理成本。其开发主要包括后台数据库的建立和维护，以及前端应用程序的开发。前者要求建立起数据一致性各完整性强、数据安全性好的数据库。而后者则要求应用程序具有功能完备、易用等特点。因此本系统结合开放式图书馆的要求，采用.NET技术各SQL SERVER 2000数据库进行系统的开发。
二．系统需求分析:
1．书目查询管理：根据一定的条件对图书进行查询，并可以查看图书的详细信息，查询范围出版社、书名、作者等查询项目进行任意条件的组合查询。
2．权限维护管理：系统管理员可以在此模块中，对已有的图书信息进行修改，
并对用户信息进行管理。
3、用户信息管理：用户登录该系统后，可以进行图书的借阅和归还操作，还可
修改密码、查询借阅信息等。
三．系统总体设计
四．数据库设计
4.1数据库的需求分析：
本系统使用SQL SERVER2000作为应用程序的数据库。考虑到系统的实际需求，系统至少需要四张数据库表，具体如下：
1．图书信息数据表（BOOKINFO）：用于图书馆的所有已录入的书目信息，这是本系统最为关键的数据部分，包括图书号、图书名、图书作者、出版时间、索取号、价格和图书条码。
2．
名字:密码
别名:
描述:用户和管理员进入管理系统的条件.
定义:1{数字或字符}8.
位置: 管理员信息
用户信息
名字:管理员号
别名:
描述:管理员在图书馆中的惟一的标识.
定义:1{数字}5.
位置: 管理员信息
名字:借阅号
别名:
描述:用户在图书馆中借还书的惟一的标识.
定义:1{数字}8.
位置: 借阅信息
名字:用户号
别名:
描述: 此用户在图书馆中的惟一的标识.
定义:1{数字}8.
位置: 用户信息
借阅信息
名字:图书号
别名:
描述: 此书在图书馆中的惟一的标识.
定义:1{数字}8.
位置: 图书信息
借阅信息
用户信息表（USER）：用户ID、用户名、密码、地址、E-MAIL、电话。
3．借阅信息表（LENDINFO）：ID、用户ID、图书ID、借阅时间、归还时间、归还与否。
4．管理员信息表（MANGER）：管理员ID、密码。
数字字典
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 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 UNIQUE ,
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 char(50) ,
phone char(10) ,
address varchar(50) ,
BookNum int(4)
);
4.6 创建视图
创建关于书名的视图，因为查询时需要绑定。
Create view book_name
As
Select bookid,pubname,bookauthor,bookname,searchNO
FROM BOOKINFO
WHERE bookname=’ASP程序设计’；
创建关于用户名的视图，因为查询时需要绑定。
Create view user_name
As
Select userid,username,phone,address,email
FROM users
WHERE username=’1’；
4.7 创建存储过程
创建建表的存储过程
USE LIBRARY
GO
CREATE PROCEDURE createtables
AS
CREATE 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 UNIQUE ,
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 char(50) ,
phone char(10) ,
address varchar(50) ,
BookNum int(4)
);
GO
4.8 创建触发器
创建关于书号和用户号的触发器，分别在BOOKINFO .USERS中修改bookid和userid时，在LENDINFO中也会有相应的修改。
CREATE TRIGGER BookidChange
ON BOOKINFO
AFTER UPDATE
AS
IF UPDATE(bookid)
BEGIN
DECLARE @book_id as int
DECLARE @old_book_id as int
SELECT @ book_id=bookid inserted
SELECT @ old_book_id =bookid deleted
Update LENDINFO
SET LENDINFO.bookid=@ book_id
WHERE LENDINFO. bookid =@ old_book_id
END
CREATE TRIGGER UserIdChanges
ON USERS
AFTER UPDATE
AS
IF UPDATE(userid)
BEGIN
DECLARE @user_id as int
DECLARE @old_user_id as int
SELECT @ user_id =userid inserted
SELECT @ old_user_id = userid deleted
Update LENDINFO
SET LENDINFO. userid =@ user_id
WHERE LENDINFO. userid =@ old_user_id
END
五、 使用ER/STUDIO完成数据库设计
六、 代码实现
6.1 “图书管理系统”的功能模块图
系统主界面：
Imports System.Data.SqlClient
Imports System.IO
Public Class searchN
Inherits System.Web.UI.Page
Private connectingstring As String
Private myConn As SqlConnection
Private ds As DataSet
Private myAdapter As SqlDataAdapter
Protected 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.DropDownList
Protected WithEvents button1 As System.Web.UI.WebControls.Button
Private myCmd As SqlCommand
Public Sub Open()
myConn.Open()
End Sub
Public Sub Close()
myConn.Close()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
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)
If Not IsPostBack Then
If Not Session("UserID") Is Nothing Then
If AllowBooking(Session("UserID")) = True Then
End If
End If
End If
End Sub
'private bind
Public Function GetRowsNum(ByVal sqlstr As String) As Integer
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
Close()
Return ds.Tables(0).Rows.Count
End Function
Public 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 Then
Return False
Else
Return True
End If
End Function
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
myDBGrd.DataSource = ds.Tables(0).DefaultView
myDBGrd.DataBind()
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = SortExp
myDBGrd.DataSource = dv
myDBGrd.DataBind()
End Sub
Private Sub BindGrid()
If Not Session("sqlstr") Is Nothing Then
Dim sqlstr As String = CType(Session("sqlstr"), String)
BindDBGrd(sqlstr, ResultGrid)
Session("sqlstr") = sqlstr
End If
End Sub
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
Dim sign As String = ""
If AnyChoice.Checked = True Then
sign = "%"
End If
Dim sqlstr As String = "select * from BookInfo where 1=1 "
sqlstr += " and " + dropdownlist1.SelectedValue + " like '" + sign + txtContent.Text.ToString.Trim + sign + "' "
Session("sqlstr") = sqlstr
BindGrid()
End Sub
Private Sub ResultGrid_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles ResultGrid.PageIndexChanged
ResultGrid.CurrentPageIndex = e.NewPageIndex
BindGrid()
End Sub
Private Sub ResultGrid_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles ResultGrid.SortCommand
Dim sqlstr As String
If viewstate("sortexp") Is Nothing Then
viewstate("sortexp") = e.SortExpression.ToString
ElseIf viewstate("sortexp") = e.SortExpression.ToString Then
viewstate("sortexp") += " desc"
Else
viewstate("sortexp") = e.SortExpression.ToString
End If
If Not Session("sqlstr") Is Nothing Then
sqlstr = CType(Session("sqlstr"), String)
BindDBGrd(sqlstr, ResultGrid, Viewstate("sortexp"))
End If
End Sub
Public Sub ExecNonSql(ByVal sqlstr As String)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
myCmd = New SqlCommand(sqlstr, myConn)
myCmd.ExecuteNonQuery()
myCmd.Dispose()
Close()
End Sub
End Class
可以根据书名，内容进行查询，我们选取任意匹配：
如上图所示最上方为自定义用户控件：bar
点击权限维护：
Imports System.Data.SqlClient
Public Class user
Inherits System.Web.UI.Page
Private connectingstring As String
Private myConn As SqlConnection
Private ds As DataSet
Private myAdapter As SqlDataAdapter
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
Protected WithEvents Login_trname As System.Web.UI.HtmlControls.HtmlTableRow
Protected WithEvents Login_trpassword As System.Web.UI.HtmlControls.HtmlTableRow
Private 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 Sub
Public Function CheckUserberInfo(ByVal Userstr As String, ByVal Pwdstr As String) As Integer
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Dim sqlstr As String
If Pwdstr = "" Then
sqlstr = "select * from manage where manageid='" + Userstr.Trim + "' and Pass is null"
Else
sqlstr = "select * from manage where manageid='" + Userstr.Trim + "' and Pass='" + Pwdstr + "'"
End If
Fill(sqlstr)
If ds.Tables(0).Rows.Count = 0 Then
Close()
Return -1
End If
ds.Clear()
Close()
Return 1
End Function
Public Sub Open()
myConn.Open()
End Sub
Public Sub Close()
myConn.Close()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If CheckUserberInfo(TextBox1.Text, TextBox2.Text) = 1 Then
Session("UserID") = TextBox1.Text.ToString
Response.Redirect("delete.aspx")
Else
Response.Write("")
Response.Write("javascript:window.location='user.aspx'")
End If
End Sub
End Class
登陆成功后：
点击删除后：
再次查询时：
Imports System.Data.SqlClient
Public Class delete
Inherits System.Web.UI.Page
Dim myconn As SqlConnection
Private connectingstring As String
Private ds As DataSet
Private myAdapter As SqlDataAdapter
Private myCmd As SqlCommand
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
myDBGrd.DataSource = ds.Tables(0).DefaultView
myDBGrd.DataBind()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)
If myconn.State = ConnectionState.Closed Then
myconn.Open()
End If
Fill(sqlstr)
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = SortExp
myDBGrd.DataSource = dv
myDBGrd.DataBind()
End Sub
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)
If Not IsPostBack Then
Dim sqlstr As String = "select * from bookinfo "
BindDBGrd(sqlstr, DataGrid1)
Dim ds As DataSet = New DataSet
End If
End Sub
Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand
Dim sql As String
Dim getdata As SqlDataReader
Dim mycmd As SqlCommand
Dim uid As String = DataGrid1.Items(e.Item.ItemIndex).Cells(0).Text
sql = "delete from bookinfo where bookid='" + uid + "'"
mycmd = New SqlCommand(sql, myconn)
myconn.Open()
mycmd.ExecuteNonQuery()
myconn.Close()
mycmd.Dispose()
Response.Write("")
End Sub
End Class
点击用户登录后：
Imports System.Data.SqlClient
Imports System.IO
Public Class WebForm1
Inherits System.Web.UI.Page
Private connectingstring As String
Private myConn As SqlConnection
Private ds As DataSet
Private myAdapter As SqlDataAdapter
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Private 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 Sub
Public Function CheckUserberInfo(ByVal Userstr As String, ByVal Pwdstr As String) As Integer
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Dim sqlstr As String
If Pwdstr = "" Then
sqlstr = "select * from Users where UserID='" + Userstr.Trim + "' and Pass is null"
Else
sqlstr = "select * from Users where UserID='" + Userstr.Trim + "' and Pass='" + Pwdstr + "'"
End If
Fill(sqlstr)
If ds.Tables(0).Rows.Count = 0 Then
Close()
Return -1
End If
ds.Clear()
Close()
Return 1
End Function
Public Sub Open()
myConn.Open()
End Sub
Public Sub Close()
myConn.Close()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If CheckUserberInfo(TextBox1.Text, TextBox2.Text) = 1 Then
Session("UserID") = TextBox1.Text.ToString
Response.Redirect("userlist.aspx")
Else
Response.Write("")
Response.Write("javascript:window.location='Login.aspx'")
End If
End Sub
End Class
登录成功后：
Imports System.Data.SqlClient
Public Class userlist
Inherits System.Web.UI.Page
Dim myConn As SqlConnection
Private myCmd As SqlCommand
Private connectingstring As String
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)
If Session("UserID") = "" Then
Response.Redirect("Login.aspx")
Else
LoadUserInfo(Session("UserID"))
End If
End Sub
Public Function ExecReaderSql(ByVal sqlstr As String) As SqlDataReader
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
myCmd = New SqlCommand(sqlstr, myConn)
Dim reader As SqlDataReader
reader = myCmd.ExecuteReader
myCmd.Dispose()
Return reader
End Function
Public 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 Then
txtUserID.Text = myreader("UserID")
txtName.Text = myreader("username")
txtphone.Text = myreader("Phone")
txtaddress.Text = myreader("address")
txtEMail.Text = myreader("EMail")
End If
myConn.Close()
End Sub
End Class
点击查阅信息：
Imports System.Data.SqlClient
Public Class Lend
Inherits System.Web.UI.Page
Dim myConn As SqlConnection
Dim ds As DataSet
Private myAdapter As SqlDataAdapter
Private connectingstring As String
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)
BindDBGrid()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
myDBGrd.DataSource = ds.Tables(0).DefaultView
myDBGrd.DataBind()
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = SortExp
myDBGrd.DataSource = dv
myDBGrd.DataBind()
End Sub
Private Sub BindDBGrid()
Dim sqlstr As String = "select * from BookInfo,LendInfo where BookInfo.BookID=LendInfo.BookID and UserID='" + Session("UserID") + "'"
BindDBGrd(sqlstr, DataGrid1)
End Sub
End Class
点击图书借阅登记：
Imports System.Data.SqlClient
Public Class lendbook
Inherits System.Web.UI.Page
Private connectingstring As String
Private myConn As SqlConnection
Private ds As DataSet
Private myAdapter As SqlDataAdapter
Private 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)
If txtUserID.Text.ToString.Trim <> "" Then
End If
End Sub
Public Sub ExecNonSql(ByVal sqlstr As String)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
myCmd = New SqlCommand(sqlstr, myConn)
myCmd.ExecuteNonQuery()
myCmd.Dispose()
myConn.Close()
End Sub
Public Sub Fill(ByVal sqlstr As String)
myAdapter = New SqlDataAdapter(sqlstr, myConn)
ds = New DataSet
myAdapter.Fill(ds)
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
myDBGrd.DataSource = ds.Tables(0).DefaultView
myDBGrd.DataBind()
End Sub
Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = SortExp
myDBGrd.DataSource = dv
myDBGrd.DataBind()
End Sub
Private 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 Sub
Public Function ExecReaderSql(ByVal sqlstr As String) As SqlDataReader
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
myCmd = New SqlCommand(sqlstr, myConn)
Dim reader As SqlDataReader
reader = myCmd.ExecuteReader
myCmd.Dispose()
Return reader
End Function
Private Function GetBookID() As String
Dim 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 Then
BookID = myreader("BookID")
End If
myConn.Close()
Return BookID
End Function
Public 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 = 0
If myreader.Read Then
num = myreader("Num")
End If
myConn.Close()
Return num
End Function
Public Function GetRowsNum(ByVal sqlstr As String) As Integer
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Fill(sqlstr)
myConn.Close()
Return ds.Tables(0).Rows.Count
End Function
Public Function GetBookState(ByVal BookID As String) As Integer
Dim sqlstr As String = "select * from BookInfo where BookState=1 and BookID=" + BookID
If GetRowsNum(sqlstr) = 0 Then
Return -2
End If
sqlstr = "select * from LendInfo where IsBack=0 and BookID =" + BookID
If GetRowsNum(sqlstr) <> 0 Then
Return -1
End If
sqlstr = "select * from BookingInfo where BookID=" + BookID
If GetRowsNum(sqlstr) <> 0 Then
Return 0
End If
Return 1
End Function
Public 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 = 0
If myreader.Read Then
num = myreader("BookDate")
End If
myConn.Close()
Return num
End Function
Public Function LendBook(ByVal UserID As String, ByVal BookID As String) As Boolean
Dim datenum As Integer = GetBookDate(UserID)
Dim nowdate As Date = Date.Now
Dim ReturnDate As Date = nowdate.AddDays(datenum)
Dim sqlstr As String = "insert into LendInfo(BookID,UserID,LendDate,ReturnDate,IsBack)values( "
sqlstr += "'" + BookID + "'"
sqlstr += ",'" + UserID + "'"
sqlstr += ",'" + nowdate.ToShortDateString + "'"
sqlstr += ",'" + ReturnDate.ToShortDateString + "'"
sqlstr += ",0)"
ExecNonSql(sqlstr)
Return True
End Function
Public Function GetMax(ByVal UserID As String) As Integer
Dim sqlstr As String = "select BookNum from Users where UserID='" + UserID + "'"
Dim myreader As SqlDataReader = ExecReaderSql(sqlstr)
Dim num As Integer = 0
If myreader.Read Then
num = myreader("BookNum")
End If
myConn.Close()
Return num
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim str As String = ""
Dim BookID As String = GetBookID()
If GetCurNum(txtUserID.Text) >= GetMax(txtUserID.Text.ToString) Then
Response.Write("")
Return
End If
Dim state As Integer = GetBookState(BookID)
If state = 1 Then
LendBook(txtUserID.Text.ToString.Trim, BookID)
Else
Select Case state
Case -2 : Response.Write("")
Case -1 : Response.Write("")
Case 0 : Response.Write("")
End Select
End If
BindDBGrid()
End Sub
End Class
七、 总结
这次课程设计用.NET来编写的，通过这次实践让我更进一步了解了.NET的基本的脚本的写法，虽然不是很熟练，但是还是知道不少，对SQL中数据库中基本操作也应用了不少，相信这次训练是受益非浅的。
