none
Multithreaded programming with SQL Server Compact

    Question

  • Is there any documentation on how to use SQL Server Compact objects correctly in a multithreaded application?

    I've looked on MSDN and SSCE BOL and can't find anything definitive.


    Tuesday, March 22, 2011 10:51 PM

Answers

  • SQL CE objects are not thread-safe and are not thread affinitive either. If an instance of SqlCeConnection or SqlCeTransaction is shared across threads without ensuring thread safety, then that may lead to Access Violation exception.

    It is recommended that each thread should use a separate connection than sharing across. If there is really a need for sharing SQL CE objects across threads, then the application should serialize access to these objects.


    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Friday, September 09, 2011 6:17 AM

All replies

  •  

    Hi turquoiseowl,

     

    Thank you for your post.

     

    To use SQL Server Compact is same with other applications, however, for multithreaded applications, a separate ServerConnection object should be used in each thread.

     

    For more detailed information about ServerConnection, please refer the below links:

    http://64.4.11.252/en-us/library/microsoft.sqlserver.management.common.serverconnection(SQL.100).aspx

     

    For using SQL Server Compact Edition in an Application, please refer the below link:

    http://msdn.microsoft.com/en-us/library/aa983340(VS.80).aspx

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Friday, March 25, 2011 9:49 AM
    Moderator
  • Hi Weilin Qiao

    Thank you for taking the time to reply.

    To use SQL Server Compact is same with other applications, however, for multithreaded applications, a separate ServerConnection object should be used in each thread.

    Hmm, I don't think that this is strictly correct, but am not certain: other posts in this forum suggest that Sessions / Connections are 'NOT thread affinitive'. It would be nice to have a definitive page on this in the documentation, one to complement the generous coverage of related issues under "Accessing and Modifying databases". Or failing that a blog item to help us use this very good database most efficiently in multithreaded apps.

    Many thanks.

    Regards Martin


    Tuesday, March 29, 2011 9:00 AM
  • SQL CE objects are not thread-safe and are not thread affinitive either. If an instance of SqlCeConnection or SqlCeTransaction is shared across threads without ensuring thread safety, then that may lead to Access Violation exception.

    It is recommended that each thread should use a separate connection than sharing across. If there is really a need for sharing SQL CE objects across threads, then the application should serialize access to these objects.


    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Friday, September 09, 2011 6:17 AM