Custom Search in jQuery Datatable - Part 3

summary of this tutorial in this tutorial we will discuss how we can implement custom search or filtering in jQuery data table using asp.net MVC so this is the output of custom search inclined site here we have added additional row for individual column search one search control for each of these columns we can search columns like name position age and salary using text boxes like this so here we have entered on down to search inside jQuery data table this is the output of custom search in server side so here we have added search box outside the jQuery data table what's up YouTube welcome to dotnet mode this is my third video indices of jeogori data table and espied or net MVC integration tutorial in this tutorial I will show you how to implement custom search or custom filtering in Decorah data table using asp.net MVC application before starting this video tutorial I would like to ask you a favor if you found this video helpful please thumbs up this video and for more awesome videos like this please do subscribe to this channel got AdMob without further ado let's get started this is my wishes studio 2013 can we have a fresh asp.net MVC application I have removed default controllers and related model classes and use from this project before continuing with this project let me show you my database here here we have a database employee DB inside that we have a tab employee it contains columns employee ID name position office age and salary so it is populated with 57 goes so this is the same DB that we have used in Part one and Part two now I am going to import this database entity model into this application for that right click on models then add new item select data from the left panel then select a do two or net entity data model then name the model here I will name it as VB models click on add select generate from database then click on next click on new connection we have to type the server details here Oh Metis local backward slash SQL e 2000 toll then from this drop-down we have to select the database I will select employee DB then click on OK now I want to change this entity connection settings in web config as DB model after creating this entity model there will be a class with this name DB model and we want to create an object of this class in order to interact with database click on next in this step we have to select database objects that we want to include in this entity model so I will select the tabla employees and click on finish so here we have added entity model DB models on the models folder so here is the diagrammatic representation of the model now I am going to create a controller for M glowing right click on controllers then add controller then select MVC 5 controller empty click on add I will name this controller as M blowy controller click on add so here we have the employ controller inside that we have default action index so I am going to set this employee controller as the default controller inside the to config file replace home with employee back to the controller M GUI now I am going to create a view for this index action I click here then click on add keep the view name as it is index MD template use layout page click on add so here we have the index view for this view this will be the default layout page underscore layout dot CSS HTML and I'm going to remove this header from this layout page and I want to change this footer here as custom feeds or filtering in jQuery data tavern now back to the view let me change this title assembly employee list I will change this header as employees now I'm going to create HTML double heel tab ID will be employee tab inside that I will add T hex with a single row inside that I will add th for each column name then position then th for office then th for age finally th for salary in order to show this column headers in photo let me copy this D hat from here and placing here change T head to T food that's it in order to activate this HTML table as jQuery data tab we have to add few references first of all we have to add this stat sheet pot for that link will paste the link here now in order to write scripts I am going to use this section script for that we can start like this section script I'm going to add scrip reference SRC let me complete this part for jQuery data tab JavaScript files and PC here now inside this script tag we can write custom script course first of all I will create the events document dot ready function dot Freddy inside that we will activate this tab for that let me copy this ID not activate this tab we can do this just call the function that doubled inside this function we have to pass the settings for configuring the standard double first of all I will type the configuration of form we will discuss each of them at the X is equal to first so for URL is equal to four slash employ forward slash get a list then type type will be get then data type but that time will be Jason after attacks we have columns columns for that we will pass an hour here data is equal to name and we have to pass name property here as name like this we have to pass form of parameters for these columns for that let me copy this line and pacing below that four times changing name to position then office then page finally salary first of all here we have specified a Jack's request not to receive that asked to populate this HTML tout this is the URL of the request employee controller then get list action method of the type gets and it should return that as in the format JSON then we have columns property it will tell the data to be binded with each of these columns so first column name will be binded with data property inside this return jessa4 position column it will be position property from this this and that and so on let me create this get list action back there inside employee controller for that we can do this public action those cells get list by default action methods are of the type get in order to receive data from SQL server we are going to use class employee inside the entity model this class so I will use this namespace let me copy that and we have to import their namespace here using the using statement inside that I will create an object of DB model as DB is equal to new DB model outside this you see statement let me create a list of employee I'm glowy as EMP lists and we will fill this ear b-list with the employee rose from the table DB dot employees to list you want to convert this into employees finally we have to return this as a decent data for that we can do this this some new data is equal to MP lists and we have to set the jason request behavior as allocated that's it let me want this for that hold ctrl then press f5 so here we have populated this decade with 57 goes from the database now I am going to add a class for this double for that go to index view then add a CSS class here class as display saving and reloading this page so this is the default theme of jQuery data table instead of this team I want to use bootstrap theme in jQuery data table for that you can go to the official website then examples below that you can see the indication and details of bootstrap for first of all in the HTML tab we have to use these classes let me copy this and paste here in the first video we have discussed how we can use different themes in Decorah data tab so we have to add these daiquiri references first of all we have to add jQuery reference it is there in the layout page then we have to add jQuery dot that dot mean dot test it is already there now we need to add one more script file s RC let me copy this link from here and paste it here now we have to add few CSS reference first of all we have to add bootstrap for orphans then data double dot bootstrap for dot min dot CSS so we don't need this reference let me copy this and paste in below that and we need to replace these paths in the copied lines by default SB rodent MVC templates comes with Musab so inside the layout page that you can see a bundle for CSS let me open this bundle configure here so you can see the bundle can then forward slash CSS so I'm going to comment this bootstrap since we have added latest version of smoothsub inside this index view here with this solution reload this page so here is the boot shop team in jQuery data tab first of all we will implement client side surfing or filtering in this jQuery data table in this jQuery data table here we have a double purpose such textbox with this search box we can type our keyword something landed here and it will bring all of the rows that contains search keyword London in any of these column so in this degree data table we are going to do custom search for each of these columns so it will look like this we will add a search box for each of these columns here and you can search name column with this search box and you can search position column with this search box first of all we have to remove these general purpose search box for that we have to edit the table settings dorm is equal to you let me say this and reloading this page so here we have removed general-purpose search box so this Dom property can be used to position elements inside the occurring data table so it is specified the official website go to examples then under basic initialization there you can see the positioning so here we have the details so here we have specified in the top part we need length or page drop down then we need R that means processing indication then we have doubled in the bottom part we have I stands for information showing 1 to 10 or 57 entries then we have P it stands for paging then finally we have a clear tag now we are going to add individual column search box in the equity data table for that we will add additional row inside P head part for that I am going to depend the even F and in it complete so this is an event inside the quarry data tab so this event will be triggered after populating data from server into jQuery data tab so here we are going to declare a function function function parameters will be all settings then decent data so this variable dicen will contain data or decent data that is returned from the server outside this I am going to create a new function function function name will be add search control and we have a parameter this and data let me call this function inside the even in it complete and you need pass the debt ease and data first of all I'm going to append a new row by copying this X single but that I'm going to do this let me copy double ID from here and paste in here inside the T head part we are going to happen a new row for that we will copy or clone existing row tr4 then call the function home saving and reloading this page so here we have copied first row into the second row inside T at spot now I want to replace these th element with search box for that I will I prayed through each of these th elements let me copy this and paste here and we are going to I trail to second book index will be 1 th each function we have a parameter index that we are going to repeat these th elements so that we can do this this this means th element dog replace with we will replace them with new th element it contains a search box or text box so type will be 'text saving and reloading this page now we have added search boxes here I want to set this search box placeholder as name switch position search office and so on for that we can do this place folder is equal to thirds so we are going to concat these texts this dot HTML saving and reloading this page that's it so these first text boxes we have to bind key up even in order to search this vehicle data tab first of all I am going to declare a variable to store this input element work search control is equal to let me copy this and paste here inside th then TR then th you can we have to pass the index here column index so that we can pass from the index variable inside the loop then input control input control so inside this variable we have this input control now we get at key up event for this control search control own keep up we'll add a function here inside this event we will search inside the Decca data tab for that we have to save this jQuery object inside a variable Y EMP table and we to move this function inside this ready function in order to tip the jQuery that a double variable EMP tab let me paste it here and we can do this EMP double dot : then column index then call the function search inside then we have to pass the search keyword that we can retrieve from this variable dot but finally call the function draw saving and reloading this page so if I type London here it will bring all of rows that convince lender now if I type software engineer in position search box it will bring all of the employees in office London with position software ending you now I am going to repeat this office search box with a drop down it will be populated with distinct office from this tab for that I am going to add if close here if index not equal to two then we will be space the th element with textbox otherwise we will replace it with drop down for office first of all I will declare a variable for office drop down office drop down is equal to we can declare dropdowns in theory like this select backward slash now I am going to append first default item for this drop-down a punch in order to upend options into drop-down we can do this option then at build value will be empty for the first element then text will be select office now we are going to find distinct element from the quorum office and we will add them into this table first of all I will type the code after that I will explain each line you want to replace these values and text with element or coffees first of all here we have declared an RA office then we have I cratered through the Jason data you can see the Jason response from the server that we have passed here inside this data property we have sent that as for the table so we will I trained to Jason dot data property so inside this variable index we will have row index inside element we have data for each columns inside the table if close we will check whether current office value is the in this office array or not if it is not the we will push it into the array and we will add the value into OFI strata now we are going to replace the th element with this strata for that we can do this this door replace whip teh then we will contact it with the outer HTML of this drop-down for that we can do this copy this and paste in here then property then we need to add two outer HTML and we need to contact the costing 40 th element now we need to save this select control inside a variable like this so that'll be copy this and place you here instead of input control here we have select control and we are going to add change even for this table on seems we will add a function here inside that we will do this search operation copy and PC here sorry here we have misspelled the closing of T its elements save and we wrote this page so here we have added office drop down successfully now if I select San Francisco it will retrieve all the rows that contains San Francisco for search boxes if I type a search keyword here it will retrieve all the data that contains search keyword anywhere in the column we cannot do that for pronouns there should be an exact match with selected prop down item and column value so we will go for exact match in the equity data table for that I will use the regular expression if selected value is empty then we will search for empty string otherwise we will go for regular expression let me concat this selected item value here and finally dollar symbol then - and force so this second parameter says this first parameter is error glove expression and third parameter says we need an exact match saving and reloading this so nothing will change so here we have done custom search we drop down in jQuery data table so that's it guys here we have completed with custom filtering in jQuery data table in client-side next we are going to implement jQuery data table customs is in server-side please watch part 2 that is previous video for server-side processing in jQuery data table that we had a detailed discussion on server-side processing so in this tutorial I am going to use the demo project from part 2 to demonstrate custom filtering in server-side so this is the number project from server-side let me open that in this project we have used same database employ DV here we have specified server-side property to true so we have to do operations like sorting paging and filtering in server-side here we have done the filtering then sorting and page so in this tutorial we are going to add custom filtering in server-side let me run this application for that hold control and press f5 right now it is fetching data from sir so in this demo project we have used semantic UI team injector a data table of the operations sorting paging and searching Eastern at server side so in this J query data table we are going to add custom search for that I am going to copy course from custom filtering in client side let me copy these properties door and this event and pasting inside this server side project and we have to add this function add search Kongo let me copy that copy and pasting here so with this drawn property we have removed general-purpose search box with this unit complete event we will add search controls unlike previous project I am going to add a new table to add search controls for that we can do this let me copy this table header from here and paste in here and we have to change the ID as search tab and we need the close tag for the Tavor and inside the function switch control we have to replace these IDs first table let me copy that and pushing here so here we have copied employ table first row into the search table and we have to change this ID here there is a small difference in behind it let me paste here and here and we want to I prayed through search table for sauce so index will be 0 and you want to replace these th elements with red candles if index not equal to 2 then we will replace these th elements with search box here we have replaced the th element with a search box and then we have declared a variable to store this element so that we have to change the table ID here and index will be 0 then binding key up given to the search box and we have to stow this jakhary data table initialization into a variable like this VAR e and pitiable that's it then then we have declared a variable to store office total added first default element then an away office then we want to add distinct element into this drop-down that we have done here then we want to replace that eh element with the propound HTML then instead of employee tab we need to add search travel index will be fuel yes that's it let me save this and reload this page something went wrong back to the application if we have missed tea head port saving and reloading this so here we have added such elements as spring so in server-side processing we have to understand few points here so let me call this search function followed by draw function it will rise suicide request same in the case of drop-down also here we have called this search function and draw function we don't want to do this regular expression here we can do that in the suicide so I will pass the selected value as it is saving now back to the employee action method as I told you in part 2 values from the daiquiri data tab will be posted with these keys like start length search value so and so so in this project we don't need this general search value instead we will do the custom filtering here custom filtering constantly filtering so these keys can be found in the official website manual go to the website data tab or store net then go to manual and server-side processing there you can see the parameters that sent from these jQuery data table for this custom search we need this parameter columns I search value if I replace Y with 0 then it will give the first keyword inside this textbox if I replace Y with 1 I will get this keyword inside this position X box for that we can do this if not equal to string is null or empty request Ric cause let me copy this parameter from here and pasting here changing index to 0 if it is not equal to null or empty then we will do the search operation inside this this EMP list we will have all of the employee list so EMP list is equal to TM be list dot words X such that X dot name to lower contains if you need to pass the search keyword and placing here to lower and finally you have to convert it into 10 boilies employee we don't need this curly braces here since we have a single draw inside this it goes and we have to do this for remaining 4 columns for that any comedies and basing below that four times for the second column column index will be one instead of instead of name we have position for the third column index will be two corresponding property is office then fourth column index will be free it is a we want to convert that into string then we have last one it is for salary salary you want to convert that into string build this solution so here you can see that these fifth boxes are working right now if I select Tokyo here it should return employees from Tokyo keys so here we have done custom search using search box outside the jQuery data tab in the next video we will discuss fix-it columns in jQuery data table like this you can fix leftmost or right most columns in jQuery data table and you can freeze this column header here along with that we will discuss column visibility in jQuery data table you can hide these columns when you have a lot of columns that would be helpful if you hide unwanted columns like start date is we can do this in jQuery data table so these two topics will be covered in next video if you found this video helpful please thumbs up this video and for more hours of videos like this please be subscribed to this channel dotnet mob we can download these two projects for client side and server side can be downloaded from the link given below in video description along with DB script if you have a doubt or feedback please comment below in comment box please like and share this video with your friends and colleagues so that they can benefit from this have a nice day bye

Channel: CodAffection

Views: 11,921

Keywords: custom search in jQuery datatable, jQuery datatable custom filtering, datatable custom search in client side, individual column search in jQuery datatable, Search jQuery Datatable Using DropDownList, Datatable Custom Search inServer Side, jquery datatable and asp.net mvc integration, using SQL server database, custom filtering with jquery datatable, jQuery Datatable in C# asp.net mvc, jQuery datatable plugin tutorial, remove search box in datatable plugin, CodAffection

Id: UOsIdG-S6bE

Length: 41min 2sec (2462 seconds)

Published: Sun Jul 30 2017