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:

query

Step 2

Create a Stored Procedure as in the following:

result

Stored Procedure

  1. create proc sp_s_Reg
  2. as
  3. begin
  4. select UserID,Username,Password,FullName,EmailID from Registration1
  5. 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:

add new item

ado dot net

select data connection

table

navigation properties

Step 5

Add a Controller as in the following:

add controller

Home controller.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Mvc;
  7. namespace MvcDatatable.Controllers {
  8. public class HomeController: Controller {
  9. //
  10. // GET: /Home/
  12. public ActionResult Index() {
  13. RBACEntities r = new RBACEntities();
  14. var data = r.sp_s_Reg().ToList();
  15. ViewBag.userdetails = data;
  16. return View();
  17. }
  19. }
  20. }

Step 6

Add a View as in the following:

add view

Index .cshtml

  1. @{
  2. ViewBag.Title = "Show Database value in DataTable";
  3. }
  6. <h2>Show Database value in DataTable</h2>
  7. <div>
  8. <table id="t01">
  9. <thead>
  10. <th>UserID</th>
  11. <th>Username</th>
  12. <th>Password</th>
  13. <th>FullName</th>
  14. <th>EmailID</th>
  15. </thead>
  16. @foreach (var item in ViewBag.userdetails)
  17. {
  19. <tr>
  20. <td>
  21. @item.UserID
  22. </td>
  23. <td>
  24. @item.Username
  25. </td>
  26. <td>
  27. @item.Password
  28. </td>
  29. <td>
  30. @item.FullName
  31. </td>
  32. <td>
  33. @item.EmailID
  34. </td>
  35. </tr>
  37. }
  39. </table>
  40. </div>
  41. <style>
  42. table#t01 {
  43. width: 100%;
  44. background-color: #f1f1c1;
  45. }
  47. table#t01 tr:nth-child(even) {
  48. background-color: #eee;
  49. }
  50. table#t01 tr:nth-child(odd) {
  51. background-color: #fff;
  52. }
  53. table#t01 th {
  54. color: white;
  55. background-color:blue;
  56. }
  57. table, th, td {
  58. border: 1px solid black;
  59. }
  60. table, th, td {
  61. border: 1px solid black;
  62. border-collapse: collapse;
  63. }
  64. table, th, td {
  65. border: 1px solid black;
  66. border-collapse: collapse;
  67. }
  68. th, td {
  69. padding: 15px;
  70. }
  71. </style>

Step 7

Run the project.

view in browser

show output