Sql Compact randomly produces AccessViolationException
access-violation, sql-server-ce
Solution
Just because you set a connection to command.Connection does not mean it is set.
command.Connection = connection;
command.Connection.GetHashCode() == connection.GetHashCode(); // may return false.
This is the cause of the problem. Can be corrected by using connection.CreateCommand();
Problem
I am running Sql Server Compact Edition 3.5.1.0 SP1 in a multi-thread application. The application randomly runs insert queries in transactions. With short transactions, it works fine. But when the transactions get longer and delay between executions get shorter or when I run the application in debug mode, SqlCE begins to throw the following exception randomly: AccessViolationException Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at SqlCompactTest.TransactedCommandGroupExecutionTest.Test() in D:\Projects\PlayGround\SqlCompactTest\SqlCompactTest\TransactedCommandGroupExecutionTest.cs:line 53 at SqlCompactTest.ExecutionTest.RunTest() in D:\Projects\PlayGround\SqlCompactTest\SqlCompactTest\ExecutionTest.cs:line 60 at SqlCompactTest.ExecutionTest.TimerElapsed(Object sender, ElapsedEventArgs e) in D:\Projects\PlayGround\SqlCompactTest\SqlCompactTest\ExecutionTest.cs:line 68 at System.Timers.Timer.MyTimerCallback(Object state) The code I am running is: ``` IDbConnection connection = m_connectionProvider.GetConnection(); // Just returns new connection connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); foreach (IDbCommand command in m_commands) { command.Connection = connection; command.ExecuteNonQuery(); // This line throws exception Thread.Sleep((int)m_delayBetweenExecutions); } transaction.Commit(); connection.Close(); ``` This code is running simultaneously in two threads. Connection string is: ``` "Data Source=testDB.sdf;Encrypt Database=True;Password=test;File Mode=Read Write;Persist Security Info=False;Max Database Size=1024" ``` I found absurd solutions on Internet like reverting back to Framework 1.1, changing the order of method arguments, enabling/disabling optimizations etc. but none of them worked for me. I also iterated through possible solutions on microsoft sites but I could not find a solution that works for me. My database file version is 3.5.0.0. How can I solve this?