Search code examples
c#sql-server-ce

SQL Server CE 4.0 not updating immediately after call to Fill

I am attempting to update a row in the database relatively often, about once per second, but SQL Server CE seems to be ignoring a majority of the updates until it feels like actually committing one of the updates.

If I debug the code and sit on the Fill(ds); line, and step over it, then check the database, it will have updated every time.

It seems like because I'm updating often, it is arbitrarily ignoring some of the updates and eventually just committing one after 5-10 seconds of processing.

I am wondering if there is something with SQL Server CE 4.0 that would cause this, or if there is some property of the adapter that needs to be set in order to push the updates instantly?

It is usually just one column being updated, which is essentially a counter, so for each ++ there will be an update to the current row. It will generally take 5+ calls until it actually updates that column, however, if I increase the time between each call to say 3-5 seconds, it will commit every change to that column.

Edit:

The code is really just:

bool isPass = false;
int iCount = 0;

if (EnsureConnectionOpen()) {
    while (!isPass && (iCount++ < 3)) {
        try {
            cmd.Prepare();
            CreateDataAdapter(cmd).Fill(ds);
            isPass = true;
        }
    }
}

With some catch logic below, but I'm not generating any exceptions here.

CreateAdapter is spitting out cmd as an SqlCeCommand, which contains parameters.

The SQL is a standard update statement (which is cmd.CommandText): 

UPDATE someTable 
SET column1 = @param1, 
    column2 = @param2. . .

Transaction code:

SqlCeTransaction trans = (cmd.Connection as SqlCeConnection).BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit(CommitMode.Immediate);

I am using the as SqlCeConnection cast because the cmd object comes into the function as a DbCommand initially.

Solution

  • SQL CE only flushes to disk at intervals, and it can be changed via the connection string:

    "Specified the interval time (in seconds) before all committed transactions are flushed to disk. If not specified, the default value is 10."

    Do not include the setting in your connection string, you will risk loosing data: http://erikej.blogspot.dk/2016/02/sql-server-compact-40-sp1-hotfix.html

    You can force it to flush by wrapping in a SqlCeTransaction with a CommitMode of Immediate.

    http://erikej.blogspot.dk/2013/05/sql-server-compact-code-snippet-of-week_21.html