While retrieving the results using stored procedure how can I retrieve and store multiple result set in view model in .net core
For e.g. from stored procedure I am returning records for below two queries
Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice
and below is view model for two tables
public class LMS_SurveyTraineeViewModel
{
public LMS_SurveyDetailsViewModel SurveyDetailsViewModel { get; set; }
public LMS_SurveyQuestionsViewModel SurveyQuestionsViewModel { get; set; }
public LMS_SurveyQuestionOptionChoiceViewModel SurveyQuestionOptionChoiceViewModel { get; set; }
}
This is how I am executing the stored procedure
public List<LMS_SurveyTraineeViewModel> GetTraineeSurvey(int surveyID)
{
try
{
List<LMS_SurveyTraineeViewModel> modelList = new List<LMS_SurveyTraineeViewModel>();
modelList = dbcontext.Set<LMS_SurveyTraineeViewModel>().FromSql("LMSSP_GetTraineeSurvey @surveyID = {0},@LanguageID = {1}", surveyID, AppTenant.SelectedLanguageID).ToList();
return modelList;
}
catch (Exception ex)
{
throw ex;
}
}
How can stored the multiple result set using stored procedure in view model ?
Currently, EF Core doesn't not support this. There is an open issue to address this.
https://github.com/aspnet/EntityFramework/issues/8127
Update 12th Sep 2018: This is still not a priority for EF Core even for release 3.0; so best use Dapper or plain ADO.NET when you have multiple results scenario
You can use this:
using (SqlCommand cmd = new SqlCommand("sp_db",
new SqlConnection("your_db_connection")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = param;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string a = (string)reader["ATTRIBUTE_A"];
string b = (string)reader["ATTRIBUTE_B"];
}
}
}
I didn't set output params for the attributes in my SP, but you can extract them by using SqlCommand.
Hope this helps.