T
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
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