Implicitly treating NULL as "" or 0?

[Sören Nils Kuklau]

Hi,

when pulling data from the database, we have plenty of cases where we
create a DataSet, then iterate through the rows, like:
Dim ds As DataSet = someFunction()

For Each currentRow As DataRow In ds.Tables(0).Rows


We then fill internal properties with the returned cells, like:
someObject.name = myRow("name")

The data is typically a string or an integer.

However, there are a few edge cases where the row in the database is
actually NULL. Normally this causes an exception; we work around this
by wrapping myRow() access in CheckDBNullInteger and CheckDBNullString
calls; the functions are simply:

Public Function CheckDBNullInteger(ByVal o As Object) As Integer

If o Is DBNull.Value Then

Return 0

Else

Return CInt(o)

End If

End Function



Public Function CheckDBNullString(ByVal o As Object) As String

If o Is DBNull.Value Then

Return ""

Else

Return o.ToString

End If

End Function

That works fine, but it's not pretty. I'm wondering if there is a way
to either implicitly call this function (by subclassing DataRow,
perhaps?), or solve this more elegantly in some other fashion.

Thanks!

 

[Scott M.]

How about not looping through the rows in the first place and just querying
the DataTable using the select method?

 

[Kerry Moorman]

Sören Nils Kuklau,

You could use nullable types for the properties you are moving possible null
values to.

But code for nullable types is at least as messy as the code for moving from
null to some value like 0 or "".

However, nullable types allow you to keep the null value and have it
available for business rules, move it back into a datarow for updating, etc.

Kerry Moorman

 

[Soeren Nils 'chucker' Kuklau]

How about not looping through the rows in the first place and just querying
the DataTable using the select method?

While querying might be more efficient, I fail to see how it is
related to the problem, much less does something to solve it.

You could use nullable types for the properties you are moving possible null
values to.

Nah, in these cases, when it's NULL, it's wrong. It's a case of "this
shouldn't happen", and I was hoping there might be an elegant way to
catch such a problem and silently work around it.

But code for nullable types is at least as messy as the code for moving from
null to some value like 0 or "".

However, nullable types allow you to keep the null value and have it
available for business rules, move it back into a datarow for updating, etc.

Sure -- but that wouldn't be much use. (It would simply shift the
problem elsewhere; rather than checking for empty strings or Integers
that are 0, we'd also have to check for NULL.)

Thanks anyways!

 

[coolCoder]

Hi,

when pulling data from the database, we have plenty of cases where we
create a DataSet, then iterate through the rows, like:
Dim ds As DataSet = someFunction()

For Each currentRow As DataRow In ds.Tables(0).Rows

We then fill internal properties with the returned cells, like:
someObject.name = myRow("name")

The data is typically a string or an integer.

However, there are a few edge cases where the row in the database is
actually NULL. Normally this causes an exception; we work around this
by wrapping myRow() access in CheckDBNullInteger and CheckDBNullString
calls; the functions are simply:

Public Function CheckDBNullInteger(ByVal o As Object) As Integer

If o Is DBNull.Value Then

Return 0

Else

Return CInt(o)

End If

End Function

Public Function CheckDBNullString(ByVal o As Object) As String

If o Is DBNull.Value Then

Return ""

Else

Return o.ToString

End If

End Function

That works fine, but it's not pretty. I'm wondering if there is a way
to either implicitly call this function (by subclassing DataRow,
perhaps?), or solve this more elegantly in some other fashion.

Thanks!

Hi,
In any case, you must have a SQL "SELECT" query, right ?
Then why dont you fix this at the root itself.

Let me go in details now ---

In simple language, use the ISNULL function which places some
default values in place where there is NULL value

For example -- If a column name col1 may contain null value
and it is of type INT then you query goes like this --

SELECT ISNULL(col1,0) FROM TABLE1

By executing this you will get 0 instead of nasty NULL
values.

Hope I have answered your question. I would like to have a
feedback on what happens next, so please post a reply after you give
it a shot.


Thanks,
coolCoder.

 

[Soeren Nils 'chucker' Kuklau]

Hi,
In any case, you must have a SQL "SELECT" query, right ?

Nope, it's actually a stored procedure.

SELECT ISNULL(col1,0) FROM TABLE1

Ah! I didn't know MSSQL had such a function. I'll definitely look into
this.

Thanks.

 

[Soeren Nils 'chucker' Kuklau]

Hi,
In any case, you must have a SQL "SELECT" query, right ?

Nope, it's actually a stored procedure.

SELECT ISNULL(col1,0) FROM TABLE1

Ah! I didn't know MSSQL had such a function. I'll definitely look into
this.

Thanks.

 

[Cor Ligthert[MVP]]

Soren,

Any reason that you don't want to use the defaults from the Server?

You need real hug data to let that be the bottleneck at disks today.

Cor

 

[Scott M.]

While querying might be more efficient, I fail to see how it is
related to the problem, much less does something to solve it.

Because sometimes the best way to solve a problem is to avoid it in the
first place.

 

[coolCoder]

Hi,
Dont have a SELECT query ??
I wonder then how you get the output from even the Stored Procedure ??
The select query might be in the SP, right ?
Even then this solution will work.