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