What is the purpose of System.Data.SqlClient.SqlParameter.IsNullable?

Question

I'm currently trying to write a simple C# wrapper class for all the stored procedures in a database.

While building some parameters in C#, I noticed the property SqlParameter.IsNullable and wondered what this is for. As far as I am aware, it is not possible to declare a stored procedure parameter as NOT NULL and therefore NULL is always allowed to be passed to any parameter.

Through testing, it appears that setting the IsNullable property to false has no effect and still allows the SqlParameter.Value property to be set to null.

Can anybody explain the purpose of this property?

Thanks for looking.

Answerers looking for the bounty should review these links:

How to restrict NULL as parameter to stored procedure SQL Server?

SQL Parameter IsNullable

I assume SqlParameter.IsNullable only makes sense when…?

http://social.msdn.microsoft.com/forums/en-US/vblanguage/thread/b7a08616-58d1-4cdc-a3e9-9353e292667b

Answer 1

The SqlParameter class inherits from the abstract base class DbParameter, which defines

    public abstract bool IsNullable {get; set;} 

So SqlParameter needs to have a public implementation of the IsNullable property. The DbParameter class is the base class for all the database parameter implementations that are included in System.Data.

One must assume then that there are other DBMS's that explicitly allow or deny procedure or function parameters to be explicitly defined as nullable or not nullable, and SqlParameter.IsNullable only exists because SqlParameter implements the more generic common database parameter class and interfaces that are common to other .NET database interaction classes.

Looking in reflector, the SqlParameter class doesn't use IsNullable, other than to pass the value along when it gets converted to an "InstanceDescriptor". I didn't dig into what the InstanceDescriptor class is used for, but I did check out the SqlCommand class, notably the BuildParamList method, which converts the SqlParameterCollection into the SQL string of parameters sent to the database.

The BuildParamList method loops through the SqlParameterCollection, and uses a StringBuilder to build the parameter string. BuildParamList doesn't use the IsNullable property or value anywhere in its implementation. In fact, a reference to SqlParameter.IsNullable doesn't appear anywhere in the SqlCommand class.

It's possible that I missed a reference to it in some internal/private method that passes a SqlParameter object to a different class, but if the BuildParamList method doesn't use it, it doesn't matter because it's not affecting the SQL string being sent to SQL Server.

In addition to the test cases you peformed, examining the contents of the SqlCommand class supports the conclusion that you can safely ignore the SqlParameter.IsNullable property value.

The SQL Server side being taken care of, I did a quick search around the internet to see if I could find any DBMS's that allow for explicitly nullable/not nullable procedure or function parameters. I stopped when I ran into a reference for DB2 that appeared to require a specific attribute to be set on a procedure to allow null values to be passed to it. I'm not aware of any contemporary RDBMS's that have this feature, and my search didn't yield anything else.