datausers.vb

asp.net程序设计实用教程(C#描述) 作者：石志国

'******************************************************************************************************
'这里定义了若干个类，用于完成用户的有关操作
'DataUsers      类， 用来查询、添加、删除和更新用户信息
'DataUser       类， 主要用来返回一个用户的具体信息，和对该用户的一些操作
'DataUserLog    类， 判断用户是否已经登录，是否具有一定的权限
'*****************************************************************************************************

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

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

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

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

	'定义一个只读属性，用来返回普通用户人数
	Public  ReadOnly Property Count As Integer                
		Get
			Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
			Dim strSql As String="Select Count(*) As total From Users Where RoleId<>'A'"
			Dim cmd As New OleDbCommand(strSql, conn)					'建立Command对象
			conn.Open
			Dim intCount As Integer=cmd.ExecuteScalar()					'返回第一行第一列的记录值，也就是总数
			conn.Close
			Return(intCount)
		End Get
	End Property

	'构造函数，用于读取数据库连接字符串
	Public Sub New()
		_strConn=ConfigurationSettings.AppSettings("strConn")		'将数据库连接字符串赋值给私有变量_strConn
	End Sub

	'该函数用来返回所有普通用户
	Public Function GetUser() As DataView
		Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
		Dim strSql As String="Select * From Users Where RoleId<>'A'"
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "Users")										
		Return ds.Tables("Users").DefaultView						'返回函数值
	End Function


	'该函数用来添加用户，返回True表示已经添加
	Public Function InsertUser(strUserId As String,strRoleId As String,strUserPWD As String,strUserName As String,strSex As String, strTel As String,strEmail As String,strQQ As String,strIntro As String, dtmSubmitDate As Date,strPostalAddress As String,strPostalCode As String) As Boolean
		'首先调用方法判断是否已经存在，已经存在就不能添加
		If IsExist(strUserId)=True Then
			Return(False)
		Else
			Dim exp As Exception
			Try
				Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
				'建立Command对象，注意这里使用了含有参数的SQL语句
				Dim strSql="Insert Into Users(UserId,RoleId,UserPWD,UserName,Sex,Tel,Email,QQ,Intro,SubmitDate,TotalNum,TotalMoney,PostalAddress,PostalCode) Values(@UserId,@RoleId,@UserPWD,@UserName,@Sex,@Tel,@Email,@QQ,@Intro,@SubmitDate,0,0,@PostalAddress,@PostalCode)"
				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("@RoleId",OleDbType.VarWChar,1))
				cmd.Parameters("@RoleId").Value=strRoleId
				cmd.Parameters.Add(New OleDbParameter("@UserPWD",OleDbType.VarWChar,20))
				cmd.Parameters("@UserPWD").Value=strUserPWD
				cmd.Parameters.Add(New OleDbParameter("@UserName",OleDbType.VarWChar,30))
				cmd.Parameters("@UserName").Value=strUserName
				cmd.Parameters.Add(New OleDbParameter("@Sex",OleDbType.VarWChar,2))
				cmd.Parameters("@Sex").Value=strSex
				cmd.Parameters.Add(New OleDbParameter("@Tel",OleDbType.VarWChar,30))
				If strTel<>"" Then
					cmd.Parameters("@Tel").Value=strTel
				Else
					cmd.Parameters("@Tel").Value=DBNULL.Value
				End If
				cmd.Parameters.Add(New OleDbParameter("@Email",OleDbType.VarWChar,100))
				cmd.Parameters("@Email").Value=strEmail
				cmd.Parameters.Add(New OleDbParameter("@QQ",OleDbType.VarWChar,100))
				If strQQ<>"" Then
					cmd.Parameters("@QQ").Value=strQQ
				Else
					cmd.Parameters("@QQ").Value=DBNULL.Value
				End If
				cmd.Parameters.Add(New OleDbParameter("@Intro",OleDbType.VarWChar,1000))
				If strIntro<>"" Then
					cmd.Parameters("@Intro").Value=strIntro
				Else
					cmd.Parameters("@Intro").Value=DBNULL.Value
				End If
				cmd.Parameters.Add(New OleDbParameter("@SubmitDate",OleDbType.Date))
				cmd.Parameters("@SubmitDate").Value=dtmSubmitDate
				cmd.Parameters.Add(New OleDbParameter("@PostalAddress",OleDbType.VarWChar,255))
				cmd.Parameters("@PostalAddress").Value=strPostalAddress
				cmd.Parameters.Add(New OleDbParameter("@PostalCode",OleDbType.VarChar,6))
				cmd.Parameters("@PostalCode").Value=strPostalCode
				'下面执行插入记录操作
				conn.open()                             
				cmd.ExecuteNonQuery()					
				conn.close() 
				Return(True)
			Catch exp
				Return(False)
			End Try
		End If
	End Function


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

	'该过程用来更新用户个人信息
	Public Sub UpdateUserInfo(strUserId As String,strUserName As String,strSex As String, strTel As String,strEmail As String,strQQ As String,strIntro As String,strPostalAddress As String,strPostalCode As String)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
		'建立Command对象，注意这里使用了含有参数的SQL语句
		Dim strSql="Update Users Set UserName=@UserName,Sex=@Sex,Tel=@Tel,Email=@Email,QQ=@QQ,Intro=@Intro,PostalAddress=@PostalAddress,PostalCode=@PostalCode Where UserId=@UserId"
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
			cmd.Parameters.Add(New OleDbParameter("@UserName",OleDbType.VarWChar,30))
			cmd.Parameters("@UserName").Value=strUserName
			cmd.Parameters.Add(New OleDbParameter("@Sex",OleDbType.VarWChar,2))
			cmd.Parameters("@Sex").Value=strSex
			cmd.Parameters.Add(New OleDbParameter("@Tel",OleDbType.VarWChar,30))
			If strTel<>"" Then
				cmd.Parameters("@Tel").Value=strTel
			Else
				cmd.Parameters("@Tel").Value=DBNULL.Value
			End If
			cmd.Parameters.Add(New OleDbParameter("@Email",OleDbType.VarWChar,100))
			cmd.Parameters("@Email").Value=strEmail
			cmd.Parameters.Add(New OleDbParameter("@QQ",OleDbType.VarWChar,100))
			If strQQ<>"" Then
				cmd.Parameters("@QQ").Value=strQQ
			Else
				cmd.Parameters("@QQ").Value=DBNULL.Value
			End If
			cmd.Parameters.Add(New OleDbParameter("@Intro",OleDbType.VarWChar,1000))
			If strIntro<>"" Then
				cmd.Parameters("@Intro").Value=strIntro
			Else
				cmd.Parameters("@Intro").Value=DBNULL.Value
			End If
			cmd.Parameters.Add(New OleDbParameter("@PostalAddress",OleDbType.VarWChar,255))
			cmd.Parameters("@PostalAddress").Value=strPostalAddress
			cmd.Parameters.Add(New OleDbParameter("@PostalCode",OleDbType.VarChar,6))
			cmd.Parameters("@PostalCode").Value=strPostalCode
			cmd.Parameters.Add(New OleDbParameter("@UserId",OleDbType.VarWChar,20))
			cmd.Parameters("@UserId").Value=strUserId
		'下面执行操作
		conn.open()                             
		cmd.ExecuteNonQuery()					
		conn.close() 
	End Sub

	'该函数用来更改密码，如果旧密码正确，就更新，返回True，否则不更新，返回False
	Public Function UpdatePWD(strUserId As String,strOldPWD As String,strNewPWD As String) As Boolean
		'调用上面的函数判断输入的旧密码是否正确，如正确，才更新
		If IsPassed(strUserId,strOldPWD)=True Then
			Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
			'建立Command对象，注意这里使用了含有参数的SQL语句
			Dim strSql="Update Users Set UserPWD='" & strNewPWD & "' Where UserId='" & strUserId & "'"
			Dim cmd As New OleDbCommand(strSql, conn)
			'下面执行操作
			conn.open()                             
			cmd.ExecuteNonQuery()					
			conn.close() 
			Return(True)
		Else
			Return(False)
		End If
	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 Users Where UserId='" & strUserId & "'"
		Dim cmd As New OleDbCommand(strSql, conn)					'建立Command对象
		conn.Open
		Dim intTotal As Integer=cmd.ExecuteScalar()
		conn.Close
		If intTotal>0 Then											'该条件表示该用户名存在
			Return(True)
		Else
			Return(False)
		End If
	End Function

	'该函数用来判断用户名和密码是否正确，如果正确，返回True，否则返回False
	Public Function IsPassed(strUserId As String,strUserPWD As String) As Boolean
		Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
		Dim strSql As String="Select Count(*) As total From Users Where UserId='" & strUserId & "' and UserPWD='" & strUserPWD & "'"
		Dim cmd As New OleDbCommand(strSql, conn)					'建立Command对象
		conn.Open
		Dim intTotal As Integer=cmd.ExecuteScalar()
		conn.Close
		If intTotal>0 Then											'该条件表示输入的用户名和密码正确
			Return(True)
		Else
			Return(False)
		End If
	End Function


	'下面的函数用来根据用户名UserId返回一个DataUser对象,就是下面定义的第2个类
	Public Function Items(strUserId As String) As DataUser
		'下面建立DataUser对象
		Dim du As New DataUser(strUserId)							'注意这里的传入参数
		Return(du)													'返回函数值
	End Function
End Class

'------------------------------------------------------------------------------------------------------
'第2个类仅仅用来返回一条记录的各个字段值
Public Class DataUser														'DataUser是自己定义的类的名称

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

	'下面定义了几个属性，用来返回对应字段值
	Public UserId As String
	Public RoleId As String
	Public UserPWD As String
	Public UserName As String
	Public Sex As String
	Public Tel As String
	Public Email As String
	Public QQ As String
	Public Intro As String
	Public SubmitDate As Date
	Public TotalNum As Integer
	Public TotalMoney As Integer
	Public PostalAddress As String
	Public PostalCode As String
	
	'这是构造函数，在其中给每一个属性赋值
	Public Sub New(strUserId As String)
		_strConn=ConfigurationSettings.AppSettings("strConn")				'将数据库连接字符串赋值给私有变量_strConn
		Dim conn As New OleDbConnection(_strConn)							'建立Connection对象
		Dim strSql As String="Select * From users Where UserId='" & strUserId & "'"		'建立SQL语句
		Dim cmd As New OleDbCommand(strSql, conn)							'建立Command对象
		conn.Open()                                            
		Dim dr As OleDbDataReader = cmd.ExecuteReader()						'建立DataReader对象
		'下面读取数据，并给属性赋值
		dr.Read()															
		UserId=dr.Item("UserId")													
		RoleId=dr.Item("RoleId")
		UserPWD=dr.Item("UserPWD")
		UserName=dr.Item("UserName").ToString()
		Sex=dr.Item("Sex")
		Tel=dr.Item("Tel").ToString()										'因为可能是空值，所以加一个ToString
		Email=dr.Item("Email").ToString()
		QQ=dr.Item("QQ").ToString()
		Intro=dr.Item("Intro").ToString()
		SubmitDate=dr.Item("SubmitDate")
		TotalNum=dr.Item("TotalNum")
		TotalMoney=dr.Item("TotalMoney")
		PostalAddress=dr.Item("PostalAddress").ToString()
		PostalCode=dr.Item("PostalCode").ToString()
		conn.Close()                                              
	End Sub

End Class
'-------------------------------------------------------------------
'第3个类，用来判断该用户是否已经登录，是否具有适当的权限
Public Class DataUserLog
	'下面是一个共享方法，用来判断该用户是否是普通用户？
	Public Shared Function IsNormal() As Boolean
		If (Not HttpContext.Current.Session("UserId") Is Nothing) And  HttpContext.Current.Session("RoleId")="N"  Then
			Return(True)
		Else	
		    Return(False)
		End If
	End Function

	'下面是一个共享方法，判断该用户是否是管理员？
	Public Shared Function IsAdmin() As Boolean
		If (Not HttpContext.Current.Session("UserId") Is Nothing) And  HttpContext.Current.Session("RoleId")="A" Then
			Return(True)
		Else	
		    Return(False)
		End If
	End Function

End Class

End NameSpace