A better way to check for IsDBNull ?

[Paul D. Fox]

I have this code snippet below where I'm loading all the labels based upon
the retrieved record from the DataReader. Unfortunatley, some of the values
are null, so I can't explicitly set the labels without checking first. Is
there a better method to write this than using a bunch of "If" statements?
Could it be a function? if so, how would I write it?

'Read the single record
While drLeadFeedback.Read()

If Not drLeadFeedback.IsDBNull(1) Then
lblLeadName.Text = drLeadFeedback.Item("Lead_Name")
End If
If Not drLeadFeedback.IsDBNull(2) Then
lblLeadEmail.Text = drLeadFeedback.Item("Lead_Email")
End If
If Not drLeadFeedback.IsDBNull(3) Then
lblLeadPhone.Text = drLeadFeedback.Item("Lead_Phone")
End If
If Not drLeadFeedback.IsDBNull(4) Then
lblUnderwriter.Text = drLeadFeedback.Item("Underwriter")
End If
If Not drLeadFeedback.IsDBNull(10) Then
lblCreateDate.Text = drLeadFeedback.Item("Create_Date")
End If
If Not drLeadFeedback.IsDBNull(18) Then
lblStatus.Text =
drLeadFeedback.Item("Lead_Feedback_Status")
End If
If Not drLeadFeedback.IsDBNull(19) Then
lblAssignedTo.Text = drLeadFeedback.Item("Assigned_To")
End If
If Not drLeadFeedback.IsDBNull(11) Then
lblDealName.Text = drLeadFeedback.Item("Deal_Name")
End If
If Not drLeadFeedback.IsDBNull(12) Then
lblClient.Text = drLeadFeedback.Item("Client_Name")
End If
If Not drLeadFeedback.IsDBNull(13) Then
lblCSMName.Text = drLeadFeedback.Item("CSM_Name")
End If
If Not drLeadFeedback.IsDBNull(14) Then
lblSpeedRating.Text =
drLeadFeedback.Item("Speed_Rating")
End If
If Not drLeadFeedback.IsDBNull(15) Then
lblAccuracyRating.Text =
drLeadFeedback.Item("Accuracy_Rating")
End If
If Not drLeadFeedback.IsDBNull(16) Then
lblKnowledgeRating.Text =
drLeadFeedback.Item("Knowledge_Rating")
End If
If Not drLeadFeedback.IsDBNull(17) Then
lblWorkEffortRating.Text =
drLeadFeedback.Item("Work_Effort_Rating")
End If
If Not drLeadFeedback.IsDBNull(8) Then
lblResolvedBy.Text = drLeadFeedback.Item("Resolved_By")
End If
If Not drLeadFeedback.IsDBNull(7) Then
lblResolutionDate.Text =
drLeadFeedback.Item("Resolution_Date")
End If
If Not drLeadFeedback.IsDBNull(9) Then
lblFinalResolution.Text =
drLeadFeedback.Item("Final_Resolution")
End If
If Not drLeadFeedback.IsDBNull(6) Then
lblDetail.Text = drLeadFeedback.Item("Detail")
End If
If Not drLeadFeedback.IsDBNull(5) Then
lblSubject.Text =
drLeadFeedback.Item("Lead_Feedback_Subject")
End If
If Not drLeadFeedback.IsDBNull(20) Then
lblCategory.Text =
drLeadFeedback.Item("Lead_Feedback_Category")
End If
End While

 

[William F. Robertson, Jr.]

There are several ways to skin this cat; here is one:

lblLeadName.Text = GetItem( drLeadFeedback, "Lead_Name" );

Shared Function GetItem( reader As IDataReader, field As String ) As String
If reader.Item( field ) <> DBNull.Value Then
Return reader.Item( field ).ToString()
End If
Return String.Empty
End Function

HTH,

bill

 

[Bruce Barker]

refactoring 101, would say to build a function:

lblLeadName.Text =
DRHelper.GetTextValue(drLeadFeedback,"Lead_Name")

where (air code)

public class DRHelper
{
static public string GetTextValue (IDataReader dr, string colName)
{
int colnum = dr.GetOrdinal(colName);
if (dr.IsDBNull(colnum))
return "";
else
return dr[colnum].ToString();
}
}

-- bruce (sqlwork.com)

 

[Paul D. Fox]

Thanks Guy's, that worked much better...

Paul

There are several ways to skin this cat; here is one:

lblLeadName.Text = GetItem( drLeadFeedback, "Lead_Name" );

Shared Function GetItem( reader As IDataReader, field As String ) As
String
If reader.Item( field ) <> DBNull.Value Then
Return reader.Item( field ).ToString()
End If
Return String.Empty
End Function

HTH,

bill