Divine Fokwa

Divine Fokwa

  • 1.7k
  • 19
  • 1.3k

How to execute stored procedure in ASP.NET Core 3.1 and assign result

Aug 18 2023 1:36 PM

I am new to ASP.NET Core (3.1). I have a stored procedure that takes two parameters: startDate and endDate.

It returns the following result sets in one go (or execution):

I want to be able to grab the result sets in 3 separate variables because I will like to output them on my view:

For example - for the 1st result set:

foreach (var maindata in Model.MainDataViewModel)
{
     <td>maindata.CaseId</td>
     <td>maindata.EpisodeId</td>
     <td>maindata.SpecimenTag</td>
     <td>maindata.PathologyOrderId</td>
}

Likewise for the second and third.

I have attempted to call the stored procedure using this code:

public SpPotentialCandidatesForOncotypeDXViewModel GetPotentialCandidatesForTheOncotypeDXData(DateTime startDate, DateTime endDate)
{
    try
    {
        List<SqlParameter> pc = new List<SqlParameter>
                        {
                           new SqlParameter("@p0", startDate),
                           new SqlParameter("@p1", endDate),
                        };
        
        var da = _dbContext.Database.ExecuteSqlRaw("spPotentialCandidatesForOncotypeDX @p0, @p1", pc.ToArray());
    }
    catch (Exception ex)
    {
        AppLog.WriteError("GetPotentialCandidatesForTheOncotypeDXData", ex.Message);
    }

    // return spPotentialCandidatesForOncotypeDXes;
}

After testing, da has value -1. Via further research I realise ExecuteSqlRaw returns the number of rows affected and not the result sets.

How can I capture these result sets individually and assign to variables?


Answers (3)