GridView with Server Side Advanced Search Implementation using JQuery DataTables in ASP.NET MVC 5
Download Source Code
Background:
In the last two posts about implementing GridView in asp.net mvc, we talked how we can create a grid like we had in asp.net webforms using JQuery DataTables plugin, then in the second post we saw that 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 datables plugin.
If you are interested to read those, you can find both of those post here:
- Beginners Guide for Creating GridView in ASP.NET MVC 5
- Grid View with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5
Introduction
In this post we will see that how we can add Advanced Search on our GridView, so that user gets more user friendly search experience while searching for data in the grid.
We won’t be repeating the steps from the previous 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 on at least the last post about server side filtering to get familiar with what we are doing, so as being said, we will be reusing the same project and code and will continue adding the new portion to it.
We won’t be repeating the steps from the previous 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 on at least the last post about server side filtering to get familiar with what we are doing, so as being 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:
Step 1 - Database Creation
We saw in previous posts that we had just one Assets table that we were using to display records in the Gird and we had all the data in just one table in De-normalized form, so we have normalized one of the column 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 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 database:
Following is the script which can be used to create 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
If 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 the things are working correctly. So following is the script for that:
If you note we have some new attributes in the button code, you don’t need to worry about 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:
Our modal popup will finally look like:
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 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> :
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:
INSERT INTO [dbo].[FacilitySites] ([FacilitySiteID], [FacilityName], [IsActive], [CreatedBy], [CreatedAt], [ModifiedBy], [ModifiedAt], [IsDeleted]) VALUES (N'526fa0d5-1872-e611-b10e-005056c00008', N'FOR', 1, N'8de72a70-6a35-4658-ae0d-ca3cc55da752', N'2016-09-04 01:56:08', NULL, NULL, 0) INSERT INTO [dbo].[FacilitySites] ([FacilitySiteID], [FacilityName], [IsActive], [CreatedBy], [CreatedAt], [ModifiedBy], [ModifiedAt], [IsDeleted]) VALUES (N'536fa0d5-1872-e611-b10e-005056c00008', N'Pryco', 1, N'8de72a70-6a35-4658-ae0d-ca3cc55da752', N'2016-09-04 01:56:08', NULL, NULL, 0) INSERT INTO [dbo].[FacilitySites] ([FacilitySiteID], [FacilityName], [IsActive], [CreatedBy], [CreatedAt], [ModifiedBy], [ModifiedAt], [IsDeleted]) VALUES (N'546fa0d5-1872-e611-b10e-005056c00008', N'6rt', 1, N'8de72a70-6a35-4658-ae0d-ca3cc55da752', N'2016-09-04 01:56:08', NULL, NULL, 0) GO 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 contains 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:
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 about 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 will finally look like:
Step 4 - 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 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 class
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 same implementation wise as was the implementation of Search action for Server Side Sort, Filter and Paging one, but there will be small change in action signatures for AdvancedSearch, it will now take 2 parameter which is quite obvious, one for maintain 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 7 - 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 View and navigate to Add Item, then from next Menu select MVC 5 Partial Page (Razor) :
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 View 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 @section Scripts 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 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 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 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 we have add 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 wrote 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 browse to see the working server side Advanced Search using JQuery datatables and with server side filtering, paging, and sorting as well in action.
Now build the project, and run it in browse to see the working server side Advanced Search using JQuery datatables and with server side filtering, paging, and sorting as well in action.
Source Code
You can download the source code from MSDN code samples gallery here
