There is a file sharing violation. SQL Server CE 3.5 SP2

Question

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.

Answer 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.