How to get both Resultset and return code?

[sonnyzhang]

I try to call a stored procedure that will return a Resultset and return code. I can't get them both together ! I get resultset but no return code with SqlCommand.ExecuteReader(). I get return code but no resultset with SqlCommand.ExecuteNonQuery().

SqlCommand.Parameters.Add("RETURN_VALUE",SqlDbType.Int) has been done. What else can I do?

Any one have some idea? thanks

[Krzemo]

....
System.Data.SqlClient.SqlParameter par=new System.Data.SqlClient.SqlParameter(strName, System.Data.SqlDbType.Int);
par.Direction=System.Data.ParameterDirection.ReturnValue; // first parameter
cmd.Parameters.Add(par);

... // input output parameters
...
cmd.SqlCommand.ExecuteReader().
int Result=(int)cmd.Parameters[0].Value


Best regards,
Krzemo.

[sonnyzhang]

Thanks for your reply.

I do have the ParameterDirection.ReturnValue specified as:

SqlParameter rc = cmd.Parameters.Add("RETURN_VALUE",SqlDbType.Int);
rc.Direction = ParameterDirection.ReturnValue;

I have tried "@RETURN_VALUE" as well. No luck.

My problem is the rc.Value only get set when use ExecuteNonQuery(). If I use ExecuteReader(), the rc.Value is null.

[Krzemo]

Post bigger sample of code please (with parameters retrieval and Reader loop code) .
I suspect that U do it in wrong order ... but maybe it is something else -show it than I will not need to guess it.

Best regards,
Krzemo.

[sonnyzhang]

Here is my code. Now I have to query twice. But I just don't understand why we can't get resultset and return code together.

try
{
SqlCommand cmd = new SqlCommand("xxxx", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter rc = cmd.Parameters.Add("RETURN_VALUE",SqlDbType.Int);
rc.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@actnum", SqlDbType.NVarChar, 20).Value = act.actnum;
...
SqlDataReader reader = cmd.ExecuteReader(); // <<< rc.Value is not set here
if ( reader.FieldCount < 5 )
{
reader.Close();
cmd.ExecuteNonQuery(); // <<< rc.Value is set
if ( ((int)rc.Value) != 0 )
throw new Exception("Account is locked, try again later)");
else
throw new Exception("Stored procedure error");
}

while (reader.Read())
{
....
}
}

[Krzemo]

It is total disaster....:rolleyes:

First of all objects like datareader should be enclosed in "using" clause.
something like this:

using(SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
/// your code
}
reader.Close();
}


Second thing: As I suspected it U do it in wrong order!
Application has to process all pending results before it can proceed with parameters retrieval.

So, U should wait for "reader" to close before cmd.Parameters[0].Value has a chance to get some usable value.

If U put "cmd.Parameters[0].Value" after a block with reader than it will return value from SP.

Best regards,
Krzemo.

[sonnyzhang]

Thanks a lot Krzemo.