Archive for the 'sql 2005' Category

May 17 2008

multiple result sets in LINQ

Published by Raja Nadar under .net, c#, linq, sql 2005

I had a stored procedure which returned multiple result sets.

the stored proc in short, looked as follows: (trimmed down for relevant code)

 

CREATE PROCEDURE SELMessages
AS
BEGIN
 
   -- prefix blah blah
 
 Declare @Messages TABLE
 (
       MessageID int,
       MessageName nvarchar(256)
 )
 
 Declare @MessageRecipients TABLE
 (
       MessageID int,
       RecipientName nvarchar(256)
 )
 
    -- interim blah blah
 
    -- Result Set 1
    SELECT  MessageID, MessageName FROM @Messages
   
    -- Result Set 2
    SELECT  MessageID, RecipientName FROM @MessageRecipients
 
END
GO

using Linq to SQL (or DLinq or L2S) i had to use this stored procedure.
I dragged the SProc into the designer, and tried to use it in the code. the Linq to SQL designer had generated the following code:

 

[System.Data.Linq.Mapping.DatabaseAttribute(Name="MessageDB")]
public partial class MessageDBDataContext : System.Data.Linq.DataContext
{
 
 private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
 
        // more code
 
 [Function(Name="dbo.SELMessages")]
 public ISingleResult SELMessages()
 {
  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
  return ((ISingleResult)(result.ReturnValue));
 }
 
        // more code
}

the result to be written was a class which looked like:

 

public partial class SELMessagesResult
{  
 private int _MessageID;
 
 private string _MessageName;
 
 public SELMessageRecipientsResult()
 {
 }
 
 [Column(Storage="_MessageID", DbType="Int NOT NULL")]
 public int MessageID
 {
  get
  {
   return this._MessageID;
  }
  set
  {
   if ((this._MessageID != value))
   {
    this._MessageID = value;
   }
  }
 }
 
 [Column(Storage="_MessageName", DbType="NVarChar(256) NOT NULL", CanBeNull=false)]
 public string MessageName
 {
  get
  {
   return this._MessageName;
  }
  set
  {
   if ((this._MessageName != value))
   {
    this._MessageName = value;
   }
  }
 }
}

my code snippet to use the Stored proc was as follows:

 

using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    return messageDbDataContext.SELMessages();
}

the above code, did not give me the expected behavior. it failed to identify the 2 result sets.

as you can see, the return type of the method is just the result SELMessagesResult,which represents our first result set only.

 I tried to solve the issue and found this post.

it pretty much solved my problem. the only difference is that, instead of the existing tables being returned, my Stored Proc returned  result sets based on some temporary tables it was creating.

based on the post, these were the steps, I followed:

  • created the following class to represent the first result set. the designer had already created this class. (SELMessagesResult)
  • created the following class to represent the second result set. SELMessageRecipientsResult)
 public partial class SELMessageRecipientsResult
{
    private int _MessageID;
 
    private string _MessageRecipient;
 
    public SELMessageRecipientsResult()
    {
    }
 
    [Column(Storage = "_MessageID", DbType = "Int NOT NULL")]
    public int MessageID
    {
        get
        {
            return this._MessageID;
        }
        set
        {
            if ((this._MessageID != value))
            {
                this._MessageID = value;
            }
        }
    }
 
    [Column(Storage = "_MessageRecipient", DbType = "NVarChar(256) NOT NULL", CanBeNull = false)]
    public string MessageRecipient
    {
        get
        {
            return this._MessageRecipient;
        }
        set
        {
            if ((this._MessageRecipient != value))
            {
                this._MessageRecipient = value;
            }
        }
    }
}
  • created a partial class obviously named same as my Data Context class. (right click on .dbml >> view code)
 partial class MessageDBDataContext
 {
 }
  • added a new method to this class with following signature:

 

public IMultipleResults SELMessagesWithMultipleRS()
  • Attributed the method with the following code:
[ResultType(typeof(SELMessagesResult))]
[ResultType(typeof(SELMessageRecipientsResult))]
[Function(Name = "dbo.SELMessages")]
public IMultipleResults SELMessagesWithMultipleRS()
  • the full method looks as follows: 
partial class MessageDBDataContext
{
    [ResultType(typeof(SELMessagesResult))]
    [ResultType(typeof(SELMessageRecipientsResult))]
    [Function(Name = "dbo.SELMessages")]
    public IMultipleResults SELMessagesWithMultipleRS()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((IMultipleResults)(result.ReturnValue));
    }
}
  • As the post explains, used the new method as follows:
using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    // Get both the result set.
    var results = messageDbDataContext.SELMessagesWithMultipleRS();
 
    // Get the first result.
    List messages = results.GetResult().ToList();
 
    // Get the second result.
    List messageRecipients = results.GetResult().ToList();
 
    // Do the necessary processing with the 2 sequences.
 
}

spot on.. all was in place and I tested the code for expected behavior.

notes:

  • since the original data context designer class is auto-generated by the LINQ to SQL designer, it is good to create a partial class. that way, you need not create it every time you change the DBML.
  • you don’t need to attribute the class with the ‘DatabaseAttribute‘ Attribute. .NET is happy with a single annotation.
  • you don’t need an instance of the DataContext in this class. (since it is a partial class)

hopefully, this issue will be resolved in their next release of Linq to SQL designer.

there’s a solution to every problem; given enough time and money.. 

No responses yet