none
SQL CE compact and EF thread safety RRS feed

Answers

  • @ErikEJ

    Actually I'm not using entity framework yet. I was thinking of using it, if it would help me with this threading issue. 

    I've been reading alot about SQL CE thread safety on the internet, and I noticed you have lots of post about SQL CE, so I know you're an expert in this area.

    I will explain how my application works and hopefully you will be able to help me.

    I have a desktop application winforms C# .net 4.0. This application uses SQL CE 4.0 database.

    Main GUI thread is writing, reading and updating database.

    When application starts SynchronizerClass is instantiated.

    SynchronizerClass has a timer that ticks every 2 minutes. So when timer ticks event is triggered on another thread. On this thread some data is read from database and then sent to my cloud wcf service. First it checks for records that are finished - those records will never be changed by GUI thread. Records that cant be changed by GUI thread are then updated with flag IsSynched=1. Thats it. Here is a posibillity that when SynchronizerClass was doing work that user was doing some work on GUI thread at the same time?

    I have IDatabaseHandler interface for SQL CE implementation and for MSSQL implementation. By the way MSSQL works because its thread safe.

    At the application start instance is created for IDatabaseHandler. Then IDatabaseHandler is injected to SynchronizerClass and also in gui forms. So both treads use the same instance...

    Sometimes very rearly some records dont get saved properly from main thread. Maybe 2 times per month our customers complain.. Im trying to reapeat this error but its impossible, I was testing program for one month and same situation happened only once.

    Now I put lock() on all my methods. You can see it in method InsertUsers bellow.

    Do you think this is good enough solution?

    Currently my implementation is like this:

    public class DatabaseHandlerSqlCe : IDatabaseHandler
        {
            private string _connectionString;
            private static object _objLock = new object();

            public DatabaseHandlerSqlCe(string connectionString = null)
            {
                if (connectionString == null)
                {
                    _connectionString =
                         "Data Source=" + Environment.CurrentDirectory + "\\somedb.sdf;Password=1234";
                }
                else
                {
                    _connectionString = connectionString;
                }

                _settingsGlobal = GetSettingsGlobal();
            }

    public bool InsertUsers(Users users) { lock (_objLock) { using (SqlCeConnection connection = new SqlCeConnection(_connectionString)) { SqlCeTransaction transaction; connection.Open(); transaction = connection.BeginTransaction(); try { foreach (User user in users.ListOfUsers) { InsertUser(connection, transaction, user); foreach (UserDetail detail in user.UserDetails) { InsertUserDetail(connection, transaction, detail); } } transaction.Commit(); return true; } catch { transaction.Rollback(); return false; } } } }

    private void InsertUser(SqlCeConnection connection, SqlCeTransaction transaction, User user)
            {
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO Users " +
                                  "           (UserID, CloudCustomerID, Username, Password, CardNumber, LoginFlag, LogoutFlag,  " +
                                  "            SaveFlag, Handedness, Email, Mobile, HourlyRate, Active, Datemod) " +
                                  "VALUES     (@UserID, @CloudCustomerID, @Username, @Password, @CardNumber, @LoginFlag, @LogoutFlag, " +
                                  "            @SaveFlag, @Handedness, @Email, @Mobile, @HourlyRate, @Active, @Datemod);";
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;

                cmd.Parameters.Add("@UserID", SqlDbType.Int);
                cmd.Parameters.Add("@CloudCustomerID", SqlDbType.Int);
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar);
                cmd.Parameters.Add("@CardNumber", SqlDbType.NVarChar);
                cmd.Parameters.Add("@LoginFlag", SqlDbType.Int);
                cmd.Parameters.Add("@LogoutFlag", SqlDbType.Int);
                cmd.Parameters.Add("@SaveFlag", SqlDbType.Int);
                cmd.Parameters.Add("@Handedness", SqlDbType.Int);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Mobile", SqlDbType.NVarChar);
                cmd.Parameters.Add("@HourlyRate", SqlDbType.Float);
                cmd.Parameters.Add("@Active", SqlDbType.Bit);
                cmd.Parameters.Add("@Datemod", SqlDbType.DateTime);

                cmd.Parameters["@UserID"].Value = user.UserID;
                cmd.Parameters["@CloudCustomerID"].Value = user.CloudCustomerID;
                cmd.Parameters["@Username"].Value = user.Username;
                cmd.Parameters["@Password"].Value = user.Password;
                cmd.Parameters["@CardNumber"].Value = user.CardNumber;
                cmd.Parameters["@LoginFlag"].Value = user.LoginFlag;
                cmd.Parameters["@LogoutFlag"].Value = user.LogoutFlag;
                cmd.Parameters["@SaveFlag"].Value = user.SaveFlag;
                cmd.Parameters["@Handedness"].Value = user.Handedness;
                cmd.Parameters["@Email"].Value = user.Email;
                cmd.Parameters["@Mobile"].Value = user.Mobile;
                cmd.Parameters["@HourlyRate"].Value = user.HourlyRate;
                cmd.Parameters["@Active"].Value = user.Active;
                cmd.Parameters["@Datemod"].Value = user.Datemod;

                cmd.ExecuteNonQuery();
            }

    }



    • Edited by wartmignef Tuesday, March 10, 2015 5:59 PM
    • Marked as answer by wartmignef Tuesday, March 10, 2015 8:47 PM
    Tuesday, March 10, 2015 5:57 PM
  • You did not use Lock before, or? But yes, Lock should Work. But what error did you see when the save failed? Do you have a retry pattern or do you use the retry options in sqlce 4,0 sp1?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by wartmignef Tuesday, March 10, 2015 8:47 PM
    Tuesday, March 10, 2015 7:47 PM

All replies

  • http://mehdi.me/ambient-dbcontext-in-ef6/

    Dbcontext is not thread-safe

    You must never access your DbContext-derived instance from multiple threads simultaneously. This might result on multiple queries being sent concurrently over the same database connection. It will also corrupt the first-level cache that DbContext maintains to offer its Identity Map, change tracking and Unit of Work functionalities.

    Tuesday, March 10, 2015 4:11 PM
  • What if I put lock() around every method that uses DBContext?

    lock(_obj)

    {

    using (DbContext db = new DbContext())

    {

    ......

    }

    }

    Tuesday, March 10, 2015 4:26 PM
  • I don't know try it and find out, but it seems to me that you maybe trying to use an ORM in a stutiation that it should not be used in.
    Tuesday, March 10, 2015 4:40 PM
  • Dont. Create a DbContext instance per thread...

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Tuesday, March 10, 2015 5:14 PM
  • @ErikEJ

    Actually I'm not using entity framework yet. I was thinking of using it, if it would help me with this threading issue. 

    I've been reading alot about SQL CE thread safety on the internet, and I noticed you have lots of post about SQL CE, so I know you're an expert in this area.

    I will explain how my application works and hopefully you will be able to help me.

    I have a desktop application winforms C# .net 4.0. This application uses SQL CE 4.0 database.

    Main GUI thread is writing, reading and updating database.

    When application starts SynchronizerClass is instantiated.

    SynchronizerClass has a timer that ticks every 2 minutes. So when timer ticks event is triggered on another thread. On this thread some data is read from database and then sent to my cloud wcf service. First it checks for records that are finished - those records will never be changed by GUI thread. Records that cant be changed by GUI thread are then updated with flag IsSynched=1. Thats it. Here is a posibillity that when SynchronizerClass was doing work that user was doing some work on GUI thread at the same time?

    I have IDatabaseHandler interface for SQL CE implementation and for MSSQL implementation. By the way MSSQL works because its thread safe.

    At the application start instance is created for IDatabaseHandler. Then IDatabaseHandler is injected to SynchronizerClass and also in gui forms. So both treads use the same instance...

    Sometimes very rearly some records dont get saved properly from main thread. Maybe 2 times per month our customers complain.. Im trying to reapeat this error but its impossible, I was testing program for one month and same situation happened only once.

    Now I put lock() on all my methods. You can see it in method InsertUsers bellow.

    Do you think this is good enough solution?

    Currently my implementation is like this:

    public class DatabaseHandlerSqlCe : IDatabaseHandler
        {
            private string _connectionString;
            private static object _objLock = new object();

            public DatabaseHandlerSqlCe(string connectionString = null)
            {
                if (connectionString == null)
                {
                    _connectionString =
                         "Data Source=" + Environment.CurrentDirectory + "\\somedb.sdf;Password=1234";
                }
                else
                {
                    _connectionString = connectionString;
                }

                _settingsGlobal = GetSettingsGlobal();
            }

    public bool InsertUsers(Users users) { lock (_objLock) { using (SqlCeConnection connection = new SqlCeConnection(_connectionString)) { SqlCeTransaction transaction; connection.Open(); transaction = connection.BeginTransaction(); try { foreach (User user in users.ListOfUsers) { InsertUser(connection, transaction, user); foreach (UserDetail detail in user.UserDetails) { InsertUserDetail(connection, transaction, detail); } } transaction.Commit(); return true; } catch { transaction.Rollback(); return false; } } } }

    private void InsertUser(SqlCeConnection connection, SqlCeTransaction transaction, User user)
            {
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO Users " +
                                  "           (UserID, CloudCustomerID, Username, Password, CardNumber, LoginFlag, LogoutFlag,  " +
                                  "            SaveFlag, Handedness, Email, Mobile, HourlyRate, Active, Datemod) " +
                                  "VALUES     (@UserID, @CloudCustomerID, @Username, @Password, @CardNumber, @LoginFlag, @LogoutFlag, " +
                                  "            @SaveFlag, @Handedness, @Email, @Mobile, @HourlyRate, @Active, @Datemod);";
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;

                cmd.Parameters.Add("@UserID", SqlDbType.Int);
                cmd.Parameters.Add("@CloudCustomerID", SqlDbType.Int);
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar);
                cmd.Parameters.Add("@CardNumber", SqlDbType.NVarChar);
                cmd.Parameters.Add("@LoginFlag", SqlDbType.Int);
                cmd.Parameters.Add("@LogoutFlag", SqlDbType.Int);
                cmd.Parameters.Add("@SaveFlag", SqlDbType.Int);
                cmd.Parameters.Add("@Handedness", SqlDbType.Int);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Mobile", SqlDbType.NVarChar);
                cmd.Parameters.Add("@HourlyRate", SqlDbType.Float);
                cmd.Parameters.Add("@Active", SqlDbType.Bit);
                cmd.Parameters.Add("@Datemod", SqlDbType.DateTime);

                cmd.Parameters["@UserID"].Value = user.UserID;
                cmd.Parameters["@CloudCustomerID"].Value = user.CloudCustomerID;
                cmd.Parameters["@Username"].Value = user.Username;
                cmd.Parameters["@Password"].Value = user.Password;
                cmd.Parameters["@CardNumber"].Value = user.CardNumber;
                cmd.Parameters["@LoginFlag"].Value = user.LoginFlag;
                cmd.Parameters["@LogoutFlag"].Value = user.LogoutFlag;
                cmd.Parameters["@SaveFlag"].Value = user.SaveFlag;
                cmd.Parameters["@Handedness"].Value = user.Handedness;
                cmd.Parameters["@Email"].Value = user.Email;
                cmd.Parameters["@Mobile"].Value = user.Mobile;
                cmd.Parameters["@HourlyRate"].Value = user.HourlyRate;
                cmd.Parameters["@Active"].Value = user.Active;
                cmd.Parameters["@Datemod"].Value = user.Datemod;

                cmd.ExecuteNonQuery();
            }

    }



    • Edited by wartmignef Tuesday, March 10, 2015 5:59 PM
    • Marked as answer by wartmignef Tuesday, March 10, 2015 8:47 PM
    Tuesday, March 10, 2015 5:57 PM
  • It seems to me that if WCF is invloved, then you can invlove it more by doing database activity behind a WCF service with EF, becuase WCF is thread safe with the Windows Deskop solution as a client to the WCF service.

    It also may be that the Windows desktop solution is not implementing Seperation of concerns and too much is happening at the UI form.

    Tuesday, March 10, 2015 7:30 PM
  • Application must work offline at all times. Synchronizer only reads data from local SQLCE and then calls wcf service to insert data into SQL Azure...

    Application is restaurant POS cashregister... if there is no internet application works normally and when connection appears then data is pushed to cloud...

    Tuesday, March 10, 2015 7:37 PM
  • You did not use Lock before, or? But yes, Lock should Work. But what error did you see when the save failed? Do you have a retry pattern or do you use the retry options in sqlce 4,0 sp1?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by wartmignef Tuesday, March 10, 2015 8:47 PM
    Tuesday, March 10, 2015 7:47 PM
  • I dont use retry pattern. Actually I dont know what retry pattern is. Should I have implemented retries in above code? If so, could you provide me with a simple example?

    You said locks should work.. SqlCeConnection is not thread safe as I read online. 

    So my above code wouldnt work if one thread called method1 and another one method2 (and both methods 1 and 2 open SqlCeConnection)? That would mean two SqlCeConnections open at the same time? Or does it mean that all other methods are locked until one method finishes?

    I only have code like I posted above. I have 100 methods - all writen like code above.

    Tuesday, March 10, 2015 8:30 PM
  • Ok. I tested locks in console, so everything should be fine.

    Here the code if somebody else will need it.

    @ErikEJ

    I dont know what exception occoured, but I'm going to put logging inside that class. And if error occours I will post it here.

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                LockTest lt = new LockTest();
    
                Task.Factory.StartNew(() => lt.Method1());
                Task.Factory.StartNew(() => lt.Method2());
    
                Console.ReadLine();
            }
        }
    
        public class LockTest
        {
    
            private object _lock = new object();
    
            public void Method1()
            {
                Console.WriteLine("Method1 entered.");
                lock (_lock)
                {
                    Console.WriteLine("Method1");
                    Thread.Sleep(5000);
                    Console.WriteLine("Method1 finished.");
                }
            }
    
            public void Method2()
            {
                Console.WriteLine("Method2 entered.");
                lock (_lock)
                {
                    Console.WriteLine("Method2 inside.");
                    Thread.Sleep(5000);
                    Console.WriteLine("Method2 finished.");
                }
            }
        }
    }


    • Edited by wartmignef Tuesday, March 10, 2015 8:46 PM
    Tuesday, March 10, 2015 8:45 PM
  • It seems to me that MS SQL Server Service Broker, which  MS SQL Server Express has SB, would be the way to go and dump the transactions into a SB queue,  and the transactions sent to your SQL Azure from the SB queue  

    Tuesday, March 10, 2015 8:56 PM