How to retrieve database data & show in a view using jquery in an ASP.Net MVC.

Introduction

In this post, I am explain How to retrieve database data & show in a view using jquery in an ASP.Net MVC.
Here is this post, I have used jquery for fetch data from database. I have used $.ajax method of Jquery for hit Action of a controller, which return json data.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Internet Application > Select view engine Razor > OK

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for fetch data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Create a Controller .

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller "UserController"

Step-6: Add new action into your controller for Get view

Here I have added "GetUser" Action into "User" Controller. Please write this following code

            namespace MVCFetchDataJquery.Controllers
            {
                public class UserController : Controller
                {
                    public ActionResult GetUser()
                    {
                        return View();
                    }
                }
            }

Step-7: Add another action into your controller for Get All data from Jquery

Here I have added "GetAllUser" Action into "User" Controller. Please write this following code

            public JsonResult GetAllUser()
            {
                List<UserMaster> allUser = new List<UserMaster>();


                // Here "MyDatabaseEntities " is dbContext, which is created at time of model creation.
            
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    allUser = dc.UserMasters.ToList();
                }

                return new JsonResult { Data=allUser, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }

Step-8: Add another action into your controller for Get filter data from Jquery

Here I have added "GetUserWithParameter" Action into "User" Controller. Please write this following code

            public JsonResult GetUserWithParameter(string prefix)
            {
                List<UserMaster> allUser = new List<UserMaster>();


                // Here "MyDatabaseEntities " is dbContext, which is created at time of model creation.

                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    allUser = dc.UserMasters.Where(a => a.Username.Contains(prefix)).ToList();
                }

                return new JsonResult { Data = allUser, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }

Step-9: Add view for your Action & design for fetch/show data in view (using jquery).

Right Click on Action Method (here right click on GetUser action) > Add View... > Enter View Name > Select View Engine (Razor) >Add.
HTML Code

@{
    ViewBag.Title = "Get User";
}

<h2>Get User</h2>
<table>
    <tr>
        <td>
            Search : <input type="text" id="txtSearch" /> <input type="button" value="Search" id="btnSearch" />
            <input type="button" value="Get All User" id="btnAllUser" />
        </td>
    </tr>
    <tr>
        <td>
            <div id="UpdatePanel">

            </div>
        </td>
    </tr>
</table>

Step-10: Write Jquery code for fetch and show Data in a view

Write this code below your view
JS Code

            @section Scripts{
            <script>
                $(document).ready(function () {

                    // This is for Get All Data
                    $("#btnAllUser").click(function () {

                        $.ajax({
                            url: "@Url.Action("GetAllUser","User")",
                            data: "",
                            type: "GET",
                            dataType: "json",
                            success: function (data) {
                                loadData(data);
                            },
                            error: function () {
                                alert("Failed! Please try again.");
                            }
                        });

                    });

                    // this will use for Get Data based on parameter
                    $("#btnSearch").click(function () {
                        $.ajax({
                            url: "@Url.Action("GetUserWithParameter","User")",
                            data: { prefix: $('#txtSearch').val() },
                            type: "GET",
                            dataType: "json",
                            success: function (data) {
                                loadData(data);
                            },
                            error: function () {
                                alert("Failed! Please try again.");
                            }
                        });
                    });

                    function loadData(data) {
                        // Here we will format & load/show data 
                        var tab = $('<table class="myTable"></table>');
                        var thead = $('<thead></thead>');
                        thead.append('<th>User ID</th>');
                        thead.append('<th>Username</th>');
                        thead.append('<th>Full Name</th>');
                        thead.append('<th>Email ID</th>');
                        thead.append('<th>Is Active</th>');

                        tab.append(thead);
                        $.each(data, function (i, val) {
                            // Append database data here
                            var trow = $('<tr></tr>');
                            trow.append('<td>' + val.UserID + '</td>');
                            trow.append('<td>' + val.Username + '</td>');
                            trow.append('<td>' + val.FullName + '</td>');
                            trow.append('<td>' + val.EmailID + '</td>');
                            trow.append('<td>' + val.IsActive + '</td>');
                            tab.append(trow);
                        });
                        $("tr:odd", tab).css('background-color', '#C4C4C4');
                        $("#UpdatePanel").html(tab);
                    };

                });
            </script>
        }

Here is the layout page

Layout Page

                <!DOCTYPE html>
                <html lang="en">
                    <head>
                        <meta charset="utf-8" />
                        <title>@ViewBag.Title - My ASP.NET MVC Application</title>
                        <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
                        <meta name="viewport" content="width=device-width" />
                        @Styles.Render("~/Content/css")
                        @Scripts.Render("~/bundles/modernizr")
                    </head>
                    <body>
                        <header>
                            <div class="content-wrapper">
                                <div class="float-left">
                                    <p class="site-title">@Html.ActionLink("your logo here", "Index", "Home")</p>
                                </div>
                                <div class="float-right">
                                    <section id="login">
                                        @Html.Partial("_LoginPartial")
                                    </section>
                                    <nav>
                                        <ul id="menu">
                                            <li>@Html.ActionLink("Home", "Index", "Home")</li>
                                            <li>@Html.ActionLink("About", "About", "Home")</li>
                                            <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
                                        </ul>
                                    </nav>
                                </div>
                            </div>
                        </header>
                        <div id="body">
                            @RenderSection("featured", required: false)
                            <section class="content-wrapper main-content clear-fix">
                                @RenderBody()
                            </section>
                        </div>
                        <footer>
                            <div class="content-wrapper">
                                <div class="float-left">
                                    <p>&copy; @DateTime.Now.Year - My ASP.NET MVC Application</p>
                                </div>
                            </div>
                        </footer>

                        @Scripts.Render("~/bundles/jquery")
                        @RenderSection("scripts", required: false)
                    </body>
                </html>

Step-11: Run Application.

Download Live Demo

Related Post :