I am new to ASP.NET Core (3.1). I have a stored procedure that takes two parameters: startDate and endDate.
startDate
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.
da
ExecuteSqlRaw
How can I capture these result sets individually and assign to variables?