Reading both result set and output parameter of SP in .Net Framework......

[Hewit]

I have a stored procedure which returns records and output
parameters(SQL2K). How to call this stored procedure using ADO.Net so that
I can use both results in my application. I have .Net Framework 1.1.
when I use ExecuteReader() of Command object I cannot read the output
parametrs of the stored procedure in code.
Please advice.
Hewit

 

[bruce barker]

in sqlserver, the output parameter values are returned after all result
sets. with a sql datareader (a forward only reader), this means you have to
read though all the rows (and result sets - MoreResults), or close the
reader which does the same. after this step, you can read the output
parameters (be sure to declare direction to include output).

-- bruce (sqlwork.com)


| I have a stored procedure which returns records and output
| parameters(SQL2K). How to call this stored procedure using ADO.Net so
that
| I can use both results in my application. I have .Net Framework 1.1.
| when I use ExecuteReader() of Command object I cannot read the output
| parametrs of the stored procedure in code.
| Please advice.
| Hewit
|
|
|
|

 

[Hewit]

Bruce,
Since I am not finding the close method for datareader, I used Dispose and
Clear methods. But no help.
Hewit

 

[IPGrunt]

Bruce,
Since I am not finding the close method for datareader, I used Dispose and
Clear methods. But no help.
Hewit

First, assume you've created appropriate parameter associated with the
command:

SqlParameter pReturn = new System.Data.SqlClient.SqlParameter
("@RETURN_VALUE", System.Data.SqlDbType.Int,
System.Data.ParameterDirection.ReturnValue, false, ... );

myCommand.Parameters.Add(pReturn);

...

Then call the command creating the reader and set it up to close the
connection:

myConnection.Open();
SqlDataReader rdr = myCommand.ExecuteReader
(CommandBehavior.CloseConnection);

Then when finished with the reader, close. It will close the associated
connection:

rdr.Close();

Then the return value parameter of the SPROC are made available by
ADO.NET

int myInt = (int)pReturn.Value;


Give it a try. But I am surprised you couldn't find a Close() method for
the SqlDataReader class?

-- ipgrunt