How to display data in a table from a SQL database in MVC

Home > Search > How-to
  by

Let's say we want to display data in a table like this:

id username
1 Jeremy
2 Natalie
3 Winston

 

One way to accomplish this is just to build an HTML table:

<table>
  <tr>    
    <td>ID: </td>    
    <td>Username</td>  
  </tr>  

  <tr>    
    <td> 1 </td>    
    <td> Jeremy </td>  
  </tr>  

  <tr>    
    <td> 2 </td>    
    <td> Natalie </td>  
  </tr>  

  <tr>    
    <td> 3 </td>   
    <td> Winston </td>  
  </tr>
</table>

 

A better way to accomplish this is to get the data from a SQL database. Explaining the many nuiances of SQL are outside the scope of this tutorial. This tutorial assumes some familiarity with SQL.

 


Install EntityFramework

  1. In Visual Studio, select Tools > NuGet Package Manager > Manage NuGet Packages for Solution
  2. If EntityFramework is not installed, Install It

Connection String

  1. In Solution Explorer, select Web.config
  2. Add the following inside the <connectionStrings> tags:
<add name="SQLdatabasename" 
connectionString="Data Source=domain\server; database=databasename; User ID=username; password=password" 
providerName="System.Data.SqlClient" 
/>

Here is an example of a real connection string:

<add name="SQL_freekb_db" 
connectionString="Data Source=freekb\Eng1; database=freekb; User ID=bthomas; password=bn56Hjn4!s" 
providerName="System.Data.SqlClient" 
/>

To verify the connection is successful:

  1. Press Ctrl + Alt + S (or near the top of Visual Studio, select Server Explorer) to use Server Explorer
  2. In Server Explorer, there should be a listing for your newly added connection string. Right-click on the new listing and select Modify Connection
  3. Select Test Connection to ensure the connection is successful

Next we will create the Model View Controller (MVC).

Model

  1. In Solution Explorer, right-click on the Models folder and select Add > Class
  2. In the Add New Item pop-up box, select Class and give the file a name, such as example_database.cs

Note: This class can contains a single database. However, the database may have numerous tables. It is good to name the class the same as the database name.

  1. Do the following to the example_database.cs file:
    1. The first line only needs using System.Data.Entity;
    2. Create a pulblic class for the table(s) and columns you want to interact with. In this example, two tables are created (table1, table2), and each table has its own unique columns.
    3. Create a public class to establish the connection to the database (db1_connect) in this example. The database name here needs to match the database name in the Connection String in the Web.Config file.
    4. protected override prevents MVC from making table names plural. For example, a table named "Content" may become "Contents" without the protected override.
    5. Create a public DbSet, where the text inside of the brackets are the table names (table1, table2 in this example)
    6. Whatever text appears after "DbSet<example>" is what will be used for the page name.
  2. Before creating the View and Controller, press Ctrl + Shift + B to build the application.
using System.Data.Entity;

namespace example.Models
{    
    public class table1    
    {
        public int id { get; set; }
        public string column1{ get; set; }
    }
    public class table2
    {
        public int id { get; set; }
        public string column1 { get; set; }
    }

    public class db1_connect : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
          base.OnModelCreating(modelBuilder);
          modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
          modelBuilder.Entity<table1>().ToTable("table1");
          modelBuilder.Entity<table2>().ToTable("table2");
        }
        public DbSet<table1> App { get; set; }
        public DbSet<table2> Account { get; set; }
    }
}

 


Controller

  1. In Solution Explorer, right-click on the Controllers folder and select Add > Controller
  2. Select MVC 5 Controller with views, using Entity Framework and select Add
  3. In the Add Controller pop-up box, select the appropriate Model class and Data context class (these come from your Model file). You also will give the controller a name. The controller name does not need to be the same as the table name. The controller name determines the sub page. For example, AppController will create www.example.com/App
  4. Select Add

Both the Controller and View are automatically built, and the application can be published. Because we named the model "Users" in this example, we can view the records in the SQL table by going to http://www.example.com/Users.

Following is a section of the AddController.cs file.

using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using example.com.Models;

namespace example.com.Controllers
{
    public class AppController : Controller
    {
        private example_db db = new example_db();

        // GET: App
        public ActionResult Index()
        {
            return View(db.Example.ToList());
        }
. . .

 


View

Following is the minimum markup needed in the View.

  • Line 1 contains IEnumerable, because IEnumerable must be used in order to use a foreach loop. <example.com.Models.Example> is the combination of the namespace in the Model (example.com.Models) and the public class being used (Example).
  • Lines 3 through 6 use a foreach loop to display every result in the SQL table from column1.
@model IEnumerable<example.com.Models.Example>

@foreach (var item in Model)
{
    @Html.DisplayFor(modelItem => item.column1)
}

 

This minimum markup would produce the following:



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments