User Activity Log Using C# With SQL Server

Introduction

The purpose of this article is to explain how to keep track of user activities in the Database Management System

Especially while working in the client-server environment where we need to keep track of the System IP, System Name, Login Id, Timestamp, and the action performed for any of the database applications. Whether the user edits a client's record or does any transaction or any of many other things.

Step 1

Create the table with the name "User_Activity_Log".

CREATE TABLE [dbo].[User_Activity_Log](
      [UAL_User_Id] [varchar](20) NOT NULL,
      [UAL_Employee_No] [varchar](7) NOT NULL,
      [UAL_Timestamp] [datetime] NOT NULL,
      [UAL_Function_Performed] [nvarchar](100) NOT NULL,
      [UAL_Other_Information] [nvarchar](100) NULL,
      [UAL_IP_Address] [nvarchar](15) NOT NULL,
PRIMARY KEY CLUSTERED
(
      [UAL_User_Id] ASC,
      [UAL_Timestamp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO
SET ANSI_PADDING OFF

Step 2

Create a Login table.

CREATE TABLE [dbo].[Login](
      [LON_User_Name] [varchar](20) NULL,
      [LON_Login_Name] [varchar](20) NULL,
      [LON_Employee_No] [varchar](10) NULL,
      [LON_Login_Password] [varchar](20) NULL,
      [LON_Type] [nvarchar](20) NULL,
      [LON_Status] [varchar](20) NULL
) ON [PRIMARY] 
GO
SET ANSI_PADDING OFF

Step 3

Now insert one record into the login table.

INSERT INTO PSH.[dbo].[Login_Client]VALUES('Naveed Zaman','naveed.zaman','22339','khan@123','A','Active')

Step 4

Start Visual Studio and create a new Desktop project with the name "UserActivityLog".

Step 5

First of all, we must create a login form that will help us to understand the basic concepts of the topic. Create the form according to the picture.

Step 6

Add the new class with the name "Main".

Step 7

Now we will create the function "GetDBConnection" as a public function so it can be accessed from anywhere in the project. But you must modify the data source setting according to your own settings.

For example

SQL Server instance name (.\\CRMIS)
SQL Server user name and password (user id sa password.###ReconcilE123)
Initial Catalog=PSH (database name)

Add the public static class in the Main. cs as in the following.

public static SqlConnection GetDBConnection()
{
   SqlConnection conn = new SqlConnection(
   "Data Source=.\\CRMIS;Initial Catalog=PSH;User ID=sa;Password=###Renp321");
   return conn;
}

Step 8

We must add one more public static function to the Main class that will fetch data from the database as in the following.

Step 9

We must add a function "Execute" in the Main class, as in the following.

Step 10

Create another form with the name "frmActivity", as in the following.

Step 11

Now add a few buttons to the form, for example, "Add-New", "Update", "Save" and "Exit" buttons on the form as shown in the following picture.

Step 12

In this step, we will write the code in FrmLogin that we have created in step 5.

We have defined the following public static variables.

• public static string CIP = "5";

• public static string EmpNo = "1";

• public static string username = "1";

SqlConnection conn = Main.GetDBConnection();

The Screenshot is attached for your reference.

Step 13

Write the following code for the form load event that will set the location of the form and get the system IP address but don't forget to add "using System.Net;".

Step 14

It's time to add code for the BtnLogin click event. This will check the database to validate the user-provided information, whether it's correct or not. If it's correct then it will load the FrmActivity. 

Step 15

Now we have added some code for the FrmActivity's "btnAdd", "btnSave", "btnDiscard" and "brnExit".

Step 16

Now execute the program. Enter a username and password, as in the following.

User Name : naveed.zaman Password : khan@123

 

Click on the login button,

then click on the "Add new" button, "Save" button, and "Logout" button.

Now check the User_Activity_Log table to ensure that we have created the data from Step 1. 

Conclusion

In this article, we learned about User Activity Log Using C# With SQL Server.