How to get output parameter of sql in codebehind

Question

hi guys, My stored procedure is like this.

 ALTER PROCEDURE [dbo].[GetImagesByDesignId] 
        @DesignID bigint,
        @RegID bigint,
        @PageIndex INT,
        @NumRows INT,
        @ImageCount INT OUTPUT
    AS

BEGIN

    SELECT @ImageCount=(SELECT COUNT(*) FROM DocManagement where DesignID=@DesignID and RegID=@RegID)

    Declare @startRowIndex INT;
    set @startRowIndex = (@PageIndex * @NumRows) + 1;

    With ImageEntries as (
     SELECT ROW_NUMBER() OVER (ORDER BY DocumentID ASC) as Row, RegID, DesignID,ImageName
     FROM DocManagement
     WHERE  DesignID=@DesignID and RegID=@RegID
    )

    Select RegID, DesignID,ImageName
    FROM ImageEntries
    WHERE Row between 
    @startRowIndex and @StartRowIndex+@NumRows-1

END

I am calling storedprocedure in my codebehind as

Dim dt As DataTable = objUpload.GetDocuments(lngRegID, lngDesignID)
            dlView.DataSource = dt
            dlView.DataBind()

dlView is datalist.Method GetDocuments is written in another class like this

Public Function GetDocuments(ByVal lngRegID As Long, ByVal lngDesID As Long) As DataTable
            Try

                Dim db As Database = DatabaseFactory.CreateDatabase()
                Dim DbCommand As DbCommand = db.GetStoredProcCommand("GetImagesByDesignId")
                db.AddInParameter(DbCommand, "@RegID", DbType.Int64, lngRegID)
                db.AddInParameter(DbCommand, "@DesignID", DbType.Int64, lngDesID)
                db.AddInParameter(DbCommand, "@PageIndex ", DbType.Int32, intPageIndex)
                db.AddInParameter(DbCommand, "@NumRows ", DbType.Int32, intNumRows)
                db.AddOutParameter(DbCommand, "ImageCount", DbType.Int32, 250)
                Return db.ExecuteDataSet(DbCommand).Tables(0)
                Dim strOutput() As String = {db.GetParameterValue(DbCommand, "ImageCount").ToString}

            Catch ex As Exception
            End Try

        End Function

Problem is i want to get datattable as well as imagecount in codebehind.How can i return back datatable and imagecount to codebehind.Can anybody help?

Answer 1

Public Function GetDocuments(ByVal lngRegID As Long, ByVal lngDesID As Long, ByRef strOutput As String) As DataTable

You can use ByRef and pass a string variable as a reference and set it in your method. The reference of strOutput will be passed to your method, and when you set the value of that variable in the method you can get back the changed value after the method call.

Dim strOutput As String = Nothing
Dim dt As DataTable = GetDocuments(lngRegID, lngDesID, strOutput)
Console.WriteLine(strOutput)

Answer 2

You can create a class to use are return value that holds both the data table and the image count. Or you can send a variable as an argument by reference:

Public Function GetDocuments(ByVal regID As Long, ByVal desID As Long, ByRef imageCount As Integer) As DataTable

In the method you just set the value of imageCount.

In your stored procedure you don't need a nested query to get the count. Just do like this:

select @ImageCount = count(*)
from DocManagement
where DesignID = @DesignID and RegID = @RegID

Note:
I see that you have a Catch block without anything in it. Never ever do that. You are catching exceptions and ignoring them, that can only lead to problems.

In there rare case where you actually need to catch an exception and ignore it, you should at least have a comment inside the Catch block explaining why it's ignored.

Also, you are catching the base class Exception, when you should catch a more specific class like SqlException.