datausers.vb

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

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

NameSpace nsChat													'nsChat为自己定义的名称空间的名称

'该类用来查询、添加、删除和更新记录
Public Class DataUsers												'DataUsers是自己定义的类的名称

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

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

	'该函数用来返回指定房间所有在线人员
	Public Function GetUser(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(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(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(strUserId As String,strIP As String,strChatRoom As String,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(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

End NameSpace