none
Problems with LINQ and CLR Stored Procedure RRS feed

  • Question

  • 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

    Thursday, April 17, 2008 11:21 AM

Answers

  • As there has been no further discussion on this topic I'm going to mark it as closed but feel free to reopen it if you wish to continue Smile

     

    [)amien

     

    Friday, June 13, 2008 5:27 PM
    Moderator

All replies

  • 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

     

    Friday, April 18, 2008 11:08 AM
  • If you execute CMS_Pages_Search from Management Studio with empty values what result shapes do you get including on the messages tab?

     

    [)amien

    Friday, May 30, 2008 6:27 PM
    Moderator
  • As there has been no further discussion on this topic I'm going to mark it as closed but feel free to reopen it if you wish to continue Smile

     

    [)amien

     

    Friday, June 13, 2008 5:27 PM
    Moderator