0

I have a user-defined stored procedure which returns multiple actual tables as result set:

CREATE PROCEDURE uspDemo
    (@UserID BIGINT = 0,
     @IsAdmin BIT = 0,
     @Title VARCHAR(120) = '')
AS
BEGIN 
    ------Retrieve Posts------
    SELECT * 
    FROM tblPost AS MP 
    INNER JOIN tblUserProfile AS UP ON UP.ID = MP.UserID
    WHERE UP.ID = @UserID 
      AND ((@IsAdmin = 0 AND MP.IsDeleted = 0 AND MP.IsApproved = 1)
           OR (@IsAdmin = 1 OR MP.IsDeleted = 0 OR MP.IsApproved = 1))

    ----- Retrieve Tags------
    SELECT *  
    FROM tblTagMasters AS MT 
    INNER JOIN tblPostTags AS MP ON MT.TagID = MP.TagID
 
    --------Retrieve User likes-----
    SELECT * 
    FROM tblUserLikes AS UV 
    INNER JOIN tblPost AS MP ON MP.PostId = UV.PostId
END

How to translate in ASP.NET MVC? Please help me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MANISH SHARMA
  • 33
  • 1
  • 8
  • I'm assuming that, you want to retrieve above 3 result sets using ado.net and store those values in different different models. Please refer this link to have an understanding. [Link] https://khalidabuhakmeh.com/multiple-result-sets-with-net-core-sql-server. And you have to enable multiple result sets in the connection string as well. Refer link https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets – Avantha Siriwardana Dec 18 '21 at 14:42
  • 2
    MultipleActiveResultSets is unrelated to this. It enables you to have two _seperate_ SqlDataReaders from two _seperate_ SqlCommands active at once. Here's there's a single SqlCommand, and a single SqlDataReader. – David Browne - Microsoft Dec 18 '21 at 16:13

1 Answers1

2

Basically, you need something like this:

// define connection string and command for your query
string connectionString = ".....";    // typically load from config
string storedProcedureName = "dbo.uspDemo";

// put your disposable ADO.NET objects into proper "using ()" blocks    
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(storedProcedureName, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    
    // define the parameters
    cmd.Parameters.Add("@UserID", SqlDbType.BigInt).Value = .....;
    cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = .....;
    cmd.Parameters.Add("@Title", SqlDbType.VarChar, 120).Value = ".....";

    // open connection
    conn.Open();

    // execute stored procedure to get the data reader
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // read the first set of data returned
        while (reader.Read())
        {
            // read the values, construct an object from it, store the object into a list or something
        }
        
        // get the next set of result data
        reader.NextResult();
        
        // read the second set of data returned
        while (reader.Read())
        {
            // read the values, construct an object from it, store the object into a list or something
        }
        
        // get the next set of result data
        reader.NextResult();
        
        // read the third set of data returned
        while (reader.Read())
        {
            // read the values, construct an object from it, store the object into a list or something
        }
    }

    conn.Close();
}

I leave the remaining details up to you - do some research!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459