Error: "Unable to set command text while datareader is active" using ExecuteNonQuery ()

Question

I listen to the data stream and save the data as insert statements in ConcurrentQueue and insert the data with bulk insert using System.Threading.Timer with an interval of 1000. The whole script 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, sqlCommand used only for insertion, just ExecuteNonQuery() in this timer. And from time to time the error message "Unable to install command text while datareader is active" appears. This is absurd, because this code has nothing to do with the internal SQLiteDataReader in sqlCommand .

How can I get rid of this error?

Answer 1

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

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

Thus, you cannot end the local state of one command affecting another command, except due to the lack of connection pool space, etc.

Developers often try to optimize using one connection (and a potential team) over and over, but this is a false economy and leads to problems similar to the ones you showed.