User Activity Log Using C# With SQL Server

The purpose of this article is to keep track of user activities in the Database Managements System. Specially while working on the client/server environment where we need to maintain the System IP/System Name, Login Id, Timestamp and the action performed on any of the database applications. Either the user edits a client record or does a transition and so on.

Step 1

Create the table with the name "User_Activity_Log" as in the following:

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 the Login table as in the following:

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 VS .Net and create a new Desktop project with the name of "UserActivityLog".



Step 5

First of all we need to create the login form that will help us to understand the basic concepts of the topic. Create the form as in the following picture:



Step 6

Add the new class with the name "Main".



Step 7

We will now create the function "GetDBConnection" as a public function so it can be accessed anywhere in the project. But you need to modify the data source setting according to your own setting.

For example:

1. SQL Server instance name.(.\\CRMIS)

2. SQL Server user name and password (user id sa and password "###ReconcilE123")

3. Initial Catalog=PSH (database name)

Add the public static class in 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 need to add one more public static function in the Main class that will fetch data from the database as in the following:



Step 9

We need to add the function "Execute" in the Main class.



Step 10

Create another form with the name "frmActivity".



Step 11

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



Step 12

In this step we will write the code on FrmLogin that we created in Step 5.

We define the following public 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.

A screenshot is attached for your reference.



Step 13

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




Step 14

It's time to add code on the BtnLogin click event. This will check the database for the use of provided information, either its correct or not. If its correct then it will load the FrmActivity.

Step 15

Now we add some code in FrmActivity for btnAdd, btnSave, btnDiscard and brnExit.


Step 16

Now execute the program. Enter the user name and password.

User Name : naveed.zaman
Password : khan@123



Click on the login button.

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



Now check the "User_Activity_Log" table that we created in Step 1.