CODEWITHSUNDEEP
.netsqlsql-serverado.netsqlparameter
Martyn
Martyn

Reputation: 1476

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

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

Upvotes: 24

Views: 2796

Answers (2)

ulty4life
ulty4life

Reputation: 3012

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.

Upvotes: 25

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

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

Upvotes: 3

Related Questions