How to maintain a log of who updated the field in access db and when in windows form

Question

I have created a windows form in which I have added many fields like insert, update etc it is updating the fields in access DB but i want to implement one more functionality and that is to maintain and display log in windows form like who updated the field and when(time) in DB .

I want to make it like a history function which will maintain record of all enteries and display it in same windows form when i click on button, How can i do that.

below is my form preview & as you can see there is a grid view to display the fields and particular buttons to perform different tasks I am using Access DB to store and view data

below is my code-

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\saurabh.ad.sharma\Documents\test2\Database21.accdb");
        int count = 0;
        public Form1()
        {
            InitializeComponent();
        }    

        private void button1_Click(object sender, EventArgs e)
        {    
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into table1 values('"+textBox1.Text+"','"+textBox2.Text+"')";
            cmd.ExecuteNonQuery();
            con.Close();
            textBox1.Text = "";
            textBox2.Text = "";
            MessageBox.Show("record inserted successfully");    
        }

        private void button4_Click(object sender, EventArgs e)
        {
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from table1";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();    
        }

        private void button2_Click(object sender, EventArgs e)
        {
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "delete from table1 where name='"+textBox1.Text+"'";
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("record deleted successfully");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "update table1 set name='"+textBox2.Text+"' where name='"+textBox1.Text+"'";
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("record updates successfully");
        }

        private void button5_Click(object sender, EventArgs e)
        {
            count = 0;
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from table1 where name='"+textBox1.Text+"' ";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            count = Convert.ToInt32(dt.Rows.Count.ToString());
            dataGridView1.DataSource = dt;
            con.Close();    

            if (count == 0)
            {
                MessageBox.Show("record not found");
            }    
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

using triggers in sql you can add audit log for each operation, ref https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/ — programtreasures, Jan 02 '18 at 07:42

Bernd Fischer · Answer 1 · 2018-01-16T11:29:47.263

You can log it yourself. Every time you write to the database, also write to another table created for the purpose. Something like this:

INSERT INTO logtable VALUES([timestamp], [current username], [changed field], [...])

You can then display the log in any way you like - probably use a gridview as well. If the log grows faster than the available diskspace, you may want to delete old log entries from time to time.

Also, you are currently creating SQL statements from unchecked user-provided strings. This is dangerous - you must make sure the strings cannot contain SQL commands or you may find your database manipulated or destroyed. Classic example: https://xkcd.com/327/

You should probably read up on SQL injection and/or input sanitization. If you're in a hurry, check this out: Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?

How to maintain a log of who updated the field in access db and when in windows form

1 Answers1