display more than one database query in .net MVC

Question

I am creating my first project using asp.net MVC - I have successfully connected to the database and displayed the information on the index page. My question is how do I get more than one query result on the one index page
for e.g

SELECT student ID,first name,surname FROM STUDENT Notes WHERE student ID = 7

Do I need to create new controllers/models for each query or need to add to the current and if I add to the current how would I do it? Below is the code I currently have in my controller.

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        //int sNumber = 1;
        List<CustomerModel> customers = new List<CustomerModel>();
        string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (MySqlConnection con = new MySqlConnection(constr))
        {

            string query = "SELECT title, `first name`, surname FROM `STUDENT REGISTER`";
            using (MySqlCommand cmd = new MySqlCommand(query))
            {
                cmd.Connection = con;
                con.Open();
                using (MySqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        customers.Add(new CustomerModel
                        {
                            // CustomerId = Convert.ToInt32(sdr["Student Number"]),
                            Title = sdr["title"].ToString(),
                            Name = sdr["first name"].ToString(),
                            Surname = sdr["surname"].ToString()
                        });
                    }
                }                

                con.Close();
            }
        }

        return View(customers);
    }

Create a class and put all your customers and other items in it. Then pass that to your view. — CodingYoshi
– CodingYoshi, Commented Mar 3, 2018 at 3:37

user9405863 · Accepted Answer · 2018-03-03 03:29:19Z

1

Create a view model with two result set for e.g. Student and Marks

public class Result
{

  public Student Student { get; set; }

  public Marks Marks { get; set; }

}

Load/Construct this Result view model in controller /service with appropriate data and pass this view model to view. I hope this helps!

answered Mar 3, 2018 at 3:29

user9405863

1,5141 gold badge13 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Over a year ago

Do I have to separate controllers or do I put it in the same one - how do I run two queries on the same controller?

user9405863 Over a year ago

you can put in the same controller and run the two queries and populate view model with data before pushing to view.

Ashiquzzaman · Accepted Answer · 2018-03-03 09:55:20Z

0

You have to create a new class with all the properties you want to display in your View.

Example:

public class StudentModel {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Name { get; set; }
        public strign Surname { get; set; }
    }
    public class MarkModel {
        public int Id { get; set; }
        public int StudentId { get; set; }
        public int SubjectId { get; set; }
        public int Mark { get; set; }
    }
    public class ResultModel
    {

      public StudentModel Student { get; set; }

      public List<MarkModel> Marks { get; set; }

    }

    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            //int sNumber = 1;
           var model= new ResultModel{
              Student = new StudentModel(),
              Marks = new List<MarkModel>();
           }

            string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                string queryStudent = "SELECT id, title, `first name`, surname FROM `STUDENT` WHERE Id=1";
                using (MySqlCommand cmd = new MySqlCommand(queryStudent))
                {
                    cmd.Connection = con;               
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {    
                               model.student.Id = Convert.ToInt32(sdr["id"]),
                               model.student.Title = sdr["title"].ToString(),
                               model.student.Name = sdr["first name"].ToString(),
                               model.student.Surname = sdr["surname"].ToString()                     
                        }
                    }               
                }

                string queryMarks = "SELECT Id, `StudentId`, StudentId,Mark FROM `MARK` WHERE StudentId=1";
                using (MySqlCommand cmd = new MySqlCommand(queryMarks))
                {
                    cmd.Connection = con;               
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            model.Marks.Add(new MarkModel
                            {
                               Id = Convert.ToInt32(sdr["Id"]),
                               StudentId = Convert.ToInt32(sdr["StudentId"]),
                               StudentId = Convert.ToInt32(sdr["StudentId"]),
                               Mark = Convert.ToInt32(sdr["Mark"]),
                            });
                        }
                    }               
                } 
            }

            return View(model);
        }

answered Mar 3, 2018 at 9:55

Ashiquzzaman

5,3144 gold badges30 silver badges40 bronze badges

10 Comments

Michael Over a year ago

on the view do I change it to @model IEnumerable<ResultModel> also what should my for look like

@foreach (StudentModel Student in Model)                               {                                     <option>@Student.Name</option>

Ashiquzzaman Over a year ago

@Michael. Student is a single item of Model. so you can get it by @Model.Student.Name.And Marks is a list item in Model. so you can get by @foreach (var item in Model.Marks) {<option>@item.Mark</option> }. Also your view model is now @model ResultModel not IEnumerable type.

Ashiquzzaman Over a year ago

if you need List of student then change public StudentModel Student { get; set; } to public Lis<StudentModel > Students { get; set; }. and change query execution as you done before and your view do as like as Marks.

Michael Over a year ago

I have made those change but I get the following error when trying to run the program with the loop Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'WebApplication3.Models.ResultModel' to 'System.Collections.IEnumerable'. An explicit conversion exists (are you missing a cast?)'

Michael Over a year ago

this is what I am using at the top of the view @model dynamic @using WebApplication3.Models

|

Tiramonium · Accepted Answer · 2018-03-03 03:38:22Z

0

You should create a new ViewModel class with all the properties you want to display in your View. Then you model your View after it.

From the properties you provided so far, the class should look like this:

public class StudentViewModel {
    public int Id { get; set; }
    public string Title { get; set; }
    public string Name { get; set; }
    public strign Surname { get; set; }
}

Then you do the value x property assignment

string query = "SELECT title, `first name`, surname FROM `STUDENT REGISTER`";
List<StudentViewModel> model = new List<StudentViewModel>();

using (MySqlCommand cmd = new MySqlCommand(query)) {
    cmd.Connection = con;
    con.Open();
    using (MySqlDataReader sdr = cmd.ExecuteReader())
    {
        while (sdr.Read())
        {
            model.Add(new StudentViewModel
            {
                Id = Convert.ToInt32(sdr["StudentNumber"]),
                Title = Convert.ToString(sdr["title"]),
                Name = Convert.ToString(sdr["first name"]),
                Surname = Convert.ToString(sdr["surname"])
            });
        }
    }

    con.Close();
}

return View(model);

answered Mar 3, 2018 at 3:38

Tiramonium

92711 silver badges17 bronze badges

4 Comments

Michael Over a year ago

how do I then run another query to get information from a separate table but display the information on the same page?

Tiramonium Over a year ago

@Michael Are they linked by some column or are they completely separate?

Michael Over a year ago

completely separate but I will also have some that are related in this project

Tiramonium Over a year ago

@Michael if they are completely separate, you define the ViewModel class like Hazarath Chillara suggested, run both queries and do the assignment in each query reading loop. I'll update my answer.

Collectives™ on Stack Overflow

display more than one database query in .net MVC

3 Answers 3

2 Comments

10 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

10 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related