returning datareader with output parameter

[Mike P]

How do you return a datareader from a stored procedure, but also return
an output parameter?

Here is my code, which is just returning a data reader at the moment :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@username",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());


SqlDataReader dr = _comm.ExecuteReader();


User u;
if (dr.HasRows)
{
while (dr.Read())

 

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

Mike,

You won't have access to the parameters values until you have cycled
through the data reader and read all the data.

 

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

Mike,

Re-reading your post, it occurs to me that my response might not have
been the answer you were looking for. If you are looking to access the
return value, or the values of output parameters, then you have to access
the SqlParameter instance exposed by the Parameters collection, and get the
value from that, as it will be changed after execution (what I said in my
previous post about reading through all the data still applies).

To get a return value, you have to create a parameter with the Direction
property set to ParameterDirection.ReturnValue.

 

[Mike P]

I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@username",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())

 

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

Mike,

As per my first post, the parameter won't be populated until AFTER you
have cycled through all the rows.

 

[Doug Semler]

I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())

I believe that you need to Close() the SqlDataReader before you can
access output parameters...

 

[Mel]

Here is from the help file

the ParameterDirection is output, and execution of the associated SqlCommand
does not return a value, the SqlParameter contains a null value.
Output, InputOut, and ReturnValue parameters returned by calling
ExecuteReader cannot be accessed until you call Close or Dispose on the
SqlDataReader.

 

[Bob Grommes]

Mike,

You're not listening to what Nicholas said. The value will not be in
the output parameter until AFTER you have read through all the data ...
so you would have to pick up the value AFTER your while loop.

If you need the value in advance then you'll have to make a separate
call to get that info. If that's not practical, and it's not a large
result set, you could consider filling a DataTable instead of using a
DataReader.

--Bob

 

[Mike P]

Thanks everybody, I got it working by reading the output parameter after
I had closed my datareader.