With ADO, using SqlDataAdapter.FillSchema clears @ReturnValues and other stored procedure parameters marked for output

Question

[Edit - an explanation of the answer is at the foot of the question because the subtlety is not totally obvious from the accepted answer.]

Original question:

I am looking for a quick 'yes this approach should be possible' answer before I commit further effort.

Scenario:

• I have a simple SQL Server stored procedure that runs a query. I also want to (1) return a varchar(100) parameter declared as OUTPUT, and (2) use the @ReturnValue to return a further (integer) value.
• I call this procedure from C# using the SqlDatAdapter.DataSet.Fill() approach.
• Back in the C# code, I can access the procedure parameters but I cannot get the values of either @ReturnValue or the other OUTPUT param.

What am I expecting ?

• The SqlDataAdapter fill process to grab the data from the select. This works and I can access this data in C#.
• The values of the SQL Server stored procedure params marked as OUTPUT, and the automatic @ReturnValue param, to be available in C#.

What do I see ?

• Data is returned from the query as expected.
• The SQL command parameters are present in C#, but their .Value properties are blank.

What research have I done ?

• A lot of googling for hits containing SqlDataAdapter and @returnvalue. No significant positive results.
• Perused the old SO questions related to SqlDataAdapter - nothing seems to overlap my question.
• Posted this question.

I need to ask the SO community if this is even possible. So - is it possible to collect OUTPUT parameter values from a stored procedure that was used with SqlDatAdapter.DataSet.Fill() ?

If the answer is yes and there are no simple examples out there then I'll cut down my code to a representative case and post it here for (hopefully) community debugging.

EDIT Thanks to @NDJ and the sample code supplied I was able to confirm that SqlDatAdapter.DataSet.Fill() DOES NOT interfere with stored procedure return values. I was then able to go on to discover that my issue was caused by an immediate call to SqlDatAdapter.DataSet.FillScheam(). This delivers the metadata for the data table into a handy matrix that we can access from C#, which is part of my use case.

However, FillSchema() clears the stored procedure parameter values. In my code I was trying to gather the stored procedure output parameter values after the call to FillSchema, which was the cause of what I witnessed. I could not find any documentation on this point.

If your code looks like this, it will work.

    da.Fill(ds);

    var returnVal = returnParam.Value; // value as expected

    da.FillSchema(ds, SchemaType.Source);

If your code looks like this it will not work

    da.Fill(ds);

    da.FillSchema(ds, SchemaType.Source);

    var returnVal = returnParam.Value; // value reset by prev line !!!!

Answer 1

yes you can.

Create PROCEDURE TestSP
    @test varchar(max) output
AS
BEGIN
    select @test = 'abc'
    select top 10 * from MyTable
    return 4

END




using (SqlConnection connection = new SqlConnection(conString))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand("TestSP", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@test", SqlDbType.VarChar, -1);
            param.Direction = ParameterDirection.Output;

            var returnParam = cmd.Parameters.Add("@Return", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add(param);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            var returnVal = returnParam.Value; //4
            var output = param.Value; // abc
            var data = ds.Tables[0].Rows[0][0];  //data from my table
        }