Jacksonville Developers User Group

I have been goofing with LINQ for awhile but have gotten the opportunity very recently to really start to get my feet wet with it.  As I was demonstrating some of it to another developer yesterday, he asked about stored procedures and if LINQ to SQL supported stored procedures.  I said sure and showed him some examples of how I was using sprocs to get data.  And then he asked if it supported stored procedures that returned multiple result sets.  I told him I didn't know, but thought it had to.

When I first started working with it, I noticed that every stored procedure returned an object of ISingleResult<T>.  And I wasn't using any sprocs that returned multiple sets.  I also noticed that there was an IMultipleResults interface.  So today I thought I would see what there was to see on it.

I started by creating a simple test sproc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE multipleResultSets
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ResultOne TABLE
	(
		  FieldOne int
		, FieldTwo varchar(50)
	)

	DECLARE @ResultTwo TABLE
	(
		  Id int
		, Color varchar(50)
	)

	INSERT INTO @ResultOne 
	SELECT 1,'Hello'
	UNION
	SELECT 2,'Blue'
	UNION
	SELECT 3,'World'

	INSERT INTO @ResultTwo
	SELECT 1,'Green'
	UNION
	SELECT 2,'Red'
	UNION
	SELECT 3,'Blue'

	SELECT * FROM @ResultOne

	SELECT * FROM @ResultTwo
	
END
GO

With that in place, I moved on to creating a .dbml file in a Visual Studio solution and dragging in the sproc.  No matter what I did, it would always create a method in the designer using the ISingleResult interface.  As an aside, if there is anyone out there that knows how to change that via the designer, please let me know.

Undaunted, I did some research in MSDN and the great book, LINQ in Action, studying the IMultipleResult interface and how to get it to work for me.  The gist is that you write a partial class to extend your datacontext, write a method that returns IMultipleResults and decorate it with the ResultType attributes of the types the method supports.  So that gets you this:

public partial class TestDBDataContext
    {
        [Function(Name = "dbo.multipleResultSets")]
        [ResultType(typeof(FirstResult))]
        [ResultType(typeof(SecondResult))]
        public IMultipleResults GetMultipleResultSets()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return (IMultipleResults)result.ReturnValue;
        }
    }

The only other thing I needed was the types that I just referred to in the above method

    public partial class SecondResult
    {
        private int mId;
        public int Id 
        {
            get
            {
                return mId;
            }
            set
            {
                if (mId != value)
                {
                    mId = value;
                }
            }
        }

        private string mColor;
        public string Color 
        {
            get
            {
                return mColor;
            }
            set
            {
                if (mColor != value)
                {
                    mColor = value;
                }
            }
        }
    }

    public partial class FirstResult
    {
        private int mFieldOne;
        public int FieldOne
        {
            get
            {
                return mFieldOne;
            }
            set
            {
                if (mFieldOne != value)
                {
                    mFieldOne = value;
                }
            }
        }

        private string mFieldTwo;
        public string FieldTwo
        {
            get
            {
                return mFieldTwo;
            }
            set
            {
                if (mFieldTwo != value)
                {
                    mFieldTwo = value;
                }
            }
        }
    }

And finally, a test for it all

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;

namespace LINQTest
{
    class Program
    {
        static void Main(string[] args)
        {
            TestDBDataContext context = new TestDBDataContext(@"Data Source=LEVIATHAN\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True");
            System.Data.Linq.IMultipleResults results = context.GetMultipleResultSets();

            List<FirstResult> ones = results.GetResult<FirstResult>().ToList<FirstResult>();
            List<SecondResult> twos = results.GetResult<SecondResult>().ToList<SecondResult>();

            Console.WriteLine("\n\nIn One:");
            ones.ForEach(delegate(FirstResult one){Console.WriteLine(one.FieldTwo);});

            Console.WriteLine("\n\nIn Two:");
            twos.ForEach(delegate(SecondResult two){Console.WriteLine(two.Color);});
            
            var both = (from o in ones
                        join t in twos on o.FieldTwo equals t.Color
                        select o).ToList<FirstResult>();

            Console.WriteLine("\n\nIn both:");
            both.ForEach(delegate(FirstResult o){Console.WriteLine(o.FieldTwo);});

            Console.ReadLine();
        }
    }
}

Viola!