Error : "Cannot set commandtext while a datareader is active" with ExecuteNonQuery()

Question

I listen a data stream and store data as insert statements in a ConcurrentQueue and insert the data with bulk insertion using a System.Threading.Timer with an interval of 1000 . The whole scenario runs on a static class. Here is the code:

static void timer_Elapsed(object sender, ElapsedEventArgs e)
{
    if (queryQueue.IsEmpty)
        return;
    string text = "";
//bulkBuilder is StringBuilder.
//queryQueue is ConcurrentQueue
    bulkBuilder.AppendLine("PRAGMA synchronous = 0;PRAGMA count_changes = FALSE;PRAGMA journal_mode=OFF;Begin;");
    while (queryQueue.TryDequeue(out text))
    {
        bulkBuilder.Append(text);
        bulkBuilder.AppendLine(";");
    }
    bulkBuilder.AppendLine("Commit;");

    try
    {
        sqlCommand.CommandText = bulkBuilder.ToString();
        sqlCommand.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        Console.WriteLine("Error while inserting Data : " + ex.Message);
    }
    finally
    {
        bulkBuilder.Clear();
    }

}

Funny thing is, sqlCommand is used just for insertion, just to ExecuteNonQuery() in this timer. And time to time a get an error saying "Cannot set commandtext while a datareader is active." This is nonsense since this code has nothing to do with the inner SQLiteDataReader in the sqlCommand.

How can I get rid of this error?

Answer 1

I would create a new SqlCommand (or whatever the type of sqlCommand is) for each SQL statement. Let the connection pool handle making it all efficient - each time you need to do something with the database:

• Create and open the connection
• Create the command
• Execute the command
• Dispose of the command and connection (with a using statement)

That way you can't end up with the local state of one command affecting another command, other than due to running out of connection pool space etc.

Developers often try to optimize by using one connection (and potentially command) over and over, but this is a false economy and leads to problems like the one you've shown.