How do I log the changes made to a database entry during update ?

See more:

SQLite

C#5.0

When I perform edit on an existing record(SQLite database), just before saving the edit I want to store the previous values of all columns of the said database row using c# in my WPF window app and store it in a txt/log file.

To do this I'm trying to do the below steps:

1. Write a query to fetch the record and store it to a list/dictionary ?
2. Update the record
3. Do comparison to check which columns value have changed with that updated record (if any)
4. Write to the txt/log file if any changes

What I have tried:

I've create a method to get the current record as

public List<Bills> GetSelectedBillData(string id)
{

    using(SQLiteConnection con = new SQLiteConnection(DatabaseLayer.dbloc))
    {
        con.Open();
        using (var cmd =  new SQLiteCommand("Select * From billdata Where Id="+id, con))
        {
            using (var sda = new SQLiteDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                var Bill = new List<Bills>();

                foreach (DataRow row in dt.Rows)
                {
                    var p = (row["PaidOn"] == DBNull.Value) ? String.Empty : (string)(row["PaidOn"]);
                    var q = (row["Remarks"] == DBNull.Value) ? String.Empty : (string)(row["Remarks"]);

                    var obj = new Bills()
                    {
                        Id = Convert.ToInt32(row["Id"]),
                        Party = (string)row["Party"],
                        BillNo = (string)row["BillNo"],
                        BillDt = (string)(row["BillDt"]),
                        Amt = (string)(row["Amt"]),
                        DueDt = (string)(row["DueDt"]),
                        PaidOn = p,
                        Remarks =q

                    };

                    Bill.Add(obj);
                }

                return Bill;
            }
        }
    }

}

Then inside the VM update method I've done

void DoUpdate(object param)
{

        UpdateBindingGroup.CommitEdit();
        var bill = SelectedInv as Bills;

        string x =bill.Id.ToString();

        List<Bills> before = GetSelectedBillData(x);

            bills.UpdateBill(bill);

            List<Bills> after = GetSelectedBillData(x);

            var comparison1 =before.Except(after).ToList();
            var comparison2 =after.Except(before).ToList();

            if (comparison1.Any() && comparison2.Any())
            {
                var comparisonResult = from i in before
                    join o in after on i.Id equals o.Id
                    select new { i.Id,
                    Diff = "Party : '"+o.Party +"' FROM '"+ "'"+i.Party+ "'",
                    Diff2 = "Bill No : '"+o.BillNo +"' FROM "+ "'"+i.BillNo+ "'",
                    Diff3 = "Bill Date : '"+o.BillDt +"' FROM "+ "'"+i.BillDt+ "'",
                    Diff4 = "Amount : '"+o.Amt +"' FROM "+ "'"+i.Amt+ "'",
                    Diff5 = "Due Date : '"+o.DueDt +"' FROM "+ "'"+i.DueDt+ "'",
                    Diff6 = "Paid On : '"+o.PaidOn +"' FROM "+ "'"+i.PaidOn+ "'",
                    Diff7 = "Remarks : '"+o.Remarks +"' FROM "+ "'"+i.Remarks+ "'"
                };


                string fileName = @"E:\log.txt";

                // Check if file already exists. Append text to it.
                if (File.Exists(fileName))
                {
                    using (StreamWriter sw = new StreamWriter(fileName, true))
                    {
                        foreach (var element in comparisonResult)
                        {
                            sw.WriteLine();
                            sw.WriteLine();
                            sw.WriteLine("Record Edited==> ID : {0}, on {1}",x, DateTime.Now.ToString());
                            sw.WriteLine("--------------------------------------------------------");
                            sw.WriteLine(element.Diff);
                            sw.WriteLine(element.Diff2);
                            sw.WriteLine(element.Diff3);
                            sw.WriteLine(element.Diff4);
                            sw.WriteLine(element.Diff5);
                            sw.WriteLine(element.Diff6);
                            sw.WriteLine(element.Diff7);
                        }
                    }
                }

                // Create a new file
                else
                {
                    using (StreamWriter sw = File.CreateText(fileName))
                    {
                        foreach (var element in comparisonResult)
                        {
                            sw.WriteLine("Record Edited==> ID : {0}, on {1}",x, DateTime.Now.ToString());
                            sw.WriteLine("--------------------------------------------------------");
                            sw.WriteLine(element.Diff);
                            sw.WriteLine(element.Diff2);
                            sw.WriteLine(element.Diff3);
                            sw.WriteLine(element.Diff4);
                            sw.WriteLine(element.Diff5);
                            sw.WriteLine(element.Diff6);
                            sw.WriteLine(element.Diff7);
                        }
                    }
                }
            }

}

But the problem is even if the none of the database table column records have changed the log.txt file still gets created or updated. So, the comparing as I want is not happening. What am I doing wrong here ?

I've also tried to use IEqualityComparer like

public class BillsComparer : IEqualityComparer<Bills>
{
    public bool Equals(Bills x, Bills y)
    {
        return (x.Party == y.Party && x.BillNo == y.BillNo && x.BillDt == y.BillDt && x.Amt == y.Amt && x.DueDt == y.DueDt && x.PaidOn == y.PaidOn && x.Remarks == y.Remarks);
    }

    public int GetHashCode(Bills obj)
    {
        unchecked
        {
            int hash = 17;
            hash = hash * 23 + obj.Party.GetHashCode();
            hash = hash * 23 + obj.BillNo.GetHashCode();
            hash = hash * 23 + obj.BillDt.GetHashCode();
            hash = hash * 23 + obj.Amt.GetHashCode();
            hash = hash * 23 + obj.DueDt.GetHashCode();
            hash = hash * 23 + obj.PaidOn.GetHashCode();
            hash = hash * 23 + obj.Remarks.GetHashCode();
            return hash;
        }
    }
}

Then in my method instead of comparison1 and comparison2 I do

var result = before.Intersect(after, new BillsComparer());

and change the if statement to

if (result.Any())

But results are still the same, I'm obviously doing something very basic in the wrong way, can anybody help me to fix it ?

UPDATE

As per suggestion of Gerry Schmitz I've tried to use reflection to compare values like

public static List<PropertyInfo> GetDifferences(Bills test1, Bills test2)
		{
			List<PropertyInfo> differences = new List<PropertyInfo>();
			foreach (PropertyInfo property in test1.GetType().GetProperties())
			{
				object value1 = property.GetValue(test1, null);
				object value2 = property.GetValue(test2, null);
				try
				{
					if (!value1.Equals(value2))
					{
						differences.Add(property);
					}
				}
				catch (Exception ecx)
				{
					
				}

			}
			return differences;
		}

Then use it in my method like

foreach (var element in before)
{
    foreach (var element2 in after)
    {

        var result = GetDifferences(element, element2);

        if (result.Any())
        {
            var inout = from i in before
                join o in after on i.Id equals o.Id
                select new { i.Id,
                Diff = "'"+o.Party +"' FROM '"+ "'"+i.Party+ "'",
                Diff2 = "'"+o.BillNo +"' FROM "+ "'"+i.BillNo+ "'",
                ...
                Diff7 = "'"+o.Remarks +"' FROM "+ "'"+i.Remarks+ "'"
            };

            string fileName = @"G:\log.txt";

            // Check if file already exists. Append text to it.
            if (File.Exists(fileName))
            {

                    using (StreamWriter sw = new StreamWriter(fileName, true))
                    {
                        foreach (var element_ in inout)
                        {
                            sw.WriteLine();
                            sw.WriteLine();
                            sw.WriteLine("Record Edited==> ID - {0} : {1}",x, DateTime.Now.ToString());
                            sw.WriteLine("-------------------");
                            sw.WriteLine(element_.Diff);
                            sw.WriteLine(element_.Diff2);
                            ...
                            sw.WriteLine(element_.Diff7);
                        }
                    }

            }

            // Create a new file
            else
            {
                using (StreamWriter sw = File.CreateText(fileName))
                {
                    foreach (var element_ in inout)
                    {
                        sw.WriteLine("Record Edited==> ID - {0} : {1}",x, DateTime.Now.ToString());
                        sw.WriteLine("-------------------");
                        sw.WriteLine(element_.Diff);
                        sw.WriteLine(element_.Diff2);
                        ...
                        sw.WriteLine(element_.Diff7);
                    }
                }
            }

        }
    }
}

I'm not sure whether this method can cause problems or not specially when value1 and/or value2 can sometimes be null...But it seems to be working!