网站导航免费论文 原创论文 论文搜索 原创论文 网学软件 学术大家 资料中心 会员中心 问题解答 原创论文 大学论文导航 设计下载 最新论文 下载排行 原创论文 设计图标 Ppt模板 Flash素材 PSD素材 JS代码
返回网学首页
网学联系
最新论文 推荐专题 热门论文 素材专题
当前位置: 网学 > 交易代码 > 课程设计 > 正文

.NET+SQL SERVER 2000图书管理系统

来源:http://myeducs.cn 联系QQ:点击这里给我发消息 作者: 用户投稿 发布时间: 09/06/28
摘  要
   
图书管理系统是典型的信息管理系统(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中数据库中基本操作也应用了不少,相信这次训练是受益非浅的。
  • 下一篇资讯: delphi图书仓库管理系统
  • 网学推荐

    免费论文

    原创论文

    设为首页 | 加入收藏 | 论文首页 | 论文专题 | 设计下载 | 网学软件 | 论文模板 | 论文资源 | 程序设计 | 关于网学 | 站内搜索 | 网学留言 | 友情链接 | 资料中心
    版权所有 QQ:3710167 邮箱:3710167@qq.com 网学网 [Myeducs.cn] 您电脑的分辨率是 像素
    Copyright 2008-2015 myeducs.Cn www.myeducs.Cn All Rights Reserved 湘ICP备09003080号 常年法律顾问:王律师