eltonsky (11) [Avatar] Offline

I try to use store proc return a output parameter and datatable, but I just cannot get the value of output parameter!

My store proc is like:


select distinct *

from ConfirmationHistory

WHERE RowNumber > ((@PageNumber - 1) * @PageSize) AND RowNumber <= (@PageNumber * @PageSize)

-- Check if we need to retrieve Total Records

IF(@RetrieveTotalRecords = 1)

SELECT @TotalRecords = COUNT(*)

FROM dbo.r_welcome_pack

where enterprise_fk = @entId


I assume the problem is my output parameter is in a different "select " which is considered as another result set by linq.

Am I correct?


jwooley (123) [Avatar] Offline
Re: Problem with output parameter!
In your procedure, you are using multiple results rather than a result with a return value (through RETURN or an OUTPUT parameter). Here you need to use the IMultipleResult rather than the default ISingleResult implementation. It appears that the designer does not map IMultipleResult in the final build, so you are going to need to do it yourself. We mention this interface in chapter 8 but didn't have a chance to include a sample. Here's a sample implementation on returning the Subjects and Books from the Book sample database. First the stored proc:

CREATE PROCEDURE dbo.GetSubjectAndBooks

Select * from subject

IF @@RowCount>0 BEGIN
Select * from Book

Now for the function mapping. We want to create a function that can return both the Subjects and the Books. To do this, we will create a function that returns the MultipleResult. Simlar to the standard stored procedure mapping, you create a function in a custom partial for the DataContext. The function will return a value of type IMultipleResults. Decorate the function with the FunctionAttribute including the name of the function. Here's the implementation in VB:

<FunctionAttribute(Name:="dbo.GetSubjectAndBooks")> _
<ResultType(GetType(Book))> _
<ResultType(GetType(Subject))> _
Public Function GetSubjectAndBooks() As IMultipleResults
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo))
Dim results As IMultipleResults = DirectCast(result.ReturnValue, IMultipleResults)
Return results
End Function

Notice, the main difference here is the addition of two attributes identifying the possible ResultTypes (Book and Subject). The rest of the function should be self explanitory. To consume the function, we call the GetResult method of IMultipleResults passing in the generic type we want to return as follows:

Dim context As New LinqBooksDataContext

ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Subject))
ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Book))

I hope this helps.

eltonsky (11) [Avatar] Offline
Re: Problem with output parameter!
Thanx very much ,

It is helpful