Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to display database records in asp.net mvc view

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>
like image 821
doncadavona Avatar asked Aug 13 '14 06:08

doncadavona


People also ask

How show data from database in asp net MVC?

Right-click the Models folder in the Solution Explorer window and the select the menu option Add, New Item. Select the Data category and select the ADO.NET Entity Data Model template. Give your data model the name MoviesDBModel. edmx and click the Add button.

How fetch data from database and display in table in ASP NET MVC?

Select Web from the left panel, choose ASP.NET Web Application, give a meaningful name of your project, then click on OK. After clicking on OK, one more window will appear. Choose Empty check on MVC and Web API checkbox and click on OK, as shown in the below screenshot.


2 Answers

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>
like image 189
Giannis Paraskevopoulos Avatar answered Oct 06 '22 01:10

Giannis Paraskevopoulos


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.

like image 36
niko619 Avatar answered Oct 06 '22 01:10

niko619