none
Needing some help with LinQ to SQL. RRS feed

  • Question

  •  

    It seems so simple, but I'm having the hardest time trying to return a result set from a stored procedure in SQL 2005 utilizing LinQ.  For instance select fields from a table and return rows of information.  I'm able to do this using LinQ Adhoc queries.  Just not able to do it returning data from a simple sproc.  Referencing the sproc from LinQ seems to default the returning value as integer.  I need something that I can query.  I've been going in the DBML and changing the return type to IEnumerable, but I encounter errors when doing this (IEnumerable is not a valid return types for mapped stored procedure linq). I have tried other return type yeilding the same result.  If anyone has done this with success or have an idea please share. 

     

    Thanks

    Friday, October 10, 2008 4:48 PM

All replies

  • Have you checked out this article?

    Using Stored Procedures with LINQ to SQL

    http://www.mssqltips.com/tip.asp?tip=1542

    Friday, October 10, 2008 5:57 PM
  • Thanks for the link, but yes I've already checked it out.  The return value from the sproc is integer, I'm having a problem changing that to something other than that.  Just trying the example as it will cause an error, because the return type is not something I can enumerate through.  I need to now how to go about changing the dbml function.  I'm using asp/vb code the code as is, is listed below:

    <FunctionAttribute(Name:="dbo.AccBcm_P_Search")> _

    Public Function AccBcm_P_Search(<Parameter(DbType:="VarChar(50)")> ByVal fname As String, <Parameter(DbType:="VarChar(50)")> ByVal mname As String, <Parameter(DbType:="VarChar(50)")> ByVal lname As String, <Parameter(DbType:="VarChar(500)")> ByVal addr As String, <Parameter(DbType:="VarChar(50)")> ByVal city As String, <Parameter(DbType:="VarChar(50)")> ByVal state As String, <Parameter(DbType:="VarChar(50)")> ByVal zip As String, <Parameter(DbType:="VarChar(50)")> ByVal phone As String, <Parameter(DbType:="VarChar(50)")> ByVal dob As String, <Parameter(DbType:="VarChar(50)")> ByVal ssn As String) As Integer

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), fname, mname, lname, addr, city, state, zip, phone, dob, ssn)

    Return CType(result.ReturnValue, Integer)

    End Function

     

    Running it like that works fine, but the return result fromthe sproc is 0.  My sproc is actually suppose to return me results.

     

    I modified the above to this:

     

     

    <FunctionAttribute(Name:="dbo.AccBcm_P_Search")> _

    Public Function AccBcm_P_Search(<Parameter(DbType:="VarChar(50)")> ByVal fname As String, <Parameter(DbType:="VarChar(50)")> ByVal mname As String, <Parameter(DbType:="VarChar(50)")> ByVal lname As String, <Parameter(DbType:="VarChar(500)")> ByVal addr As String, <Parameter(DbType:="VarChar(50)")> ByVal city As String, <Parameter(DbType:="VarChar(50)")> ByVal state As String, <Parameter(DbType:="VarChar(50)")> ByVal zip As String, <Parameter(DbType:="VarChar(50)")> ByVal phone As String, <Parameter(DbType:="VarChar(50)")> ByVal dob As String, <Parameter(DbType:="VarChar(50)")> ByVal ssn As String) As IEnumerable

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), fname, mname, lname, addr, city, state, zip, phone, dob, ssn)

    Return CType(result.ReturnValue, IEnumerable)

    End Function

    End Class

     

     

    Here is a sample of my sproc.  As you can see it's quite simple, just returning a result set based on values that were passed.

     

    ALTER PROCEDURE AccBcm_P_Search

    (

    -- Add the parameters for the stored procedure here

    @fname as varchar(50) = null,

    @mname as varchar(50) = null,

    @lname as varchar(50) =null,

    @addr as varchar(500) =null,

    @city as varchar(50) =null,

    @state as varchar(50) =null,

    @zip as varchar(50) =null,

    @phone as varchar(50)= null,

    @dob as varchar(50) =null,

    @ssn as varchar(50)= null

    )

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON

    DECLARE @sql as varchar(300)

    DECLARE @sqlField as varchar(300)

    SELECT @sql = ''

    If @fname is not null

    SELECT @sql = @sql + ' AND firstname=''' + @fname + ''''

    if @lname is not null

    SELECT @sql = @sql + ' AND lastname=''' + @lname + ''''

    if @mname is not null

    SELECT @sql = @sql + ' AND middlename=''' + @mname + ''''

    if @addr is not null

    SELECT @sql = @sql + ' AND addr=''' + @addr + ''''

    if @city is not null

    SELECT @sql = @sql + ' AND city=''' + @city + ''''

    if @state is not null

    SELECT @sql = @sql + ' AND state=''' + @state + ''''

    if @zip is not null

    SELECT @sql = @sql + ' AND zip=''' + @zip + ''''

    if @phone is not null

    SELECT @sql= @sql + ' AND phone=''' + @phone + ''''

    if @dob is not null

    SELECT @sql = @sql + ' AND dob=''' + @dob + ''''

    if @ssn is not null

    SELECT @sql = @sql + ' AND ssn=''' + @ssn + ''''

    SELECT @sqlField = 'SELECT ''name''= firstname + '' '' +middlename + '' ''+lastname,

    ssn,address,city,state,zip,phone,dob

    FROM BcmAcc_main '

    SELECT @sqlField = @sqlField + ' WHERE search_Type IS NOT NULL ' + @sql

    exec(@sqlField)

     

    END

    GO

     

     

    Thanks in advance for any help.

    Friday, October 10, 2008 6:55 PM
  • Moving to LINQ to SQL forum.

    Friday, October 10, 2008 9:33 PM