locked
Using a background worker for syncing, cannot access sql ce db from application RRS feed

  • Question

  • - Sync Framework 1.0
    - SQL Compact Edition 3.5 sp1
    - Visual Studio 2008
    - SQL Server 2008

    I have an occasionally connected application, and have run into a problem trying to re-do the synchronization aspect. 

    The project was initially set up to either work online or offline, and if it was online, then use the network sql db as the connection for everything.  Next, if the program was offline, then it would use the local cache database.  The synchronization part works fine, but as the database started to increase in size we noticed that a lag issue was happening and decided to save all data to the local database, then if we are connected to the network, sync the data in the background. 

    The problem is in connecting to the local database from the application, while syncing is happening in the background is throwing a sqlce excpetion "There is a file sharing violation. A different process might be using the file."  All the SQL connections are immediately closed after being used.  The documentation on the Sync Framework does say that it supports background sync.  Is there a point in the sync process, where the entire file is locked.

    Any help on this would be appreciated.

    I have gone through the Sync Framework examples on the MSDN to learn how to set up the sync process.
    Wednesday, February 17, 2010 8:33 PM

Answers

  • I have been thinking a little more about this and here is the process that is happening.

    -The user clicks save on the form
    -The form saves to the ce db
    -A value is set letting the background thread know that it needs to sync
    -once the save is finished the user gets a message stating that the form saved successfully and then they need to click "ok"
    -The user clicks "ok", then the form reloads to populate a datagridview



    The wait may work, but occasionally would still throw errors because if the user delayed in clicking "ok", the two apps, could end up trying to connect at the same time.

    I was thinking maybe the process to let the background thread know to sync shouldn't be triggered until after the form has saved and reloaded.  I will look into this tomorrow.

    This error popped up because I had to modify the form to add the grid view because the users needed to see all the data for a given time when they are in the form.


    I will post again tomorrow after I look into it a bit more.  It will probably be later in the day, so if you have any other ideas before then please let me know.
    • Marked as answer by JTM30 Thursday, February 18, 2010 6:01 PM
    Thursday, February 18, 2010 4:31 AM

All replies

  • From the error message, it seems that there are 2 processes trying to access the same SQL CE database as the same moment.  If this is true, then raising such exception is expected.  In such case, you can try to have the back ground thread to catch the exception and wait for another while to do another sync.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 18, 2010 12:27 AM
    Answerer
  • Leo - doesnt SQLCE support concurrent access?

    JTM -does it always raise the exception or is it intermittent? how does your connection string look like?
    Thursday, February 18, 2010 1:27 AM
  • Leo,

    I kind of thought that was what was happening.  The problem is that the error is happening on the application end, instead of the background thread.  Would it help at all if I made the background thread wait for a couple seconds to begin the sync, to ensure that the other connection was already made.

    Thursday, February 18, 2010 4:23 AM
  • I have been thinking a little more about this and here is the process that is happening.

    -The user clicks save on the form
    -The form saves to the ce db
    -A value is set letting the background thread know that it needs to sync
    -once the save is finished the user gets a message stating that the form saved successfully and then they need to click "ok"
    -The user clicks "ok", then the form reloads to populate a datagridview



    The wait may work, but occasionally would still throw errors because if the user delayed in clicking "ok", the two apps, could end up trying to connect at the same time.

    I was thinking maybe the process to let the background thread know to sync shouldn't be triggered until after the form has saved and reloaded.  I will look into this tomorrow.

    This error popped up because I had to modify the form to add the grid view because the users needed to see all the data for a given time when they are in the form.


    I will post again tomorrow after I look into it a bit more.  It will probably be later in the day, so if you have any other ideas before then please let me know.
    • Marked as answer by JTM30 Thursday, February 18, 2010 6:01 PM
    Thursday, February 18, 2010 4:31 AM
  • Is it a must to use a background thread to do the sync?  In this scenario, can we do sync after the data is saved and reload the data grid view successfully after the OK is pressed?

    Otherwise we probably need to think of using sort of inter-process synchronization to deal with this issue.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 18, 2010 5:14 AM
    Answerer
  • Leo,

    The user could go several days without connecting to our network, and have hundreds of forms filled out, or several thousand forms may have been entered by other users.  If this happens a sync is going to take a little longer and would prevent the user from being able access the application while the sync process is taking place.

    Do you have any examples of inter-process synchronization, or links to examples that I could look at to see if that fits my need.
    Thursday, February 18, 2010 1:53 PM
  • can you check the connection string to open the connection to the db from your application ? if the connection opens the db exclusively, this might be the cause.


    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 18, 2010 4:53 PM
  • The connection string is not opening the db exclusively.  I have tested sending multiple connections to the db and have had no problems with them.  The connection string is defined in my app.config file, so it does not change either for different connections.


    I have changed the form, so that it does not reload until after the background thread has started syncing.  This has fixed the error in almost every spot on the application, and I am just looking at some switching between forms, where there is a potential trouble spot. 


    We have also been discussing the possibility of switching over to SQL express for the deployed computers.  this would be on the heavy side, but it would get rid of any concurrent access issues for good.  We are looking into this, but probably wont make a decision for several weeks. 

    I am going to consider the issue closed because we do have a work around that seems to work almost all the time.  We will just pass a message on to the user to reload the form manually if the connection fails for now.


    Leo,

    Thanks for getting me to think about it a different way.  That really opened up a better way to approach it to get rid of most errors.
    Thursday, February 18, 2010 6:01 PM