Thursday, July 26, 2007

Handling Database NULL in .Net while using SqlParameter (.Net 2.0)

If you want to set the NULL for database column while inserting or updating, normal “null” OR “DBNULL.value” is not suitable.

C# code:

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = null;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = DBNULL.value;


If you are using Sqlparameter for passing the parameter value to stored procedure, the above null types does not support. It will throw an error like “Parameter @MiddleName not specified.”.

C# code:

using System.Data.SqlTypes;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = SqlString.Null;

The above code will execute properly without any error.

The same way you have to use for other data types like
DataTime --> SqlDateTime.Null
int32 --> SqlInt32.Null, etc

No comments: