SQLDataReader: Retrieving sproc output parameters

[Zach Corera]

If I want to return both a recordset to a SqlDataReader and an output
parameter from a stored procedure to .Net, I must close the SqlDataReader
first or the value of the output parameter is not passed to .Net

Are their any workarounds?

Can a SqlDataReader be "re-opened" after it's closed?

Does that reset it back to the first record?

 

[Nicholas Paldino [.NET/C# MVP]]

Zach,

I'm not sure about this, but you must have gotten the reader from a
command that you executed, right? Did you check the parameters on the
command to see if the output parameters were executed correctly? If not at
the beginning, then perhaps after all the items were iterated through?

Hope this helps.

 

[Miha Markic [MVP C#]]

Yup, I think they are returned only after traversing all items....

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Zach,

I'm not sure about this, but you must have gotten the reader from a
command that you executed, right? Did you check the parameters on the
command to see if the output parameters were executed correctly? If not
at the beginning, then perhaps after all the items were iterated through?

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

If I want to return both a recordset to a SqlDataReader and an output
parameter from a stored procedure to .Net, I must close the SqlDataReader
first or the value of the output parameter is not passed to .Net

Are their any workarounds?

Can a SqlDataReader be "re-opened" after it's closed?

Does that reset it back to the first record?

 

[William \(Bill\) Vaughn]

This is by design. Output parameters are passed close to the end of the TDS
stream generated for each resultset. You must accept all of the rows or
close the DataReader (which completes population) before the Output
parameters are exposed on the Parameters.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Yup, I think they are returned only after traversing all items....

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Zach,

I'm not sure about this, but you must have gotten the reader from a
command that you executed, right? Did you check the parameters on the
command to see if the output parameters were executed correctly? If not
at the beginning, then perhaps after all the items were iterated through?

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

If I want to return both a recordset to a SqlDataReader and an output
parameter from a stored procedure to .Net, I must close the
SqlDataReader
first or the value of the output parameter is not passed to .Net

Are their any workarounds?

Can a SqlDataReader be "re-opened" after it's closed?

Does that reset it back to the first record?

 

[Zach Corera]

This is by design. Output parameters are passed close to the end of the
TDS stream generated for each resultset. You must accept all of the rows
or close the DataReader (which completes population) before the Output
parameters are exposed on the Parameters.

That really makes it hard for me.

In my case, I want to return both a SqlDataReader as the return from a
method and the rowcount as an /out/ /int/ parameter.

So, what I have to do is:

SqlDataReader dr, dr1;
dr = cmd.ExecuteReader();
dr1 = dr;
while(dr.Read());
dr.Close()

Then my output parameter from the parameters collection of cmd has a value;
and I send back dr1.

There is no way to reset a SqlDataReader right?

I guess I could do another cmd.ExecuteReader() but that seems *highly*
costly !

 

[William \(Bill\) Vaughn]

Rt. Why not return a DataTable instead? This will be far easier to do in 2.0
if that's any consolation.
No, once you return all of the rows from a DataReader, there's nothing to
pass--it's empty. It's like sending back an empty glass when someone asks
for water and the amount they're getting. You have to pour it out to measure
it...


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Mikael Gustavsson]

Hi!

You should be able to get the output parameter from the command.

SqlCommand cmd = new SqlCommand("SPName", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("OutputParam", SqlDbType.Int);
cmd.Parameters["OutputParam"].Direction = ParameterDirection.Output;

//Open connection and execute the reader and do whatever you wish.

To get the output parameters you should be able to retrieve it like this
int outputValue = (int)cmd.Parameters["OutputParam"].Value;

I hope this helps

//Micke

 

[Percival Palbuck]

Hey Mikael,

That's exactly what I'm doing.

The problem is that originally my method returned SqlDataReader as the
return value, and it was consumed by another process.

In order to get the output parameter, I have to read and close
SqlDataReader or OutputParam is never set to a value !

So, now I ask:

1) Can a SqlDataReader be re-opened after I close it to get the output
parameter to have a value

2) If not, is there another way to get both a Data Reader and an output
parameter without consuming the DataReader?

My workaround has been to set the DataReader to another DataReader.
Then consume the first one to get the output parameter -- and then pass
the second one back in the methods return statement.

But that seems /very/ costly !

Hi!

You should be able to get the output parameter from the command.

SqlCommand cmd = new SqlCommand("SPName", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("OutputParam", SqlDbType.Int);
cmd.Parameters["OutputParam"].Direction = ParameterDirection.Output;

//Open connection and execute the reader and do whatever you wish.

To get the output parameters you should be able to retrieve it like this
int outputValue = (int)cmd.Parameters["OutputParam"].Value;

I hope this helps

//Micke

If I want to return both a recordset to a SqlDataReader and an output
parameter from a stored procedure to .Net, I must close the SqlDataReader
first or the value of the output parameter is not passed to .Net

Are their any workarounds?

Can a SqlDataReader be "re-opened" after it's closed?

Does that reset it back to the first record?

 

[Percival Palbuck]

Thanks (Bill) thats what I thought.


I'm just curious though -- what exactly is a Data Reader that it can
only be used once? I have never heard of such a thing in programming.
WHat is its basis?

 

[William \(Bill\) Vaughn]

It's really very common in the data access world. It's a data stream also
called a "firehose" cursor (but it's not really a cursor). It exposes the
low-level chunks of data returned by the provider. It was last exposed
directly in DB-Library (see editions 1 and 2 of my HHG books).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________