Return value before reading DataReader

[tshad]

I am trying to set up a class to handle my database accesses.

I can't seem to figure out how to get the return value from my dataReader
from these routines (most of which I got elsewhere). They do work pretty
well, except for the change I made to get the return value.

For example, I have the following:
************************************************** ********************
Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef returnValue As Integer) _
As SqlDataReader

Dim returnReader As SqlDataReader

myConnection.Open()
Dim command As SqlCommand = _
BuildIntCommand(storedProcName, parameters)

returnReader = command.ExecuteReader( CommandBehavior.CloseConnection)
' Connection will be closed automatically
returnValue = CInt(command.Parameters("ReturnValue").Value)

Return returnReader

End Function

Private Function BuildIntCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As SqlCommand = _
BuildQueryCommand(storedProcName, parameters)
Dim parameter As New SqlParameter()

With parameter
.ParameterName = "ReturnValue"
.DbType = SqlDbType.Int
.Size = 4
.Direction = ParameterDirection.ReturnValue
.IsNullable = False
.Precision = 0
.Scale = 0
.SourceColumn = String.Empty
.SourceVersion = DataRowVersion.Default
.Value = Nothing
End With
command.Parameters.Add(parameter)

Return command

End Function

Private Function BuildQueryCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As New SqlCommand(storedProcName, myConnection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter
For Each parameter In parameters
command.Parameters.Add(parameter)
Next

Return command

End Function
************************************************** **************

The problem seems to be that I found out I either need to close the
connection or run NextResult() to get the return value. I would need to get
access to the Command object to get the return value (I think). Since I
would need to Read the DataReader before I close the connection - I have no
access to Command (since I am only passing back the DataReader to the
caller).

Is there anyway to get the return value from the DataReader itself?

Thanks,

Tom

[Ben]

Sql return values are a lot like method/function return values. They aren't
available until the function has finished running. You need to close your
reader to access the return value or output parameters iirc... If you
really need to access the return value before closing the reader try
returning the return value as a seperate result set if possible...

HTH,
Ben
"tshad" <ts**********@ftsolutions.com> wrote in message
news:OX**************@TK2MSFTNGP09.phx.gbl...

I am trying to set up a class to handle my database accesses.

I can't seem to figure out how to get the return value from my dataReader
from these routines (most of which I got elsewhere). They do work pretty
well, except for the change I made to get the return value.

For example, I have the following:
************************************************** ********************
Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef returnValue As Integer) _
As SqlDataReader

Dim returnReader As SqlDataReader

myConnection.Open()
Dim command As SqlCommand = _
BuildIntCommand(storedProcName, parameters)

returnReader = command.ExecuteReader(
CommandBehavior.CloseConnection)
' Connection will be closed automatically
returnValue = CInt(command.Parameters("ReturnValue").Value)

Return returnReader

End Function

Private Function BuildIntCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As SqlCommand = _
BuildQueryCommand(storedProcName, parameters)
Dim parameter As New SqlParameter()

With parameter
.ParameterName = "ReturnValue"
.DbType = SqlDbType.Int
.Size = 4
.Direction = ParameterDirection.ReturnValue
.IsNullable = False
.Precision = 0
.Scale = 0
.SourceColumn = String.Empty
.SourceVersion = DataRowVersion.Default
.Value = Nothing
End With
command.Parameters.Add(parameter)

Return command

End Function

Private Function BuildQueryCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As New SqlCommand(storedProcName, myConnection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter
For Each parameter In parameters
command.Parameters.Add(parameter)
Next

Return command

End Function
************************************************** **************

The problem seems to be that I found out I either need to close the
connection or run NextResult() to get the return value. I would need to
get access to the Command object to get the return value (I think). Since
I would need to Read the DataReader before I close the connection - I have
no access to Command (since I am only passing back the DataReader to the
caller).

Is there anyway to get the return value from the DataReader itself?

Thanks,

Tom

[Bruce Barker]

in the datastream (tds) the return value comes after all other result sets.
to access the return value, you need to read thru all rows and result sets
first (or close the reader - which will do this automatically).

-- bruce (sqlwork.com)
"tshad" <ts**********@ftsolutions.com> wrote in message
news:OX**************@TK2MSFTNGP09.phx.gbl...

I am trying to set up a class to handle my database accesses.

I can't seem to figure out how to get the return value from my dataReader
from these routines (most of which I got elsewhere). They do work pretty
well, except for the change I made to get the return value.

For example, I have the following:
************************************************** ********************
Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef returnValue As Integer) _
As SqlDataReader

Dim returnReader As SqlDataReader

myConnection.Open()
Dim command As SqlCommand = _
BuildIntCommand(storedProcName, parameters)

returnReader = command.ExecuteReader(
CommandBehavior.CloseConnection)
' Connection will be closed automatically
returnValue = CInt(command.Parameters("ReturnValue").Value)

Return returnReader

End Function

Private Function BuildIntCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As SqlCommand = _
BuildQueryCommand(storedProcName, parameters)
Dim parameter As New SqlParameter()

With parameter
.ParameterName = "ReturnValue"
.DbType = SqlDbType.Int
.Size = 4
.Direction = ParameterDirection.ReturnValue
.IsNullable = False
.Precision = 0
.Scale = 0
.SourceColumn = String.Empty
.SourceVersion = DataRowVersion.Default
.Value = Nothing
End With
command.Parameters.Add(parameter)

Return command

End Function

Private Function BuildQueryCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As New SqlCommand(storedProcName, myConnection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter
For Each parameter In parameters
command.Parameters.Add(parameter)
Next

Return command

End Function
************************************************** **************

The problem seems to be that I found out I either need to close the
connection or run NextResult() to get the return value. I would need to
get access to the Command object to get the return value (I think). Since
I would need to Read the DataReader before I close the connection - I have
no access to Command (since I am only passing back the DataReader to the
caller).

Is there anyway to get the return value from the DataReader itself?

Thanks,

Tom

[tshad]

"Ben" <be*@online.nospam> wrote in message
news:b9***************************@FUSE.NET...

Sql return values are a lot like method/function return values. They
aren't available until the function has finished running. You need to
close your reader to access the return value or output parameters iirc...
If you really need to access the return value before closing the reader
try returning the return value as a seperate result set if possible...
So in the case of where you have something like

function GetReader("stored Procedure Name", parameters) as
SqlDataReader

There would be no way to get the return value, since I don't have the
command object (where you get the return value) and I obviously can't close
the SqlDataReader to get it in my function - because then the DataReader
would have nothing in it.

I don't really want to write my Stored procedure to accommodate the
DataReader. I want the return value to be a return value.

Thanks,

Tom
HTH,
Ben
"tshad" <ts**********@ftsolutions.com> wrote in message
news:OX**************@TK2MSFTNGP09.phx.gbl...

I am trying to set up a class to handle my database accesses.

I can't seem to figure out how to get the return value from my dataReader
from these routines (most of which I got elsewhere). They do work pretty
well, except for the change I made to get the return value.

For example, I have the following:
************************************************** ********************
Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef returnValue As Integer) _
As SqlDataReader

Dim returnReader As SqlDataReader

myConnection.Open()
Dim command As SqlCommand = _
BuildIntCommand(storedProcName, parameters)

returnReader = command.ExecuteReader(
CommandBehavior.CloseConnection)
' Connection will be closed automatically
returnValue = CInt(command.Parameters("ReturnValue").Value)

Return returnReader

End Function

Private Function BuildIntCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As SqlCommand = _
BuildQueryCommand(storedProcName, parameters)
Dim parameter As New SqlParameter()

With parameter
.ParameterName = "ReturnValue"
.DbType = SqlDbType.Int
.Size = 4
.Direction = ParameterDirection.ReturnValue
.IsNullable = False
.Precision = 0
.Scale = 0
.SourceColumn = String.Empty
.SourceVersion = DataRowVersion.Default
.Value = Nothing
End With
command.Parameters.Add(parameter)

Return command

End Function

Private Function BuildQueryCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As New SqlCommand(storedProcName, myConnection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter
For Each parameter In parameters
command.Parameters.Add(parameter)
Next

Return command

End Function
************************************************** **************

The problem seems to be that I found out I either need to close the
connection or run NextResult() to get the return value. I would need to
get access to the Command object to get the return value (I think).
Since I would need to Read the DataReader before I close the connection -
I have no access to Command (since I am only passing back the DataReader
to the caller).

Is there anyway to get the return value from the DataReader itself?

Thanks,

Tom