dbcontroller.vb

图书借阅系统：建立一个图书管理系统

Imports System.Data.SqlClient
Imports System.IO
Public Class DBController
    Private connectingstring As String
    Private myConn As SqlConnection
    Private ds As DataSet
    Private myAdapter As SqlDataAdapter
    Private myCmd As SqlCommand
    Public Sub New()
        connectingstring = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString").ToString.Trim
        myConn = New SqlConnection(connectingstring)
        'Close()
    End Sub
    '打开关闭连接
    Public Sub Open()
        myConn.Open()
    End Sub
    Public Sub Close()
        myConn.Close()
    End Sub
    Public Function GetDS() As DataSet
        Return ds
    End Function
    '=============================================================
    ' 函 数 名：Fill
    ' 功能描述：填充ds
    ' 输入参数：sqlstr，SQL字符串
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    Public Sub Fill(ByVal sqlstr As String)
        myAdapter = New SqlDataAdapter(sqlstr, myConn)
        ds = New DataSet()
        myAdapter.Fill(ds)
    End Sub
    '=============================================================
    ' 函 数 名：ExecNonSql
    ' 功能描述：执行无返回值的数据库操作
    ' 输入参数：sqlstr，查询的SQL字符串
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    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
    '=============================================================
    ' 函 数 名：ExecReaderSql
    ' 功能描述：执行查询操作
    ' 输入参数：sqlstr，查询的SQL字符串
    ' 返 回 值：查询结果，返回SqlDataReader对象
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    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
    '=============================================================
    ' 函 数 名：BindDBGrd
    ' 功能描述：对DataGrid进行数据绑定，无排序
    ' 输入参数：sqlstr，查询的SQL字符串；myDBGrd，需数据绑定的DataGrid控件
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    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
    '=============================================================
    ' 函 数 名：BindDBGrd
    ' 功能描述：对DataGrid进行数据绑定，排序
    ' 输入参数：sqlstr，查询的SQL字符串；myDBGrd，需数据绑定的DataGrid控件
    '           SortExp：排序方式
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    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
    '=============================================================
    ' 函 数 名：BindListBox
    ' 功能描述：对ListBox中进行绑定
    ' 输入参数：sqlstr，查询的SQL字符串；myListBox：需数据绑定的ListBox控件
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================
    Public Sub BindListBox(ByVal sqlstr As String, ByVal myListBox As ListBox)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        Dim dv As DataView = ds.Tables(0).DefaultView
        myListBox.DataSource = dv
        myListBox.DataBind()
    End Sub
 
    '=============================================================
    ' 函 数 名：BindDrpDownList
    ' 功能描述：对DropDownList进行数据绑定
    ' 输入参数：sqlstr，查询的SQL字符串；DrpDownList：需数据绑定的DropDownList控件
    ' 返 回 值：无
    ' 创建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加说明：
    '==============================================================

    Public Sub BindDrpDownList(ByVal sqlstr As String, ByVal DrpDownList As DropDownList)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        DrpDownList.DataSource = ds.Tables(0).DefaultView
        DrpDownList.DataBind()
        Close()
    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 UserInfo where UserID='" + Userstr.Trim + "' and Pass is null"
        Else
            sqlstr = "select * from UserInfo 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
    ' 功能描述：获取SQL查询记录条数
    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

End Class
Public Class BookController
    Private obj As DBController
    Public Sub New()
        obj = New DBController
    End Sub
    '获取用户最大的借书量
    Public Function GetMax(ByVal UserID As String) As Integer
        Dim sqlstr As String = "select BookNum from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID"

        Dim myreader As SqlDataReader = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("BookNum")
        End If
        obj.Close()
        Return num
    End Function
    '获取该用户允许借阅时间
    Public Function GetBookDate(ByVal UserID As String) As Integer
        Dim sqlstr As String = "select BookDate from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and UserID='" + UserID + "'"

        Dim myreader As SqlDataReader = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("BookDate")
        End If
        obj.Close()
        Return num
    End Function
    '判断当前书的状态
    '-2：表示该书不存在或不在流通中
    '-1：表示被借
    '0：已经被续借状态
    '1：可借
    Public Function GetBookState(ByVal BookID As String) As Integer

        Dim sqlstr As String = "select * from BookInfo where BookState=1 and  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return -2
        End If

        sqlstr = "select * from LendInfo where IsBack=0 and BookID =" + BookID
        If obj.GetRowsNum(sqlstr) <> 0 Then
            Return -1
        End If

        sqlstr = "select * from BookingInfo where  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) <> 0 Then
            Return 0
        End If

        Return 1

    End Function
    '判断用户是否预定该本书
    Public Function IsUserBooking(ByVal UserID As String, ByVal BookID As String) As Boolean

        Dim sqlstr As String = "select * from BookingInfo where UserID='" + UserID + "' and  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    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 = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("Num")
        End If
        obj.Close()
        Return num
    End Function
    '进行借书操作，如果借书成功，返回true，否则返回false
    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)"
        obj.ExecNonSql(sqlstr)
        If IsUserBooking(UserID, BookID) Then
            sqlstr = "delete from  BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'"
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '取消预约书信息
    Public Function CancelBooking(ByVal UserID As String, ByVal BookID As String) As Boolean
        If IsUserBooking(UserID, BookID) Then
            Dim sqlstr As String = "delete from  BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'"
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '续借图书
    Public Function ReLendBook(ByVal UserID As String, ByVal BookID As String) As Boolean
        Dim bookdate As Integer = GetBookDate(UserID)
        Dim sqlstr As String = "update LendInfo set ReturnDate=ReturnDate+" + bookdate.ToString + " where IsBack=0 and BookID=" + BookID + " and UserID='" + UserID + "'"
        obj.ExecNonSql(sqlstr)
        Return True
    End Function
    '还书操作
    Public Function ReturnBook(ByVal UserID As String, ByVal BookID As String) As Boolean
        Dim sqlstr As String = "update LendInfo set IsBack=1 where BookID=" + BookID + " and UserID='" + UserID + "'"
        obj.ExecNonSql(sqlstr)
        '修改预约状态
        If Me.GetBookState(BookID) = 0 Then
            sqlstr = "update BookingInfo set BookingState=1 where BookID=" + BookID
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '预约图书信息
    Public Sub BookingBook(ByVal userID As String, ByVal BookID As String)
        Dim sqlstr As String = "insert into BookingInfo(BookID,UserID,BookingState)values( "
        sqlstr += BookID + ",'" + userID + "',0)"
    End Sub
    '是否允许预约
    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 obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否允许续借
    Public Function AllowReLend(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and ReLend=1  and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否为图书管理员
    Public Function IsBookManager(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsBookManager=1 and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否为系统管理员
    Public Function IsSysManager(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsSysManager=1  and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

End Class