Using SQL Server 2005 w/ latest PetaPoco w/ the following code:
Dim count = New SqlParameter("@Count", System.Data.ParameterDirection.Output)
count.DbType = Data.DbType.Int32
Dim results = db.Query(Of User)(";exec SearchUserPaged @@page=@page, @@maximumRows=@maximumRows, @@ClientID=@ClientID, @@orderBy=@SortExp, @@Count=@Count OUT",
New With {.page = pageEx, .maximumRows = maximumRowIndex, .ClientID = Me.ClientID, .SortExp = sortExpression, .Count = count}).ToList()
'Dim results = db.EDPEntities.Query(Of User)(";exec SearchUserPaged @@page=@0, @@maximumRows=@1, @@ClientID=@2, @@orderBy=@3, @@Count=@4 OUTPUT",
' pageEx, maximumRowIndex, Me.ClientID, sortExpression, count).ToList()
If IsDBNull(count.Value) Then
Me.Count = 0
Else
Me.Count = count.Value
End If
But the output param always returns a value of 0. Pretty new to PetaPoco so not sure if I'm missing something super obvious.
Here is the SQL generated by Peta which runs fine in query analyzer:
DECLARE @0 int,@1 int,@2 int,@3 nvarchar(4000),@4 int
SET @0=1
SET @1=25
SET @2=10145
SET @3=NULL
SET @4=2
exec SearchUserPaged @page=@0, @maximumRows=@1, @ClientID=@2, @orderBy=@3, @Count=@4 OUT
SELECT @4
When I SELECT @4
the correct value is returned.
This worked (not sure why):
Dim count = New SqlParameter("@Count", System.Data.SqlDbType.Int)
count.Direction = System.Data.ParameterDirection.Output
count.Value = DBNull.Value
Dim results = db.Query(Of User)(";exec SearchUserPaged @@page=@page, @@maximumRows=@maximumRows, @@ClientID=@ClientID, @@orderBy=@SortExp, @@Count=@Count OUT", New With { _
Key .page = pageEx, _
Key .maximumRows = maximumRowIndex, _
Key .ClientID = ClientID, _
Key .SortExp = sortExpression, _
Key .Count = count _
}).ToList()
If IsDBNull(count.Value) Then
Me.Count = 0
Else
Me.Count = count.Value
End If