users.cls

Oracle 10g数据库Java开发 源码

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Users"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1   UserId  Number  用户编号
'2   UserName    Varchar2 40 用户名
'3   UserType    Number 1    用户类型（1  表示管理员用户，2  表示普通用户）
'4   Pwd Varchar2 40 密码
'5   EmpName Varchar2 40 使用用户的员工姓名

Public UserId As Integer
Public UserName As String
Public UserType As Integer
Public Pwd As String
Public EmpName As String

Public Sub Init()
  UserId = 0
  UserName = ""
  UserType = 0
  Pwd = ""
  EmpName = ""
End Sub

'删除Users数据
Public Sub Delete(ByVal TmpId As Integer)
  '如果编号为1，则不能删除Admin用户
  If TmpId = 1 Then
    Exit Sub
  End If
  
  SQLstmt = "DELETE FROM Users WHERE UserId=" + Trim(TmpId)
  SQLExt (SQLstmt)
  
  '记录日志
  With MyLog
  .Title = "删除用户记录"
  .Body = "用户编号:" + Trim(TmpId)
  .LogType = 2
  .Save
  End With
End Sub

Public Function GetInfo(ByVal TmpUser As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  '设置SELECT语句，读取编号为TmpId的记录
  SQLstmt = "SELECT * FROM Users WHERE UserName='" + Trim(TmpUser) + "'"
  '将结果集读取到rs中
  Set rs = QueryExt(SQLstmt)

  If rs.EOF = True Then
    '如果结果集为空，则初始化
    Init
    GetInfo = False
  Else
    '将结果集中的数据赋值到成员变量中
    '用户编号
    If IsNull(rs.Fields(0)) Then
      UserId = 0
    Else
      UserId = rs.Fields(0)
    End If
    '用户名
    If IsNull(rs.Fields(1)) Then
      UserName = ""
    Else
      UserName = rs.Fields(1)
    End If
    '用户类型
    If IsNull(rs.Fields(2)) Then
      UserType = 0
    Else
      UserType = rs.Fields(2)
    End If
    '密码
    If IsNull(rs.Fields(3)) Then
      Pwd = ""
    Else
      Pwd = rs.Fields(3)
    End If
    '员工姓名
    If IsNull(rs.Fields(4)) Then
      EmpName = ""
    Else
      EmpName = rs.Fields(4)
    End If
    GetInfo = True
  End If
End Function

Public Function GetPwd(ByVal TmpUser As String) As String
  '连接数据库的Connection对象
  Dim cnn1 As ADODB.Connection
  '创建Command对象cmd
  Dim cmd As New ADODB.Command
  '========连接到数据库========
  '关键New用于创建新对象cnn
  Set cnn1 = New ADODB.Connection
  '设置连接字符串ConnectionString属性
  cnn1.ConnectionString = "DSN=" + DSN + ";UID=" + DB_USER_NAME _
                       + ";PWD=" + DB_PASSWORD + ";"
  '打开到数据库的连接
  cnn1.Open
  '判断连接的状态
  If cnn1.State <> adStateOpen Then
    '如果连接不成功，则显示提示信息，退出程序
    MsgBox "数据库连接失败"
    End
  End If
  '设置cmd的ActiveConnection属性，指定与其关联的数据库连接
  Set cmd.ActiveConnection = cnn1
  '设置命令的类型为存储过程
  cmd.CommandType = adCmdStoredProc
  '设置要执行的存储过程
  cmd.CommandText = "GetPwd"
  '添加参数
  cmd.Parameters.Append cmd.CreateParameter("varUserName", adVarChar, adParamInput, 40, TmpUser)
  cmd.Parameters.Append cmd.CreateParameter("varPwd", adVarChar, adParamOutput, 40)
  '执行命令
  cmd.Execute
  '取出OUT参数值
  GetPwd = cmd("varpwd")
  '断开连接
  cnn1.Close
  '清空cnn1对象
  Set cnn1 = Nothing
  '清空cmd对象
  Set cmd = Nothing
End Function

Public Function In_DB(ByVal TmpUser As String) As Boolean
  Dim rs As New ADODB.Recordset
  '设置SELECT语句，读取用户名为TmpUser的记录
  SQLstmt = "SELECT * FROM Users WHERE UserName='" + Trim(TmpUser) + "'"
  '将结果集读取到rs中
  Set rs = QueryExt(SQLstmt)
  If rs.EOF = True Then
    '如果结果集为空，则表示用户名为TmpUser的记录不存在
    In_DB = False
  Else
    '否则，则表示用户名为TmpUser的记录存在
    In_DB = True
  End If
End Function

Public Sub Insert()
  SQLstmt = "INSERT INTO Users Values(USERID.NEXTVAL, '" _
          + Trim(UserName) + "'," + Trim(UserType) + ",'" _
          + Trim(Pwd) + "','" + Trim(EmpName) + "')"
  SQLExt (SQLstmt)

  '记录日志
  With MyLog
  .Title = "插入用户记录"
  .Body = "用户名:" + Trim(UserName)
  .LogType = 2
  .Save
  End With
End Sub

Public Sub Update(ByVal TmpId As Integer)
  SQLstmt = "Update Users Set UserName='" + Trim(UserName) _
          + "',  UserType=" + Trim(UserType) + ", Pwd='" _
          + Trim(Pwd) + "', EmpName='" + Trim(EmpName) _
          + "' WHERE UserId=" + Trim(TmpId)
  SQLExt (SQLstmt)

  '记录日志
  With MyLog
  .Title = "修改用户记录"
  .Body = "用户编号:" + Trim(TmpId)
  .LogType = 2
  .Save
  End With
End Sub