Problems with LINQ and CLR Stored Procedure

[Jules140]

Hi all,

I'm having a bit of a problem with Linq and a CLR Stored Procedure.

I'm just trying to fill a gridview with a simple query that depends on multiple
(one to n) search terms.

The sproc is as follows:

ALTER PROCEDURE [dbo].[CMS_Pages_Search]
@searchString varchar(500)
AS
BEGIN
SET NOCOUNT ON

EXEC clr_CMS_Pages_Search @searchString
END

This works fine (returning a dataset from SQL Management Studio).

The VB Procedure is as follows:

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim db As New migkbDataContext
grdResults.DataSource = db.CMS_Pages_Search(txtKeywords.Text.Trim)
grdResults.DataBind()
Dim iRowCount As Integer = grdResults.Rows.Count
If iRowCount > 0 Then
ResultMessage1.ShowSuccess("Search returned " & iRowCount & " results")
Else
ResultMessage1.ShowFail("No records found for search " & txtKeywords.Text.Trim)
End If
End Sub

From the cms_Pages_Search call, there is no Intellisense to specify ToList
etc.

The code generated from the *.dbml designer file is trying to return an Integer
(not what I want):

<FunctionAttribute(Name:="dbo.CMS_Pages_Search")> _
Public Function CMS_Pages_Search(<Parameter(DbType:="VarChar(500)")> ByVal
searchString As String) As Integer
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod,MethodInfo),
searchString)
Return CType(result.ReturnValue,Integer)
End Function
End Class

When the btnSearch_Click sub is executed, when attempting to bind the grid
to the datsource, the message "Data source is an invalid type. It must be
either an IListSource, IEnumerable, or IDataSource." is displayed.

Anyone have any ideas what's going on or ideas to work around this?

Regards

Jules

 

[Jules140]

Hi all,

I've overridden this in a partial class, but does anybody have an explanation
to why this is happening in the first place?

Regards

Jules