Linq To Sql, stored procedure returns multiple temp tables

Question

How can I return multiple temp tables with in a stored procedure using LINQ to SQL? I have a stored procedure, when called returns three temporary tables. How can I access the data in the temporary tables in .NET C# code using LINQ to SQL?

I believe what you are looking for is here: http://stackoverflow.com/questions/371445/linq-to-sql-stored-procedures-with-multiple-results — CodeLikeBeaker, Feb 04 '15 at 21:39

score 0 · Accepted Answer · answered Feb 04 '15 at 23:16

I am able to access the data, below is the approach i followed

Stored Procedure:

CREATE PROCEDURE [dbo].[DemoSP] 
AS
BEGIN
    DECLARE @Temp1 TABLE   
    (   
        DocumentID NVARCHAR(256),
        DocumentName VARCHAR(100)
    )  
    DECLARE @Temp2 TABLE   
    (   
        DocumentID NVARCHAR(256)
    )  
    DECLARE @Temp3 TABLE   
    (   
        DocumentID NVARCHAR(256)
    )  

    INSERT INTO @Temp1 VALUES('123','Description')
    INSERT INTO @Temp1 VALUES('456','Content')
    INSERT INTO @Temp1 VALUES('789','Summary')

    INSERT INTO @Temp2 VALUES('XYZ')
    INSERT INTO @Temp2 VALUES('ABC')

    INSERT INTO @Temp3 VALUES('123')

    SELECT * from @Temp1 
    SELECT * from @Temp2 
    SELECT * from @Temp3 
END

Create a dbml file, drag and drop the stored procedure on to design surface. This step will generate below snippet of code

global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.DemoSP")]
        public ISingleResult<DemoSPResult> DemoSP()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return ((ISingleResult<DemoSPResult>)(result.ReturnValue));
        }

     public partial class DemoSPResult
     {
        private string _DocumentID;
        private string _DocumentName;
        public DemoSPResult()
        {
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentID", DbType = "NVarChar(256)")]
        public string DocumentID
        {
            get
            {
                return this._DocumentID;
            }
            set
            {
                if ((this._DocumentID != value))
                {
                    this._DocumentID = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentName", DbType = "VarChar(100)")]
        public string DocumentName
        {
            get
            {
                return this._DocumentName;
            }
            set
            {
                if ((this._DocumentName != value))
                {
                    this._DocumentName = value;
                }
            }
        }
    }

Comment the above properties, create classes with properties that matches with the schema of temp tables

     class Temp1
     {
        private string _DocumentID;
        private string _DocumentName;

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentID", DbType = "NVarChar(256)")]
        string DocumentID
        {
            get
            {
                return this._DocumentID;
            }
            set
            {
                if ((this._DocumentID != value))
                {
                    this._DocumentID = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentName", DbType = "VarChar(100)")]
        string DocumentName
        {
            get
            {
                return this._DocumentName;
            }
            set
            {
                if ((this._DocumentName != value))
                {
                    this._DocumentName = value;
                }
            }
        }
    }

    class Temp2
    {
        private string _DocumentID ;

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentID ", DbType = "NVARCHAR(256)")]
        string DocumentID 
        {
            get
            {
                return this._DocumentID ;
            }
            set
            {
                if ((this._DocumentID != value))
                {
                    this._DocumentID = value;
                }
            }
        }
    }

    class Temp3
    {
        private string _DocumentID;

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DocumentID", DbType = "NVarChar(256)")]
        string DocumentID
        {
            get
            {
                return this._DocumentID;
            }
            set
            {
                if ((this._DocumentID != value))
                {
                    this._DocumentID = value;
                }
            }
        }
    }

Create a method to handle the multiple result sets returned by the stored procedure as shown below

[Function(Name = "dbo.DemoSP")]
[ResultType(typeof(Temp1))]
[ResultType(typeof(Temp2))]
[ResultType(typeof(Temp3))]
public IMultipleResults GetDocumentDetails()
{
      IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
      return (IMultipleResults)result.ReturnValue;
}

We can access the data by calling the above method

 IMultipleResults d = db.GetDocumentDetails();

 IList<Temp1> temptable1= d.GetResult<Temp1>().ToList();
 IList<Temp2> temptable2= d.GetResult<Temp2>().ToList();
 IList<Temp3> temptable3= d.GetResult<Temp3>().ToList();

Linq To Sql, stored procedure returns multiple temp tables

1 Answers1