Help at SqlCeChangeTracking

Question

I am trying to use a new class in SqlCe 3.5 SP2 called SqlCeChangeTracking . This class (presumably) allows you to enable change tracking in a table without using RDA replication or synchronization services.

Assuming you have open SqlCeConnection, you enable change tracking in the table as follows:

 SqlCeChangeTracking tracker = new SqlCeChangeTracking(conn); tracker.EnableTracking(TableName, TrackingKeyType.PrimaryKey, TrackingOptions.All); 

This seems to work. When I open the SDF file and view it in SQL Server Management Studio, the table has three additional fields: __sysChangeTxBsn , __sysInsertTxBsn and __sysTrackingContext . According to sparse documentation, these columns (together with the __sysOCSDeletedRows system table) are used to track changes.

The problem is that these three columns always contain NULL values ​​for all rows , no matter what I do. I can add, delete, edit, etc., And these columns remain NULL no matter what (and no deleted records have ever appeared in __sysOCSDeletedRows ).

I have not found any documentation for this class at all, and the promised MSDN API does not exist. Does anyone know how to successfully use this class?

Update: I tried changing this to use TrackingKeyType.Guid , for example:

 tracker.EnableTracking(TableName, TrackingKeyType.Guid, TrackingOptions.All); 

but this raises SqlCeException 29010 "There is no primary key in the table. [Table name = EMPLOYEES]". This is strange because I create the table as follows:

 CREATE TABLE EMPLOYEES (BADGE NVARCHAR(5) PRIMARY KEY, NAME NVARCHAR(50), DEPARTMENT NVARCHAR(10)) 

so he has a primary key (and I can see this PC when opening an SDF file in SQL Management Studio).

Update 2:. If I try to enable tracking with one of the other two parameters ( TrackingKeyType.None or TrackingKeyType.Max ), the application will instantly disappear and disappear without a trace, even using a try / catch block around the line. Never a sign.

Answer 1

I believe there is a problem when enabling change tracking does not start until you close and open SqlCeConnection after calling EnableTracking. Here is some simple code that shows this, the first insert and update do nothing for the tracking columns, but after calling Close and Open in the connection, the second update will be tracked.

 using (var cmd = new SqlCeCommand("CREATE TABLE t1 (c1 int primary key, c2 int)", conn)) { cmd.ExecuteNonQuery(); using (var ceCt = new SqlCeChangeTracking(conn)) { ceCt.EnableTracking("t1", TrackingKeyType.PrimaryKey, TrackingOptions.All); } cmd.CommandText = "insert into t1 (c1, c2) values (1,1)"; cmd.ExecuteNonQuery(); cmd.CommandText = "select __sysChangeTxBsn from t1 where c1 = 1"; var val = cmd.ExecuteScalar(); // This will be null since the connection has not been closed/reopened Console.WriteLine((val is DBNull) ? "NULL" : val); cmd.CommandText = "update t1 set c2 = 2 where c1 = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select __sysChangeTxBsn from t1 where c1 = 1"; val = cmd.ExecuteScalar(); // This will be null since the connection has not been closed/reopened Console.WriteLine((val is DBNull) ? "NULL" : val); // Recycle the connection to get change tracking working conn.Close(); conn.Open(); cmd.CommandText = "update t1 set c2 = 3 where c1 = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select __sysChangeTxBsn from t1 where c1 = 1"; val = cmd.ExecuteScalar(); // This will be non-null and subsequent updates will increase it. Console.WriteLine((val is DBNull) ? "NULL" : val); } 

Answer 2

More documentation on ms-help is available in SQL Compact 3.5 SP2 documents: //MS.SSC.v35/MS.SSC.v35.EN/sscprog/html/5be071e5-41c9-4775-85d4 -a41d6a370fe7.htm - download from here: http://www.microsoft.com/downloads/details.aspx?familyid=746C3A6E-FFB1-4C92-93FA-B3BA41FDE681&displaylang=en .