Returning 2 items from function

Question

I have stored procedure which I will execute its result and pass it into a Dataset and return it but at the same time I have a Output parameter in my stored procedure which I also want to return, how can I return both the dataset and the integer output value with my function, or is there a more 'appropriate way' to handle such situation? Please kindly advice, thanks.

Public Function SelectCampaignManagementTableListing(ByVal objCampaignManagmentClassBLL As CampaignManagementBLL, ByVal dbConnection As DbConnection, ByVal SortExpression As String, ByVal SortFilterText As String, ByVal SortFilterField As String, ByVal SortDirection As String, ByVal PageNumber As Integer, ByVal PageCount As Integer, ByVal TotalCount As Integer) As DataSet

            Dim dbCommand As DbCommand = Nothing
            Dim retDS As DataSet = Nothing

            Try
                If dbConnection.State <> ConnectionState.Open Then
                    dbConnection.Open()
                End If

                dbCommand = GetStoredProcedureCommand("Campaign_LoadListing")
                dbCommand.Connection = dbConnection

                With objCampaignManagmentClassBLL

                    Select Case SortFilterField
                        Case "Code"
                            AddInParameter(dbCommand, "@Code", DbType.String, 50, DBNull.Value)
                            dbCommand.Parameters("@Code").Value = SortFilterText
                        Case "Name"
                            AddInParameter(dbCommand, "@Name", DbType.String, 150, DBNull.Value)
                            dbCommand.Parameters("@Name").Value = SortFilterText
                    End Select


                    Select Case SortDirection
                        Case "True"
                            AddInParameter(dbCommand, "@SortOrder", DbType.Boolean, 1, DBNull.Value)
                            dbCommand.Parameters("@SortOrder").Value = True
                        Case "False"
                            AddInParameter(dbCommand, "@SortOrder", DbType.Boolean, 1, DBNull.Value)
                            dbCommand.Parameters("@SortOrder").Value = False
                    End Select

                    AddInParameter(dbCommand, "@SortOrder", DbType.String, 50, DBNull.Value)
                    If Not String.IsNullOrEmpty(SortDirection) Then
                        dbCommand.Parameters("@SortOrder").Value = SortDirection
                    End If

                    Select Case SortExpression
                        Case "Code"
                            'sortType = "@SortByCampaignCode"
                            AddInParameter(dbCommand, "@SortByCampaignCode", DbType.Boolean, 1, DBNull.Value)
                            dbCommand.Parameters("@SortByCampaignCode").Value = True
                        Case "Name"
                            'sortType = "@SortByCampaignName"
                            AddInParameter(dbCommand, "@SortByCampaignName", DbType.Boolean, 1, DBNull.Value)
                            dbCommand.Parameters("@SortByCampaignName").Value = True
                        Case "Priority"
                            AddInParameter(dbCommand, "@SortByPriority", DbType.Boolean, 1, DBNull.Value)
                            dbCommand.Parameters("@SortByPriority").Value = True

                    End Select

                    AddInParameter(dbCommand, "@PageNumber", DbType.Int32, 4, DBNull.Value)
                    If Not IsNothing(PageNumber) Then
                        dbCommand.Parameters("@PageNumber").Value = PageNumber
                    End If

                    AddInParameter(dbCommand, "@PageCount", DbType.Int32, 4, DBNull.Value)
                    If Not IsNothing(PageCount) Then
                        dbCommand.Parameters("@PageCount").Value = PageCount
                    End If

' Heres the output parameter
                    Dim objOutputParameter As New SqlParameter("@Return_TotalCount", SqlDbType.Int)
                    dbCommand.Parameters.Add(objOutputParameter)
                    objOutputParameter.Direction = ParameterDirection.Output

                End With

' These are the 2 items I need to return.
                retDS = ExecuteDataSet(dbCommand)
                Dim tCount As Integer = CInt(dbCommand.Parameters("@Return_TotalCount").Value)

            Catch ex As Exception
                Throw New DALException(ex, dbCommand, Caching.GetCustomerCodeCookie(), "SelectCampaignManagementTableListing")
            Finally

                If Not dbCommand Is Nothing Then
                    dbCommand.Dispose()
                End If
            End Try
            Return retDS
        End Function

Thanks for all the helpful answers, sry if this seem out of place but I just read about Structs, are they are better choice then using a custom class? — k80sg, Oct 26 '11 at 10:26
Possible duplicate of http://stackoverflow.com/questions/2434222/is-it-possible-for-a-function-to-return-two-values?lq=1 — Michael Freidgeim, Jun 03 '16 at 23:25

score 8 · Accepted Answer · answered Oct 26 '11 at 04:10

You have a number of options:

Define your own custom class that exposes your output properties, and return an instance of that class.
Return an instance of System.Tuple that contains your output values.
Use output parameters to return your output values.

score 2 · Answer 2 · edited May 23 '17 at 10:29

2

Can Use class or struct which has DataSet property and intiger property. You can return that class or struct from your function by setting values inside the function

few links for many other alternatives:

edited May 23 '17 at 10:29

Community

1
1

answered Oct 26 '11 at 04:04

Damith

62,401
13
102
153

score 1 · Answer 3 · answered May 17 '12 at 18:37

1

The easiest thing to do would be if your data is the same type(string, int etc) then put them into a list and pass back that list. No custom anything required.

answered May 17 '12 at 18:37

Axxelsian

787
3
9
25

score 0 · Answer 4 · answered Oct 26 '11 at 04:08

You can either create a custom class that encapsulates the DataSet object and the output parameter and return that through the name of the function, or include an out parameter to the function to return the stored procedures out parameter and continue to return the DataSet through the name of the function as you are currently.

score 0 · Answer 5 · answered Oct 26 '11 at 04:53

Another option you have, in addition to the excellent answers given by Joviee, us to pass in a Action(Of Integer) or an Action(Of Exception). Using actions gives you the ability to return zero or more results from your code, rather than one and one only that the options force you to do.

Returning 2 items from function

5 Answers5