How to display database records in asp.net mvc view

Question

Using ASP.NET MVC with C#, how do you pass some database records to a View and display them in table form?

I need to know how I can transfer/pass some rows of records from a database that have been returned to an SqlDataReader object and pass that object to the View so I can display all the records contained by the object in the View using foreach.

The following code is what I'm I'm trying to do. But its not working.

The Controller:

public ActionResult Students()
{
    String connectionString = "<THE CONNECTION STRING HERE>";
    String sql = "SELECT * FROM students";
    SqlCommand cmd = new SqlCommand(sql, connectionString);

    using(SqlConnection connectionString = new SqlConnection(connectionString))
    {
        connectionString.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
    }

    ViewData.Add("students", rdr);

    return View();
}

The View:

<h1>Student</h1>

<table>
    <!-- How do I display the records here? -->
</table>

It won't work - the design is bad - you cannot fetch data from SqlDataReader after connection disposal (using clausule). Have you read some MVC / Razor tutorials? — user2160375
– user2160375, Commented Aug 13, 2014 at 6:38
Yes, I know its bad structure. Thats why I need to know from you experienced guys to share what you know is the best way. :) I just started with ASP.NET MVC with C# — doncadavona
– doncadavona, Commented Aug 19, 2014 at 8:52
Thank you Giannis! from (stackoverflow.com/users/2401981/giannis-paraskevopoulos) Your answer works and I believe its the best way to solve my case. — doncadavona
– doncadavona, Commented Aug 19, 2014 at 8:55
I also found this link from DotFunda.com which solves my case and is same solution as Gianinis said: dotfunda.com/articles/show/2550/… — doncadavona
– doncadavona, Commented Aug 19, 2014 at 8:56

Giannis Paraskevopoulos · Accepted Answer · 2016-11-21 08:36:35Z

61

1. First create a Model that will hold the values of the record. for instance:

public class Student
{
    public string FirstName {get;set;}
    public string LastName {get;set;}
    public string Class {get;set;}
    ....
}

2. Then load the rows from your reader to a list or something:

public ActionResult Students()
{
    String connectionString = "<THE CONNECTION STRING HERE>";
    String sql = "SELECT * FROM students";
    SqlCommand cmd = new SqlCommand(sql, conn);

    var model = new List<Student>();
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read())
        {
            var student = new Student();
            student.FirstName = rdr["FirstName"];
            student.LastName = rdr["LastName"];
            student.Class = rdr["Class"];
            ....

            model.Add(student);
        }

    }

    return View(model);
}

3. Lastly in your View, declare the kind of your model:

@model List<Student>

<h1>Student</h1>

<table>
    <tr>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Class</th>
    </tr>
    @foreach(var student in Model)
    {
    <tr>
        <td>@student.FirstName</td>  
        <td>@student.LastName</td>  
        <td>@student.Class</td>  
    </tr>
    }
</table>

edited Nov 21, 2016 at 8:36

answered Aug 13, 2014 at 6:47

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

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

3 Comments

doncadavona Over a year ago

Thank you ver much this really helped. I even learned how to use Models with this!

Giannis Paraskevopoulos Over a year ago

I am very glad this was a help to you. You may as well accept it as an answer for future readers, and if you wish also upvote.

doncadavona Over a year ago

O yes sure. I still can't up vote now. But I'll do when I have enough reputation here at SOF. Thanks!

niko619 · Accepted Answer · 2014-10-15 01:28:39Z

5

If you dont have to use an sql reader would it not be easier to have the Controller like this.

Controller.cs

private ConnectContext db = new ConnectContext();

public ActionResult Index()
   {
     return View(db.Tv.ToList());
   }

ConnectContext.cs

public class ConnectContext : DbContext
{
    public DbSet<Student> Student{ get; set; }
}

This way your connection string will be in your web.config and the View + Model will remain the same.

answered Oct 15, 2014 at 1:28

niko619

4531 gold badge9 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to display database records in asp.net mvc view

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related