Implicitly treating NULL as "" or 0?

  • Thread starter Thread starter Sören Nils Kuklau
  • Start date Start date
S

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!
 
How about not looping through the rows in the first place and just querying
the DataTable using the select method?
 
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
 
How about not looping through the rows in the first place and just querying
the DataTable using the select method?
Click to expand...

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.
Click to expand...

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.
Click to expand...

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!
 
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!
Click to expand...

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.
 
Hi,
In any case, you must have a SQL "SELECT" query, right ?
Click to expand...

Nope, it's actually a stored procedure.
SELECT ISNULL(col1,0) FROM TABLE1
Click to expand...

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

Thanks. :)
 
Hi,
In any case, you must have a SQL "SELECT" query, right ?
Click to expand...

Nope, it's actually a stored procedure.
SELECT ISNULL(col1,0) FROM TABLE1
Click to expand...

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

Thanks. :)
 
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
 
Soeren Nils 'chucker' Kuklau said:
While querying might be more efficient, I fail to see how it is
related to the problem, much less does something to solve it.
Click to expand...

Because sometimes the best way to solve a problem is to avoid it in the
first place.
 
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.
 
Back
Top