4

I've been getting this random issue in my .NET CF application that uses a SQL Server CE 3.5 SP2 database.

When the CF application starts up it performs database maintenance to verify the .SDF database file to check using the SQL Server CE Engine class:

using (SqlCeEngine engine = new SqlCeEngine(Resources.SqlCeConnectionString))
{
   Log.Info("Starting database Verification.");

   if (!engine.Verify())
   {
      Log.Warn("Database failed verification.");
      engine.Repair(null, RepairOption.RecoverAllOrFail);
      Log.Info("Database successfully repaired.");
   }
   else
   {
      Log.Info("Database Verification successful.");
   }
}

If the application is verified properly then I copy the .SDF into a backup folder:

if (File.Exists(Resources.DatabaseFileLocation))
{
   File.Delete(Resources.BackupDatabaseFileLocation);
   File.Copy(Resources.DatabaseFileLocation, Resources.BackupDatabaseFileLocation);
   Log.Info("Database backup complete.");
}
else
{
   Log.Info("Could not find Device Database.");
}

Once the backup is complete the application starts up and the first connection the application tries to make to the .SDF results in this exception:

There is a file sharing violation. A different process might be using the file.

Here is the Log file info:

2013-05-13 11:52:29,894 [INFO ] - Starting database Verification.
2013-05-13 11:52:33,832 [INFO ] - Database Verification successful.
2013-05-13 11:52:33,838 [INFO ] - Database backup starting.
2013-05-13 11:52:46,941 [INFO ] - Database backup complete.
2013-05-13 11:52:47,933 [ERROR] - There is a file sharing violation. A different process might be using the file. [ \Program Files\ApplicationName\DB.sdf ]
    at System.Data.SqlServerCe.SqlCeConnection.ProcessResults(Int32 hr)    at System.Data.SqlServerCe.SqlCeConnection.Open(Boolean silent)
    at System.Data.SqlServerCe.SqlCeConnection.Open()
    at CFApp.MainScreen.GetStartupData()
    at CFApp.MainScreen..ctor()
    at CFApp.Program.RunInReleaseMode() 
    at CFApp.Program.Main()

I've been researching the causes of this problem for weeks now and I cannot find anything. Any guidance or help would be very much appreciated.

Based on my efforts so far I can confirm these things

  • The issue happens randomly. The startup procedure executes properly a number of times and then it will randomly throw this error
  • There are no other connections being made to the database at the time considering that the application is just in the startup process.
  • Every time the app opens a connection, it disposes it as soon as its finished. This is done with using statements. There is no open connection being left open in case of an unexpected shutdown/restart.
  • The SqlCeEngine is being disposed of with a using statement. Is there a possible delay between when the object is disposed, to when its connection is closed?
  • There are no other processes running at this time. The device is locked down to only allow my application to be executed. The task manager only shows ActiveSync and my app. The device occasionally communicates with another desktop application using OpenNETCF.Desktop.Communication RAPI library through USB. Active Sync has to be turned on in order for this to work properly. The desktop computer it communicates with is Win XP.

  • I've seen on some other forums that ActiveSync could be keeping a lock on the file which could cause the problem. In the ActiveSync section of the device there is nothing selected to sync with the desktop. The "Files" checkbox is unchecked. If anyone else has any suggestions on how I can ensure that this file is excluded from ActiveSync, it would also be very helpful.

  • UPDATE - I used dotPeek to look at the SqlCeEngine Dispose method. I think everything seems to be in order here and my connection should be getting disposed properly?

    private void Dispose(bool disposing)
{
  if (!disposing)
    return;
  this.connStr = (string) null;
  this.connTokens = (Hashtable) null;
  NativeMethods.DllRelease();
  this.isDisposed = true;
}

  • UPDATE - I tried running this test to see if I could reproduce the error and I came up with nothing. The code executed properly and the application started as it should without any file sharing issues.

    for (int i = 0; i < 50; i++)
{
    using (SqlCeEngine engine = new SqlCeEngine(Resources.SqlCeConnectionString))
    {
        //Log.Info("Starting database Verification.");
        if (!engine.Verify())
        {
            Log.Warn("Database failed verification.");
            engine.Repair(null, RepairOption.RecoverAllOrFail);
            Log.Info("Database successfully repaired.");
        }
        else
        {
            Log.Info("Verified" + i);
        }
    }

    if (File.Exists(Resources.BackupDatabaseFileLocation))
    {
        File.Delete(Resources.BackupDatabaseFileLocation);
    }

    File.Copy(Resources.DatabaseFileLocation, Resources.BackupDatabaseFileLocation);
    Log.Info("File Copied " + i);
    GetStartupData();
}

Thank you very much in advance for any info you can provide.

Fabian N.
  • 3,807
  • 2
  • 23
  • 46
neverseenjack
  • 316
  • 2
  • 13
  • I can only assume that either the database verification or backup did not yet close the file. File operations may be cached and still run in background when you try to access the DB. For file.write etc. we have an explicit Flush() method but I do not see that for file.copy. What happens if you place the database access (the open) in a try catch block and retry the access several times with some sleep in between? – josef May 16 '13 at 04:01
  • @josef I will try that first thing tomorrow. I assumed that it was either one of those methods keeping the file open, but I cannot tell which one. I know SqlCeEngine opens a connection with FileMode=Exclusive, which would cause the error if something tries to access it, but I dispose of it immediately which should release the lock. I wonder if File.Copy would have an exclusive lock on it as well? – neverseenjack May 16 '13 at 04:34

1 Answers1

1

I have found that not all classes implement IDisposable like they are supposed to. Microsoft's OLE JET database engine, for example, will not close a database connection when called from a using statement.

You could be having those issues here.

I would suggest, as a start, to explicitly call Close() on your connections. If that does not solve the issues, you could add an explicit call to Dispose().

It could also be that the Garbage Collector (GC) has not had a chance to handle all of the IDisposable code before your application attempts to access it again.

  • I thought of this as well but I found a couple things to lead me believe otherwise: – neverseenjack May 16 '13 at 19:03
  • 1. This post - http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/fef2a33a-d5a8-46f8-84ed-cb334cb7647f 2. SqlCeEngine does not provide an explicit "Close" method. I used dotPeek to look at the class and its Dispose method seems to be cleaning up the resources correctly. Calling Dispose() within a using statement is redundant. – neverseenjack May 16 '13 at 19:22
  • ...and I see it ActiveSync is not a suspect either. Can you listen for and handle when your device goes into and comes out of Suspend Mode? If so, you might experiment around with keeping an Open Connection until your device goes into Suspend Mode, Close the Connection, then re-establish your connection when the device comes Out of Suspend Mode. ...although, I personally do not like the idea of keeping an open connection. –  May 16 '13 at 19:35
  • Although it might solve this problem, I feel that having that open connection the whole time might cause a whole different set of headaches, particularity (but not only) in the suspend/wake cycle scenario you described. Not to mention that it would also require a complete redesign of my implementation at this point which I would definitely have a hard time selling. :-) – neverseenjack May 17 '13 at 00:48