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.