Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

SqlCommand oddity raises NullReferenceException on an otherwise valid query

Bit of a head scratcher for this one. I was working on some ADO.NET code that involved calling a stored procedure with many (10k+) table valued parameter rows being passed in. Occasionally, I’d see a bug where ExecuteNonQuery would result in an exception with the following stack trace (I tried it with ExecuteReader and ExecuteScalar just to be sure as well):

System.NullReferenceException was unhandled by user code
Message=Object reference not set to an instance of an object.
at System.Data.SqlClient.SqlCommand.OnReturnStatus(Int32 status)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

I knew for sure the command object was not null, and so I started looking at the Reference Source. It seemed the parameter collection was the cause of the issue.

I enabled CLR debugging in Visual Studio and dove in. The most relevant block of that function is here:

 // see if a return value is bound
int count = GetParameterCount(parameters);
for (int i = 0; i < count; i++) {
	SqlParameter parameter = parameters[i];
	if (parameter.Direction == ParameterDirection.ReturnValue) {
		object v = parameter.Value;

In my case, count was over 65,000, and some of the later members of the list weren’t fully initialized yet (due to some multi-threading issues, and creating many many parameters) – and I was not paying attention to the return value, so I had never added it. However, the exception completely went away by adding the following before any other parameters:

SqlCommand cmd = ...; // initialization code for SqlCommand here
SqlParameter retParam = new SqlParameter("@result", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;

This ensures that the ReturnValue parameter is the first in the collection, and when that iteration occurs in OnReturnStatus, it will find the parameter immediately (instead of potentially traversing 65k parameters); it also got rid of the non-sensical exception in ADO.NET so I could bear down on the other issues related to this particular piece of code. The big take away here is that it seems like it would be best to always add the ReturnValue parameter immediately to a SqlParameterCollection, even if you don’t intend to use it. This would likely apply to EntityFramework EntityCommands (which are based on SqlCommand) as well.

Learn more about Tallan or see us in person at one of our many Events!

Share this post:

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>