Class Object Users

Public Class UsersObj

Private m_UserID As String
Private m_UserName As String
Private m_Password As String
Private m_UserGroupID As Int32
Private m_LastTime As DateTime
Private m_LastUser As String

Public Sub New()

End Sub

Public Property UserID() As String
Get
Return m_UserID
End Get
Set(ByVal value As String)
m_UserID = value
End Set
End Property

Public Property UserName() As String
Get
Return m_UserName
End Get
Set(ByVal value As String)
m_UserName = value
End Set
End Property

Public Property Password() As String
Get
Return m_Password
End Get
Set(ByVal value As String)
m_Password = value
End Set
End Property

Public Property UserGroupID() As Int32
Get
Return m_UserGroupID
End Get
Set(ByVal value As Int32)
m_UserGroupID = value
End Set
End Property

Public Property LastTime() As DateTime
Get
Return m_LastTime
End Get
Set(ByVal value As DateTime)
m_LastTime = value
End Set
End Property

Public Property LastUser() As String
Get
Return m_LastUser
End Get
Set(ByVal value As String)
m_LastUser = value
End Set
End Property

End Class

Class DBFunction untuk Users

Imports System.Data.SqlClient

Public Class UsersDB

Public Shared Function Select_Record(ByVal userid As String,ByVal password As String) As UsersObj
Dim dbo_users As New UsersObj
Dim connection As SqlConnection = PSCDATDB.GetConnection
Dim selectStatement As String _
= "SELECT * FROM [dbo].[Users] " _
& "WHERE [UserID] = @UserID and [Password]=@Password"
Dim selectCommand As New SqlCommand(selectStatement, connection)
selectCommand.CommandType = CommandType.Text
selectCommand.Parameters.AddWithValue("@UserID", userid)
selectCommand.Parameters.AddWithValue("@Password", password)
Try
connection.Open()
Dim reader As SqlDataReader _
= selectCommand.ExecuteReader(CommandBehavior.SingleRow)
If reader.Read Then
dbo_users.UserID = System.Convert.ToString(IIf(reader("UserID") Is DBNull.Value, "", reader("UserID")))
dbo_users.UserName = System.Convert.ToString(IIf(reader("UserName") Is DBNull.Value, "", reader("UserName")))
dbo_users.Password = System.Convert.ToString(IIf(reader("Password") Is DBNull.Value, "", reader("Password")))
dbo_users.UserGroupID = System.Convert.ToInt32(IIf(reader("UserGroupID") Is DBNull.Value, 0, reader("UserGroupID")))
dbo_users.LastTime = System.Convert.ToDateTime(IIf(reader("LastTime") Is DBNull.Value, System.DateTime.Now, reader("LastTime")))
dbo_users.LastUser = System.Convert.ToString(IIf(reader("LastUser") Is DBNull.Value, "", reader("LastUser")))
Else
dbo_users = Nothing
End If
reader.Close()
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
Return dbo_users
End Function

Public Shared Function Add(ByVal dbo_users As UsersObj) As Boolean
Dim connection As SqlConnection = PSCDATDB.GetConnection
Dim insertStatement As String _
= "INSERT [dbo].[Users] " _
& "([UserID], [UserName], [Password], [UserGroupID], [LastTime], [LastUser]) " _
& "VALUES (@UserID, @UserName, @Password, @UserGroupID, @LastTime, @LastUser)"
Dim insertCommand As New SqlCommand(insertStatement, connection)
insertCommand.CommandType = CommandType.Text
insertCommand.Parameters.AddWithValue("@UserID", dbo_users.UserID)
insertCommand.Parameters.AddWithValue("@UserName", dbo_users.UserName)
insertCommand.Parameters.AddWithValue("@Password", dbo_users.Password)
If dbo_users.UserGroupID <> vbNullString Then
insertCommand.Parameters.AddWithValue("@UserGroupID", dbo_users.UserGroupID)
Else
insertCommand.Parameters.AddWithValue("@UserGroupID", DBNull.Value)
End If
insertCommand.Parameters.AddWithValue("@LastTime", dbo_users.LastTime)
insertCommand.Parameters.AddWithValue("@LastUser", dbo_users.LastUser)
Try
connection.Open()
Dim count As Integer = insertCommand.ExecuteNonQuery()
If count > 0 Then
Return True
Else
Return False
End If
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
End Function

Public Shared Function Update(ByVal oldDbo_Users As UsersObj, _
ByVal newDbo_Users As UsersObj) As Boolean
Dim connection As SqlConnection = PSCDATDB.GetConnection
Dim updateStatement As String _
= "UPDATE [dbo].[Users] SET " _
& "[UserID] = @NewUserID, " _
& "[UserName] = @NewUserName, " _
& "[Password] = @NewPassword, " _
& "[UserGroupID] = @NewUserGroupID, " _
& "[LastTime] = @NewLastTime, " _
& "[LastUser] = @NewLastUser " _
& "WHERE [UserID] = @OldUserID "
Dim updateCommand As New SqlCommand(updateStatement, connection)
updateCommand.CommandType = CommandType.Text
updateCommand.Parameters.AddWithValue("@NewUserID", newDbo_Users.UserID)
updateCommand.Parameters.AddWithValue("@NewUserName", newDbo_Users.UserName)
updateCommand.Parameters.AddWithValue("@NewPassword", newDbo_Users.Password)
If newDbo_Users.UserGroupID <> vbNullString Then
updateCommand.Parameters.AddWithValue("@NewUserGroupID", newDbo_Users.UserGroupID)
Else
updateCommand.Parameters.AddWithValue("@NewUserGroupID", DBNull.Value)
End If
updateCommand.Parameters.AddWithValue("@NewLastTime", newDbo_Users.LastTime)
updateCommand.Parameters.AddWithValue("@NewLastUser", newDbo_Users.LastUser)
updateCommand.Parameters.AddWithValue("@OldUserID", oldDbo_Users.UserID)
Try
connection.Open()
Dim count As Integer = updateCommand.ExecuteNonQuery()
If count > 0 Then
Return True
Else
Return False
End If
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
End Function

Public Shared Function Delete(ByVal dbo_users As UsersObj) As Boolean
Dim connection As SqlConnection = PSCDATDB.GetConnection
Dim deleteStatement As String _
= "DELETE FROM [dbo].[Users] " _
& "WHERE [UserID] = @OldUserID "
Dim deleteCommand As New SqlCommand(deleteStatement, connection)
deleteCommand.CommandType = CommandType.Text
deleteCommand.Parameters.AddWithValue("@OldUserID", dbo_users.UserID)
Try
connection.Open()
Dim count As Integer = deleteCommand.ExecuteNonQuery()
If count > 0 Then
Return True
Else
Return False
End If
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
End Function

End Class

form login pada saat klik tombol Login/OK :

Dim dbo_users As UsersObj
Try
dbo_users = UsersDB.Select_Record(txtUserName.text,txtPassword.text)
Catch ex As Exception
MessageBox.Show(ex.Message, ex.GetType.ToString)
End Try
If dbo_users Is Nothing Then
MessageBox.Show("Login salah", msgCaptionDBError, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Me.Hide()
FormUtama.Show()
End If