CODEWITHSUNDEEP
asp.netvb.net
user42348
user42348

Reputation: 4319

How to get output parameter of sql in codebehind

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?

Upvotes: 0

Views: 2564

Answers (2)

Guffa
Guffa

Reputation: 700152

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.

Upvotes: 2

Kirtan
Kirtan

Reputation: 21695

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)

Upvotes: 1

Related Questions