static void TrackDataChanges()
{
//Change tracking allow users to track data changes in user defined tables.
// Tracking is done for all schema's and all user tables for INSERT, UPDATE and DELETE queries
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection())
{
cnn.ConnectionString = "SchemaName=db;uri=file://" + ExampleDatabaseFile + ";";
//Enable Tracking
cnn.TrackDataChanges = true;
//Set buffer size, default is to track last 1000 changes.
// for this example we will change it to 10
cnn.TrackedChangesMaxCount = 10;
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS UsersTestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
cmd.ExecuteNonQuery();
// INSERT
cmd.CommandText = "INSERT INTO UsersTestTable VALUES ('johndoe', 'John' , 'DOE');";
cmd.CommandText += "INSERT INTO UsersTestTable VALUES ('janedoe', 'Jane' , 'DOE');";
cmd.ExecuteNonQuery();
// UPDATE
cmd.CommandText = "UPDATE UsersTestTable SET LastName = 'Doe' WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// DELETE - The actual row is not recoverable after the delete
// Only RowId is stored without deleted data.
cmd.CommandText = "DELETE FROM UsersTestTable WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// INSERT again to show new RowId has been generated
cmd.CommandText = "INSERT INTO UsersTestTable VALUES ('johndoe', 'John' , 'DOE');";
cmd.ExecuteNonQuery();
// To view changes we will call GetTrackedDataChanges() function which will yeild
foreach (SqlDatabaseDataChanges tdc in cnn.GetTrackedDataChanges())
{
string DMLType = string.Empty;
switch (tdc.ChangeType)
{
case 1:
DMLType = "INSERT";
break;
case 2:
DMLType = "UPDATE";
break;
case 3:
DMLType = "DELETE";
break;
default:
DMLType = "UNKNOWN";
break;
}
Console.WriteLine("ChangeType {0} \t SchemaName: {1} \t TableName: {2} \t RowId: {3} \t DateTime: {4}"
, DMLType
, tdc.SchemaName
, tdc.TableName
, tdc.RowId
, new DateTime(tdc.NowTicks));
}
// To view changed row simply query using SELECT with where clause
// By default each row is given unique RowId
// Using Linq with changeType = 2 for updated rows only.
Int64 RowId = cnn.GetTrackedDataChanges().Where(item => item.ChangeType == 2).FirstOrDefault().RowId;
cnn.MultipleActiveResultSets = true;
cmd.CommandText = "SELECT * FROM UsersTestTable WHERE RowId = " + RowId;
SQLDatabaseResultSet[] rs = cmd.ExecuteReader(false);
if (rs != null)
{
if (!string.IsNullOrWhiteSpace(rs[0].ErrorMessage))
{
Console.WriteLine(rs[0].ErrorMessage);
return;
}
foreach (object[] row in rs[0].Rows)
{
foreach (object column in row)
{
Console.WriteLine(column);
}
}
}
}
}
}
