点击查阅信息：
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("<script>javascript:alert('借书数已满！！！');</script>")
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("<script>javascript:alert('此书不在流通状态或此书不存在！！！');</script>")
Case -1 : Response.Write("<script>javascript:alert('此书不在书架上！！！');</script>")
Case 0 : Response.Write("<script>javascript:alert('此书已经有人预约！！！');</script>")
End Select
End If
BindDBGrid()
End Sub
End Class
七、 总结
这次课程设计用.NET来编写的，通过这次实践让我更进一步了解了.NET的基本的脚本的写法，虽然不是很熟练，但是还是知道不少，对SQL中数据库中基本操作也应用了不少，相信这次训练是受益非浅的。