VB.NET PostgreSQL : How to Create a Multi-User Role-Based Login System.
Visual
Basic .NET กับฐานข้อมูล PostgreSQL สร้างระบบ login แบบแบ่งระดับผู้ใช้
Full tutorial: VB.NET with MS Access : How to Create a Multi-User Role-Based Login System.
VB.NET Source Code by iBasskung.
Module: ModCenter.vb
Option Explicit On
Option Strict On
Imports Npgsql
Module ModCenter
Public con As New NpgsqlConnection
Public da As NpgsqlDataAdapter
Public cmd As NpgsqlCommand
Public sql As String
Public Const strLang As String = "VB.NET 2022"
Public Const strAnd As String = Chr(38)
Public Const strSpace As String = Chr(32)
Public Const dbName As String = "PostgreSQL"
Public Const mainProjectName As String = "Dormitory Management System."
Public Const subProjectName As String = "Login System with User Authentication."
Public appTitle1 As String = String.Format("{0} {1}", strLang, mainProjectName)
Public appTitle1withDb As String = String.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, mainProjectName)
Public appTitle2 As String = String.Format("{0} {1}", strLang, subProjectName)
Public appTitle2withDb As String = String.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, subProjectName)
Public conState As Boolean = False
Public currentUserID As String = ""
Public currentUsername As String = ""
Public currentPermission As String = ""
Public Function GetConnectionString() As String
Dim host As String = "Host=localhost;"
Dim port As String = "Port=5432;"
Dim db As String = "Database=vbdormsystem;"
Dim user As String = "Username=postgres;"
Dim pass As String = "Password=1234;"
Dim conString As String = String.Format("{0}{1}{2}{3}{4}", host, port, db, user, pass)
Return conString
End Function
Public Sub ManageConnection()
conState = False
Try
Dim conString As String = GetConnectionString()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = conString
con.Open()
conState = True
' MessageBox.Show("Connected to PostgreSQL Database.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
conState = False
MessageBox.Show("Error Database Connection: " & ex.Message, appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
End Sub
Public Function DisplayData(str As String, tbl As String, myDs As DataSet) As DataSet
If con.State = ConnectionState.Closed Then
ManageConnection()
End If
Dim ds As New DataSet
Try
ds.Clear()
da = New NpgsqlDataAdapter(str, con)
Dim cmdBuilder As New NpgsqlCommandBuilder(da)
da.Fill(myDs, tbl)
ds = myDs
Catch ex As Exception
ds = Nothing
conState = False
MessageBox.Show("Error Displaying Data: " & ex.Message, appTitle1withDb,
MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
conState = False
con.Close()
End Try
DisplayData = ds
End Function
Public Function ExecuteDb(ByVal mySQL As String) As Boolean
Dim bCheck As Boolean = False
If conState = False Then
ManageConnection()
End If
Try
cmd = New NpgsqlCommand(mySQL, con)
cmd.CommandTimeout = 600
cmd.ExecuteNonQuery()
bCheck = True
Catch ex As Exception
bCheck = False
MessageBox.Show("Error: " & ex.Message, appTitle1withDb, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
conState = False
con.Close()
End Try
ExecuteDb = bCheck
End Function
End Module
Form: LoginForm.vb
Option Explicit On
Option Strict On
Public Class LoginForm
Private Sub LoginForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
Me.AcceptButton = LoginButton
Me.Text = appTitle2
ToolStripStatusLabel2.Text = dbName
UsernameTextBox.Text = "Admin"
PasswordTextBox.Text = "1234"
Catch ex As Exception
End Try
End Sub
Private Sub ExitButton_Click(sender As Object, e As EventArgs) Handles ExitButton.Click
End
End Sub
Private Sub LoginButton_Click(sender As Object, e As EventArgs) Handles LoginButton.Click
If UsernameTextBox.Text = "" Then
MessageBox.Show("Please Input Username.", subProjectName, MessageBoxButtons.OK, MessageBoxIcon.Warning)
UsernameTextBox.Focus()
Exit Sub
End If
If PasswordTextBox.Text = "" Then
MessageBox.Show("Please Input Password.", subProjectName, MessageBoxButtons.OK, MessageBoxIcon.Warning)
PasswordTextBox.Focus()
Exit Sub
End If
Dim ds1 As New DataSet()
Try
sql = "SELECT * FROM ""tblUser"" WHERE ""Username"" = '" & UsernameTextBox.Text.Trim() & "' "
sql &= "AND ""Password"" = '" & PasswordTextBox.Text.Trim() & "';"
ds1 = DisplayData(sql, "user", ds1)
If ds1 Is Nothing Then
Return
End If
If ds1.Tables("user").Rows.Count > 0 Then
currentUserID = ds1.Tables("user").Rows(0)("AutoID").ToString().Trim()
currentUsername = ds1.Tables("user").Rows(0)("Username").ToString().Trim()
currentPermission = ds1.Tables("user").Rows(0)("Permission").ToString().Trim()
Me.Close()
Else
MessageBox.Show("The Username or Password is incorrect. Try again.", appTitle2, MessageBoxButtons.OK, MessageBoxIcon.Error)
UsernameTextBox.Clear()
PasswordTextBox.Clear()
UsernameTextBox.Focus()
Exit Sub
End If
Catch ex As Exception
MessageBox.Show("Error: " + ex.Message.ToString(), appTitle2, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class
Form: MainForm.vb
Option Explicit On
Option Strict On
Public Class MainForm
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' ManageConnection()
Try
Dim login As Form = New LoginForm()
login.ShowDialog()
ShowPermission()
Catch ex As Exception
End Try
End Sub
Private Sub ShowPermission()
Me.Text = String.Format("{0} {1} {2} {3} : {4} ({5}).", "Main Form :", strLang, strAnd, dbName, mainProjectName, subProjectName)
UserLoginToolStripButton.Text = currentUsername
RoleLabel.Visible = True
RoleLabel.Text = "(ID: " & currentUserID & ") ---> " & "Log-in as: " & currentPermission.ToUpper()
ToolStripStatusLabel2.Text = "VB.NET 2022 and PostgreSQL Database : Dormitory Management System v1.0.0 developed by iBasskung."
End Sub
Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
Application.Exit()
End Sub
Private Sub LogoutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LogoutToolStripMenuItem.Click
Dim strText As String = "Are you sure you want to log out?"
Dim strCaption As String = "VB.NET 2022 : Log Out : iBasskung Tutorial."
If (MessageBox.Show(strText, strCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No) Then
Return
End If
currentUserID = ""
currentUsername = ""
currentPermission = ""
Me.Text = String.Format("{0} {1} {2} {3}.", "Main Form :", strLang, strAnd, dbName)
UserLoginToolStripButton.Text = "Login User"
RoleLabel.Text = "Logged-Out."
' RoleLabel.Visible = False
ToolStripStatusLabel2.Text = "(Logged-Out)"
MainForm_Load(sender, e)
End Sub
Private Sub YouTubeToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles YouTubeToolStripMenuItem.Click
YouTubeToolStripButton.PerformClick()
End Sub
Private Sub YouTubeToolStripButton_Click(sender As Object, e As EventArgs) Handles YouTubeToolStripButton.Click
Process.Start("https://www.youtube.com/c/iBasskung")
End Sub
Private Sub UserToolStripButton_Click(sender As Object, e As EventArgs) Handles UserToolStripButton.Click
Try
Dim user As Form = New UserForm()
user.ShowDialog()
ShowPermission()
Catch ex As Exception
End Try
End Sub
Private Sub UserToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UserToolStripMenuItem.Click
UserToolStripButton.PerformClick()
End Sub
Private Sub CurrentUserToolStripButton_Click(sender As Object, e As EventArgs) Handles UserLoginToolStripButton.Click
MessageBox.Show("User id: " & currentUserID & vbNewLine & vbNewLine &
"Username: " & currentUsername & vbNewLine & vbNewLine &
"Permission: " & currentPermission,
"VB.NET 2022 : Current logged in user.", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub AboutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AboutToolStripMenuItem.Click
MessageBox.Show("Visual Basic .NET and PostgreSQL Database." & vbNewLine & "Dormitory Management System Version 1.0.0" & vbNewLine &
"Developed by Mr. Tanin Sangngam (iBasskung).", "VB.NET and PostgreSQL : Dormitory Management System.",
MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub RoomToolStripButton_Click(sender As Object, e As EventArgs) Handles RoomToolStripButton.Click
MessageBox.Show("Room Management : iBasskung Tutorial.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub CustomerToolStripButton_Click(sender As Object, e As EventArgs) Handles CustomerToolStripButton.Click
MessageBox.Show("Customer Management : iBasskung Tutorial.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
Try
If (MessageBox.Show("Are you sure you want to exit the application?", "VB.NET 2022 : Form Closing : iBasskung Tutorial.",
MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No) Then
e.Cancel = True
End If
If con.State = ConnectionState.Open Then
con.Close()
End If
Catch ex As Exception
End Try
End Sub
Private Sub RoomToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RoomToolStripMenuItem.Click
RoomToolStripButton.PerformClick()
End Sub
Private Sub CustomerToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CustomerToolStripMenuItem.Click
CustomerToolStripButton.PerformClick()
End Sub
Private Sub FileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles FileToolStripMenuItem.Click
End Sub
End Class
Form: UserForm.vb
Option Explicit On
Option Strict On
Public Class UserForm
Private selectedID As String = ""
Private Sub UserForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
ShowUserData()
End Sub
Private Sub CloseToolStripButton_Click(sender As Object, e As EventArgs) Handles CloseToolStripButton.Click
Me.Close()
End Sub
Private Sub ShowUserData()
Me.Text = "User Management Form | Current logged-in user: " & currentUsername.ToUpper() &
" (ID: " & currentUserID & ")" & " (Log-in as: " & currentPermission & ")"
ToolStripStatusLabel1.Text = "Status:"
Dim ds1 As New DataSet()
sql = "SELECT * FROM ""tblUser"" ORDER BY ""AutoID"" ASC;"
ds1 = DisplayData(sql, "tblUser", ds1)
If ds1 Is Nothing Then
Return
End If
If ds1.Tables("tblUser").Rows.Count > 0 Then
DataGridView1.DataSource = ds1.Tables("tblUser")
Else
DataGridView1.DataSource = Nothing
End If
Dim dgv1 As DataGridView = DataGridView1
If dgv1.RowCount > 0 Then
dgv1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
dgv1.Columns(0).HeaderText = "AutoID"
dgv1.Columns(1).HeaderText = "Username"
dgv1.Columns(2).HeaderText = "Password"
dgv1.Columns(3).HeaderText = "Permission"
dgv1.Columns(0).Visible = False
If currentPermission.ToUpper() = "USER" Then
AddToolStripButton.Enabled = False
EditToolStripButton.Enabled = False
DeleteToolStripButton.Enabled = False
' ClearToolStripButton.Enabled = False
PasswordTextBox.UseSystemPasswordChar = True
dgv1.Columns(1).Width = 336
dgv1.Columns(2).Visible = False
Else
AddToolStripButton.Enabled = True
EditToolStripButton.Enabled = True
DeleteToolStripButton.Enabled = True
'ClearToolStripButton.Enabled = True
PasswordTextBox.UseSystemPasswordChar = False
dgv1.Columns(1).Width = 168
dgv1.Columns(2).Visible = True
dgv1.Columns(2).Width = 168
dgv1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
End If
dgv1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dgv1.Columns(3).Visible = False
dgv1.ClearSelection()
End If
ClearControls()
End Sub
Private Sub SaveUserData(dbCommand As String)
Try
If dbCommand.ToUpper() = "UPDATE" Or dbCommand.ToUpper() = "DELETE" Then
If selectedID = "" Then
MessageBox.Show("ID not found. Please select at least one item.", appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Information)
Return
End If
End If
If dbCommand.ToUpper() = "INSERT" Or dbCommand.ToUpper() = "UPDATE" Then
If UsernameTextBox.Text.Trim() = "" Then
MessageBox.Show("Please input Username.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)
UsernameTextBox.Focus()
Exit Sub
End If
If PasswordTextBox.Text.Trim() = "" Then
MessageBox.Show("Please input Password.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)
PasswordTextBox.Focus()
Exit Sub
End If
End If
If dbCommand.ToUpper() <> "DELETE" Then
If RoleComboBox.SelectedIndex = 0 Then
MessageBox.Show("Please select permission from the ComboBox.", appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Information)
Return
End If
End If
Dim intID As Integer
Select Case dbCommand.ToUpper()
Case "INSERT"
If (MessageBox.Show("Do you want to add this new user?", "Add New User : iBasskung Tutorial.", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) = DialogResult.No) Then
Exit Sub
End If
sql = "INSERT INTO ""tblUser""(""Username"", ""Password"", ""Permission"") "
sql &= " VALUES('" & UsernameTextBox.Text.Trim() & "', '" & PasswordTextBox.Text.Trim() & "', "
sql &= "'" & RoleComboBox.SelectedItem.ToString().Trim() & "');"
Case "UPDATE"
If (MessageBox.Show("Do you want to update the selected record?", appTitle1, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) = DialogResult.No) Then
Exit Sub
End If
intID = Convert.ToInt32(selectedID.ToString.Trim())
sql = "UPDATE ""tblUser"" SET ""Username"" = '" & UsernameTextBox.Text.Trim() & "', "
sql &= """Password"" = '" & PasswordTextBox.Text.Trim() & "', "
sql &= """Permission"" = '" & RoleComboBox.SelectedItem.ToString().Trim() & "' "
sql &= "WHERE ""AutoID"" = " & intID & ""
If currentUserID = selectedID Then
currentUsername = UsernameTextBox.Text.Trim()
currentPermission = RoleComboBox.SelectedItem.ToString().Trim()
End If
Case "DELETE"
If currentUserID = selectedID Then
MessageBox.Show("Error deleting record: " & "The user " & Chr(39) & currentUsername & Chr(39) & " Is currently logged in.",
appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
' Are you sure you wish to delete this row?
If (MessageBox.Show("Do you want to delete the selected record?", appTitle1, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) = DialogResult.No) Then
Exit Sub
End If
intID = Convert.ToInt32(selectedID.ToString.Trim())
sql = "DELETE FROM ""tblUser"" WHERE ""AutoID"" = " & intID & ""
End Select
If ExecuteDb(sql) = True Then
selectedID = ""
MessageBox.Show("Your SQL " & dbCommand & " QUERY has been executed successfully.", appTitle1withDb, MessageBoxButtons.OK, MessageBoxIcon.Information)
ShowUserData()
End If
Catch ex As Exception
MessageBox.Show("Error: " + ex.Message.ToString(), appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
End Sub
Private Sub ClearControls()
selectedID = ""
UsernameTextBox.Text = ""
PasswordTextBox.Text = ""
If RoleComboBox.Items.Count > 0 Then
RoleComboBox.SelectedIndex = 0
End If
ToolStripStatusLabel1.Text = "Status:"
End Sub
Private Sub AddToolStripButton_Click(sender As Object, e As EventArgs) Handles AddToolStripButton.Click
SaveUserData("INSERT")
End Sub
Private Sub EditToolStripButton_Click(sender As Object, e As EventArgs) Handles EditToolStripButton.Click
SaveUserData("UPDATE")
End Sub
Private Sub DeleteToolStripButton_Click(sender As Object, e As EventArgs) Handles DeleteToolStripButton.Click
SaveUserData("DELETE")
End Sub
Private Sub ClearToolStripButton_Click(sender As Object, e As EventArgs) Handles ClearToolStripButton.Click
ClearControls()
End Sub
Private Sub DataGridView1_CellMouseDoubleClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDoubleClick
Try
If e.RowIndex = -1 Then
Exit Sub
End If
selectedID = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString().Trim()
ToolStripStatusLabel1.Text = "Selected id: " & selectedID
UsernameTextBox.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString()
PasswordTextBox.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value.ToString()
' MsgBox(Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim())
If Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim().ToUpper = "ADMIN" Then
RoleComboBox.SelectedIndex = 1
Else
RoleComboBox.SelectedIndex = 2
End If
' RoleComboBox.SelectedItem = Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim()
Catch ex As Exception
End Try
End Sub
End Class
Comments
Post a Comment