Click here to Skip to main content
15,848,157 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

C#
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

C#
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
C#
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

C#
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

C#
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!
Posted
Updated 16-Jan-23 4:07am
v2
Comments
PIEBALDconsult 14-Jan-23 11:13am    
Redesign the database to store history for the table.
Member 12692000 14-Jan-23 21:09pm    
Mind showing how exactly ?
Gerry Schmitz 14-Jan-23 11:53am    
You could serialize the classes and compare the text. You could use reflection to compare values. You've got comparing mixed up with data access. You need to "partition" things better.
Member 12692000 14-Jan-23 21:28pm    
As per your suggestion I've tried to use reflection to compare values (UPDATE portion of the OQ). Do you suggest some improvements on it ?
Richard Deeming 16-Jan-23 5:46am    
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

There's various ways of doing this.

Google for "sqlite database change history[^]" for techniques and discussions.
 
Share this answer
 
before.Except(after) will depend on the Bills class correctly implementing the IEquatable<Bills> interface, or at the very least providing correct overloads of GetHashCode and Equals(object).

before.Intersect(after, new BillsComparer()) will only return pairs which the comparer thinks are equal. That's no good for detecting changes.

The reflection code will be very slow, and not really necessary since you know the types at compile-time, and you don't have to worry about derived types.

Perhaps try something like this:
C#
public readonly struct PropertyDifference
{
    public PropertyDifference(string propertyName, object oldValue, object newValue)
    {
        PropertyName = propertyName;
        OldValue = oldValue;
        NewValue = newValue;
    }
    
    public string PropertyName { get; }
    public object OldValue { get; }
    public object NewValue { get; }
}

public sealed class BillsDifference
{
    private BillsDifference(int id, List<PropertyDifference> changedProperties)
    {
        Id = id;
        ChangedProperties = changedProperties;
    }
    
    public int Id { get; }
    public List<PropertyDifference> ChangedProperties { get; }
    
    private static BillsDifference Create(Bills oldValue, Bills newValue)
    {
        int id;
        List<PropertyDifference> changedProperties = new List<PropertyDifference>(7);

        if (oldValue != null && newValue != null)
        {
            // Item exists in both old and new lists - look for property changes:
            id = oldValue.Id;
            
            if (!Equals(oldValue.Party, newValue.Party)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.Party), oldValue.Party, newValue.Party));
            }
            if (!Equals(oldValue.BillNo, newValue.BillNo)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.BillNo), oldValue.BillNo, newValue.BillNo));
            }
            if (!Equals(oldValue.BillDt, newValue.BillDt)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.BillDt), oldValue.BillDt, newValue.BillDt));
            }
            if (!Equals(oldValue.Amt, newValue.Amt)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.Amt), oldValue.Amt, newValue.Amt));
            }
            if (!Equals(oldValue.DueDt, newValue.DueDt)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.DueDt), oldValue.DueDt, newValue.DueDt));
            }
            if (!Equals(oldValue.PaidOn, newValue.PaidOn)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.PaidOn), oldValue.PaidOn, newValue.PaidOn));
            }
            if (!Equals(oldValue.Remarks, newValue.Remarks)) 
            {
                changedProperties.Add(new PropertyDifference(nameof(Bills.Remarks), oldValue.Remarks, newValue.Remarks));
            }
        }
        else if (oldValue != null)
        {
            // Item has been removed:
            id = oldValue.Id;
            changedProperties.Add(new PropertyDifference(nameof(Bills.Party), oldValue.Party, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.BillNo), oldValue.BillNo, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.BillDt), oldValue.BillDt, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.Amt), oldValue.Amt, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.DueDt), oldValue.DueDt, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.PaidOn), oldValue.PaidOn, null));
            changedProperties.Add(new PropertyDifference(nameof(Bills.Remarks), oldValue.Remarks, null));
        }
        else if (newValue != null)
        {
            // Item has been added:
            id = newValue.id;
            changedProperties.Add(new PropertyDifference(nameof(Bills.Party), null, newValue.Party));
            changedProperties.Add(new PropertyDifference(nameof(Bills.BillNo), null, newValue.BillNo));
            changedProperties.Add(new PropertyDifference(nameof(Bills.BillDt), null, newValue.BillDt));
            changedProperties.Add(new PropertyDifference(nameof(Bills.Amt), null, newValue.Amt));
            changedProperties.Add(new PropertyDifference(nameof(Bills.DueDt), null, newValue.DueDt));
            changedProperties.Add(new PropertyDifference(nameof(Bills.PaidOn), null, newValue.PaidOn));
            changedProperties.Add(new PropertyDifference(nameof(Bills.Remarks), null, newValue.Remarks));
        }
        else
        {
            // Shouldn't happen:
            return null;
        }
        
        if (changedProperties.Count == 0) return null;
        return new BillsDifference(id, changedProperties);
    }
    
    public static IEnumerable<BillsDifference> FindChanges(IEnumerable<Bills> before, IEnumerable<Bills> after)
    {
        // Handle items which have been changed or removed:
        List<Bills> afterList = after.ToList();
        foreach (Bills oldValue in before)
        {
            Bills newValue = afterList.FirstOrDefault(b => b.Id == oldValue.Id);
            if (newValue != null) afterList.Remove(newValue);
            
            BillsDifference diff = Create(oldValue, newValue);
            if (diff != null) yield return diff;
        }
        
        // Handle items which have been added:
        foreach (Bills newValue in afterList)
        {
            yield return Create(null, newValue);
        }
    }
}
C#
List<Bills> before = GetSelectedBillData(x);
bills.UpdateBill(bill);
List<Bills> after = GetSelectedBillData(x);

List<BillDifference> differences = BillDifference.FindChanges(before, after).ToList();
if (differences.Count != 0)
{
    // Write the differences to your log file...
}
 
Share this answer
 
Comments
Member 12692000 16-Jan-23 9:32am    
I'm getting The modifier 'readonly' is not valid for this item (CS0106) error for the struct PropertyDifference. Also, I'm using C#5.0, how do I handle nameof
Richard Deeming 16-Jan-23 9:37am    
OK, readonly struct was introduced in C# 7.2, so you'll need to change that to public struct PropertyDifference instead.

nameof was introduced in C# 6; for earlier versions, you'll have to use a string literal instead. Eg:
changedProperties.Add(new PropertyDifference("Party", oldValue.Party, newValue.Party));

The down-side is that the string won't be updated if you refactor your code to rename one of the properties.
Member 12692000 16-Jan-23 10:27am    
After applying the changes you mentioned I was getting the errors '....PropertyDifference.PropertyName.get' must declare a body because it is not marked abstract or extern. Automatically implemented properties must define both get and set accessors. (CS0840) , so I added set accessors to all the properties.

But now I'm getting this error on PropertyDifference.
Richard Deeming 16-Jan-23 10:29am    
Old versions of C# can be a pain.

Try:
public struct PropertyDifference
{
    private readonly string _propertyName;
    private readonly object _oldValue;
    private readonly object _newValue;
    
    public PropertyDifference(string propertyName, object oldValue, object newValue)
    {
        _propertyName = propertyName;
        _oldValue = oldValue;
        _newValue = newValue;
    }
    
    public string PropertyName { get { return _propertyName; } }
    public object OldValue { get { return _oldValue; } }
    public object NewValue { get { return _newValue; } }
}
Member 12692000 16-Jan-23 11:05am    
Thanks.

But how can I get the name of the properties whose value have changed ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900