I'm trying to use FillSchema to get the results schema for a stored
procedure that, simplified, does this:

CREATE PROCEDURE SomeProc

@Flag    bit

AS

IF @Flag = 1
  SELECT 'foo' = 'bar'

SELECT TOP 5 'baz' = snafu FROM SomeTable

GO

If I then call

objSQLCommand.CommandType = CommandType.StoredProcedure
objSQLCommand.CommandText = "SomeProc"
objDataAdapter.MissingSchemaAction = MissingSchemaAction.Add
objDataAdapter.SelectCommand = objSQLCommand
objDataAdapter.FillSchema(objDataSet, SchemaType.Source)
objDataAdapter.Fill(objDataSet)

with @Flag = 1, I get the correct dataset back:  two tables, the first
with one column called 'foo' and one row, the second with one column
called 'baz' and 5 rows.  However, if I call this code with @Flag = 0,
what I get back are two tables, the first with two columns called
'foo' and 'baz' and 5 rows, and the second with one column called
'baz' and no rows at all.

This seems to be because the FillSchema goes down into the IF @Flag
branch in my stored procedure for schema information regardless of the
value of @Flag and that then the DataAdapter.Fill is putting its one
table of data into the first available table.

My question is, what other options do I have for getting the schema
information here?  Can I change this behavior of FillSchema's?  In
this particular instance I could skip the FillSchema step entirely if
I know that @Flag <> 1, but I'd really prefer a more general solution
that would allow me to put more branches in my stored procedure if
they're needed someday.

Thanks,
Caitlin Shaw

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n
Access vs vb.net/ado.net
Copy database schema from Access to SQL Server
Creating a new Access database file (.mdb) in .NET/C#
How much of the functionality of SQL Server is mirrored in ADO.Net