Checking if a DataRow is "empty"

[mcbobin]

Hi,

Here's hoping someone can help...

I'm using a stored procedure to return a single row of data ie a
DataRow

e.g.

public static DataRow GetManualDailySplits(string prmLocationID, string
prmType, DateTime prmEffectiveDate)
{
CRUDXHelper myCRUDXHelper = new CRUDXHelper("StorePlanner",
"TrackerGetUserDailySplits");
myCRUDXHelper.AddInParam("LocationID", DbType.String,
prmLocationID);
myCRUDXHelper.AddInParam("Type", DbType.String, prmType);
myCRUDXHelper.AddInParam("EffectiveDate", DbType.DateTime,
prmEffectiveDate);
return (myCRUDXHelper.GetRowBy());
}

This row of data should be of the form
PercentSat,PercentSun,PercentMon,...,PercentFri

e.g.

10.0 20.0 20.0 5.0 20.0 10.0 15.0

Sometimes, there will be absolutely no data to return (i.e. no DataRow
is returned) and when this happens, my c# code is throwing an
exception.

So, how do I check if the DataRow is empty?

 

[Dave Sexton]

Hi,

I assume that you wrote CRUDXHelper and it sounds like a bug to me. On which
line is the exception being thrown and what is the exception?

 

[mcbobin]

Hi Dave,

Thanks for replying...

The CRUDXHelper forms part of our data access classes and works
perfectly well (so I'm told!)

-=-=-=-=-=-=-

A bit of background:

I'm developing a system for a retail chain. Part of this system helps
the stores to track daily sales against a total weekly sales target. I
have a table full of historical daily sales splits that are used to
spread the store's forcasted sales over the days of the week. However,
if a store wishes to provide their own (manual) daily splits, they can
do so (via some text boxes).

-=-=-=-=-=-=-

As the store's daily splits take precedence over the historical splits,
I am first checking to see if any records exist within the manual daily
sales table. If the store has never entered any manual splits, there
is no record of the store within the manual daily splits table. The
stored procedure then returns an "empty" DataRow...

However, I can't figure out how to check if the DataRow that is
returned by the stored procedure is "empty".

 

[Dave Sexton]

Hi,

But you have to provide more information - and I don't mean background - I
mean CRUDXHelper code or at least the Exception being thrown and the line
where your application is breaking. We're forced to make many assumptions
otherwise.

As I said before, it sounds like a bug in your DAL code.

 

[Marc Gravell]

Well, you need to define what "empty" means...

Yousay it returns an "empty" row... so either you mean it returns a row that
is blank, or you mean it doesn't return a row - just the headers... or that
maybe it doesn't even return headers... in any event, your code
(CRUDXHelper?) needs to understand this and react accordingly.

You haven't told us what the exception is... if the exception is an
out-of-bounds style one, it probably means you aren't returning a row, and
your GetByRow() is doing something like "return table.Rows[0];". If it is a
casting exception, it probably means that you are getting a blank row with
e.g. DBNull / 0 / "" values, and your downstream code doesn't like them.

But fundamentally the problem is in code that you haven't shown us...

CRUDXHelper: Kudos on the name though ;-p

Marc

 

[mcbobin]

Sorry for the useless info...

The error message is:

"Column 'PercentSat' does not belong to table IsNull."

The code line shown as reponsible is:

DailyTrackers NewTracker = new DailyTrackers(LoadedPage.States.PlanID,
LoadedPage.States.StoreID, Type);

but I know that this is only the code that creates the DailyTrackers
object...

Debugging reveals the code is breaking at:

decimal PercentSat =
decimal.Parse(drSplitsToUse["PercentSat"].ToString());

where PercentSat is a column in the table described in my first post.

Thanks for having a look...

 

[Dave Sexton]

Hi,

If the following error

"Column 'PercentSat' does not belong to table IsNull."

is being thrown at the following line

decimal PercentSat =
decimal.Parse(drSplitsToUse["PercentSat"].ToString());

and assuming that drSplitsToUse is a DataRow, that means drSplitsToUse is part
of a DataTable named, "IsNull".

It sounds like you have a major DAL bug, probably due to a type-o in a textual
query. Do you want to post the code that is filling the DataTable?

--
Dave Sexton

Sorry for the useless info...

The error message is:

"Column 'PercentSat' does not belong to table IsNull."

The code line shown as reponsible is:

DailyTrackers NewTracker = new DailyTrackers(LoadedPage.States.PlanID,
LoadedPage.States.StoreID, Type);

but I know that this is only the code that creates the DailyTrackers
object...

Debugging reveals the code is breaking at:

decimal PercentSat =
decimal.Parse(drSplitsToUse["PercentSat"].ToString());

where PercentSat is a column in the table described in my first post.

Thanks for having a look...

 

[mcbobin]

Thanks for keeping on looking Dave!

Sorry for the delay in replying, I left work at after my previous post
as I was getting too frustrated to concentrate...

I think my problem is that I'm not first creating a DataTable and then
extracting a DataRow from it - I'm only creating the DataRow itself.
This means that when the stored procedure returns an empty table (i.e.
the store has never entered any manual daily splits), the DataRow
object that is then returned (from the GetManualDailySplits method
described in my first post) cannot be used in any meaningful way.

I think I'll just create a DataTable and count the number of
rows...then have a word with the chap who sorted out the DAL and see
what he can do.

Thanks again

 

[Guest]

Let's go back to your original post. You have a method that returns type
DataRow.
The possibilities are:
1) the DataRow could be null. You can check for this with
if( GetManualDailySplits(blah, blah....)==null)
{
defensive code here.

}

2. You could get back a DataRow where some or all of the columns contain
System.DbNull. You can check on those the same way, except check for
System.DbNull.Value instead of "null".

Peter

 

[Dave Sexton]

Hi,

The error you posted is not indicating to me that you have a null or "empty"
DataRow - it's indicating that the DataRow is not what you expect it to be.

myCRUDXHelper.GetRowBy();

I know that myCRUDXHelper is filling a DataTable (or DataSet) and returning a
single DataRow. I know this because DataRow doesn't supply a public
constructor.

I assumed that drSplitsToUse was generated as follows:

DataRow drSplitsToUse = SomeObj.GetManualDailySplits(id, type, dateTime)

decimal.Parse(drSplitsToUse["PercentSat"].ToString());

If the above line throws the following error

"Column 'PercentSat' does not belong to table IsNull."

then I must assume that it's being thrown in the following part:

drSplitsToUse["PercentSat"]

and that the following line contains buggy code because it's producing the
DataRow that has no "PercentStat" column defined:

myCRUDXHelper.GetRowBy();

You expect a column named, "PercentStat", but the DataRow you are checking
doesn't have one.

The error indicates that the name of the DataTable is, "IsNull". IsNull is a
T-SQL function. It's also a function supported in "expressions" in the
context of DataSets. Also, it's a method on DataRow. It's just a really
strange name for a DataTable and so I assume you got some bugs in your dynamic
DAL code.

The stored procedure then returns an "empty" DataRow

"Empty" doesn't usually mean "no schema", but that's what I suspect is
happening here and your DAL can't handle it.

In your stored procedure, make sure that at least the schema is returned even
if no rows are because if you're not using a strong-typed DataSet then you
would get the error that you are getting. You can do this quite easily:

IF NOT EXISTS(...)
SELECT * FROM TheTable WHERE ...
ELSE -- I'm guessing that you have the "IF" but no "ELSE"
SELECT TOP 0 * FROM TheTable

You should fix the DAL, the stored proc or both, depending on which is buggy.
You will probably want the DAL's GetRowBy() method to return a null reference
when no records are returned in a result set instead of a corrupt DataRow.
Then, check the result for null before attempting to use it in code (as Peter
suggested in a different post in this thread).

GL

 

[mcbobin]

Thanks for the good advice! I'll try and sort it tomorrow...good night!