Search code examples
c#sql-serverlinq-to-sqlmultiple-select

C# Linq-to-SQL Trying to recive multiple selects

I got this SQL code in a SP: (MS SQL 2008)

    DECLARE @type tinyint
    SELECT @type = Type FROM Contents WHERE ContentID = @ContentID    

    SELECT [ParentContentID], [Headline], [ShortDescription], [CategoryID], [Type], [State], [DatePublished], [Name] FROM Contents INNER JOIN Users ON Users.ID = Contents.PublishedBy WHERE ContentID = @ContentID

    IF (@type = 2) -- Content with text
    BEGIN
  SELECT [Preamble], [ContentText], [FaceBook], [Twitter], [PrintPage], [TipAFriend] FROM ContentText WHERE ContentID = @ContentID
    END

    SELECT [ID], [ImagePath], [ImageType] FROM ContentImages WHERE ContentID = @ContentID
    SELECT [ID], [BoxID] FROM ContentBoxes WHERE ContentID = @ContentID

I thought that i should be smart so i added a Linq-to-SQL class to my project and dragged the SP to the class. However, i can't seem to access the data from the second, third and forth select statement. I was hoping that the Linq-to-SQL class would produce 4 data tables with the information thus letting me access them like: data[2].Row[0].ImagePath.

Do i have to create my own code to get the code from the SQL-server in order to get this functionality?

Solution

  • LINQ to SQL does support multiple results sets from stored procedures. You'll want to look at the documentation for IMultipleResults, and you'll need to write some code in the data context partial (rather than just relying on what the designer generates). Some links to start you off: