Cant insert into Identity Column when Multithreading

Question

I have a data migration tool which until recently seemed to work really well.

I run through a collection of data rows using Parallel.Foreach, calculate some variables to be inserted into a new record of a table and then run an SQL statement to insert the data.

Parallel.ForEach<DataRow>(dataTable.Rows, row =>
{
     string reference = row["ref"].ToString();
     int version = (int)row["version"];

     string insertStatement = "INSERT INTO Item (Reference, Version) VALUES (@reference, @version)";

     _database.ExecuteCommand(insertStatement, new SqlServerCeParameter[]
     {
         new SqlServerCeParameter("@reference", reference, SqlDbType.NVarChar),
         new SqlServerCeParameter("@version", version, SqlDbType.Int),
     });
});

public void ExecuteCommand(string sql, SqlServerCeParameter[] parameters)
{
    //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 
            if (_connection.State == ConnectionState.Closed) _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();
        }
    }
}

However I get an aggregate exception, the inner exception of which is as follows:

{"The column cannot contain null values. [ Column name = ID,Table name = Item ]"}

The structure of the table is as follows:

CREATE TABLE Item
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Reference NVARCHAR(50) NOT NULL,
    Version INT NOT NULL
);

Now I don't understand this error as the ID is an identity column.

One thought that I had was that it cant calculate two ids at the same time because of the multithreading but this seems like a really flimsy reason as SqlServerCe is meant to be ok for multi user environments.

Answer 1

IMPORTANT: SQL CE objects are not thread-safe. You are using _connection in each call which I am guessing is a single instance of SqlCeConnection?

It is recommended that each thread should use its own separate connection rather than sharing it across multiple threads. So, try creating a new SqlCeConnection in your ExecuteCommand method and connecting each time.

This may not achieve the speed increase you were hoping / expecting but then I am not sure multi-threading works as you expect. You need multiple cores / processors for this to be effective and is a deep topic in itself.