locked
Is sql server ce capable of handling multithreaded access? RRS feed

  • Question

  • Hi,

    I am developing a managed application in C#. Since there is no support to get notification for the remote change in contacts and outgoing sms in managed app, I have written two Native DLL's to achieve the same.

    1. First DLL called Mapi.DLL registers for the MAPI notifications and whenever a message is arrived to or sent out from device, it gets the notification and writes the details of the sms to SQL CE database.
    2.  Second DLL called POOM.DLL registers for POOM notifications and whenever contacts or calendar are changed, it gets the notification and writes the details to the SQL CE Database.
    3. In my managed application, I call the function from these two native DLL's using "DLLImport" which registers for both notifications.

    Now, I read the data from the same SQL CE Database and use it in my managed application.

    Hence, I have three applications using the same Database simultaneously.

    I wanted to know what care should I take so that all the three applications can access the same database without fighting among themselves? what synchronization steps I have to take, if any required?

    Please keep in mind I am having mixture of Native and Managed code here (2 Native DLL's and 1 Managed EXE). So please suggest the solution which works on both.

    Thanks and Regards,

    Himanshu

     


    Himanshu chhaya - Windows Mobile and Windows Phone 7 Developer
    Wednesday, May 4, 2011 10:54 AM

Answers

All replies

  • Hi,

    Multi processes accessing is possible with SQL CE. Check this for more information

    http://msdn.microsoft.com/en-us/library/ms171750.aspx

    "Multiple applications—multiple applications on a device can access a single database at the same time. The developer is not required to manage exclusive connections. Users can use database tools, such as SQL Server Compact 3.5 Query Analyzer, while another application is connected to the database. "

    Regards,

    Malleswar


    Regards, Malleswar
    Wednesday, May 4, 2011 11:33 AM
  • Hi,

    Malleswar,

    Thanks for the reply.

    I already went through the link suggested by you.

    In my application, I continuously read from the database at the interval of 5 seconds or so in my Managed app. Meanwhile, If SMS or Call arrives, even that app will try to write the data in the same table... So what happens when both of them try to access the same table and try to change the data?

    I think I have to implement locking, (I may be wrong, what is your take?) but i do not know how to achieve that?

    How to achieve concurrency in my scenario?

    Following is the code snippet of my managed app. After registering for the notifications, I have code which reads from the database continuously at some interval.

    Will all the notifications and this reading from db in managed app run on same thread or seperate threads? How to achieve concurrency in this scenario?

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    
    namespace MyNamespace
    {
      public partial class frmMain : Form
      {
        #region DLL Imports
    
        [DllImport("poomdll.dll")]
        private static extern bool RegisterPOOMNotifications();
    
        [DllImport("Mapidll.dll")]
        private static extern void RegisterSMSNotifications();
    
        #endregion
        
        public frmMain()
        {
          InitializeComponent();
        }
    
        private void frmMain_Load(object sender, EventArgs e)
        {
          try
          {
             sqlUtility.createDatabase();
            //Register for notifications
              RegisterPOOMNotifications();
              RegisterSMSNotifications();
          }
          catch (Exception ex)
          {
            MessageBox.Show("frmMain_Load() : " + ex.Message);
          }
        }
      }
    }
    

     


    Himanshu chhaya - Windows Mobile and Windows Phone 7 Developer
    Wednesday, May 4, 2011 12:18 PM
  • So nobody has faced this kind of problem?

    Please anybody can throw some light on this issue?


    Himanshu chhaya - Windows Mobile and Windows Phone 7 Developer
    Friday, May 6, 2011 9:20 AM
  • Hi Himanshu,

     

    I think you can use lock in SQL to solve this problem. You can use the optimistic or pessimistic locking in Stored Procedure for database or in SQL Command in C++.

     

    Please follow these document:

    Optimistic Locking in SQL Server using the ROWVERSION Data Type

     

    6 ways of doing locking in .NET (Pessimistic and optimistic)

     

     

    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.

     

    I hope my suggestions can help you to solve this problem.

     

    Best regards,

    Jesse


    Jesse Jiang [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Jesse Jiang Tuesday, May 17, 2011 7:18 AM
    • Marked as answer by Jesse Jiang Thursday, May 19, 2011 12:44 PM
    Tuesday, May 10, 2011 9:00 AM