Get Multiple Table In View Using AJAX In ASP.NET MVC

Introduction

In this article, I will explain how you can display multiple tables in a view using Ajax with asp.net MVC. Here I will create Ajax calls for multiple tables. For this article, you should have a basic understating of following

Bootstrap 4
JQuery Data Table
Ajax
MVC
Entity Framework
View Model
SQL Server

Step 1

Open your favourite SQL server database -- any version. It doesn’t really matter. Create tables’ customer product, Order and OrderItem.

CREATE TABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](40) NOT NULL,
[LastName] [nvarchar](40) NOT NULL,
[City] [nvarchar](40) NULL,
[Country] [nvarchar](40) NULL,
[Phone] [nvarchar](20) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[SupplierId] [int] NOT NULL,
[UnitPrice] [decimal](12, 2) NULL DEFAULT ((0)),
[Package] [nvarchar](30) NULL,
[IsDiscontinued] [bit] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_PRODUCT_REFERENCE_SUPPLIER] FOREIGN KEY([SupplierId])
REFERENCES [dbo].[Supplier] ([Id])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_PRODUCT_REFERENCE_SUPPLIER]
GO
CREATE TABLE [dbo].[Order](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [nvarchar](50) NOT NULL CONSTRAINT [DF__Order__OrderDate__25869641] DEFAULT (getdate()),
[OrderNumber] [nvarchar](10) NULL,
[CustomerId] [int] NOT NULL,
[TotalAmount] [decimal](12, 2) NULL CONSTRAINT [DF__Order__TotalAmou__267ABA7A] DEFAULT ((0)),
CONSTRAINT [PK_ORDER] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_ORDER_REFERENCE_CUSTOMER] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_ORDER_REFERENCE_CUSTOMER]
GO
CREATE TABLE [dbo].[OrderItem](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrderId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[UnitPrice] [decimal](12, 2) NOT NULL DEFAULT ((0)),
[Quantity] [int] NOT NULL DEFAULT ((1)),
CONSTRAINT [PK_ORDERITEM] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItem] WITH CHECK ADD CONSTRAINT [FK_ORDERITE_REFERENCE_ORDER] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([Id])
GO
ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_ORDERITE_REFERENCE_ORDER]
GO
ALTER TABLE [dbo].[OrderItem] WITH CHECK ADD CONSTRAINT [FK_ORDERITE_REFERENCE_PRODUCT] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_ORDERITE_REFERENCE_PRODUCT]
GO

Step 2

Now open your favorite Visual Studio 2017 or any version you wish to.

Step 3

Create an empty project in Visual Studio and give an appropriate name. Checked MVC checkbox and click on OK.

Step 4

Right-click on the Models folder and add a database model. Add Entity Framework now. For that, right-click on Models folder, select Add, then select New Item.

You will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name MyModel (this name is not mandatory, you can give any name) and click "Add"

After you click on "Add a window", the wizard will open. Choose EF Designer from the database and click "Next".

After clicking on "Next", a window will appear. Choose New Connection. Another window will appear. Add your server name - if it is local, then enter a dot (.). Choose your database and click "OK".

The connection will be added. If you wish, save the connection name as you want. You can change the name of your connection below. It will save the connection in the web config. Now, click "Next".

After clicking on NEXT, another window will appear. Choose the database table name as shown in the below screenshot and click "Finish".

Entity Framework gets added and the respective class gets generated under the Models folder.

Step 5

Right-click on Controllers folder and add a controller.

A window will appear. Choose MVC5 Controller-Empty and click "Add".

After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home.

Controller Class Code

using DisplayMultipleTableAjax_Demo.Models;
using System.Linq;
using System.Web.Mvc;
namespace DisplayMultipleTableAjax_Demo.Controllers
{
public class HomeController : Controller
{
private readonly SampleDBContext db = new SampleDBContext();
public ActionResult Index()
{
return View();
}
public ActionResult DataTable()
{
return View();
}
public JsonResult GetCustomers()
{
db.Configuration.ProxyCreationEnabled = false;
var customers = db.Customers.ToList();
return Json(new { data = customers }, JsonRequestBehavior.AllowGet);
}
public JsonResult GetProducts()
{
db.Configuration.ProxyCreationEnabled = false;
var orders = db.Products.ToList();
return Json(new { data = orders }, JsonRequestBehavior.AllowGet);
}
public JsonResult GetOrders()
{
db.Configuration.ProxyCreationEnabled = false;
var orders = db.Orders.ToList();
return Json(new { data = orders }, JsonRequestBehavior.AllowGet);
}
public JsonResult GetOrderItem()
{
db.Configuration.ProxyCreationEnabled = false;
var orders = db.OrderItems.ToList();
return Json(new { data = orders }, JsonRequestBehavior.AllowGet);
}
}
}

Step 6

Right click on Index action method in controller class “Add View”

Index View Code

@{
ViewBag.Title = "Index";
}
<link href="@Url.Content("~/Content/DataTables/css/dataTables.bootstrap4.min.css")" rel="stylesheet" />
<ul class="nav nav-pills">
<li class="nav-item">
<a class="nav-link active" data-toggle="pill" href="#home">Customers</a>
</li>
<li class="nav-item">
<a class="nav-link" data-toggle="pill" href="#menu1">Products</a>
</li>
<li class="nav-item">
<a class="nav-link" data-toggle="pill" href="#menu2">Order</a>
</li>
<li class="nav-item">
<a class="nav-link" data-toggle="pill" href="#menu3">Order Items</a>
</li>
</ul>
<div class="tab-content">
<div class="tab-pane container active p-0" id="home" style="margin-top:15px;">
<table id="cutomerTable" class="table table-bordered">
<thead class="thead-dark text-white">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>Country</th>
<th>Phone</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<div class="tab-pane container p-0" id="menu1" style="margin-top:15px;">
<table id="productTable" class="table table-bordered" style="width:100%;">
<thead class="thead-dark text-white">
<tr>
<th>Product Name</th>
<th>Supplier Id</th>
<th>Unit Price</th>
<th>Package</th>
<th>Is Discontinued</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<div class="tab-pane container fade p-0" id="menu2" style="margin-top:15px;">
<table id="orderTable" class="table table-bordered" style="width:100%;">
<thead class="thead-dark text-white">
<tr>
<th>Order Date</th>
<th>Order Number</th>
<th>Customer Id</th>
<th>Total Amount</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<div class="tab-pane container fade p-0" id="menu3" style="margin-top:15px;">
<table id="orderItemTable" class="table table-bordered" style="width:100%;">
<thead class="thead-dark text-white">
<tr>
<th>Order Id</th>
<th>Product Id</th>
<th>Unit Price</th>
<th>Quantity</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<script src="@Url.Content("~/Scripts/jquery-3.4.1.min.js")"></script>
<script src="@Url.Content("~/Scripts/DataTables/jquery.dataTables.min.js")"></script>
<script src="@Url.Content("~/Scripts/DataTables/dataTables.bootstrap4.min.js")"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#cutomerTable").DataTable({
"ajax": {
"url": "@Url.Action("GetCustomers", "Home")",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "FirstName" },
{ "data": "LastName" },
{ "data": "City" },
{ "data": "Country" },
{ "data": "Phone" }
]
});
//Product Table
$("#productTable").DataTable({
"ajax": {
"url": "@Url.Action("GetProducts", "Home")",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "ProductName" },
{ "data": "SupplierId" },
{ "data": "UnitPrice" },
{ "data": "Package" },
{ "data": "IsDiscontinued" },
]
});
//Order Table
$("#orderTable").DataTable({
"ajax": {
"url": "@Url.Action("GetOrders", "Home")",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "OrderDate" },
{ "data": "OrderNumber" },
{ "data": "CustomerId" },
{ "data": "TotalAmount" }
]
});
//Order Item
$("#orderItemTable").DataTable({
"ajax": {
"url": "@Url.Action("GetOrderItem", "Home")",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "OrderId" },
{ "data": "ProductId" },
{ "data": "UnitPrice" },
{ "data": "Quantity" }
]
});
});
</script>

Step 7

Install the latest version of bootstrap and JQuery and JQuery data table from NuGet package manager under tools in Visual Studio.

Step 8

Build your project and Run by pressing Ctrl+F5