Finding NULLs in untyped DataSet

[Jeff]

I'm importing data from an Excel file into an untyped DataSet.
I need to find any rows in the DataSet that have a NULL value in a specific
column (say, the "FirstName" column). These would result from any any
"blank" cells coming in from the Excel file.

I don't want to loop through the entire DataSet using the row.IsNull()
function, as I would prefer some more set based approach, like .Select.

So, finding no way to .Select() NULLs specifically; something like this:
DataRow[] nullRows;
nullRows = DT_ExcelData.Select("FirstName = System.DBNull.Value"); //
obviously won't work

I went with this, which *seems* to do the trick:
int nullRowCount = 0;
DataRow[] notNullRows;
notNullRows= DT_ExcelData.Select("FirstName LIKE '%'"); // This gets all of
the NON NULL values:
nullRowCount = (DT_ExcelData.Rows.Count - notNullRows.Length); // The
difference between ALL the rows and the NON Null ones tells me if I have any
NULLs and, if so, how many.
if (nullRowCount > 0) {
// do one thing
}
else {
// do another
}

While I searched high and low for something more reasonable, I'm hoping I
just missed it, as my "work-around" smacks of a Rube Goldberg device.

Any suggestions? Thoughts? Perspective?

Thanks!

 

[Cowboy \(Gregory A. Beamer\)]

The problem here is there is no concept of null in XML, as it is string
based. There is a concept of an empty tag, however. If you want to poke at
the underlying XML, you can certaily XPath for empty tags and find all of
the "nulls". This is a more set based approach, but does force a paradigm
change in your handling of DataSets (at least while you are searching for
nulls).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

 

[Jeff]

Ahhh, of course, XPath.

Until then, what is your perspective on the LIKE '%' approach, as in the
following line:
....MyDataSet.Select("FirstName LIKE '%'");

In my testing I found that Excel cells that are empty: after being imported
into an untyped DataSet have the System.DBNull.Value value in the DataSet,
AND that the LIKE '%' wildcard does *not* match that. So it appears that the
approach works at least some of the time. But I need a method that works all
of the time, so short of doing a paradigm shift over to XPath, I would sleep
better if I could find some documentation on what, specifically, % will and
will not match on. I have been unable to locate such documentation. Any
ideas on that? Or is it simply too dangerous or foolish to rely on % for my
purposes.

-Jeff

The problem here is there is no concept of null in XML, as it is string
based. There is a concept of an empty tag, however. If you want to poke at
the underlying XML, you can certaily XPath for empty tags and find all of
the "nulls". This is a more set based approach, but does force a paradigm
change in your handling of DataSets (at least while you are searching for
nulls).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

I'm importing data from an Excel file into an untyped DataSet.
I need to find any rows in the DataSet that have a NULL value in a
specific column (say, the "FirstName" column). These would result from
any any "blank" cells coming in from the Excel file.

I don't want to loop through the entire DataSet using the row.IsNull()
function, as I would prefer some more set based approach, like .Select.

So, finding no way to .Select() NULLs specifically; something like this:
DataRow[] nullRows;
nullRows = DT_ExcelData.Select("FirstName = System.DBNull.Value"); //
obviously won't work

I went with this, which *seems* to do the trick:
int nullRowCount = 0;
DataRow[] notNullRows;
notNullRows= DT_ExcelData.Select("FirstName LIKE '%'"); // This gets all
of the NON NULL values:
nullRowCount = (DT_ExcelData.Rows.Count - notNullRows.Length); // The
difference between ALL the rows and the NON Null ones tells me if I have
any NULLs and, if so, how many.
if (nullRowCount > 0) {
// do one thing
}
else {
// do another
}

While I searched high and low for something more reasonable, I'm hoping I
just missed it, as my "work-around" smacks of a Rube Goldberg device.

Any suggestions? Thoughts? Perspective?

Thanks!