[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:
varchar(100)
parameter declared as OUTPUT
, and (2) use the @ReturnValue
to return a further (integer) value.SqlDatAdapter.DataSet.Fill()
approach.@ReturnValue
or the other OUTPUT
param.What am I expecting ?
SqlDataAdapter
fill process to grab the data from the select. This works and I can access this data in C#.OUTPUT
, and the automatic @ReturnValue
param, to be available in C#.What do I see ?
.Value
properties are blank.What research have I done ?
SqlDataAdapter
and @returnvalue
. No significant positive results.SqlDataAdapter
- nothing seems to overlap my 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 !!!!
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
}