datausers.vb

《ASP.NET C#程序设计案例教程》

Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration          '因为用到了ConfigurationSettings类

Public Class DataUsers
    Private _strConn As String          '定义一个私有变量，用来设置数据库连接字符串

    Public Sub New()
        _strConn = ConfigurationSettings.AppSettings("strConn") '将数据库连接字符串赋值给私有变量_strConn
    End Sub

    '该函数用来返回指定房间所有在线人员
    Public Function GetUser(ByVal strChatRoom As String) As DataView
        Dim conn As New OleDbConnection(_strConn)     '这里使用了_strConn表示的数据库连接字符串
        Dim strSql As String = "Select * From OnlineUsers Where ChatRoom='" & strChatRoom & "'"
        Dim adp As New OleDbDataAdapter(strSql, conn)
        Dim ds As New DataSet
        adp.Fill(ds, "OnlineUsers")
        Return (ds.Tables("OnlineUsers").DefaultView)   '返回函数值
    End Function

    '该函数用来返回指定房间的在线人数
    Public Function Count(ByVal strChatRoom As String) As Integer
        Dim conn As New OleDbConnection(_strConn)     '这里使用了_strConn表示的数据库连接字符串
        Dim strSql As String = "Select Count(*) As total From OnlineUsers Where ChatRoom='" & strChatRoom & "' And UserId<>'大家'"
        Dim cmd As New OleDbCommand(strSql, conn)     '建立Command对象
        conn.Open()
        Dim intCount As Integer = cmd.ExecuteScalar()   '返回第一行第一列的记录值，也就是总数
        conn.Close()
        Return (intCount)
    End Function

    '该函数用来查找该用户名是否已经存在，如果存在，返回True，否则返回False
    Public Function IsExist(ByVal strUserId As String) As Boolean
        Dim conn As New OleDbConnection(_strConn)     '这里使用了_strConn表示的数据库连接字符串
        Dim strSql As String = "Select Count(*) As total From OnlineUsers Where UserId='" & strUserId & "'"
        Dim cmd As New OleDbCommand(strSql, conn)     '建立Command对象
        conn.Open()
        Dim intTotal As Integer = cmd.ExecuteScalar()   'cmd.ExecuteScalar会返回第1行第1列的值，也就是total的值。
        conn.Close()
        If intTotal > 0 Then
            Return (True)
        Else
            Return (False)
        End If
    End Function

    '该函数用来插入记录，返回True表示已经插入
    Public Function InsertUser(ByVal strUserId As String, ByVal strIP As String, ByVal strChatRoom As String, ByVal dtmLogTime As Date) As Boolean
        '首先调用上面的方法判断是否已经存在，已经存在就不能插入
        If IsExist(strUserId) = True Then
            Return (False)
        Else
            Dim conn As New OleDbConnection(_strConn)     '使用了私有变量的值
            '建立Command对象，注意这里使用了含有参数的SQL语句
            Dim strSql = "Insert Into OnlineUsers(UserId,IP,ChatRoom,LogTime) Values(@UserId,@IP,@ChatRoom,@LogTime)"
            Dim cmd As New OleDbCommand(strSql, conn)
            '下面给参数赋值
            cmd.Parameters.Add(New OleDbParameter("@UserId", OleDbType.VarWChar, 20))
            cmd.Parameters("@UserId").Value = strUserId
            cmd.Parameters.Add(New OleDbParameter("@IP", OleDbType.VarWChar, 15))
            cmd.Parameters("@IP").Value = strIP
            cmd.Parameters.Add(New OleDbParameter("@ChatRoom", OleDbType.VarWChar, 4))
            cmd.Parameters("@ChatRoom").Value = strChatRoom
            cmd.Parameters.Add(New OleDbParameter("@LogTime", OleDbType.Date))
            cmd.Parameters("@LogTime").Value = dtmLogTime
            '下面执行插入记录操作
            conn.open()
            cmd.ExecuteNonQuery()
            conn.close()
            Return (True)
        End If
    End Function

    '该过程用来删除记录，传入参数为用户名
    Public Sub DeleteUser(ByVal strUserId As String)
        Dim conn As New OleDbConnection(_strConn)
        Dim strSql = "Delete from OnlineUsers Where UserId='" & strUserId & "'"
        Dim cmd As New OleDbCommand(strSql, conn)
        conn.Open()
        cmd.ExecuteNonQuery()           '删除记录
        conn.Close()
    End Sub

    '该过程用来清空列表，删除”大家“除外的所有人
    Public Sub DeleteAllUser()
        Dim conn As New OleDbConnection(_strConn)
        Dim strSql = "Delete from OnlineUsers Where UserId<>'大家'"
        Dim cmd As New OleDbCommand(strSql, conn)
        conn.Open()
        cmd.ExecuteNonQuery()           '删除记录
        conn.Close()
    End Sub

End Class