Advanced Login System

Introduction

In this article we will show you how to Creating Form to Manage Users using Windows Application in Visual Studio using C#.

Prerequisites

Visual Studio 2010/2012/2013/15/17, SQL Server 2005/08/2012

Project used version

VS2017, SQL SERVER 2012

User Management Form

Step 1: Create new Stored Procedures "spReadUsers", "spSearchViewUsers" and "spManageUsers" as shown below 

Below stored procedure "spReadUsers" used to read user information by invoking ID as input.

 
USE [DBLoginSystem]
GO

/****** Object:  StoredProcedure [dbo].[spReadUsers]    Script Date: 19-12-2018 8.05.17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spReadUsers](@Id int,
@UserName varchar(50) output,
@Email varchar(50) output,
@Role varchar(50) output,
@isActivated varchar(50) output
)
AS
BEGIN

	SET NOCOUNT ON;
	select @UserName = UserName, @Email= Email, @Role = RoleID, @isActivated = isActivated from Users where ID = @Id
END

GO

Below stored procedure "spSearchViewUsers" used to search and read user information by invoking User Name as input.

 
	USE [DBLoginSystem]
	GO

	/****** Object:  StoredProcedure [dbo].[spSearchViewUsers]    Script Date: 19-12-2018 8.07.10 PM ******/
	SET ANSI_NULLS ON
	GO

	SET QUOTED_IDENTIFIER ON
	GO


	CREATE PROCEDURE [dbo].[spSearchViewUsers]
	(
	@Search varchar(50)
	)
	AS
	BEGIN

		SET NOCOUNT ON;
	SELECT        Users.ID, Users.UserName, Users.Email, Users.RetryAttempts, Users.isActivated, Users.isLocked, Users.DeactivatedDateTime, Users.LockedDateTime, 
							 Users.CreatedDate, Users.LastLogin, Roles.RoleName
	FROM            Users INNER JOIN
							 Roles ON Users.RoleId = Roles.RoleId
	WHERE ( Users.UserName like '%'+IsNull(@Search, Users.UserName)+'%')
	END
                                    
	GO

 Below stored procedure "spManageUsers" used to Managing Users such as activate or deaction and change user role such as Admin, Employee.

 
USE [DBLoginSystem]
GO

/****** Object:  StoredProcedure [dbo].[spManageUsers]    Script Date: 19-12-2018 8.09.17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spManageUsers]
(
@Id int,
@Role int,
@isActive bit,
@Result varchar(max) output
)
AS
BEGIN
declare @count int, @countSelect int , @null_Date DATETIME=NULL
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	 select @count = count(*) from Users where Users.RoleID = 1 and isActivated = 1  ;
	 if(@count=1)

	 begin

	  if(@Role = 1) 

	 begin

	 SELECT @countSelect = count(*) FROM ((SELECT ID FROM Users where ID=@Id) EXCEPT (SELECT ID FROM Users where RoleID = 1 and isActivated = 1)) t1_not_in_t2;

	 if (@countSelect = 0 ) 

	 begin

	 select @Result = 'One admin role account must exists.';

	 end

	 else

	 begin
	 
	 if(@isActive = 1 )
  begin
		update Users set RoleID = @Role, DeactivatedDateTime = @null_Date,  isActivated = @isActive, isLocked = 0 where ID = @Id ;
	  select @Result = 'Successfully updated.';
  end
 else
 begin
 update Users set RoleID = @Role, DeactivatedDateTime = getdate(), isActivated = @isActive, isLocked = 0 where ID = @Id ;
	  select @Result = 'Successfully updated.';
 end
	 end

	 end

	 else

	 begin

	 select @Result = 'One admin role account must exists.';

	 end

	 end
	 
	 else

	 begin

	 
	 if(@isActive = 1 )
  begin
		update Users set RoleID = @Role, DeactivatedDateTime = @null_Date,  isActivated = @isActive where ID = @Id ;
	  select @Result = 'Successfully updated.';
  end
 else
 begin
 update Users set RoleID = @Role, DeactivatedDateTime = getdate(), isActivated = @isActive where ID = @Id ;
	  select @Result = 'Sucessfully updated.';
 end

	 end
	 
END
                                
GO

Step 2: Open LINQ to SQL Classess file, drag and drop stored procedures from Server Explorer.

 

Step 3: Add new window form as "ManageUsers". 

Controls	Text	Name
Group Box	User Information	grbUserInformation
Label	User Name	lblUserName
Label	Email	lblEmail
Label	Role	lblRole
Label	Active	lblActive
Label	Search	lblSearch
TextBox		txtUserName
TextBox		txtEmail
TextBox		txtSearch
Button		btnSave
Button		btnSearch
Data Grid View		dgvUserData
Check Box		chkIsActived
Combo Box		cobRole

 

Step 5: Declare Variables as shown below. 

 

Step 6: Create new function "RefreshDataGrid()" as shown below.

RefreshDataGrid() function is used refresh to gridview on form load or any event fired when function called. 

public void RefreshDataGrid()
        {
            using (DataClassesDataContext db = new DataClassesDataContext())
            {
                dgvUserdata.DataSource = db.spSearchViewUsers(txtSearch.Text);
                dgvUserdata.Refresh();
            }

        }

 

Step 7: Copy and Paste the following code,

Below code required Selection Change Event in GridView. (Read User Information by Selecting(event) Rows in GridView ).

private void dgvUserdata_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvUserdata.SelectedCells.Count > 0)
            {
                temp = dgvUserdata.SelectedCells[0].Value.ToString();
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spReadUsers(Convert.ToInt32(temp), ref userName, ref Email, ref Role, ref isActive);
                }
            }
            txtUserName.Text = userName;
            txtEmail.Text = Email;
            cobRole.SelectedValue = Convert.ToInt32(Role);
            if (isActive == "1")
            {
                chkIsActived.Checked = true;
            }
            else
            {
                chkIsActived.Checked = false;
            }
        }
 

Step 8: Copy and Paste the following code on Form Load Event. 

Employee role is loaded in Combo Box. RefreshDataGrid() function is used to refresh Data Grid View.

private void ManageUsers_Load(object sender, EventArgs e)
        {
            dgvUserdata.SelectionChanged += new EventHandler(
          dgvUserdata_SelectionChanged);
            using (DataClassesDataContext db = new DataClassesDataContext())
            {
                cobRole.Items.Clear();
                cobRole.DataSource = db.Roles.ToList();
                cobRole.DisplayMember = "RoleName";
                cobRole.ValueMember = "RoleId";
            }
            RefreshDataGrid();
        }

 

Step 9: Doublic click on Search button & paste the following code.  

private void btnSearch_Click(object sender, EventArgs e)
        {
            RefreshDataGrid();
        }

 

The following code is used search users using RefreshDataGrid() function.

Step 10: Double Click Save Button, paste the following code. 

Below code used to save users information which is selected on GridView. 

private void BtnSave_Click(object sender, EventArgs e)
        {
            if (chkIsActived.Checked == true)
            {
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spManageUsers(Convert.ToInt32(temp), Convert.ToInt32(cobRole.SelectedValue), true, ref result);
                    MessageBox.Show(result);
                    RefreshDataGrid();
                }
            }
            else
            {
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spManageUsers(Convert.ToInt32(temp), Convert.ToInt32(cobRole.SelectedValue), false, ref result);
                    MessageBox.Show(result);
                    RefreshDataGrid();
                }
            }
        }

 

Complete Source Code

using System;
using System.Linq;
using System.Windows.Forms;

namespace AdvancedLoginSystem
{
    public partial class frmManageUsers : Form
    {
        public string userName, Email;
        public string Role;
        public string isActive;
        public string temp;
        public string result;
        public frmManageUsers()
        {
            InitializeComponent();
        }

        private void ManageUsers_Load(object sender, EventArgs e)
        {
            dgvUserdata.SelectionChanged += new EventHandler(
          dgvUserdata_SelectionChanged);
            using (DataClassesDataContext db = new DataClassesDataContext())
            {
                cobRole.Items.Clear();
                cobRole.DataSource = db.Roles.ToList();
                cobRole.DisplayMember = "RoleName";
                cobRole.ValueMember = "RoleId";
            }
            RefreshDataGrid();
        }
        public void RefreshDataGrid()
        {
            using (DataClassesDataContext db = new DataClassesDataContext())
            {
                dgvUserdata.DataSource = db.spSearchViewUsers(txtSearch.Text);
                dgvUserdata.Refresh();
            }

        }

        private void dgvUserdata_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvUserdata.SelectedCells.Count > 0)
            {
                temp = dgvUserdata.SelectedCells[0].Value.ToString();
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spReadUsers(Convert.ToInt32(temp), ref userName, ref Email, ref Role, ref isActive);
                }
            }
            txtUserName.Text = userName;
            txtEmail.Text = Email;
            cobRole.SelectedValue = Convert.ToInt32(Role);
            if (isActive == "1")
            {
                chkIsActived.Checked = true;
            }
            else
            {
                chkIsActived.Checked = false;
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            RefreshDataGrid();
        }

        private void BtnSave_Click(object sender, EventArgs e)
        {
            if (chkIsActived.Checked == true)
            {
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spManageUsers(Convert.ToInt32(temp), Convert.ToInt32(cobRole.SelectedValue), true, ref result);
                    MessageBox.Show(result);
                    RefreshDataGrid();
                }
            }
            else
            {
                using (DataClassesDataContext db = new DataClassesDataContext())
                {
                    db.spManageUsers(Convert.ToInt32(temp), Convert.ToInt32(cobRole.SelectedValue), false, ref result);
                    MessageBox.Show(result);
                    RefreshDataGrid();
                }
            }
        }
    }
}

Step 11: Double Click on User Management item from MenuStrip in Admin Form.

The following code used to show Manage Users form.

Step 12: Run Program and Login as a admin, then go to User Management

 

 

Download Complete Source Code C#

 

Video Tutorial