Post
Ask Question

Grid View With Server Side Advanced Search Using jQuery DataTables In ASP.NET MVC 5

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 how we can enhance the performance of our Grid by implementing the sorting, searching, and paging. As in the first post ,we implemented a Grid but the problem was that all rows were getting rendered on the page as html when page was first time loaded and filtering, paging, and sorting were being done on the client side and handled by the DataTable plugin.

If you are interested to read those, you can find both of those posts here.

I hope that after reading previous posts, you are now in better position to create a GridView in ASP.NET MVC which, for most of the beginners, is a difficult thing especially for those who come from the web forms development experience.

Introduction

In this post, we will see how we can add Advanced Search on our GridView, so that the user gets more user-friendly search experience while searching for data in the Grid. 

We won’t be repeating the steps from the previous articles as we have done them already - 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, 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 which looked like the following.

 

Database Creation

We saw in previous posts 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 same values 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 that can be used to create database.

  1. CREATE DATABASE [AdvancedSearchGridExampleMVC]    
  2.  GO    
  3.   
  4. CREATE TABLE [dbo].[FacilitySites] (  
  5.     [FacilitySiteID] UNIQUEIDENTIFIER NOT NULL,  
  6.     [FacilityName]   NVARCHAR (MAX)   NULL,  
  7.     [IsActive]       BIT              NOT NULL,  
  8.     [CreatedBy]      UNIQUEIDENTIFIER NOT NULL,  
  9.     [CreatedAt]      DATETIME         NOT NULL,  
  10.     [ModifiedBy]     UNIQUEIDENTIFIER NULL,  
  11.     [ModifiedAt]     DATETIME         NULL,  
  12.     [IsDeleted]      BIT              NOT NULL  
  13. );  
  14. GO  
  15.   
  16.   
  17. CREATE TABLE [dbo].[Assets] (  
  18.     [AssetID]                   UNIQUEIDENTIFIER NOT NULL,  
  19.     [Barcode]                   NVARCHAR (MAX)   NULL,  
  20.     [SerialNumber]              NVARCHAR (MAX)   NULL,  
  21.     [PMGuide]                   NVARCHAR (MAX)   NULL,  
  22.     [AstID]                     NVARCHAR (MAX)   NOT NULL,  
  23.     [ChildAsset]                NVARCHAR (MAX)   NULL,  
  24.     [GeneralAssetDescription]   NVARCHAR (MAX)   NULL,  
  25.     [SecondaryAssetDescription] NVARCHAR (MAX)   NULL,  
  26.     [Quantity]                  INT              NOT NULL,  
  27.     [Manufacturer]              NVARCHAR (MAX)   NULL,  
  28.     [ModelNumber]               NVARCHAR (MAX)   NULL,  
  29.     [Building]                  NVARCHAR (MAX)   NULL,  
  30.     [Floor]                     NVARCHAR (MAX)   NULL,  
  31.     [Corridor]                  NVARCHAR (MAX)   NULL,  
  32.     [RoomNo]                    NVARCHAR (MAX)   NULL,  
  33.     [MERNo]                     NVARCHAR (MAX)   NULL,  
  34.     [EquipSystem]               NVARCHAR (MAX)   NULL,  
  35.     [Comments]                  NVARCHAR (MAX)   NULL,  
  36.     [Issued]                    BIT              NOT NULL,  
  37.     [FacilitySiteID]            UNIQUEIDENTIFIER NOT NULL  
  38. );  
  39. GO  
  40.   
  41. CREATE NONCLUSTERED INDEX [IX_FacilitySiteID]  
  42.     ON [dbo].[Assets]([FacilitySiteID] ASC);  
  43.   
  44.   
  45. GO  
  46. ALTER TABLE [dbo].[Assets]  
  47.     ADD CONSTRAINT [PK_dbo.Assets] PRIMARY KEY CLUSTERED ([AssetID] ASC);  
  48.   
  49.   
  50. GO  
  51. ALTER TABLE [dbo].[Assets]  
  52.     ADD CONSTRAINT [FK_dbo.Assets_dbo.FacilitySites_FacilitySiteID] FOREIGN KEY ([FacilitySiteID]) REFERENCES [dbo].[FacilitySites] ([FacilitySiteID]) ON DELETE CASCADE;  
  53. GO   

If database gets created successfully, 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, the following is the script for that.

  1. 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)  
  2. 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)  
  3. 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)  
  4. GO  
  5.   
  6. 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')  
  7. 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')  
  8. 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')  
  9. 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')  
  10. 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')  
  11. 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')  
  12. 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')  
  13. 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')  
  14. 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')  
  15. 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')  
  16. 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')  
  17. 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')  
  18. 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')  
  19. 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')  
  20. GO   

Step 2 - Advanced Search Form Creation

First of all, 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, expand the Asset folder, and open the Index.cshtml file. We will add html for the Advanced Search button that will appear above the Grid. Add the following html in View.

  1. <button type="button" class="btn btn-default btn-md" data-toggle="modal"   
  2.         data-target="#advancedSearchModal" id="advancedsearch-button">  
  3.    <span class="glyphicon glyphicon-search" aria-hidden="true"></span> Advanced Search  
  4. </button>  

If you note, we have some new attributes in the button code. You don’t need to worry about that because those are for bootstrap model, 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.

  1. <div class="row">  
  2.     <div class="col-md-12">  
  3.         <div class="panel panel-primary list-panel" id="list-panel">  
  4.             <div class="panel-heading list-panel-heading">  
  5.                 <h1 class="panel-title list-panel-title">Assets</h1>  
  6.                 <button type="button" class="btn btn-default btn-md" data-toggle="modal" data-target="#advancedSearchModal" id="advancedsearch-button">  
  7.                     <span class="glyphicon glyphicon-search" aria-hidden="true"></span> Advanced Search  
  8.                 </button>  
  9.             </div>  
  10.             <div class="panel-body">  
  11.                 <table id="assets-data-table" class="table table-striped table-bordered" style="width:100%;">  
  12.                 </table>  
  13.             </div>  
  14.         </div>  
  15.     </div>  
  16. </div>  
  17.   
  18. @section Scripts  
  19. {  
  20.       
  21. <script type="text/javascript">  
  22.         var assetListVM;  
  23.         $(function () {  
  24.             assetListVM = {  
  25.                 dt: null,  
  26.   
  27.                 init: function () {  
  28.                     dt = $('#assets-data-table').DataTable({  
  29.                         "serverSide": true,  
  30.                         "processing": true,  
  31.                         "ajax": {  
  32.                             "url": "@Url.Action("Get","Asset")"  
  33.                         },  
  34.                         "columns": [  
  35.                             { "title": "Bar Code", "data": "BarCode", "searchable": true },  
  36.                             { "title": "Manufacturer", "data": "Manufacturer", "searchable": true },  
  37.                             { "title": "Model", "data": "ModelNumber", "searchable": true },  
  38.                             { "title": "Building", "data": "Building", "searchable": true },  
  39.                             { "title": "Room No", "data": "RoomNo" },  
  40.                             { "title": "Quantity", "data": "Quantity" }  
  41.                         ],  
  42.                         "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],  
  43.                     });  
  44.                 }  
  45.             }  
  46.   
  47.             // initialize the datatables  
  48.             assetListVM.init();  
  49.   
  50.         });  
  51.   
  52. </script>  
  53.       
  54.  }   

Our modal popup will finally look like this.


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.

  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3. using System.ComponentModel.DataAnnotations.Schema;  
  4.   
  5.   
  6. namespace GridAdvancedSearchMVC.Models  
  7. {  
  8.     public class FacilitySite  
  9.     {  
  10.         [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
  11.         public System.Guid FacilitySiteID { get; set; }  
  12.         [Display(Name = "Facility-Site")]  
  13.         public string FacilityName { get; set; }  
  14.         public bool IsActive { get; set; }  
  15.         public System.Guid CreatedBy { get; set; }  
  16.         [Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]  
  17.         public DateTime CreatedAt { get; set; }  
  18.         public System.Guid? ModifiedBy { get; set; }  
  19.         public DateTime? ModifiedAt { get; set; }  
  20.         public bool IsDeleted { get; set; }  
  21.     }  
  22. }   

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>. 
  1. public class ApplicationDbContext : IdentityDbContext<ApplicationUser>  
  2.    {  
  3.        public ApplicationDbContext()  
  4.            : base("DefaultConnection", throwIfV1Schema: false)  
  5.        {  
  6.        }  
  7.   
  8.        public DbSet<Asset> Assets { get; set; }  
  9.   
  10.        public DbSet<FacilitySite> FacilitySites { get; set; }  
  11.   
  12.        public static ApplicationDbContext Create()  
  13.        {  
  14.            return new ApplicationDbContext();  
  15.        }  
  16.    }   

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. The updated Asset model should be.

  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3. using System.ComponentModel.DataAnnotations.Schema;  
  4.   
  5. namespace GridAdvancedSearchMVC.Models  
  6. {  
  7.     public class Asset  
  8.     {  
  9.         public System.Guid AssetID { get; set; }  
  10.         [Display(Name = "Barcode")]  
  11.         public string Barcode { get; set; }  
  12.   
  13.         [Display(Name = "Serial-Number")]  
  14.         public string SerialNumber { get; set; }  
  15.         //[Display(Name = "Facility-Site")]  
  16.         //public string FacilitySite { get; set; }  
  17.         [ForeignKey("FacilitySite")]  
  18.         public Guid FacilitySiteID { get; set; }  
  19.         [Display(Name = "PM-Guide-ID")]  
  20.         public string PMGuide { get; set; }  
  21.         [Required]  
  22.         [Display(Name = "Asset-ID")]  
  23.         public string AstID { get; set; }  
  24.         [Display(Name = "Child-Asset")]  
  25.         public string ChildAsset { get; set; }  
  26.         [Display(Name = "General-Asset-Description")]  
  27.         public string GeneralAssetDescription { get; set; }  
  28.         [Display(Name = "Secondary-Asset-Description")]  
  29.         public string SecondaryAssetDescription { get; set; }  
  30.         public int Quantity { get; set; }  
  31.   
  32.         [Display(Name = "Manufacturer")]  
  33.         public string Manufacturer { get; set; }  
  34.   
  35.         [Display(Name = "Model-Number")]  
  36.         public string ModelNumber { get; set; }  
  37.         [Display(Name = "Main-Location (Building)")]  
  38.         public string Building { get; set; }  
  39.         [Display(Name = "Sub-Location 1 (Floor)")]  
  40.         public string Floor { get; set; }  
  41.         [Display(Name = "Sub-Location 2 (Corridor)")]  
  42.         public string Corridor { get; set; }  
  43.         [Display(Name = "Sub-Location 3 (Room No)")]  
  44.         public string RoomNo { get; set; }  
  45.         [Display(Name = "Sub-Location 4 (MER#)")]  
  46.         public string MERNo { get; set; }  
  47.         [Display(Name = "Sub-Location 5 (Equip/System)")]  
  48.         public string EquipSystem { get; set; }  
  49.         public string Comments { get; set; }  
  50.         public bool Issued { get; set; }  
  51.   
  52.         public virtual FacilitySite FacilitySite { get; set; }  
  53.   
  54.     }  
  55. }   

Step 4 -  ViewModel Creation

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.

  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3. using System.Web.Mvc;  
  4.   
  5. namespace GridExampleMVC.Models  
  6. {  
  7.     public class AdvancedSearchViewModel  
  8.     {  
  9.         [Display(Name = "Facility-Site")]  
  10.         public Guid FacilitySite { get; set; }  
  11.   
  12.         [Display(Name = "Main-Location (Building)")]  
  13.         public string Building { get; set; }  
  14.   
  15.         public string Manufacturer { get; set; }  
  16.   
  17.         public string Status { get; set; }  
  18.   
  19.         public SelectList FacilitySiteList { get; set; }  
  20.         public SelectList BuildingList { get; set; }  
  21.         public SelectList ManufacturerList { get; set; }  
  22.         public SelectList StatusList { get; set; }  
  23.   
  24.     }  
  25. }   

Step 5 - Advanced Search Get Implementation in Controller

Navigate to Controllers folder and expand it. 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.

  1. [HttpGet]  
  2. public ActionResult AdvancedSearch()  
  3. {  
  4.     var advancedSearchViewModel = new AdvancedSearchViewModel();  
  5.   
  6.     advancedSearchViewModel.FacilitySiteList = new SelectList(DbContext.FacilitySites  
  7.                                                                     .Where(facilitySite => facilitySite.IsActive && !facilitySite.IsDeleted)  
  8.                                                                     .Select(x => new { x.FacilitySiteID, x.FacilityName }),  
  9.                                                                       "FacilitySiteID",  
  10.                                                                       "FacilityName");  
  11.   
  12.    advancedSearchViewModel.BuildingList = new SelectList(DbContext.Assets  
  13.                                                                            .GroupBy(x => x.Building)  
  14.                                                                            .Where(x => x.Key != null && !x.Key.Equals(string.Empty))  
  15.                                                                            .Select(x => new { Building = x.Key }),  
  16.                                                                   "Building",  
  17.                                                                   "Building");  
  18.   
  19.     advancedSearchViewModel.ManufacturerList = new SelectList(DbContext.Assets  
  20.                                                                                .GroupBy(x => x.Manufacturer)  
  21.                                                                                .Where(x => x.Key != null && !x.Key.Equals(string.Empty))  
  22.                                                                                .Select(x => new { Manufacturer = x.Key }),  
  23.                                                                       "Manufacturer",  
  24.                                                                       "Manufacturer");  
  25.   
  26.    advancedSearchViewModel.StatusList = new SelectList(new List<SelectListItem>  
  27.             {  
  28.                                                                   new SelectListItem { Text="Issued",Value=bool.TrueString},  
  29.                                                                   new SelectListItem { Text="Not Issued",Value = bool.FalseString}  
  30.                                                                   },  
  31.                                                                   "Value",  
  32.                                                                   "Text"  
  33.                                                                 );  
  34.   
  35.     return View("_AdvancedSearchPartial", advancedSearchViewModel);  
  36. }   

Step 6 - Advanced Searching Post Handling in Controller

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 parameters 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,        

  1. private IQueryable<Asset> SearchAssets(IDataTablesRequest requestModel, AdvancedSearchViewModel searchViewModel, IQueryable<Asset> query)  
  2.         {  
  3.   
  4.             // Apply filters  
  5.             if (requestModel.Search.Value != string.Empty)  
  6.             {  
  7.                 var value = requestModel.Search.Value.Trim();  
  8.                 query = query.Where(p => p.Barcode.Contains(value) ||  
  9.                                          p.Manufacturer.Contains(value) ||  
  10.                                          p.ModelNumber.Contains(value) ||  
  11.                                          p.Building.Contains(value)  
  12.                                    );  
  13.             }  
  14.   
  15.             /***** Advanced Search Starts ******/  
  16.             if (searchViewModel.FacilitySite != Guid.Empty)  
  17.                 query = query.Where(x => x.FacilitySiteID == searchViewModel.FacilitySite);  
  18.   
  19.             if (searchViewModel.Building != null)  
  20.                 query = query.Where(x => x.Building == searchViewModel.Building);  
  21.   
  22.             if (searchViewModel.Manufacturer != null)  
  23.                 query = query.Where(x => x.Manufacturer == searchViewModel.Manufacturer);  
  24.   
  25.             if (searchViewModel.Status != null)  
  26.             {  
  27.                 bool Issued = bool.Parse(searchViewModel.Status);  
  28.                 query = query.Where(x => x.Issued == Issued);  
  29.             }  
  30.   
  31.             /***** Advanced Search Ends ******/  
  32.   
  33.             var filteredCount = query.Count();  
  34.   
  35.             // Sort  
  36.             var sortedColumns = requestModel.Columns.GetSortedColumns();  
  37.             var orderByString = String.Empty;  
  38.   
  39.             foreach (var column in sortedColumns)  
  40.             {  
  41.                 orderByString += orderByString != String.Empty ? "," : "";  
  42.                 orderByString += (column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? " asc" : " desc");  
  43.             }  
  44.   
  45.             query = query.OrderBy(orderByString == string.Empty ? "BarCode asc" : orderByString);  
  46.   
  47.             return query;  
  48.   
  49.         }   

Step 7 - Update DataTables Call Back action method

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,       

  1. public ActionResult Get([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel, AdvancedSearchViewModel searchViewModel)  
  2.         {  
  3.             IQueryable<Asset> query = DbContext.Assets;  
  4.             var totalCount = query.Count();  
  5.   
  6.             // searching and sorting  
  7.             query = SearchAssets(requestModel, searchViewModel,query);  
  8.             var filteredCount = query.Count();  
  9.   
  10.             // Paging  
  11.             query = query.Skip(requestModel.Start).Take(requestModel.Length);  
  12.   
  13.               
  14.   
  15.             var data = query.Select(asset => new  
  16.             {  
  17.                 AssetID = asset.AssetID,  
  18.                 BarCode = asset.Barcode,  
  19.                 Manufacturer = asset.Manufacturer,  
  20.                 ModelNumber = asset.ModelNumber,  
  21.                 Building = asset.Building,  
  22.                 RoomNo = asset.RoomNo,  
  23.                 Quantity = asset.Quantity  
  24.             }).ToList();  
  25.   
  26.             return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);  
  27.   
  28.         }   

Step 8 - Modal Popup Implementation for Advanced Search View

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),


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, 

  1. @model TA_UM.ViewModels.AdvancedSearchViewModel  
  2. @{  
  3.     Layout = null;  
  4. }  
  5.   
  6. <div class="modal fade" id="advancedSearchModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" data-backdrop="static">  
  7.     <div class="modal-dialog">  
  8.         <div class="modal-content">  
  9.             <div class="modal-header">  
  10.                 <h4 class="modal-title">Advanced Search</h4>  
  11.             </div>  
  12.             @using (Html.BeginForm("Get", "Asset", FormMethod.Get, new { id = "frmAdvancedSearch", @class = "form-horizontal", role = "form" }))  
  13.             {  
  14.                 <div class="modal-body">  
  15.                     <div class="form-horizontal">  
  16.                         <hr />  
  17.                         <div class="form-group">  
  18.                             @Html.LabelFor(model => model.FacilitySite, htmlAttributes: new { @class = "control-label col-md-3" })  
  19.                             <div class="col-md-8">  
  20.                                 <div class="dropdown">  
  21.                                     @Html.DropDownListFor(model => model.FacilitySite, Model.FacilitySiteList, "Any", new { @class = "form-control" })  
  22.                                 </div>  
  23.                             </div>  
  24.                         </div>  
  25.   
  26.                         <div class="form-group">  
  27.                             @Html.LabelFor(model => model.Building, htmlAttributes: new { @class = "control-label col-md-3" })  
  28.                             <div class="col-md-8">  
  29.                                 <div class="dropdown">  
  30.                                     @Html.DropDownListFor(model => model.Building, Model.BuildingList, "Any", new { @class = "form-control" })  
  31.                                 </div>  
  32.                             </div>  
  33.                         </div>  
  34.   
  35.                         <div class="form-group">  
  36.                             @Html.LabelFor(model => model.Manufacturer, htmlAttributes: new { @class = "control-label col-md-3" })  
  37.                             <div class="col-md-8">  
  38.                                 <div class="dropdown">  
  39.                                     @Html.DropDownListFor(model => model.Manufacturer, Model.ManufacturerList, "Any", new { @class = "form-control" })  
  40.                                 </div>  
  41.                             </div>  
  42.                         </div>  
  43.   
  44.                         <div class="form-group">  
  45.                             @Html.LabelFor(model => model.Status, htmlAttributes: new { @class = "control-label col-md-3" })  
  46.                             <div class="col-md-8">  
  47.                                 <div class="dropdown">  
  48.                                     @Html.DropDownListFor(model => model.Status, Model.StatusList, "Both", new { @class = "form-control" })  
  49.                                 </div>  
  50.                             </div>  
  51.                         </div>  
  52.                     </div>  
  53.                 </div>  
  54.                 <div class="modal-footer">  
  55.                     <button id="btnPerformAdvancedSearch" type="button" class="btn btn-default btn-success" data-dismiss="modal">Search</button>  
  56.                     <button id="btnCancel" type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>  
  57.                 </div>  
  58.             }  
  59.         </div>  
  60.     </div>  
  61. </div>   

Final Step - Passing Advanced Search Parameters 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, 

  1. "data": function (data) {  
  2.   
  3.                                 data.FacilitySite = $("#FacilitySite").val();  
  4.                                 data.Building = $("#Building").val();  
  5.                                 data.Manufacturer = $("#Manufacturer").val();  
  6.                                 data.Status = $("#Status").val();  
  7.                             }   

Our Index View would contain following code, 

  1. <div class="row">  
  2.     <div class="col-md-12">  
  3.         <div class="panel panel-primary list-panel" id="list-panel">  
  4.             <div class="panel-heading list-panel-heading">  
  5.                 <h1 class="panel-title list-panel-title">Assets</h1>  
  6.                 <button type="button" class="btn btn-default btn-md" data-toggle="modal" data-target="#advancedSearchModal" id="advancedsearch-button">  
  7.                     <span class="glyphicon glyphicon-search" aria-hidden="true"></span> Advanced Search  
  8.                 </button>  
  9.             </div>  
  10.             <div class="panel-body">  
  11.                 <table id="assets-data-table" class="table table-striped table-bordered" style="width:100%;">  
  12.                 </table>  
  13.             </div>  
  14.         </div>  
  15.     </div>  
  16. </div>  
  17.   
  18. @Html.Action("AdvancedSearch")  
  19.   
  20. @section Scripts  
  21. {  
  22.       
  23. <script type="text/javascript">  
  24.         var assetListVM;  
  25.         $(function () {  
  26.             assetListVM = {  
  27.                 dt: null,  
  28.   
  29.                 init: function () {  
  30.                     dt = $('#assets-data-table').DataTable({  
  31.                         "serverSide": true,  
  32.                         "processing": true,  
  33.                         "ajax": {  
  34.                             "url": "@Url.Action("Get","Asset")",  
  35.                             "data": function (data) {  
  36.   
  37.                                 data.FacilitySite = $("#FacilitySite").val();  
  38.                                 data.Building = $("#Building").val();  
  39.                                 data.Manufacturer = $("#Manufacturer").val();  
  40.                                 data.Status = $("#Status").val();  
  41.                             }  
  42.                         },  
  43.                         "columns": [  
  44.                             { "title": "Bar Code", "data": "BarCode", "searchable": true },  
  45.                             { "title": "Manufacturer", "data": "Manufacturer", "searchable": true },  
  46.                             { "title": "Model", "data": "ModelNumber", "searchable": true },  
  47.                             { "title": "Building", "data": "Building", "searchable": true },  
  48.                             { "title": "Room No", "data": "RoomNo" },  
  49.                             { "title": "Quantity", "data": "Quantity" }  
  50.                         ],  
  51.                         "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],  
  52.                     });  
  53.                 },  
  54.   
  55.                 refresh: function () {  
  56.                     dt.ajax.reload();  
  57.                 }  
  58.             }  
  59.   
  60.             // Advanced Search Modal Search button click handler   
  61.             $('#btnPerformAdvancedSearch').on("click", assetListVM.refresh);  
  62.             }  
  63.   
  64.             // initialize the datatables  
  65.             assetListVM.init();  
  66.   
  67.         });  
  68.   
  69. </script>  
  70.       
  71.  }   

You can see that above, 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 pressed, 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.


Similar Articles