How to capture previous values of a row in a sqlite database?

Question

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.

How can I do that in a proper manner without hogging to much resources ?

Answer 1

Using @AgaveJoe 's suggested approach I've tryied the below steps:

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

public static 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 DoSave(object param)
		{
				UpdateBindingGroup.CommitEdit();
				var bill = SelectedInv as Bills;
				
				string x =bill.Id.ToString();
				
				List<Bills> before = GetSelectedBillData(x);
				if (SelectedIndex == -1)
				{
					bills.AddBill(bill);
				}
				else
				{
					bills.UpdateBill(bill);
					
	
					List<Bills> after = GetSelectedBillData(x);
					
					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+ "'",
									Diff3 = "'"+o.BillDt +"' FROM "+ "'"+i.BillDt+ "'",
									Diff4 = "'"+o.Amt +"' FROM "+ "'"+i.Amt+ "'",
									Diff5 = "'"+o.DueDt +"' FROM "+ "'"+i.DueDt+ "'",
									Diff6 = "'"+o.PaidOn +"' FROM "+ "'"+i.PaidOn+ "'",
									Diff7 = "'"+o.Remarks +"' FROM "+ "'"+i.Remarks+ "'"
								};
								
								
								string fileName = @"G:\log.txt";
								
								// Check if file already exists. Append text to it.
								if (File.Exists(fileName))
								{
									File.SetAttributes(fileName, FileAttributes.Normal);
									
									if (IsFileLocked(new FileInfo(fileName)))
									{
										QModernMessageBox.Error("Please close the following file :\n"+fileName,"File Already Open");
										return;
									}
									
									else
									{
										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_.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 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_.Diff3);
											sw.WriteLine(element_.Diff4);
											sw.WriteLine(element_.Diff5);
											sw.WriteLine(element_.Diff6);
											sw.WriteLine(element_.Diff7);
										}
									}
								}
								
								File.SetAttributes(fileName, FileAttributes.ReadOnly);
							}
						}
					}
				}
		}

Using reflection helper method to compare values

	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;

	}

But I'm not sure if this method can become error prone or resource heavy as the database becomes larger ...

Answer 2

Here is my advice, use a library that monitor changes and provides various outputs e.g. text file, json file, database etc.

One such library is Audit.NET

Generate audit logs with evidence for reconstruction and examination of activities that have affected specific operations or procedures.

With Audit.NET you can generate tracking information about operations being executed. It gathers environmental information such as the caller user id, machine name, method name, exceptions, including execution time and exposing an extensible mechanism to enrich the logs and handle the audit output.