SQL Server Ce 4.0 Error 2700

Question

I have a C# application intended to migrate data from MS Access to SQL Server Compact 4.0

There is a large amount of data and as such I am utilizing a large number of Parallel.Foreach statements to go through the data and there are a large number of Reads and Writes to the SQL Server CE database (but only one read from the access database).

It works really well for a good half hour and in fact the database is filling up (I can see this from the file size and checking after the error has occured)

However eventually I get an Aggregate exception thrown whose inner exception is:

SQLServerCEException: Too many Sessions are Open, NativeError 27000

I have searched google and the only solution I found was to explicitly close all commands, connections and adapters when finished, even if they are in Using statements.

These are the three methods I use to communicate with the database:

/// <summary>
/// Executes a single INSERT, UPDATE, DELETE or other Sql Command that modifies the schema or data of the database
/// </summary>
/// <param name="sql">The command to execute</param>
/// <param name="parameters">Any parameters in the command</param>
public void ExecuteCommand(string sql, SqlServerCeParameter[] parameters)
{
    //print debug statements if necessary
    if (_outputSqlStatementsToFile == true) PrintSqlDebuggingInformation(sql, parameters);

    //create a new connection using the connection string
    using (var connection = new SqlCeConnection(_connectionString))
    {
        //create the command that will execute the Sql
        using (var command = new SqlCeCommand(sql, connection))
        {
            //add any parameters
            if (parameters != null) command.Parameters.AddRange(parameters.Select(p => p.ParameterBehind).ToArray());

            try
            {
                //open the connection 
                connection.Open();

                //execute the command
                command.ExecuteNonQuery();
            }
            catch (SqlCeException ex)
            {
                //only catch if the native error is the duplicate value exception otherwise throw as normal
                if (ex.NativeError != 25016) throw;
            }
            finally
            {
                //explicitly close command
                command.Dispose();
            }
        }
        //explicitly close the connection
        connection.Close();
    }
}

/// <summary>
/// Executes a query that returns a single value, for example a COUNT(*) query
/// </summary>
/// <typeparam name="T">The type of the value returned by the query, for example COUNT(*) would be an Integer</typeparam>
/// <param name="sql">The query to execute</param>
/// <param name="parameters">Any parameters in the query</param>
/// <returns>A single value cast to type T</returns>
public T ExecuteQuery<T>(string sql, SqlServerCeParameter[] parameters)
{
    //print debug statements if necessary
    if (_outputSqlStatementsToFile == true) PrintSqlDebuggingInformation(sql, parameters);

    //the result
    T result;

    //create a new connection using the connection string
    using (var connection = new SqlCeConnection(_connectionString))
    {
        //create the command that will execute the Sql
        using (var command = new SqlCeCommand(sql, connection))
        {
            //add any parameters
            if (parameters != null) command.Parameters.AddRange(parameters.Select(p => p.ParameterBehind).ToArray());

                //open the connection 
                connection.Open();

                //execute the command and cast the result to the type given to the method
                result = (T)command.ExecuteScalar();

                //explicitly dispose the command
                command.Dispose();
        }
        connection.Dispose();
    }
    //return the result
    return result;
}

/// <summary>
/// Executes a query that returns a list of rows in a data table
/// </summary>
/// <param name="sql">The query to execute</param>
/// <param name="parameters">Any parameters in the query</param>
/// <returns>A data table of rows matching the query</returns>
public DataTable ExecuteQuery(string sql, SqlServerCeParameter[] parameters)
{
    //print debug statements if necessary
    if (_outputSqlStatementsToFile == true) PrintSqlDebuggingInformation(sql, parameters);

    //the returnable data table
    var table = new DataTable();

    //create a new connection using the connection string
    using (var connection = new SqlCeConnection(_connectionString))
    {
        //create the command that will execute the Sql
        using (var command = new SqlCeCommand(sql, connection))
        {
            //add any parameters
            if (parameters != null) command.Parameters.AddRange(parameters.Select(p => p.ParameterBehind).ToArray());

            //create an adapter to which will fill the data table that is to be returned
            using (var adapter = new SqlCeDataAdapter(command))
            {
                //open the connection 
                connection.Open();

                //fill the table using the adapter
                adapter.Fill(table);

                //explicitly dispose the adapter
                adapter.Dispose();
            }
            command.Dispose();
        }
        connection.Dispose();
    }
    //return the result
    return table;
}

These methods are called countless times and it is usually on the command.ExecuteNonQuery() in the first method that the error occurs.

Does anyone have any suggestions?

Answer 1

I suggest not to open/close connection for each SQL statement. Instead open such a number connections how many are parallel processes - one for each. Open connections at start of migration and close after. You can issue many SQL statements using the same open connection.

Alternatively you can use simple connection pool implementation like this: "Creating a custom database connection pool - CodeProject"

This also boosts migration performance.