NullReferenceException in SqlCommand.ExecuteNonQuery() after calling SqlCommand.Prepare()

Question

I am running into a problem where a NullReferenceException is being thrown. Here is the code:

using (SqlConnection connection = new SqlConnection(@"server=localhost\SQL2005;database=UintaTest;Integrated Security=SSPI;Connect Timeout=240"))
using (SqlCommand command = new SqlCommand(@"SELECT * FROM tblCaseActual WHERE CaseID = @Param", connection))
{
    connection.Open();
    connection.StatisticsEnabled = true;
    SqlParameter commandParameter = new SqlParameter
    {
        ParameterName = "Param",
        SqlDbType = SqlDbType.VarChar,
        Size = 12,
        Value = "1-19"
    };
    command.Parameters.Add(commandParameter);

    command.Prepare();
    //this does not throw an exception
    command.ExecuteNonQuery();

    //This throws an exception
    command.ExecuteNonQuery();
}

The exception is thrown when the second "ExecuteNonQuery" function is called. The first call works fine.

I've looked at this msdn link but that did not help. The code snippet there is very similar to what I am trying to do. I have also searched for other people that have had similar issues, but I haven't found anything.

This doesn't happen with all SQL statements, but I have reproduced it on several different databases and several different tables.

Here is the stack trace as well:

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SqlCommandPrepareTest.Program.Main(String[] args) in C:\Users\jadams\Documents\Visual Studio 2010\Projects\SqlCommandPrepareTest\SqlCommandPrepareTest\Program.cs:line 33
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

Any help would be appreciated. Thanks.

I am doing some automated performance testing on views that are in a database. The code that I posted is a simpler version of what I am doing that still reproduces the problem. I don't need the results from the query, so I use ExecuteNonQuery. Perhaps that is my problem though, because if I use ExecuteReader then it works fine. — Jacob Adams, May 15 '12 at 19:38
Your performance test will be inaccurate because you allow SQL Server to stop generating results early. — usr, May 15 '12 at 21:28

score 0 · Accepted Answer · answered May 16 '12 at 16:05

Thanks for the comments. I ended up changing it to an ExecuteReader() call instead. From my testing, it appears that you can't use a combination of a SELECT statement with an ExecuteNonQuery() call if you also Prepare the statement before hand.

The fact that the first call succeeds and the second call fails tells me that this is likely a bug in the .NET framework.

score 0 · Answer 2 · answered Oct 30 '13 at 10:58

0

.Net does not have bug. it causes because ExecuteNonQuery is not Thread-safe. make it Thread-safe to solve prblem

answered Oct 30 '13 at 10:58

Amir Abolhasani

61
1
5

NullReferenceException in SqlCommand.ExecuteNonQuery() after calling SqlCommand.Prepare()

2 Answers2