How to get two tables returned by stored procedure

Question

My method:

[Function(Name = "get_values")]
[ResultType(typeof(Values_Result))]
public IMultipleResults getvaluesresult([Parameter(DbType = "Int")] System.Nullable<int> Id1, [Parameter(DbType = "VarChar(100)")] string Id2)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), Id1, Id2);
    return ((IMultipleResults)(result.ReturnValue));
}

Method call:

var resultValues = cont1.getvaluesresult(Convert.ToInt32(clsSession.id1), "101");
var resultValuesList = resultValues.GetResult<Values_Result>().ToList<Values_Result>();

if (resultValuesList != null && resultValuesList.Count > 0)
{
    var dataTable = resultValuesList.ToDataTable();
    // performing some tasks
}

My stored procedure looks like this:

CREATE  PROCEDURE get_values
    (@Id2 VARCHAR(50),  
     @Id1 INT)
AS                  
BEGIN 
    DECLARE @temp TABLE (id INT)

    INSERT INTO @temp 
        (SELECT id FROM table1)

    SELECT * FROM table2
    SELECT * FROM @temp
END

I want to know how to get the two returned tables from the stored procedure.

I don't want to join the tables in the stored procedure as one

I would go for a ViewModel and return 2 tables as 1 – Arijit Mukherjee Dec 20 '17 at 05:54 — Arijit Mukherjee, Dec 20 '17 at 05:54

score 3 · Answer 1 · answered Dec 20 '17 at 05:53

Using ADO.NET:

SqlConnection con = new SqlConnection("YourConnection String");
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("get_values", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id2", id); // if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

// ds.Tables[0] your first select statement
// ds.Tables[1] your second select statement

Using Entity Framework:

// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand(); // db is your data context
cmd.CommandText = "[dbo].[get_values]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id2", id); // if you have parameters.

db.Database.Connection.Open();
var reader = cmd.ExecuteReader();

// Read Table2 from the first result set
var table2 = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Table2Model>(reader, "table2", MergeOption.AppendOnly);   

// Move to second result set and read @temp
reader.NextResult();
var temp = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<TempModel>(reader, "temp", MergeOption.AppendOnly); 
// I forgot if you need to use @temp or temp only

Serkan Arslan · Answer 2 · 2017-12-20T06:48:55.217

You need to call GetResult function again for the second table.

If you have multiple table result as a result of the stored procedure, you should call GetResult function again for retrieving the next table result.

        var resultValues = cont1.getvaluesresult(Convert.ToInt32(clsSession.id1), "101");

        // For table2
        var resultValuesList1 = resultValues.GetResult<Values_Result>().ToList<Values_Result>();

        if (resultValuesList1 != null && resultValuesList1.Count > 0)
        {
            var dataTable = resultValuesList1.ToDataTable();
            // performing some tasks
        }

        // For temp table
        var resultValuesList2 = resultValues.GetResult<Values_Result>().ToList<Values_Result>();

        if (resultValuesList2 != null && resultValuesList2.Count > 0)
        {
            var dataTable = resultValuesList2.ToDataTable();
            // performing some tasks
        }

score 0 · Answer 3 · answered Dec 20 '17 at 05:16

0

var resultValues = cont1.getvaluesresult(Convert.ToInt32(clsSession.id1), 
"101");
var resultValuesList = resultValues.GetResult<Values_Result>
().ToList<Values_Result>();
if (resultValuesList != null && resultValuesList.Count > 0)
 {
DataSet ds = resultValuesList.ToDataTable();
 // performing some tasks
 }

this data set can store multiple table and you can work with it

answered Dec 20 '17 at 05:16

vishal Jaiswal

1
2

The idea is good. but the 'resultValuesList ' has the first table values only – user7415073 Dec 20 '17 at 05:23

How to get two tables returned by stored procedure

3 Answers3