Read select from stored procedures using LINQ

Question

In this way, I retrieve data using the stored procedure:

var returnParameter = new SqlParameter("@RV", SqlDbType.Int) { Direction = ParameterDirection.Output };
var xmlParameter = new SqlParameter("@XML", XML) { DbType = DbType.Xml };
var familySentParameter = new SqlParameter("@FamilySent", SqlDbType.Int) { Direction = ParameterDirection.Output };

//var resultParameter = new SqlParameter("Result", SqlDbType.Text) { Direction = ParameterDirection.ReturnValue };

var sql = "exec @RV = uspConsumeSomething @XML, @FamilySent OUT";

var result = myContext.Database.ExecuteSqlCommand(sql, returnParameter, xmlParameter, familySentParameter);

RV = (int)returnParameter.Value;

Below the fragment of the stored procedure (termination of the procedure):

BEGIN CATCH
    SELECT ERROR_MESSAGE();
    IF @@TRANCOUNT > 0 ROLLBACK TRAN ENROLL;

    SELECT * FROM @tblResult;
    RETURN -400
END CATCH

SELECT Result FROM @tblResult;

RETURN 0 --ALL OK
END

How to obtain in C# data obtained from:

SELECT * FROM @tblResult;
SELECT Result FROM @tblResult;

They are not needed for the operation of the procedure but contain information about the type of error that arises in the stored procedure.

The use of Return in a stored procedure is supported by @RV

EDIT:

I'm trying a different approach but I still do not know how to get the values from SELECT here

DbConnection connection = myContext.Database.GetDbConnection();

using (DbCommand cmd = connection.CreateCommand())
{

    cmd.CommandText = sql;

    cmd.Parameters.AddRange(new[] { xmlParameter, returnParameter, familySentParameter });

    if (connection.State.Equals(ConnectionState.Closed)) connection.Open();

    using (var reader = cmd.ExecuteReader())
    {
        var entities = new List<string>();
        while (reader.Read())
        {
            Debug.WriteLine("Reader: " + reader.GetValue(0));
        }
    }
}

As a result, there are 3 SELECT. How do you get access to 2 and 3?

It's a **stored** procedure - not a "storage" procedure ..... — marc_s, Aug 01 '19 at 11:33
https://stackoverflow.com/questions/21918368/database-sqlquery-calling-stored-procedure-that-has-multiple-output-parameters — TheMikeInNYC, Aug 01 '19 at 14:26
Well, [`ExecuteSqlCommand`](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.executesqlcommand?view=entity-framework-6.2.0) doesn't return a resultset. So you will need another way, like just using ADO.Net — stuartd, Aug 01 '19 at 17:18
See [How to read multiple resultsets](https://stackoverflow.com/questions/3840484/how-to-handle-multiple-resultsets-each-with-multiple-rows-idatareader-nextresu) — stuartd, Aug 02 '19 at 10:08

Read select from stored procedures using LINQ

0 Answers0