Checking for nulls in SQL record set

I am wanting to know if there is a way to check for null values in a record set for all columns at one time, instead of doing if a == null then or else for each column?  I call a SP that returns one row (always). My code behind looks like this without any null checking:
                       
try
{
  oRs = oCmd.ExecuteReader();
  if (oRs.Read())
 {
     QSNumber = oRs["QS#"].ToString();
     ProductName = oRs["Product_Name"].ToString();
			:
			:
			:

Open in new window

66chawgerAsked:
Who is Participating?
 
Jaime OlivaresConnect With a Mentor Software ArchitectCommented:
no, you cannot check all, but you can design your own function, like:

public static GetStr(object o)
{
     return o==null ? "" : o.ToString();
}

So you can use as:
    QSNumber = GetStr(oRs["QS#"]);
 
0
 
donjon56Connect With a Mentor Commented:
you will likely want to modify the above return to read as below.

If a column contains a null value in a reader it will be reflected as a DBNull.Value.


return o==DBNull.Value ? "" : o.ToString();

Open in new window

0
 
66chawgerAuthor Commented:
Is the ? for .NET 2.0?   I am using 1.1, unfortunately we have not upgraded yet.  Also

what does the "" represent after the ?.   Can I include to return "N/A" if the value is null?
I like the function though, just taking me some time to get up to speed again on .NET after being in JAVA for so long.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jaime OlivaresConnect With a Mentor Software ArchitectCommented:
? is the ternary operator, it is present in every C# version (not .net related)
And yes, you can replace the ""  with  "n/a"
0
 
donjon56Commented:
<test> ? <true value> : <false value>

It works in 1.1

It performs the test and if true, returns the true value otherwise returns the false value.
0
 
donjon56Connect With a Mentor Commented:
Also

return o==DBNull.Value ? "" : o.ToString();

is syntatically the same as

if (o==DBNull.Value)
    return "";
else
    return o.ToString();
 
0
 
66chawgerAuthor Commented:
This is why knowledge sharing is great!  Great job guys, thanks alot!
0
 
66chawgerAuthor Commented:
Ok, receiving "Class, Struct, or interface method must have a return type for

public static GetStr(object o)

My namespace is QSNumberInfo
then public class is GetQSNumberInfo : System.Web.UI.Page
then private void for page load
then public void GetQSNumInfo(object sender, System.EventArgs e)

I tried to place the new function at the end. I have encountered this message before when a return type is missing.
0
 
Jaime OlivaresSoftware ArchitectCommented:
it should be:
public static string GetStr(object o)
0
 
66chawgerAuthor Commented:
DUH!!!!!   Thanks jaime.....let me go bang my head against the wall for a while :)
0
 
66chawgerAuthor Commented:
Jaime, I have a bit of a situation.  This is in regards to the solution you provided me last Friday.  In our Dev and QA SQL DB's the following code works great checking for nulls prior to setting as string:
     return 0==DBNull.Value ? "n/a" : o.ToString();
In our prod SQL DB, even though I see "NULL" represented in some of the SQL column fields, this code is not picking it up?    can I a logical or to also check for isempty?.....  e.g.,   || isEmpty()  ??????   This is the only thing I can think of if the these columns are not actually null, but are emtpy?
0
 
Jaime OlivaresSoftware ArchitectCommented:
you can do this:

public static GetStr(object o)
{
     if (o is string)
           return string.IsNullOrEmpty((string)o) ? "n/a" : o.ToString();
    else
           return o==null ? "n/a" : o.ToString();
}
0
 
66chawgerAuthor Commented:
ahhh!!!  did not think of the IsNullOrEmpty!!!!!   that kills two birds with one stone!  I know I submitted this as a comment, but is there a way to award you points?   by the way it took until now to find this problem out as it only happens in prod, and my code was not promoted until yesterday......
0
 
Jaime OlivaresSoftware ArchitectCommented:
Don't worry, this question has the maximum points (500), so it is not possible to add more.
0
 
66chawgerAuthor Commented:
Jaime, I hate to ask this, is the IsNullOrEmtpy definition part of .NET 2.0?  I am working in .NET 1.1.  IsNullOrEmpty is not included as a definition for string.   I work in both environments, however, this particular one is .NET 1.1, my apologies
0
 
donjon56Commented:
It is a 2.0 method.

This might work better for you:

    public static string GetStr(object o)
    {
        string s = o as string;

        return (s != null && s.Length > 0 ? s : "n/a");
    }

this tries to cast o as a string (if it is not a string then s will be null)

if s is not null and the length is greater than zero, then return the string.  Otherwise return "n/a"
0
 
Jaime OlivaresSoftware ArchitectCommented:
it is just compatible with .net 2.0 and 3.x
Alternative:

public static GetStr(object o)
{
     if (o==null)
           return "n/a";
     else
     {
          if (o is string)
                return ((string)o).Length==0 ? "n/a" : o.ToString();
          else
                return o.ToString();
     }
}
0
 
Jaime OlivaresSoftware ArchitectCommented:
this will work with non-empy string but filled with spaces:

public static GetStr(object o)
{
     if (o==null)
           return "n/a";
     else
     {
          if (o is string)
                return ((string)o).Trim().Length==0 ? "n/a" : o.ToString();
          else
                return o.ToString();
     }
}
0
 
66chawgerAuthor Commented:
Jamie, the second if in your example is basically checking for length of 0 and if true set to "n/a" correct?
With the addition of the trim() this will still work with non-empty strings but as you said strings filled with spaces, correct?  

So trim or with out trim, will this cover all the bases, e.g., NULL values and Empty strings?
0
 
Jaime OlivaresSoftware ArchitectCommented:
>>So trim or with out trim, will this cover all the bases, e.g., NULL values and Empty strings?
yes, I think so, empty string are equal to strings with length=0
0
 
66chawgerAuthor Commented:
Jaime, everything works great now.  Thanks alot for the assistance.  It definitely helped me think outside the box :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.