Using stored procedure to get data from multiple columns and display it in a View

Question

I want to display data from two tables in a view with the help of a stored procedure. I'm getting stuck in retrieving records, can't write view model class in DbSet as its wrong, have posted my code.

And also I want to pass data from the controller to view in strongly typed.

Please refer snap short for further understanding.

The count of data is correct but there is no data in it.

Model

public class tblStudents
{   
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }
    public DateTime? CreatedDate { get; set; }
}

public class tblStudentDetails
{
    [Key]
    public int StudentId { get; set; }

    public string Address { get; set; }
    public DateTime? CreatedDate { get; set; }
}

//This class contains columns from both the tables
public class AllDataa
{
    public class Dataa
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
    }
}

Viewmodel:

 public class VMStudentDetails
 {
    public List<tblStudents> students { get; set; }

    public tblStudentDetails StudentDetails { get; set; }

    public IEnumerable<AllDataa> AllData { get; set; }
 }

Stored procedure:

CREATE PROCEDURE usp_StudentDetail
AS
BEGIN
    SELECT
        S.Name, SD.Address 
    FROM
        tblStudents S
    INNER JOIN 
        tblStudentDetails SD ON S.Id = Sd.StudentId
END

Controller

public ActionResult Index()
{
        var model = new MVCLearning.Models.VMStudentDetails();
        //What to write in Sqlquery? 
       //model.AllData = db.Database.SqlQuery<>("usp_StudentDetail").ToList();
      //This is not working the data is not coming in Alldata
       model.AllData = db.Database.SqlQuery<AllDataa>("usp_StudentDetail").ToList();
          return View(model);
}

Unless there is some other issue due to wrong parameter or name mismatch. db.Database.SqlQuery should work — David Chelliah, Jun 11 '16 at 18:49
Sir its a normal SQL select call,any parameter is not involved in it. — Dave, Jun 11 '16 at 18:53
I would recommend you checking the underlying SQL generated by entity framework — David Chelliah, Jun 11 '16 at 19:01
http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — David Chelliah, Jun 11 '16 at 19:03

score 0 · Answer 1 · answered Jun 11 '16 at 18:36

0

If you are using code first approach, then the recommended way would be to join both the tables through code like below

from student in tblStudents
Join details in tblStudentDetails
On student.id equals details.studentid 
Select new AllData() {Id =student.id, name = student. Name, Address =details.Address}

This is more similar to SP you have written

answered Jun 11 '16 at 18:36

David Chelliah

1,319
1
13
24

Yes its similar to it but the data base is ready,this is just a short description in real time my SP will be complicated,just want to know how to get the data in model. – Dave Jun 11 '16 at 18:37

score 0 · Answer 2 · answered Jun 11 '16 at 19:07

Thanks for your support I have fixed it the issue was in the AllDataa class there was another class in it which is why it was not working.

 public class AllDataa
 {
        public int Id { get; set; }

        public string Name { get; set; }

        public string Address { get; set; }

  }

Using stored procedure to get data from multiple columns and display it in a View

2 Answers2