User Activity Log Using C# With SQL Server

The purpose of this article is to explain how to keep track of user activities in the Database Managements 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 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 the 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:

1. SQL Server instance name (.\\CRMIS)
2. SQL Server user name and password (user id sa password.###ReconcilE123)
3. 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 show 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 pulic static variables:

• public static string CIP = "5";
• public static string EmpNo = "1";
• public static string username = "1";

SqlConnection conn = Main.GetDBConnection();

Then define the conn connection that will create the connection to SQL Server.

The Screen shot 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, either 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 user name 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.