empty resultset help

[rodchar]

hey all,
here's a snippet from my datalayer class:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);
dts = ds.Tables;

i was just wondering if the following is possible.
my problem is when the Fill returns no tables because much of the code was
designed to see at least one table. and i guess i could go around the code
checking for an empty table but i'd like to please get some other possible
ideas if i may.

such as, is it possible in sql side to return an empty row with the schema
thru a select statement or something?

or is there a way on the c# side to create the table and a blank row with
the schema seeing as the dataset is untyped?

i just would like to know if i can get a table with a blank row of the
schema if the .Fill method returns no tables.

thanks for any suggestions,
rodchar

 

[Marc Gravell]

Well, it depends a lot on what your SQL looks like...

You can get an empty row by running your existing SELECT, but with
"WHERE 1 = 0" or similar (i.e. returns zero rows, but includes the
schema metadata). There is also a SET FMTONLY ON/OFF statement, but I
don't recommend it in most circumstances (not least becaus it isn't
exactly portable).

It also depends on what your reasons are for a "fill" method that
doesn't do any SELECTs... it sounds to me like an error, so I would be
tempted to check for zero tables and throw an exception...

Marc

 

[Marc Gravell]

an empty row
Sorry, I meant an empty results grid... no row ;-p

 

[DDD]

Sorry, I meant an empty results grid... no row ;-p

That's easy.
You just need an union statement like this:
select * from yourTable where ...
UNION select '' as column1, '' as column2, ...

 

[Marc Gravell]

You just need an union statement like this:

select  * from yourTable  where ...
UNION select '' as column1, '' as column2, ...

Warning: SQL Server 2005 gets very, very upset if you try to union
different data-types into the same ordinal.

 

[rodchar]

thanks everyone for the feedback,
rod.