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

Bedroll

if you define a stored proc parameter 'without' a null directive, then you cannot pass in null (db will throw you an error) – Storyteller

@Storyteller I'm not sure what you mean by null directive. Are you talking about setting the default value to null i.e: @MyInt int = null Could you provide an example of a parameter declaration with and without this 'null directive' – Bedroll

correct - @MyInt int vs @MyInt int = null. When you do the former, you can't pass in null anyway.... – Storyteller

I'm afraid you're wrong there. The default value (or null directive as you call it) only dictates what value the parameter is set to when no parameter is provided. It has nothing to do with what values will or will not be accepted by the parameter. Null can always be passed to any parameter. – Bedroll

'@MyInt int' simply means that you have to provide a parameter, you can however set the parameter to Null. – Bedroll

@Bedroll - you could be right, but in my experience, I've always had a problem passing in null when it was not designated as such.. – Storyteller

Even though this is tagged SQL Server might be worth pointing out it comes from the System.Data.IDataParameter interface which is also implemented by System.Data.OracleClient.OracleParameter so might have no effect in SqlParameter context? – Injured

@MartinSmith Yes I think you're right there, I think this is a leftover from the interface that is not applicable to SqlServer. Maybe when dealing with other db providers this property has some meaning and relevance. – Bedroll

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.

Miletus

Thanks for the thorough research. I think this bears out that it has no effect in SqlParameter and that it's just an implementation requirement because of the underlying interface. It's a shame that 1) the documentation doesn't say this and 2) it doesn't appear to be correctly always set to true for SQL Server, since SQL Server always allows NULL to be passed, so if you inspect this to get some idea about the interface, it's incorrect. – Stem

Answer 2

After looking at the links and reading few articles on MSDN.

SqlParameter.IsNullable Property implements interface IDataParameter.IsNullable and reading an article it states "Null values are handled using the DBNull class"

DBNull class implements only one interface method IConvertible.ToType Infrastructure. Converts the current DBNull object to the specified type." which also states "DBNull.Value can be used to explicitly assign a nonexistent value to a database field, although most ADO.NET data providers automatically assign values of DBNull when a field does not have a valid value"

Reading this signifies most of the ADO.NET classes automatically implements this property and do type conversion of NULL to DBNull behind the picture. As per my understanding, I am assuming if we are writing our own API eg(System.Data.CustomDatabaseClient, instead of System.Data.SqlClient) for making a connection and other database tasks and I am not implementing this property then anyone using System.Data.CustomDatabaseClient must convert Null values to DBNull explicitly.

Looking for your comments and any correction

Westphalia