up vote 1 down vote favorite

Here's a simple code snippet of a larger function as an example. 

Using conn as New SqlConnection("conn string")
    Using find as new SqlCommand("ExampleProc",conn)
        Dim rParam as new SqlParameter("@RESULT",SqlDbType.Int)
        rParam.Direction = ParameterDirection.Output
        find.Pareameters.Add(rParam)

        Using f as SqlDataReader = find.ExecuteReader
              'Do stuff with datareader
        End Using

        updateResult.Success = Convert.ToBoolean(find.Parameters("@RESULT").Value)
    End Using
  End Using

I know the Output parameter is returned after the SqlDataReader is closed. From what I think I know, the Using statement will call .Dispose on the SqlDataReader, so will that basically wipe out the Output parameter? If so, what is the best method to call a SqlDataReader that also contains Output parameters that closes and disposes everything correctly? I couldn't find any specific info or examples online in my searching. Thanks!

Let me add that based on what I've read you only have access to the Output parameter using a SqlDataReader after you call .Close on the SqlDataReader.

share|improve this question
Disposing the reader should only affect that one object (and any objects or resources it owns). I think it shouldn't remove anything from the command. – svick Aug 21 '11 at 18:29

2 Answers

up vote 1 down vote

You really only need the value returned in the output parameter.

You can copy the value to a variable declared outside the Using block and return that, or return the value directly, as soon as you have access to it.

share|improve this answer
Based on what I've read, you only have access to the value if you call .close on the sqldatareader. So will it be returned in the above example to use in the first place? I know how to reference and use the value in the rest of the function. – NinjaBomb Aug 22 '11 at 2:22
@NinjaBomb - From the article: "I found that you must completely process the entire recordset BEFORE retrieving the output parameters otherwise, the output params will be empty." - this means all records need to be read first, not that .Close() needs to be called. – Oded♦ Aug 22 '11 at 7:09
A short copy and paste summary from the end of the article: -Make sure you retrieve the recordset first as an SqlDataReader via cmd.ExecuteReader() -Process through all recordsets and then... -Make sure you CLOSE the recordset, or SqlDataReader via reader.Close() before trying to retrieve the output parameters. – NinjaBomb Aug 22 '11 at 16:32
up vote 0 down vote

not sure that I understand what you want to achieve but this seems a better approach

    updateResult.Success = False

    Using f as SqlDataReader = find.ExecuteReader
          'Do stuff with datareader
          updateResult.Success = Convert.ToBoolean(find.Parameters("@RESULT").Value)
    End Using

You read the result within the Using statement and if anything fails you preset it to false...

share|improve this answer
I thought your example would not work because the SqlDataReader must be closed before you can access the output parameter? Do you close it in the Using statement first? – NinjaBomb Aug 21 '11 at 19:36
why must the reader be closed first ? I don't work with sql sever but with other DBs (like oracle) never had such a limitation... – Yahia Aug 21 '11 at 19:51
Check this blog post out for good explanation: dominicpettifer.co.uk/Blog/8/… – NinjaBomb Aug 22 '11 at 1:35
I understand... that is different with Oracle - there you could just return a cursor and work with that... this just means that before you read the output param you need to have read all records (for example with a while loop within the Using)... the above would still work... – Yahia Aug 22 '11 at 8:23
That's exactly what I do to retrieve the records in the Using block. Not so sure the Output parameter will be available though. Hopefully doing some testing today – NinjaBomb Aug 22 '11 at 16:35

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.