Handling DBNull?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a newbie to .Net. I run into problem when I try to assign null value
from the database to, say, a textbox.

Say if I had a simple table called "Contact" in the database and it has
three fields: ContactID, Name, Email. If I want to display the data. I would
do something like the following:

txtName = dsContact.Contact.Name;
txtEmail = dsContact.Contact.Email;

But if the Email field is null in database. I would get an error message
saying that I cannot convert "DBNull" to "string". After searching the
Internet for a little while, I found a way to get around this problem.

txtName = dsContact.Contact.IsNameNull() ? " " : dsContact.Contact.Name;
txtEmail = dsContact.Contact.IsEmailNull() ? " " : dsContact.Contact.Email;

Although this can solve the problem, it is pretty ugly and it wouldn't work
if I want to write a helper function that dynamically reads data from a
non-typed dataset and assigns the data to, say, textboxes.

Does anyone know a more graceful way to get around this problem? Thanks!
 
Cor,

Thanks for replying my question. Actually, I am working with web form, sorry
that I forgot to mention it. But I will look at the event and format in the
binding to see if I can get some ideas from there. Once again, thanks :)

Welles
 
Hi Welles,

That's pretty it.
You might use mix of strong and non-strong typing though, for example:
ds.Contact.Rows[0][ds.Contact.NameColumn.ToString()]

You might use similar construct to pass as a parameter, or you might pass
row and column separately to method that deals with null values.
 
Here is how I would check for DBNull

ds.Contact.Rows[0]["Name"] == DBNull.Value ? "" :
ds.Contact.Rows[0]["Name"].ToString();

You get an exception when you try to access a column in a row that has a
DBNull as its value of DBNull. I personally think that is a bad design and
it should let you check it, but it must be the way they evaluate the
property.

One thing that I have done is created some helper functions for stuff like
Dates, where you want to store a date as NULL for no entry. Here is the
code that I have done:

public static DateTime DBToDateTime( object dt )
{
DateTime dtRtnValue;

if ( dt == DBNull.Value )
dtRtnValue = new DateTime( 0 );
else
dtRtnValue = Convert.ToDateTime( dt );

return dtRtnValue;
}

public static object DateTimeToDB( DateTime dt )
{
object dtReturn;

if ( dt.Ticks == 0 )
dtReturn = DBNull.Value;
else
dtReturn = dt;

return dtReturn;
}

You would then call it like this:
DateTime myDT = DBToDateTime( ds.Contact.Rows[0]["DOB"] );

Then set it back:
ds.Contact.Rows[0]["DOB"] = DateTimeToDB( myDT );

This will help you go back and forth from DBNull.

Hope this helps.
 
Thank you, Thank you all!!!
Your reply was very helpful.

Eric Renken said:
Here is how I would check for DBNull

ds.Contact.Rows[0]["Name"] == DBNull.Value ? "" :
ds.Contact.Rows[0]["Name"].ToString();

You get an exception when you try to access a column in a row that has a
DBNull as its value of DBNull. I personally think that is a bad design and
it should let you check it, but it must be the way they evaluate the
property.

One thing that I have done is created some helper functions for stuff like
Dates, where you want to store a date as NULL for no entry. Here is the
code that I have done:

public static DateTime DBToDateTime( object dt )
{
DateTime dtRtnValue;

if ( dt == DBNull.Value )
dtRtnValue = new DateTime( 0 );
else
dtRtnValue = Convert.ToDateTime( dt );

return dtRtnValue;
}

public static object DateTimeToDB( DateTime dt )
{
object dtReturn;

if ( dt.Ticks == 0 )
dtReturn = DBNull.Value;
else
dtReturn = dt;

return dtReturn;
}

You would then call it like this:
DateTime myDT = DBToDateTime( ds.Contact.Rows[0]["DOB"] );

Then set it back:
ds.Contact.Rows[0]["DOB"] = DateTimeToDB( myDT );

This will help you go back and forth from DBNull.

Hope this helps.

--
Eric Renken
Demiuirge Software LLC
http://www.demiurge.us


Welles said:
I am a newbie to .Net. I run into problem when I try to assign null value
from the database to, say, a textbox.

Say if I had a simple table called "Contact" in the database and it has
three fields: ContactID, Name, Email. If I want to display the data. I
would
do something like the following:

txtName = dsContact.Contact.Name;
txtEmail = dsContact.Contact.Email;

But if the Email field is null in database. I would get an error message
saying that I cannot convert "DBNull" to "string". After searching the
Internet for a little while, I found a way to get around this problem.

txtName = dsContact.Contact.IsNameNull() ? " " : dsContact.Contact.Name;
txtEmail = dsContact.Contact.IsEmailNull() ? " " :
dsContact.Contact.Email;

Although this can solve the problem, it is pretty ugly and it wouldn't
work
if I want to write a helper function that dynamically reads data from a
non-typed dataset and assigns the data to, say, textboxes.

Does anyone know a more graceful way to get around this problem? Thanks!
Click to expand...
Click to expand...
 
Eric Renken said:
Here is how I would check for DBNull

ds.Contact.Rows[0]["Name"] == DBNull.Value ? "" :
ds.Contact.Rows[0]["Name"].ToString();

You get an exception when you try to access a column in a row that has a
DBNull as its value of DBNull. I personally think that is a bad design
and it should let you check it, but it must be the way they evaluate the
property.
Click to expand...

They do "let you check it". You're checking it in your code above.

Many people complain that they can't treat a DBNull like a string. This is
because a DBNull is not a string! It's something completely different,
something that's as valid in an nvarchar field as in an integer field.

As to a helper function, surely you can write a function like:

public static object IfDBNull(DataRow dr, string columnName, object
valueIfNull)
{
if (dr.IsNull(columnName))
{
return valueIfNull;
}
else
{
return dr[columnName];
}
}

You can then say:

txtName.Text = (string) IfDBNull(ds.Contact.Rows[0], "Name", ""); // or
txtPurchaseAuthority.Text = string.Format("{0:C}",
IfDBNull(ds.Contact.Rows[0], "Authority", 0.0));
---
John Saunders

P.S. Haven't tried this yet, but it at least compiles in VS2005 Beta 1:

public static T IfDBNull<T>(DataRow dr, string columnName, T valueIfNull)
{
if (dr.IsNull(columnName))
{
return valueIfNull;
}
else
{
return (T)dr[columnName];
}
}
 
Back
Top