GridView with Server Side Advanced Search using JQuery DataTables in ASP.NET MVC 5
Introduction
In this post, let's see how we can add Advanced Search on our GridView, so that users get more user friendly search experience while searching for data in the grid.
We won't be repeating the steps from the previous one which we have done already which includes database creation and inserting sample data, setting up a new web application project with the required NuGet packages, if you are directly reading this, you might want to take a look at least at the last post about server side filtering to get familiar with what we are doing, so as said, we will be reusing the same project and code and will continue adding the new portion to it.
At the end of previous article, we had a working grid with server side pagination, filtering and sorting and after implementing the Advanced Search feature in the grid, our application will look like：
Background
In the previous two articles about implementing GridView in ASP.NET MVC, we talked about how we can create a grid like we had in ASP.NET web-forms using JQuery Data-tables plugin, then in the second post, we saw how we can enhance the performance of our gird by implementing the sorting, searching and paging, as in the first post we implemented a gird but the problem was all rows were getting rendered on the page as HTML when page is first time loaded and filtering, paging and sorting was being done on the client side and handled by the datatables plug in.
If you are interested in reading about those, you can find both posts by clicking on the links below：
- Beginners Guide for Creating GridView in ASP.NET MVC 5
- Grid View with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5
I hope that after reading the previous posts on grid in ASP.NET MVC, you are now in a better position to create a grid view in ASP.NET MVC which for most beginners is a difficult thing, especially for those who come from the web-forms development experience.
Step 1 - Database Creation
We saw in previous articles that we had just one Assets table that we were using to display records in the Grid and we had all the data in just one table in de-normalized form, so we have normalized one of the columns of Assets table and created a Lookup table named FacilitySites to demonstrate how advanced search can be implemented using datatables on server side, normalization is also done mostly to avoid data duplication so that instead of repeating the same value in multiple rows, we store it as a row in another table and just reference the unique identifier in the other table.
Following is the script which can be used to create a database：
CREATE DATABASE [AdvancedSearchGridExampleMVC]
GO
CREATE TABLE [dbo].[FacilitySites] ([FacilitySiteID] UNIQUEIDENTIFIER NOT NULL,
[FacilityName] NVARCHAR (MAX) NULL,
[IsActive] BIT NOT NULL,
[CreatedBy] UNIQUEIDENTIFIER NOT NULL,
[CreatedAt] DATETIME NOT NULL,
[ModifiedBy] UNIQUEIDENTIFIER NULL,
[ModifiedAt] DATETIME NULL,
[IsDeleted] BIT NOT NULL
);
GO
CREATE TABLE [dbo].[Assets] (
[AssetID] UNIQUEIDENTIFIER NOT NULL,
[Barcode] NVARCHAR (MAX) NULL,
[SerialNumber] NVARCHAR (MAX) NULL,
[PMGuide] NVARCHAR (MAX) NULL,
[AstID] NVARCHAR (MAX) NOT NULL,
[ChildAsset] NVARCHAR (MAX) NULL,
[GeneralAssetDescription] NVARCHAR (MAX) NULL,
[SecondaryAssetDescription] NVARCHAR (MAX) NULL,
[Quantity] INT NOT NULL,
[Manufacturer] NVARCHAR (MAX) NULL,
[ModelNumber] NVARCHAR (MAX) NULL,
[Building] NVARCHAR (MAX) NULL,
[Floor] NVARCHAR (MAX) NULL,
[Corridor] NVARCHAR (MAX) NULL,
[RoomNo] NVARCHAR (MAX) NULL,
[MERNo] NVARCHAR (MAX) NULL,
[EquipSystem] NVARCHAR (MAX) NULL,
[Comments] NVARCHAR (MAX) NULL,
[Issued] BIT NOT NULL,
[FacilitySiteID] UNIQUEIDENTIFIER NOT NULL
);
GO
CREATE NONCLUSTERED INDEX [IX_FacilitySiteID]
ON [dbo].[Assets]([FacilitySiteID] ASC);
GO
ALTER TABLE [dbo].[Assets]
ADD CONSTRAINT [PK_dbo.Assets] PRIMARY KEY CLUSTERED ([AssetID] ASC);
GO
ALTER TABLE [dbo].[Assets]
ADD CONSTRAINT [FK_dbo.Assets_dbo.FacilitySites_FacilitySiteID] FOREIGN KEY ([FacilitySiteID]) _
REFERENCES [dbo].[FacilitySites] ([FacilitySiteID]) ON DELETE CASCADE;
GO
After database gets created successfully which will of course, after that we need to dump some records in the table so that when we query from the application, we could have something displaying on the page to see if things are working correctly. So following is the script for that：
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'd37cc16b-3d13-4eba-8c98-0008b409a77b', N'D04-056',
N'N/A', N'D-04', N'D04-056', N'N/A',
N'DOOR, HYDR/ELEC/PNEUM OPERATED', N'N/A', 1, N'KM',
N'N/A', N'South', N'7', N'E', N'019',
N'', N'', N'Swing', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'7be68b37-5ec3-4a8b-be48-00490049f66b', N'C06-114',
N'N/A', N'C-06', N'C06-114', N'A11-13,C08-16',
N'CONTROLS, CENTRAL SYSTEM, HVAC', N'N/A', 1, N'N/A',
N'N/A', N'South', N'9', N'F', N'004',
N'MER5 ', N'AC-SE-2', N'rtn damper', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem], [Comments],
[Issued], [FacilitySiteID])
VALUES (N'e8a8af59-a863-4757-93bd-00561f36122b', N'C03-069',
N'N/A', N'C-03', N'C03-069', N'',
N'COILS, REHEAT/PREHEAT (REMOTE)', N'N/A', 1, N'N/A',
N'N/A', N'North', N'4', N'A', N'222',
N'', N' RH-N-17', N'', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem], [Comments],
[Issued], [FacilitySiteID]) VALUES (N'69dcdb07-8f60-4bbf-ad05-0078f3902c48',
N'D06-300', N'N/A', N'D-06', N'D06-300',
N'', N'DRAIN, AREAWAY/DRIVEWAY/STORM', N'N/A', 1,
N'N/A', N'N/A', N'South', N'Exterior', N'',
N'1s0?', N'SB areaway 1st', N'', N'', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber], [Building],
[Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem], [Comments], [Issued],
[FacilitySiteID]) VALUES (N'5b229566-5226-4e48-a6c7-008d435f81ae',
N'A05-46', N'N/A', N'A-05', N'A05-46', N'',
N'Air Conditioning Machine, Split System Chilled Water Coils',
N'10 Tons and Under', 1, N'Trane', N'N/A', N'South',
N'1', N'G', N'022', N'Headquarter Protective Force',
N'', N'Above Ceilg', 0, N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription], [SecondaryAssetDescription],
[Quantity], [Manufacturer], [ModelNumber], [Building], [Floor], [Corridor], [RoomNo],
[MERNo], [EquipSystem], [Comments], [Issued], [FacilitySiteID])
VALUES (N'108d1792-7aa1-4865-a3d3-00a0ea973aa3', N'C06-252',
N'N/A', N'C-06', N'C06-252', N'F27-35,C08-33',
N'CONTROLS, CENTRAL SYSTEM, HVAC', N'N/A', 1, N'N/A',
N'N/A', N'South', N'9', N'F', N'004',
N'MER5 ', N'E-SE-1', N'exh damper', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem], [Comments],
[Issued], [FacilitySiteID]) VALUES (N'80b9e4f9-71a4-4bd6-85c1-00a404cfee2b',
N'D06-409', N'N/A', N'D-06', N'D06-409', N'',
N'DRAIN, AREAWAY/DRIVEWAY/STORM', N'N/A', 1, N'N/A',
N'N/A', N'North', N'Exterior', N'',
N'eas0?', N'NB lawn east', N'', N'', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'bdad32e0-9c21-4451-8cc9-00b47b155eb9', N'D04-182',
N'N/A', N'D-04', N'D04-182', N'N/A', N'DOOR,
HYDR/ELEC/PNEUM OPERATED', N'N/A', 1, N'N/A', N'N/A',
N'South', N'2', N'E', N'2E-115',
N'Bathrooms', N'', N'HYDR/ELEC/PNEUM', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'4d859a1b-10e0-4cb0-96a4-00c164a7237e', N'C03-222',
N'N/A', N'C-03', N'C03-222', N'', N'COILS,
REHEAT/PREHEAT (REMOTE)', N'N/A', 1, N'N/A',
N'N/A', N'West', N'G', N'GJ, GI', N'086,052',
N'MER8 ', N'SW-26', N'', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem], [Comments],
[Issued], [FacilitySiteID]) VALUES (N'3df536d8-9f25-40dd-a83f-00c4434ad58e',
N'D06-348', N'N/A', N'D-06', N'D06-348',
N'', N'DRAIN, AREAWAY/DRIVEWAY/STORM', N'N/A', 1,
N'N/A', N'N/A', N'West', N'Exterior',
N'', N'2n4?', N'WB areaway 2nd', N'',
N'', 0, N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'26c671bc-47f1-4d0e-acc6-00cdfb94b67d', N'C06-165',
N'N/A', N'C-06', N'C06-165', N'A11-17,C08-22',
N'CONTROLS, CENTRAL SYSTEM, HVAC', N'N/A', 1, N'N/A',
N'N/A', N'South', N'9', N'F', N'004',
N'MER5 ', N'AC-SE-6', N'min OA', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'be09535a-0fb6-4f7b-a74e-00dab4730211', N'D04-034',
N'N/A', N'D-04', N'D04-034', N'N/A',
N'DOOR, HYDR/ELEC/PNEUM OPERATED', N'N/A', 1,
N'Dor-O-Matic, Jr', N'N/A', N'North', N'G',
N'A', N'064', N'', N'', N'Swing', 0,
N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode], [SerialNumber],
[PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'65a0abaa-75cf-489a-9367-0118486218b9', N'D05-049',
N'N/A', N'D-05', N'D05-049', N'N/A',
N'DOOR, ENTRANCE, MAIN', N'N/A', 1,
N'N/A', N'N/A', N'South', N'G_
1st', N'E', N'283', N'Ped Mall east', N'',
N'', 0, N'526fa0d5-1872-e611-b10e-005056c00008')
INSERT INTO [dbo].[Assets] ([AssetID], [Barcode],
[SerialNumber], [PMGuide], [AstID], [ChildAsset], [GeneralAssetDescription],
[SecondaryAssetDescription], [Quantity], [Manufacturer], [ModelNumber],
[Building], [Floor], [Corridor], [RoomNo], [MERNo], [EquipSystem],
[Comments], [Issued], [FacilitySiteID])
VALUES (N'c0101cf3-d1f1-4d32-a4b5-0135dc54645a',
N'C03-046', N'N/A', N'C-03', N'C03-046',
N'', N'COILS, REHEAT/PREHEAT (REMOTE)', N'N/A', 1,
N'N/A', N'N/A', N'North', N'5',
N'A', N'084', N'', N'RH-N-30',
N'', 0, N'526fa0d5-1872-e611-b10e-005056c00008')
GO
Step 2 - Advanced Search Form Creation
We will create a new view for our advanced search, which will contain a form with few input HTML controls that will be posted to controller action for filtering the records.
In Solution Explorer, Expand the Views folder, then again expand the Asset folder and open the Index.cshtml file, we will add the HTML for the Advanced Search button that will appear above the grid. Add the following HTML in the view：
<button type="button" class="btn btn-default btn-md" data-toggle="modal" data-target="#advancedSearchModal" id="advancedsearch-button">
<span class="glyphicon glyphicon-search" aria-hidden="true"></span> Advanced Search
</button>
If you note, we have some new attributes in the button code, you don't need to worry that those are for bootstrap modal, as clicking the button will open a Modal dialog, and user would be able to select the search criteria and search for results. The data-toggle="modal" attribute dictates that this button will toggle a Modal Dialog and data-target="#advancedSearchModal" specifies the HTML element of the page which would be displayed as Modal Dialog.
After adding the above HTML code in the Index.cshtml, the view will have the following code in it：
<div class="row">
<div class="col-md-12">
<div class="panel panel-primary list-panel"
id="list-panel">
<div class="panel-heading list-panel-heading">
<h1 class="panel-title list-panel-title"
>Assets</h1>
<button type="button"
class="btn btn-default btn-md"
data-toggle="modal"
data-target="#advancedSearchModal"
id="advancedsearch-button">
<span class="glyphicon glyphicon-search"
aria-hidden="true"></span> Advanced Search
</button>
</div>
<div class="panel-body">
<table id="assets-data-table"
class="table table-striped table-bordered"
style="width:100%;">
</table>
</div>
</div>
</div>
</div>
@section Scripts
{
<script type="text/javascript">
var assetListVM;
$(function () {
assetListVM = {
dt: null,
init: function () {
dt = $('#assets-data-table').DataTable({
"serverSide": true,
"processing": true,
"ajax": {
"url":
"@Url.Action("Get","Asset")"
},
"columns": [
{ "title": "Bar Code",
"data": "BarCode", "searchable": true },
{ "title": "Manufacturer",
"data": "Manufacturer", "searchable": true },
{ "title": "Model",
"data": "ModelNumber",
"searchable": true },
{ "title": "Building",
"data": "Building",
"searchable": true },
{ "title": "Room No",
"data": "RoomNo" },
{ "title": "Quantity",
"data": "Quantity" }
],
"lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
});
}
}
// initialize the datatables
assetListVM.init();
});
</script>
}
Our modal popup UI will look like this if we run the application：
Step 3 - Adding Models with Entity Framework
The next step is to create a new Model (DTO) class named FacilitySite which will be used for getting the data from FacilitySites Lookup table which we created above with the database script. So add a new class in the Models folder in Solution Explorer named FacilitySite and following is the code for that：
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace GridAdvancedSearchMVC.Models
{
public class FacilitySite
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public System.Guid FacilitySiteID { get; set; }
[Display(Name = "Facility-Site")]
public string FacilityName { get; set; }
public bool IsActive { get; set; }
public System.Guid CreatedBy { get; set; }
[Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreatedAt { get; set; }
public System.Guid? ModifiedBy { get; set; }
public DateTime? ModifiedAt { get; set; }
public bool IsDeleted { get; set; }
}
}
Right now, we have just added the Model class which will hold data for FacilitySites table, but as we are using Entity Framework for Data Access purpose, we will have to let it know that there is a new table added on which data operations can be performed. For that in Models folder, open the IdentityModel.cs file and update the ApplicationDbContext code to include a new property of type DbSet<FacilitySite>：
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
}
public DbSet<Asset> Assets { get; set; }
public DbSet<FacilitySite> FacilitySites { get; set; }
public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}
Update the Asset model as well by removing the FacilitySite column which was of type String before and instead add a new column named FacilitySiteId which will be foreign key in Asset table of FacilitySite table, updated Asset model should be：
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace GridAdvancedSearchMVC.Models
{
public class Asset
{
public System.Guid AssetID { get; set; }
[Display(Name = "Barcode")]
public string Barcode { get; set; }
[Display(Name = "Serial-Number")]
public string SerialNumber { get; set; }
//[Display(Name = "Facility-Site")]
//public string FacilitySite { get; set; }
[ForeignKey("FacilitySite")]
public Guid FacilitySiteID { get; set; }
[Display(Name = "PM-Guide-ID")]
public string PMGuide { get; set; }
[Required]
[Display(Name = "Asset-ID")]
public string AstID { get; set; }
[Display(Name = "Child-Asset")]
public string ChildAsset { get; set; }
[Display(Name = "General-Asset-Description")]
public string GeneralAssetDescription { get; set; }
[Display(Name = "Secondary-Asset-Description")]
public string SecondaryAssetDescription { get; set; }
public int Quantity { get; set; }
[Display(Name = "Manufacturer")]
public string Manufacturer { get; set; }
[Display(Name = "Model-Number")]
public string ModelNumber { get; set; }
[Display(Name = "Main-Location (Building)")]
public string Building { get; set; }
[Display(Name = "Sub-Location 1 (Floor)")]
public string Floor { get; set; }
[Display(Name = "Sub-Location 2 (Corridor)")]
public string Corridor { get; set; }
[Display(Name = "Sub-Location 3 (Room No)")]
public string RoomNo { get; set; }
[Display(Name = "Sub-Location 4 (MER#)")]
public string MERNo { get; set; }
[Display(Name = "Sub-Location 5 (Equip/System)")]
public string EquipSystem { get; set; }
public string Comments { get; set; }
public bool Issued { get; set; }
public virtual FacilitySite FacilitySite { get; set; }
}
}
Step 4 - Creating ViewModel
We will also need to create a ViewModel class which will be used for posting the search criteria to server side which will be controller action for performing the search. Let's add the ViewModel then. Following is the code for the AdvancedSearchViewModel class：
using System;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
namespace GridExampleMVC.Models
{
public class AdvancedSearchViewModel
{
[Display(Name = "Facility-Site")]
public Guid FacilitySite { get; set; }
[Display(Name = "Main-Location (Building)")]
public string Building { get; set; }
public string Manufacturer { get; set; }
public string Status { get; set; }
public SelectList FacilitySiteList { get; set; }
public SelectList BuildingList { get; set; }
public SelectList ManufacturerList { get; set; }
public SelectList StatusList { get; set; }
}
}
Step 5 - Implement Advanced Search Get Action
Navigate to Controllers folder and expand it, and open the AssetController.cs file, we will add a new get action that will be used to populate the AdvancedSeachViewModel and we will be setting the SelectList properties with data from their respective data sources for populating the Dropdown List controls on the advanced search modal popup：
[HttpGet]
public ActionResult AdvancedSearch()
{
var advancedSearchViewModel = new AdvancedSearchViewModel();
advancedSearchViewModel.FacilitySiteList = new SelectList(DbContext.FacilitySites
.Where(facilitySite => facilitySite.IsActive && !facilitySite.IsDeleted)
.Select(x => new { x.FacilitySiteID, x.FacilityName }),
"FacilitySiteID",
"FacilityName");
advancedSearchViewModel.BuildingList = new SelectList(DbContext.Assets
.GroupBy(x => x.Building)
.Where(x => x.Key != null && !x.Key.Equals(string.Empty))
.Select(x => new { Building = x.Key }),
"Building",
"Building");
advancedSearchViewModel.ManufacturerList = new SelectList(DbContext.Assets
.GroupBy(x => x.Manufacturer)
.Where(x => x.Key != null && !x.Key.Equals(string.Empty))
.Select(x => new { Manufacturer = x.Key }),
"Manufacturer",
"Manufacturer");
advancedSearchViewModel.StatusList = new SelectList(new List<SelectListItem>
{
new SelectListItem { Text="Issued",Value=bool.TrueString},
new SelectListItem { Text="Not Issued",Value = bool.FalseString}
},
"Value",
"Text");
return View("_AdvancedSearchPartial", advancedSearchViewModel);
}
Step 6 - Advanced Search Post Action Implementation
Our AdvancedSearch post action will be almost the same implementation wise as was the implementation of Search action for Server Side Sort, Filter and Paging one, but there will be a small change in action signatures for AdvancedSearch, it will now take 2 parameters which is quite obvious, one for maintaining the DataTables state which was already there before as well and the new one will be the instance of AdvancedSearchViewModel class which will have the state of controls of Advanced Search Modal popup.
We need to update the SearchAssets private method which we created in the previous post about Grid View Server Side Processing, add the advanced searching database logic in this method, so this method will not take another parameter which is we know instance of AdvancedSearchViewModel：
private IQueryable<Asset> SearchAssets(IDataTablesRequest requestModel,
AdvancedSearchViewModel searchViewModel, IQueryable<Asset> query)
{
// Apply filters
if (requestModel.Search.Value != string.Empty)
{
var value = requestModel.Search.Value.Trim();
query = query.Where(p => p.Barcode.Contains(value) ||
p.Manufacturer.Contains(value) ||
p.ModelNumber.Contains(value) ||
p.Building.Contains(value)
);
}
/***** Advanced Search Starts ******/
if (searchViewModel.FacilitySite != Guid.Empty)
query = query.Where(x => x.FacilitySiteID == searchViewModel.FacilitySite);
if (searchViewModel.Building != null)
query = query.Where(x => x.Building == searchViewModel.Building);
if (searchViewModel.Manufacturer != null)
query = query.Where(x => x.Manufacturer == searchViewModel.Manufacturer);
if (searchViewModel.Status != null)
{
bool Issued = bool.Parse(searchViewModel.Status);
query = query.Where(x => x.Issued == Issued);
}
/***** Advanced Search Ends ******/
var filteredCount = query.Count();
// Sort
var sortedColumns = requestModel.Columns.GetSortedColumns();
var orderByString = String.Empty;
foreach (var column in sortedColumns)
{
orderByString += orderByString != String.Empty ? "," : "";
orderByString += (column.Data) +
(column.SortDirection == Column.OrderDirection.Ascendant ?
" asc" : " desc");
}
query = query.OrderBy(orderByString ==
string.Empty ? "BarCode asc" : orderByString);
return query;
}
Step 7 - Updating DataTables Post Call Action
Now update the action as well which is called for handles the grid server side processing to accept the advanced search parameter as well and pass them to the SearchAssets method for more granular filtering, here is the updated code of the action：
public ActionResult Get([ModelBinder(typeof(DataTablesBinder))]
IDataTablesRequest requestModel, AdvancedSearchViewModel searchViewModel)
{
IQueryable<Asset> query = DbContext.Assets;
var totalCount = query.Count();
// searching and sorting
query = SearchAssets(requestModel, searchViewModel,query);
var filteredCount = query.Count();
// Paging
query = query.Skip(requestModel.Start).Take(requestModel.Length);
var data = query.Select(asset => new
{
AssetID = asset.AssetID,
BarCode = asset.Barcode,
Manufacturer = asset.Manufacturer,
ModelNumber = asset.ModelNumber,
Building = asset.Building,
RoomNo = asset.RoomNo,
Quantity = asset.Quantity
}).ToList();
return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);
}
Step 8 - Implement Modal Popup View for Advanced Search
Now, we will move towards the view part, as you can see, we have last four properties of type SelectList which are there because we will have few dropdown list controls in the advanced form which user will be selecting from pre-populated values for searching the records.
The data-target="#advancedSearchModal" which we added in the html of Index.cshtml view will be referenced in this partial view, so create a new partial view under Views >> Asset named _AdvancedSearchPartial, for that, right click the Asset folder under **Views ** and navigate to Add Item, then from next Menu, select MVC 5 Partial Page (Razor)：
Type the partial view name which would be _AdvancedSearchPartial in this case and click the OK button：
And then, open the file _AdvancedSearchPartial.cshtml and add the HTML in the partial view that will be displayed as modal popup when the user will click the Advanced Search button that we created in the Index.cshtml view, following the code of the advanced search partial view：
@model TA_UM.ViewModels.AdvancedSearchViewModel
@{
Layout = null;
}
<div class="modal fade" id="advancedSearchModal"
tabindex="-1" role="dialog"
aria-labelledby="myModalLabel"
aria-hidden="true" data-backdrop="static">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Advanced Search</h4>
</div>
@using (Html.BeginForm("Get", "Asset",
FormMethod.Get, new { id = "frmAdvancedSearch",
@class = "form-horizontal", role = "form" }))
{
<div class="modal-body">
<div class="form-horizontal">
<hr />
<div class="form-group">
@Html.LabelFor(model => model.FacilitySite,
htmlAttributes: new { @class = "control-label col-md-3" })
<div class="col-md-8">
<div class="dropdown">
@Html.DropDownListFor(model => model.FacilitySite,
Model.FacilitySiteList, "Any", new { @class = "form-control" })
</div>
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Building,
htmlAttributes: new { @class = "control-label col-md-3" })
<div class="col-md-8">
<div class="dropdown">
@Html.DropDownListFor(model => model.Building,
Model.BuildingList, "Any", new { @class = "form-control" })
</div>
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Manufacturer,
htmlAttributes: new { @class = "control-label col-md-3" })
<div class="col-md-8">
<div class="dropdown">
@Html.DropDownListFor(model => model.Manufacturer,
Model.ManufacturerList, "Any", new { @class = "form-control" })
</div>
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Status,
htmlAttributes: new { @class = "control-label col-md-3" })
<div class="col-md-8">
<div class="dropdown">
@Html.DropDownListFor(model => model.Status,
Model.StatusList, "Both", new { @class = "form-control" })
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button id="btnPerformAdvancedSearch"
type="button" class="btn btn-default btn-success"
data-dismiss="modal">Search</button>
<button id="btnCancel" type="button"
class="btn btn-default" data-dismiss="modal">Cancel</button>
</div>
}
</div>
</div>
</div>
Final Step - Pass Advanced Search Parameters from View in POST
Finally, open the Index.cshtml located in Views >> Asset and call the AdvancedSearch get action before the <span style="background-color: yellow">@section Scripts</span> start for adding the Advanced Search Modal popup HTML in the browser which will be displayed when button is triggered, another thing to note is that we have not specified anywhere about how the dropdown selected values will be posted with DataTables server side processing in the same action, though we have added the parameter in action but we haven't changed anything specific to that in View, we will have to update the jquery datatables initialization code for that, and specify the values for posting to the AdvancedSearchViewModel using data property for which we would have to define the property, so add the following code just after the line where we are specifying URL for datatable which is "url": "@Url.Action("Get","Asset")", and after adding that final Index view, the code should be：
"data": function (data) {
data.FacilitySite = $("#FacilitySite").val();
data.Building = $("#Building").val();
data.Manufacturer = $("#Manufacturer").val();
data.Status = $("#Status").val();
}
Our Index View would contain the following code：
<div class="row">
<div class="col-md-12">
<div class="panel panel-primary list-panel" id="list-panel">
<div class="panel-heading list-panel-heading">
<h1 class="panel-title list-panel-title">Assets</h1>
<button type="button"
class="btn btn-default btn-md" data-toggle="modal"
data-target="#advancedSearchModal" id="advancedsearch-button">
<span class="glyphicon glyphicon-search"
aria-hidden="true"></span> Advanced Search
</button>
</div>
<div class="panel-body">
<table id="assets-data-table"
class="table table-striped table-bordered"
style="width:100%;">
</table>
</div>
</div>
</div>
</div>
@Html.Action("AdvancedSearch")
@section Scripts
{
<script type="text/javascript">
var assetListVM;
$(function () {
assetListVM = {
dt: null,
init: function () {
dt = $('#assets-data-table').DataTable({
"serverSide": true,
"processing": true,
"ajax": {
"url": "@Url.Action("Get","Asset")",
"data": function (data) {
data.FacilitySite = $("#FacilitySite").val();
data.Building = $("#Building").val();
data.Manufacturer = $("#Manufacturer").val();
data.Status = $("#Status").val();
}
},
"columns": [
{ "title": "Bar Code", "data":
"BarCode", "searchable": true },
{ "title": "Manufacturer", "data":
"Manufacturer", "searchable": true },
{ "title": "Model", "data":
"ModelNumber", "searchable": true },
{ "title": "Building", "data":
"Building", "searchable": true },
{ "title": "Room No", "data": "RoomNo" },
{ "title": "Quantity", "data": "Quantity" }
],
"lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
});
},
refresh: function () {
dt.ajax.reload();
}
}
// Advanced Search Modal Search button click handler
$('#btnPerformAdvancedSearch').on("click", assetListVM.refresh);
}
// initialize the datatables
assetListVM.init();
});
</script>
}
You can see above that we have added a new function in our datatable view model named refresh, whose purpose is to reload the datatable from server side using the model of DataTables, we have written the event handler for Advanced Search Popup button that when it is pressed, it causes the datatable to be reloaded and in the Ajax call of it, we are passing the user selected search criteria from advanced search view as well using that data property of jQuery datatables.
Now build the project, and run it in browser to see the working server side Advanced Search funcationality using JQuery datatables and with server side filtering, paging, and sorting as well in action.
Download Source Code
The source code is available at MSDN Gallery here.