How can I pass a char null in a Stored Procedure method in C# 3.0?

Question

Hi everyone!

I'm trying to make this SP run:

myDataContext.InsertEvent(codEvent, dateOfEvent, statusOfEvent);

codEvent is an int; dateOfEvent is a DateTime; statusOfEvent is a char.

The problem is Visual Studio is throwing an error if I pass a statusOfEvent null. And, my SP accpts nullable char just like my table.

I've tried to do this too:

char? statusOfEvent= null;
      if (!String.IsNullOrEmpty(f["statusOfEvent"]))
           statusOfEvent= Convert.ToChar(f["statusOfEvent"]);

But it also throws an error...

thanks!!

I've tried to do this ternary expression:

!String.IsNullOrEmpty(f["statusOfEvent"]) ? Convert.ToChar(f["statusOfEvent"]) : DBNull.Value

But it is says that 'there is no implicit conversion between char and DBNull'

And I also looked at '*.dbml' designer.cs file and my SP method has this line:

[Parameter(DbType="Char(1)")] System.Nullable<char> statusOfEvent

Answer 1

I believe you need DBNull.Value rather than null.

Update
To use, you would pass it where you might other wise pass null. So, assuming that statusOfEvent is nullable, you could do the following.

myDataContext.InsertEvent(
    codEvent,
    dateOfEvent,
    statusOfEvent ?? DBNull.Value);

However, InsertEvent would need to accept it as a valid input for this parameter. If you're using some .NET support like an XSD dataset to describe the stored procedure. You can modify this to change the parameter to accept char? from char. Then you should be able to pass null and the underlying system will convert this to DBNull.Value for you. In that scenario, you can leave the call as:

myDataContext.InsertEvent(
    codEvent,
    dateOfEvent,
    statusOfEvent);

This should also work with Linq2Sql. To change the stored procedure's representation in your Linq2Sql objects, you need to open your dbml and edit the properties for the stored procedure.

Answer 2

DBNull.Value is what that database expects...

Here is how I am passing IsActive (which is a nullable boolean) as a parameter...

_ExecutedSP = new StoredProcedure("EvaluationType_GetList", base._SPInputOutput);
if (this.IsActive == null)
{
    _ExecutedSP.AddParameter("@IsActive", SqlDbType.Bit, DBNull.Value);
}
else
{
    _ExecutedSP.AddParameter("@IsActive", SqlDbType.Bit, this.IsActive);
}