jQuery datatables get data from database table

this is part one zero six of jQuery tutorial in this video we'll discuss how to use database data with jQuery data tables plugin in part one zero five of this video series we had an introduction to jQuery data tables plugin in that video all the data was hard-coded on the page in this video we'll discuss how to retrieve data from a database table like this and then use that data with jQuery data tables plugin there are several ways to load data into data tables if the data set is small then we can load all data at once and all the processing that is performing search sort paging etc can be done on the client side on the other hand if the data set is huge then it does not make any sense to load all data at once from a performance standpoint in that case we load a subset of data and all the processing that is paging sorting searching etc will be handled by the server we'll discuss that in a later video session in this video we'll discuss how to load all data at once and then perform you know all the processing that is searching paging sorting etc on the client side so the first step here is to create this database table TBL employee which I have already done and here we have the create table script and I have also created a stored procedure which is going to basically retrieve all the rows and columns from this table TBL employees so at the moment in this table we have got 14 rows so first we need to create the table and the stored procedure so now let's flip to visual studio here I have an asp.net web application project within the web config file I have a connection string that is pointing to our sample DB database so the next step is to create an employee class so let's add a new class file to a project and let's call this employee dot cs and this class is going to contain several properties which are going to correspond to these columns in the table and in the interest of time I've already created a type the required code so let's copy those properties from here and paste them so if you look at the properties we've got ID name last name gender of the employee job title the website URL if they have a website their annual salary and their hire date okay so the next step is to add a web service to a project so basically this is the web service that is going to retrieve data from our database table so make sure you select the web and we want to add a web service and let's call this employee service and we want this web service to be callable from the JavaScript so I'm going to uncomment this attribute right here and we also need some area dot nickname spaces in the interest of time I have already copied I mean typed the namespaces that we need so let's copy and paste them right here and let's go ahead and change the name of this method to a meaningful name so if we want to get employees so I'm going to call this get employees and I'm going to change the return type of the function to white because we are not going to return anything we are going to convert the list of employees to a JSON array and then we will write that to the response stream so within the function we want a straightforward ad or dotted code to call the stored procedure and retrieve the data again in the interest of time I have already typed the required area dotnet code so I'm going to copy that from our notepad and paste it within our get employees function and if I look at this code again it's straightforward we are reading the connection string from web config file we are creating a list of employee object and we are creating a single connection object here and then we are building a sequel command object using this object we want to execute this stored procedure SP get employees since that is a stored procedure we have to tell that to the command object and then we are opening the connection executing the command and whatever rules we have we are looping through each row and while we are looking to be creating an employee object and populating all its properties ID first name last name gender etc and then we are adding that employee object to the list that we have created here and finally we are passing that Liz to the serialized method of the JavaScript serializer class which is going to serialize that list of employee objects to a JSON array and then we write it to the parent response stream so let's go ahead and quickly test our web service so we should have one web method available and when we click on that web method when we invoke that web method we should get all the employees in a JSON format okay so this is the data that we want to use with jQuery data tables plug-in so on the web page right here on our web form what I have done is I have included a table here I've given it an ID data table and within that we have the head section and we have got the headers ID first name last name gender etc ok so we have all the headers that's all I have you know as far as the HTML is concerned a table with P header section with all the headers ok and we also have the ready function wired up within the script section now what do we want to do we want to call this web service and use that data with jQuery data tables plugin so I'm going to use our Ajax function and let's go ahead and specify our options so the first option is going to be the URL that we want to call so that URL is going to be our employee service dot ASM X and within that we have a function and the name of the function is get employees so that's the function that we want to call and we want to issue a post request so I'm going to specify the method as post and the type of data that we are expecting back from the server is a JSON and then the request successfully completes this is the callback function that gets called and whatever data that we get from the server that will be passed to this parameter so what do we want to do with that data so that's our data which we want to use as the source for data tables ok so first of all let's go ahead and find a table here so the idea of the table is data table so let's find that using the jQuery ID selector so within our success function so this is the function that is called after the Ajax request is complete at that point we have the data that we want to use with jQuery data tables plug-in so I'm finding the table element here using its ID and on that I'm going to call data table function now we are going to specify the options for data table so the first thing that I want to specify is the data so the data option the data is going to be in this parameter right here it contains all our data so I'm going to pass that as it is as the value for data option okay and another thing that I'm going to specify is you know the columns for our data table so I'm going to specify columns and in the interest of time I have again typed all the column names just to speed things up so within data so let's format this a bit so within data we've got these columns or these properties first name you know ID first name last name gender job title website salary and hire date so the order of these columns right here match the order that we have right here okay so now with these changes let's save it and run the web form so when the page loads look at that all of a sudden we have all the database data we have all the columns here and look at the header column names they match with what we have specified right here within the header section ok and you know for each header the data is going to come from these respective columns right here okay and at the moment if you look at the column header you know the column name that's present only within the header if you also want that call name in the footer what you can do is include a footer for your table so just like table head I'm going to include table footer and I'm going to make a copy of this okay so let's see what changes and let's reload this and now we should get column names even in the footer okay and let's also get in our boarder around this table and we have seen a small trick in the previous video session so what I'm going to do here is included development and I'm going to specify the style attribute so basically I'm going to set a border to one pixel solid black and I'm going to set padding to three pixels and let's set weight to something around 1200 pixels for example okay and let's move this closing development to the end of the page all right so let's save our changes reload this page and we should get a nice little border now now if you look at the higher date column here look at that let's not display it as a date okay so that's adjacent date string so we want to convert that to a proper date let's say we want to display the date in maybe ddmm yyy so we want first date and then month and then the four digits here so we want to convert this JSON date string to a proper date format so for that what I'm going to do is within our column section in our jQuery code so here we have the higher date so what I'm going to do is specify additional options here notice this is a JavaScript object so we can specify multiple options here so I'm going to specify a render so I'm going to associate a function with this so then that column of data is being rendered what we want is we want to format the data so I'm going to call this function and the data of the column will be passed to this function so I'm going to give it a meaningful name I'm going to call it jason date okay so you can give it any name that you so what do we want to do with that now if you look at what we have here notice that the date string actually starts from here right and if you look at the number of characters there it's like four characters in the word date we have a closing parenthesis 5 and this 4 / 6 so actually since the index starts at 0 so the date string starts from 6th position so what I'm going to do here is on this JSON date string I'm going to call javascript substring function and I'm going to ask it to return me all the characters from the sixth index position and I want to convert that to an integer so I'm going to use parse int function so convert that string into an integer and I'm going to use the date function so new date and to that we are going to pass that integer so this is going to convert that to a JavaScript date so I'm going to create a variable and store that date within that so now what we can do let's create another variable let's call month and from the date object we can use get month function and this is going to return as the month okay and in JavaScript months usually start from 0 so but in reality months start from 1 so I'm going to increase month whatever value that we have a four month by one okay so that's going to give us the proper number and what do we want to return from here we want to return date dot get date so that's going to give us the date and two that we are going to append in our forward slash and then we want month so we have that in a variable and to that I'm going to append another four slash and then we want here so date object dot I have this function get folia which is going to return us all the four digits in a year so we have date month and year so let's save all these changes reload this page and look at what's going to happen so now we have date month Ania okay and if you look at salary for example here you know again we want salary to have that currency symbol let's say we want dollar next to every salary amount that we have here again we can do something similar so let's go ahead and hook up render event handler so render and we want to call this function and let's pass salary to the render function and what we are going to do we are going to return dollar symbol and to that just append the value of salary that's that so let's save our changes reload this page and notice that now we have a currency symbol okay and if you look at the website here at the moment look at that it's a URL but we cannot click on that URL we want to make that clickable but before that you know look at this all the columns now are searchable you know look at that they are sortable paging is enabled and they are searchable as well now it doesn't really make sense for website to be sortable right so we want to remove that sorting on this website column and another thing i don't want to include this column data in search so for example if i type presume here look at that that word pra is present in pledging techcom so and that's why that row is included but i don't want to include the data of this column in search okay so i want to exclude it and in order to do that what you can do is here is our website column so what i'm going to do here in addition to you know what we already have I'm going to say searchable false so this is going to exclude this column data from the search and similarly we don't want this to be sortable so I'm going to set sortable to false as well so this is going to exclude that from B sortable so look at this when we reload that notice that we don't have those two triangles that I cannot sort this and similarly if I type regime for example look at that it says no rows found because no the column has that three letters in it okay so it's excluded from search and sorting and we want to make this clickable so let's go ahead and associate a render function so render and let's associate an event handler and let's pause the website okay so what do we want to do we want to return an anchor element right so that's what is clickable so I'm going to include that in a single code so href equals two that we are going to append whatever we have in this because that's the URL that we want to navigate to and then we want to close that angle bracket and to that you know the text within you know clickable hyperlink should be the URL itself and then finally close the anchor element okay so let's save the changes and reload this page and look at this you know now it's clickable when I click on for example this you are a look at that it turns to a hand symbol when I click on that it navigates me to presume techcom and for some reason if you don't want to live you know display the full URL let's say I want to display only 10 characters you know because I don't want the full URL there if it if the URL length is like 100 characters I don't want to display all those hundred characters there so to do that what you can actually do is we can use this substring so website dot substring and I'm basically going to say start from 0 at character and give me or you know the first 10 characters and to that probably I want to append dot dot dot indicating that there are more characters which are not visible so let's save the changes and reload this page and look at that now we only see 10 ters okay but look at this dose which doesn't have a URL you know it displays this dot dot underscore we don't want that let's say for any developer who doesn't have a website we want to display n/a not applicable if that's the case we can have a quick check here if not website okay in that case we want to return n/a not applicable else we want to return this strain okay so let's save our changes reload this page and now look at this for those developers who doesn't have a website it displays n/a and for those people who have a website it displays the first ten characters and dot dot and when we click on any of these URLs we should be able to navigate to their respective websites okay and for some reason let's say on the data table itself if you want to disable paging on all the I mean at the data table level here you can specify the options so you can say paging and you can set that to false so it's going to turn off all you know the paging on the entire data table so let's reload this look at that we don't have paging now similarly if you don't want sorting you can simply say sort and you can set that to false so let's save the changes reload that at the moment look at this it's sortable so when I reload it now look at that I cannot click on any column and it's not suitable similarly if you don't want searching capability again you can use searching and you can set that to false so the moment we reload this notice that the search box disappears if you want to turn them back just put put them back to true okay so let's include all the features and reload the page and look at this now on the first page we've got around and rose and then when I go to the second page we only have in a full rose and look at what happened to the height it is all of a sudden radios let's say we want to fix it with for the table what you can do is you can use scroll Y option and I'm going to set this to 200 pixels okay so set the height of the table to 200 pixels and beyond that give me a scroll bar if if you know if there are more rows to be displayed so look at this now with that option I get a scroll bar now so I can lead up see the first 10 records and now using scroll and when I go to the second page look at that I have the same height okay so anytime you want to fix the height of your data table you can use the scroll Y option so here is you know the output thank you for listening and have a great day

Channel: kudvenkat

Views: 249,289

Keywords: jquery datatable format date column, jquery datatable format hyperlink column, jquery datatable height scroll, jquery datatable disable sorting, jquery datatable disable pagination, jquery datatable hide search text box

Id: TgD24a9gxXw

Length: 21min 4sec (1264 seconds)

Published: Sun Aug 02 2015