C# with MS Access : Multi-User Role-Based Login System.
C# กับฐานข้อมูล
Microsoft Access ระบบ login แบบแบ่งระดับผู้ใช้
Full tutorial: VB.NET with MS Access : How to Create a Multi-User Role-Based Login System.
C# .NET Source Code by iBasskung.
Class: DbClass.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace UserAuthentication
{
class DbClass
{
public static OleDbConnection con = new
OleDbConnection();
public static OleDbDataAdapter da = new
OleDbDataAdapter();
public static OleDbCommand cmd = default;
public static string sql = "";
public static string strLang = "C# 2022";
public static string strAnd =
Convert.ToString((char)38);
public static string strSpace =
Convert.ToString((char)32);
public static string dbName = "Access";
public static string mainProjectName =
"Dormitory Management System.";
public static string subProjectName = "Login
System with User Authentication.";
public static string appTitle1 =
string.Format("{0} {1}", strLang, mainProjectName);
public static string appTitle1withDb =
string.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, mainProjectName);
public static string appTitle2 =
string.Format("{0} {1}", strLang, subProjectName);
public static string appTitle2withDb =
string.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, subProjectName);
bool conState = false;
public static string currentUserID = "";
public static string currentUsername = "";
public static string currentPermission = "";
public void manageConnection()
{
conState = false;
try{
// string dbPath =
"\\CSDormSystemDB.accdb";
string conString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath;
conString +=
"\\CSDormSystemDB.accdb";
if (con.State ==
ConnectionState.Open)
{
con.Close();
}
con.ConnectionString
= conString;
con.Open();
conState = true;
}
catch (Exception ex)
{
conState = false;
MessageBox.Show("Error Database Connection: " + ex.Message,
appTitle1withDb,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public DataSet displayData(string str, string
tbl, DataSet myDs)
{
if (con.State ==
ConnectionState.Closed)
{
manageConnection();
}
DataSet ds;
try
{
myDs.Clear();
da = new
OleDbDataAdapter(str, con);
OleDbCommandBuilder
cmdBuilder = new OleDbCommandBuilder(da);
da.Fill(myDs, tbl);
ds = myDs;
}
catch (Exception ex)
{
ds = null;
conState = false;
MessageBox.Show("Error Displaying Data: " + ex.Message,
appTitle1withDb,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conState = false;
con.Close();
}
return ds;
}
public bool executeDb(string mySQL)
{
bool bSuccess;
if (conState == false)
{
manageConnection();
}
try
{
cmd = new
OleDbCommand();
cmd.CommandType =
CommandType.Text;
cmd.CommandText =
mySQL;
cmd.Connection =
con;
cmd.ExecuteNonQuery();
bSuccess = true;
}
catch (Exception ex)
{
bSuccess = false;
MessageBox.Show("Error :" + ex.Message.ToString(), appTitle1withDb,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return bSuccess;
}
}
}
Form: loginForm.cs
using System;
using System.Data;
using System.Windows.Forms;
namespace UserAuthentication
{
public partial class loginForm :
Form
{
public loginForm()
{
InitializeComponent();
this.Text = DbClass.appTitle2;
this.AcceptButton = loginButton;
toolStripStatusLabel2.Text =
DbClass.dbName;
}
DbClass db = new DbClass();
private void exitButton_Click(object sender,
EventArgs e)
{
Environment.Exit(0); // End
}
private void loginButton_Click(object sender,
EventArgs e)
{
if (usernameTextBox.Text == "")
{
MessageBox.Show("Please Input
Username.", DbClass.subProjectName, MessageBoxButtons.OK,
MessageBoxIcon.Warning);
usernameTextBox.Focus();
return;
}
if (passwordTextBox.Text == "")
{
MessageBox.Show("Please Input
Password.", DbClass.subProjectName, MessageBoxButtons.OK,
MessageBoxIcon.Warning);
passwordTextBox.Focus();
return;
}
DataSet ds = new DataSet();
try
{
DbClass.sql = "SELECT * From
tblUser WHERE [Username] = '" + usernameTextBox.Text.Trim() + "' ";
DbClass.sql += "AND [Password] =
'" + passwordTextBox.Text.Trim() + "'";
ds = db.displayData(DbClass.sql,
"user", ds);
if(ds == null)
{
MessageBox.Show("Dataset was
Nothing.");
return;
}
if (ds.Tables["user"].Rows.Count
> 0)
{
DbClass.currentUserID =
ds.Tables["user"].Rows[0]["AutoID"].ToString().Trim();
DbClass.currentUsername =
ds.Tables["user"].Rows[0]["Username"].ToString().Trim();
DbClass.currentPermission =
ds.Tables["user"].Rows[0]["Permission"].ToString().Trim();
this.Close();
}
else
{
MessageBox.Show("The Username or
Password is incorrect. Try again.", DbClass.appTitle2,
MessageBoxButtons.OK, MessageBoxIcon.Stop);
usernameTextBox.Clear();
passwordTextBox.Clear();
usernameTextBox.Focus();
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" +
ex.Message.ToString(), DbClass.appTitle2, MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
}
private void loginForm_Load(object
sender, EventArgs e)
{
usernameTextBox.Text = "Admin";
passwordTextBox.Text = "1234";
}
}
}
Form: mainForm.cs
using System;
using System.Data;
using System.Windows.Forms;
namespace UserAuthentication
{
public partial class mainForm : Form
{
public mainForm()
{
InitializeComponent();
}
DbClass db = new DbClass();
private void mainForm_Load(object sender,
EventArgs e)
{
db.manageConnection();
try
{
loginForm Frm =
new loginForm();
Frm.ShowDialog();
showPermission();
}
catch (Exception)
{
}
}
private void showPermission()
{
string PermissionDetail;
if
(DbClass.currentPermission.ToUpper() == "USER")
{
PermissionDetail
= " (Read Only).";
}
else
{
PermissionDetail
= " (Full Control).";
}
this.Text = string.Format("{0}
{1} {2} {3} : {4} ({5})", "Main Form :", DbClass.strLang,
DbClass.strAnd, DbClass.dbName, DbClass.mainProjectName,
DbClass.subProjectName);
userLoginToolStripButton.Text
= DbClass.currentUsername;
roleLabel.Visible = true;
roleLabel.Text = "Log-in as: "
+ DbClass.currentPermission.ToUpper()+ PermissionDetail;
toolStripStatusLabel2.Text =
"C# 2022 and MS Access Database : Dormitory Management System v1.0.0
developed by iBasskung.";
}
private void
exitToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void
logOutToolStripMenuItem_Click(object sender, EventArgs e)
{
string strText = "Are you sure
you want to log out?";
string strCaption = "C# 2022 :
Log Out : iBasskung Tutorial.";
if (MessageBox.Show(strText,
strCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) == DialogResult.No)
{
return;
}
DbClass.currentUserID = "";
DbClass.currentUsername = "";
DbClass.currentPermission =
"";
this.Text = string.Format("{0}
{1} {2} {3} Database.", "Main Form :", DbClass.strLang, DbClass.strAnd,
DbClass.dbName);
userLoginToolStripButton.Text
= "Login User";
roleLabel.Text =
"Logged-Out.";
// roleLabel.Visible = false;
toolStripStatusLabel2.Text =
"(Logged-Out)";
mainForm_Load(sender, e);
}
private void
aboutToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show("C# 2022 and
Microsoft Access Database." + Environment.NewLine + "Dormitory
Management System Version 1.0.0" +
Environment.NewLine + "Developed by Mr. Tanin Sangngam (iBasskung).",
"C# and Access Database : Dormitory Management System.",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void
userToolStripButton_Click(object sender, EventArgs e)
{
try
{
userForm Frm =
new userForm();
Frm.ShowDialog();
showPermission();
}
catch (Exception)
{
throw;
}
}
private void
roomToolStripButton_Click(object sender, EventArgs e)
{
MessageBox.Show("Room
Management : iBasskung Tutorial.", DbClass.appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void
customerToolStripButton_Click(object sender, EventArgs e)
{
MessageBox.Show("Customer
Management : iBasskung Tutorial.", DbClass.appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void
youtubeToolStripButton_Click(object sender, EventArgs e)
{
System.Diagnostics.Process.Start("https://www.youtube.com/c/iBasskung");
}
private void
userLoginToolStripButton_Click(object sender, EventArgs e)
{
MessageBox.Show("User id: " +
DbClass.currentUserID + Environment.NewLine + Environment.NewLine +
"Username: " +
DbClass.currentUsername + Environment.NewLine + Environment.NewLine +
"Permission: " +
DbClass.currentPermission, "C# : Current logged in user.",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void
userToolStripMenuItem_Click(object sender, EventArgs e)
{
userToolStripButton.PerformClick();
}
private void
roomToolStripMenuItem_Click_1(object sender, EventArgs e)
{
roomToolStripButton.PerformClick();
}
private void
customerToolStripMenuItem_Click_1(object sender, EventArgs e)
{
customerToolStripButton.PerformClick();
}
private void
youTubeToolStripMenuItem_Click(object sender, EventArgs e)
{
youtubeToolStripButton.PerformClick();
}
private void mainForm_FormClosing(object
sender, FormClosingEventArgs e)
{
try
{
if
(MessageBox.Show("Are you sure you want to exit the application?", "C#
2022 : Form Closing : iBasskung Tutorial.",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) ==
DialogResult.No)
{
e.Cancel = true;
}
if
(DbClass.con.State == ConnectionState.Open)
{
DbClass.con.Close();
}
}
catch (Exception)
{
}
}
}
}
Form: userForm.cs
using System;
using System.Data;
using System.Windows.Forms;
namespace UserAuthentication
{
public partial class userForm : Form
{
public userForm()
{
InitializeComponent();
}
private string selectedID = "";
DbClass db = new DbClass();
private void userForm_Load(object sender,
EventArgs e)
{
showUserData();
}
private void clearControls()
{
selectedID = "";
usernameTextBox.Text = "";
passwordTextBox.Text = "";
if (roleComboBox.Items.Count
> 0)
{
roleComboBox.SelectedIndex = 0;
}
toolStripStatusLabel1.Text =
"Status:";
}
private void showUserData()
{
this.Text = "User Management
Form | Current logged-in user: " + DbClass.currentUsername.ToUpper()
+
" (ID: " +
DbClass.currentUserID + ")" + " (Log-in as: " +
DbClass.currentPermission + ")";
DataSet ds = new DataSet();
DbClass.sql = "SELECT
[AutoID], Username, [Password], [Permission] FROM tblUser";
try{
ds =
db.displayData(DbClass.sql, "tblUser", ds);
if (ds == null)
{
MessageBox.Show("Error: Dataset was Nothing.");
return;
}
if
(ds.Tables["tblUser"].Rows.Count > 0)
{
dgv.DataSource = ds.Tables["tblUser"];
}
else
{
dgv.DataSource = null;
}
if
(dgv.Rows.Count > 0)
{
dgv.ColumnHeadersDefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;
dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgv.Columns[0].HeaderText = "AutoID";
dgv.Columns[1].HeaderText = "Username";
dgv.Columns[2].HeaderText = "Password";
dgv.Columns[3].HeaderText = "Permission";
dgv.Columns[0].Visible = false;
if
(DbClass.currentPermission.ToUpper() == "USER")
{
addToolStripButton.Enabled = false;
editToolStripButton.Enabled = false;
deleteToolStripButton.Enabled = false;
// clearToolStripButton.Enabled = false;
passwordTextBox.UseSystemPasswordChar = true;
dgv.Columns[1].Width = 336;
dgv.Columns[2].Visible = false;
}
else
{
addToolStripButton.Enabled = true;
editToolStripButton.Enabled = true;
deleteToolStripButton.Enabled = true;
// clearToolStripButton.Enabled = true;
passwordTextBox.UseSystemPasswordChar = false;
dgv.Columns[1].Width = 168;
dgv.Columns[2].Visible = true;
dgv.Columns[2].Width = 168;
dgv.Columns[2].DefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;
}
dgv.Columns[1].DefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;
dgv.Columns[3].Visible = false;
dgv.ClearSelection();
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message.ToString(), "Error Message",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
clearControls();
}
private void
closeToolStripButton_Click(object sender, EventArgs e)
{
this.Close();
}
private void
clearToolStripButton_Click(object sender, EventArgs e)
{
clearControls();
}
private void addToolStripButton_Click(object
sender, EventArgs e)
{
saveUserData("INSERT");
}
private void
editToolStripButton_Click(object sender, EventArgs e)
{
saveUserData("UPDATE");
}
private void
deleteToolStripButton_Click(object sender, EventArgs e)
{
saveUserData("DELETE");
}
private void saveUserData(string dbCommand)
{
try{
if
(dbCommand.ToUpper() == "UPDATE" || dbCommand.ToUpper() == "DELETE")
{
if
(selectedID == "")
{
MessageBox.Show("ID not found. Please Select an Item from
the List.", DbClass.appTitle1withDb,
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
if
(dbCommand.ToUpper() == "INSERT" || dbCommand.ToUpper() == "UPDATE")
{
if
(usernameTextBox.Text.Trim() == "")
{
MessageBox.Show("TextBox is Empty. Please Enter
Username.", DbClass.appTitle1, MessageBoxButtons.OK,
MessageBoxIcon.Warning);
usernameTextBox.Focus();
return;
}
if
(passwordTextBox.Text.Trim() == "")
{
MessageBox.Show("TextBox is Empty. Please Enter
Password.", DbClass.appTitle1, MessageBoxButtons.OK,
MessageBoxIcon.Warning);
passwordTextBox.Focus();
return;
}
}
if
(dbCommand.ToUpper() != "DELETE")
{
if(roleComboBox.SelectedIndex == 0)
{
MessageBox.Show("Please select permission from the
ComboBox.", DbClass.appTitle1withDb,
MessageBoxButtons.OK,
MessageBoxIcon.Information);
return;
}
}
int intID;
switch
(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)
{
return;
}
DbClass.sql = "INSERT INTO tblUser([Username], [Password],
[Permission]) ";
DbClass.sql += " VALUES('" + usernameTextBox.Text.Trim() +
"', '" + passwordTextBox.Text.Trim() + "', ";
DbClass.sql += "'" +
roleComboBox.SelectedItem.ToString().Trim() + "')";
break;
case "UPDATE":
if (MessageBox.Show("Do you want to update the selected
record?", DbClass.appTitle1, MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) == DialogResult.No)
{
return;
}
intID = Convert.ToInt32(selectedID.ToString().Trim());
DbClass.sql = "UPDATE tblUser SET [Username] = '" +
usernameTextBox.Text.Trim() + "', [Password] = '" +
passwordTextBox.Text.Trim() + "' ";
DbClass.sql += ", [Permission] = '" +
roleComboBox.SelectedItem.ToString().Trim() + "' ";
DbClass.sql += "WHERE [AutoID] = " + intID + "";
if(DbClass.currentUserID == selectedID)
{
DbClass.currentUsername =
usernameTextBox.Text.Trim();
DbClass.currentPermission =
roleComboBox.SelectedItem.ToString().Trim();
}
break;
case "DELETE":
if (DbClass.currentUserID == selectedID)
{
MessageBox.Show("Error deleting record: " +
"The user " + DbClass.currentUsername + " Is currently logged
in.",
DbClass.appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (MessageBox.Show("Do you want to delete the selected
record?", DbClass.appTitle1, MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) == DialogResult.No)
{
return;
}
intID = Convert.ToInt32(selectedID.ToString().Trim());
DbClass.sql = "DELETE FROM tblUser WHERE [AutoID] = " +
intID + "";
break;
}
if(db.executeDb(DbClass.sql) == true)
{
selectedID = "";
MessageBox.Show("SQL " + dbCommand + " QUERY has been executed
successfully.", DbClass.appTitle1withDb, MessageBoxButtons.OK,
MessageBoxIcon.Information);
showUserData();
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), DbClass.appTitle1,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void dgv_CellMouseDoubleClick(object
sender, DataGridViewCellMouseEventArgs e)
{
try
{
if (e.RowIndex
== -1)
{
return;
}
selectedID =
dgv.Rows[e.RowIndex].Cells[0].Value.ToString().Trim();
toolStripStatusLabel1.Text = "Selected id: " + selectedID;
usernameTextBox.Text = dgv.Rows[e.RowIndex].Cells[1].Value.ToString();
passwordTextBox.Text = dgv.Rows[e.RowIndex].Cells[2].Value.ToString();
//
MessageBox.Show(dgv.Rows[e.RowIndex].Cells[3].Value.ToString(),
DbClass.appTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
if
(dgv.Rows[e.RowIndex].Cells[3].Value.ToString().Trim().ToUpper() ==
"ADMIN")
{
roleComboBox.SelectedIndex = 1;
}
else
{
roleComboBox.SelectedIndex = 2;
}
}
catch (Exception)
{
}
}
}
}
Comments
Post a Comment