Show Database Values in Table Using MVC Razor

Introduction

This article explains how to retreive values from a database using a Stored Procedure and bind the data to a DataTable using a MVC Razor view.

Step 1

Create a table as in the following:



Step 2

Create a Stored Procedure as in the following:



Stored Procedure

create proc sp_s_Reg
as
begin
select UserID,Username,Password,FullName,EmailID from Registration1
end

Step 3

Create a project. Go to File, then New and click Project. Select ASP.NET MVC 4 Web Application and enter the project name, then click OK, select Empty, select View Engine Razor and press OK.

Step 4

Add the Entity model as in the following:











Step 5

Add a Controller as in the following:



Home controller.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcDatatable.Controllers {
    public class HomeController: Controller {
        //
        // GET: /Home/

        public ActionResult Index() {
            RBACEntities r = new RBACEntities();
            var data = r.sp_s_Reg().ToList();
            ViewBag.userdetails = data;
            return View();
        }

    }
}

Step 6

Add a View as in the following:



Index .cshtml

@{
ViewBag.Title = "Show Database value in DataTable";
}


<h2>Show Database value in DataTable</h2>
<div>
    <table id="t01">
        <thead>
            <th>UserID</th>
            <th>Username</th>
            <th>Password</th>
            <th>FullName</th>
            <th>EmailID</th>
        </thead>
@foreach (var item in ViewBag.userdetails)
{

        <tr>
            <td>
@item.UserID
</td>
            <td>
@item.Username
</td>
            <td>
@item.Password
</td>
            <td>
@item.FullName
</td>
            <td>
@item.EmailID
</td>
        </tr>

}

    </table>
</div>
<style>
table#t01 {
width: 100%;
background-color: #f1f1c1;
}

table#t01 tr:nth-child(even) {
background-color: #eee;
}
table#t01 tr:nth-child(odd) {
background-color: #fff;
}
table#t01 th {
color: white;
background-color:blue;
}
table, th, td {
border: 1px solid black;
}
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 15px;
}
</style>

Step 7

Run the project.