How to check for null

[tshad]

I am sending data to my Sql Server and in some cases get null back.

In this case, I am sending back an int, but if it is null, I get a
"Specified cast not valid".

So I did the following:
if (parameters[7].Value == null)
return -5;
else
return (int)parameters[7].Value;

but get the same error.

How do I test for null here?

Thanks,

Tom

 

[Bruce Wood]

You need to test against DBNull.Value, not against null.

 

[tshad]

I am sending data to my Sql Server and in some cases get null back.

In this case, I am sending back an int, but if it is null, I get a
"Specified cast not valid".

So I did the following:
if (parameters[7].Value == null)
return -5;
else
return (int)parameters[7].Value;

I also tried:

return (parameters[7].Value ?? 0);

and

return ((int)parameters[7].Value ? (int)parameters[7].Value : 0);

The error here was:

User.cs(198,13): error CS0029: Cannot implicitly convert type 'int' to
'bool'

Tom

 

[Bruce Wood]

What's happening in your code, roughly speaking, is this:

ADO.NET represents each cell of data coming back from a SELECT as
either an object of the correct data type for the column (in your case,
a boxed int) or as DBNull.Value. DBNull is a singleton class, so there
only ever is one instance of DBNull.Value for your whole program.

So, when you try to grab data from a cell (row[column]) of an ADO.NET
table, you first have to check to see what type it is. Since there are
only two possibilities, you can test:

int param7;
if (parameters[7] == DBNull.Value)
{
// ... handle null case ...
param7 = -1;
}
else
{
param7 = (int)(parameters[7]);
}

You can't cast the cell to (int) until you determine that it's _not_
DBNull.Value. If it's not DBNull.Value, the only other possibility is
that it is a value of the correct type for the column, so it's safe to
cast it to int.

By the way, you can do an == comparison against DBNull.Value because,
even though it's a reference type, it's a singleton, so the ==
reference comparison will get the job done. You don't need to call
DBNull.Value.Equals(), although that will do the same thing.

In C# 2.0 all of this is made much simpler by nullable types. You can
declare an int? instead of an int and do this:

int? param7 = (int?)(parameters[7]);

which I'm glad for, since I'm sick and tired of testing for
DBNull.Value. (Caveat: I'm not using C# 2.0 yet. Apologies for any
errors in syntax in the above line.)

In C# 1.1 I've gotten around this problem by writing static utility
methods like this one:

public static int DBValueToInt(object cellValue, int defaultValue)
{
if (cellValue == DBNull.Value)
{
return defaultValue;
}
else
{
return (int)cellValue;
}
}

So now I just write:

int param7 = DBValueToInt(parameters[7], -1);

to get the same effect as the first bit of code, above.

 

[Tomislav Tustonic]

I also tried:

return (parameters[7].Value ?? 0);

and

return ((int)parameters[7].Value ? (int)parameters[7].Value : 0);

The error here was:

User.cs(198,13): error CS0029: Cannot implicitly convert type 'int' to
'bool'

In c# 0's and nulls are not automatically converted to boolean
false, as in c or c++. The ternary operator above expects boolean,
while you give it an integer. You should check if (int)Value == 0,
or value == null, or something like that.

HTH, Tom

 

[Christof Nordiek]

if (parameters[7] == DBNull.Value)
{

or:
if (parameters[7] is DBNull)

also you have to use DBNull.Value if you want to pass a NULL to a
SQL-Satement in a parameter:
parameter[7] = DBNull.Value;

 

[tshad]

What's happening in your code, roughly speaking, is this:

ADO.NET represents each cell of data coming back from a SELECT as
either an object of the correct data type for the column (in your case,
a boxed int) or as DBNull.Value. DBNull is a singleton class, so there
only ever is one instance of DBNull.Value for your whole program.

So, when you try to grab data from a cell (row[column]) of an ADO.NET
table, you first have to check to see what type it is. Since there are
only two possibilities, you can test:

int param7;
if (parameters[7] == DBNull.Value)
{
// ... handle null case ...
param7 = -1;
}
else
{
param7 = (int)(parameters[7]);
}

That's actually how I do it in VB.Net. Not sure why I didn't think to do it
here.

You can't cast the cell to (int) until you determine that it's _not_
DBNull.Value. If it's not DBNull.Value, the only other possibility is
that it is a value of the correct type for the column, so it's safe to
cast it to int.

By the way, you can do an == comparison against DBNull.Value because,
even though it's a reference type, it's a singleton, so the ==
reference comparison will get the job done. You don't need to call
DBNull.Value.Equals(), although that will do the same thing.

In C# 2.0 all of this is made much simpler by nullable types. You can
declare an int? instead of an int and do this:

int? param7 = (int?)(parameters[7]);

which I'm glad for, since I'm sick and tired of testing for
DBNull.Value. (Caveat: I'm not using C# 2.0 yet. Apologies for any
errors in syntax in the above line.)

Me too (in VB.Net as well).

Do you know if 2.0 will have this in VB.Net also?

In C# 1.1 I've gotten around this problem by writing static utility
methods like this one:

public static int DBValueToInt(object cellValue, int defaultValue)
{
if (cellValue == DBNull.Value)
{
return defaultValue;
}
else
{
return (int)cellValue;
}
}

Do you have to put this in all your programs or do you have it set up as
part of a class?

Thanks,

Tom

So now I just write:

int param7 = DBValueToInt(parameters[7], -1);

to get the same effect as the first bit of code, above.

 

[Bruce Wood]

public static int DBValueToInt(object cellValue, int defaultValue)

Do you have to put this in all your programs or do you have it set up as
part of a class?

You can create a

public sealed class DataUtilities
{
private DataUtilities() { }

... put your DBValueTo... methods here...
}

class to hold useful static methods that don't really belong anywhere
else. Then you just say

int param7 = DataUtilities.DBValueToInt(parameters[7], -1);

 

[tshad]

Do you have to put this in all your programs or do you have it set up as
part of a class?

You can create a

public sealed class DataUtilities
{
private DataUtilities() { }

... put your DBValueTo... methods here...
}

class to hold useful static methods that don't really belong anywhere
else. Then you just say

int param7 = DataUtilities.DBValueToInt(parameters[7], -1);

Is that similar to a share?

I have some General purpose routines that are not object, but are in my
MyFunctions Namespace

*************************************************************************************
NameSpace MyFunctions

Public Class BitHandling

'*----------------------------------------------------------*
'* Name : BitSet *
'*----------------------------------------------------------*
'* Purpose : Sets a given Bit in Number *
'*----------------------------------------------------------*
Public Shared Function BitSet(Number As Integer, _
ByVal Bit As Integer) As Long
If Bit = 31 Then
Number = &H80000000 Or Number
Else
Number = (2 ^ Bit) Or Number
End If
BitSet = Number
End Function

**************************************************************

Is that the same thing?

Thanks,

Tom

 

[Bruce Wood]

Yes, it's the same thing. "Shared" in VB.net corresponds to "static" in
C#.

 

[tshad]

Yes, it's the same thing. "Shared" in VB.net corresponds to "static" in
C#.

OK.

Thanks,

Tom

 

[tshad]

I am sending data to my Sql Server and in some cases get null back.

In this case, I am sending back an int, but if it is null, I get a
"Specified cast not valid".

So I did the following:
if (parameters[7].Value == null)
return -5;
else
return (int)parameters[7].Value;

I also tried:

return (parameters[7].Value ?? 0);

and

return ((int)parameters[7].Value ? (int)parameters[7].Value : 0);

The error here was:

User.cs(198,13): error CS0029: Cannot implicitly convert type 'int' to
'bool'

Tom

I just ended up always testing for null - which is probably best.

************************************************************************************
public class User
{
private string clientID = "";
private int userID = 0;
private long companyID= 0;
private string firstName = "";
private string lastName = "";
private string userName = "";
private string secretQuestion = "";
private string secretAnswer = "";
private string email = "";
private string password = "";
private bool firstTime = true;
private bool emailHtml = true;
private string dateCreated;
private string connectionString = "";
private int status = 0;

....

private void LoadDetails (DataRow user)
{
if (user["FirstName"] != DBNull.Value)
firstName = (string)user["FirstName"];
if (user["LastName"] != DBNull.Value)
lastName = (string)user["LastName"];
if (user["UserName"] != DBNull.Value)
userName = (string)user["UserName"];
if (user["SecretQuestion"] != DBNull.Value)
secretQuestion = (string)user["SecretQuestion"];
if (user["SecretAnswer"] != DBNull.Value)
secretAnswer = (string)user["SecretAnswer"];
if (user["Email"] != DBNull.Value)
email = (string)user["Email"];
if (user["Password"] != DBNull.Value)
password = (string)user["Password"];
if (user["DateCreated"] != DBNull.Value)
dateCreated = user["DateCreated"].ToString();
if (user["UserID"] != DBNull.Value)
userID = (int)user["UserID"];
if (user["FirstTime"] != DBNull.Value)
firstTime = (bool)user["FirstTime"];
if (user["emailHtml"] != DBNull.Value)
emailHtml = (bool)user["emailHtml"];
if (user["CompanyID"] != DBNull.Value)
companyID = (int)user["CompanyID"];
if (user["ClientID"] != DBNull.Value)
clientID = (string)user["ClientID"];
}
******************************************************************

Seems like the best way to handle it to make sure there are no errors.

Thanks,

Tom

 

[Mike]

Have you guys tried this?

private void LoadDetails (DataRow user)
{
if (dr.IsNull("emailHtml"))
{
//it's null;
}
else
{
//it's not null;
}
}

I am sending data to my Sql Server and in some cases get null back.

In this case, I am sending back an int, but if it is null, I get a
"Specified cast not valid".

So I did the following:
if (parameters[7].Value == null)
return -5;
else
return (int)parameters[7].Value;

I also tried:

return (parameters[7].Value ?? 0);

and

return ((int)parameters[7].Value ? (int)parameters[7].Value : 0);

The error here was:

User.cs(198,13): error CS0029: Cannot implicitly convert type 'int' to
'bool'

Tom

I just ended up always testing for null - which is probably best.

************************************************************************************
public class User
{
private string clientID = "";
private int userID = 0;
private long companyID= 0;
private string firstName = "";
private string lastName = "";
private string userName = "";
private string secretQuestion = "";
private string secretAnswer = "";
private string email = "";
private string password = "";
private bool firstTime = true;
private bool emailHtml = true;
private string dateCreated;
private string connectionString = "";
private int status = 0;

...

private void LoadDetails (DataRow user)
{
if (user["FirstName"] != DBNull.Value)
firstName = (string)user["FirstName"];
if (user["LastName"] != DBNull.Value)
lastName = (string)user["LastName"];
if (user["UserName"] != DBNull.Value)
userName = (string)user["UserName"];
if (user["SecretQuestion"] != DBNull.Value)
secretQuestion = (string)user["SecretQuestion"];
if (user["SecretAnswer"] != DBNull.Value)
secretAnswer = (string)user["SecretAnswer"];
if (user["Email"] != DBNull.Value)
email = (string)user["Email"];
if (user["Password"] != DBNull.Value)
password = (string)user["Password"];
if (user["DateCreated"] != DBNull.Value)
dateCreated = user["DateCreated"].ToString();
if (user["UserID"] != DBNull.Value)
userID = (int)user["UserID"];
if (user["FirstTime"] != DBNull.Value)
firstTime = (bool)user["FirstTime"];
if (user["emailHtml"] != DBNull.Value)
emailHtml = (bool)user["emailHtml"];
if (user["CompanyID"] != DBNull.Value)
companyID = (int)user["CompanyID"];
if (user["ClientID"] != DBNull.Value)
clientID = (string)user["ClientID"];
}
******************************************************************

Seems like the best way to handle it to make sure there are no errors.

Thanks,

Tom