datausers.vb

电子商务买书的网站

Imports System.Data
Imports System.Data.OleDb
'------------------------------------------------------------------------------------------------------
'第一个类用来查询、添加、删除和更新记录
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(ByVal strUserId As String, ByVal strRoleId As String, ByVal strUserPWD As String, ByVal strUserName As String, ByVal strSex As String, ByVal strTel As String, ByVal strEmail As String, ByVal strQQ As String, ByVal strIntro As String, ByVal dtmSubmitDate As Date, ByVal strPostalAddress As String, ByVal 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(ByVal 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(ByVal strUserId As String, ByVal strUserName As String, ByVal strSex As String, ByVal strTel As String, ByVal strEmail As String, ByVal strQQ As String, ByVal strIntro As String, ByVal strPostalAddress As String, ByVal 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(ByVal strUserId As String, ByVal strOldPWD As String, ByVal 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(ByVal 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(ByVal strUserId As String, ByVal 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(ByVal 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(ByVal 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