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

 Introduction

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

Code Snippet

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.”.

Code Snippet

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

Conclusion

When you are call stored procedure in .NET you have to take care always corect data type and size.or else there is alternative method SqlCommand.Parametets.AddWithValue(name.value) will able to use without specify the size or type.