However, I still find some web systems and websites which don’t implement a correct pagination getting all the data from database and leaving the work for the Jquery Datatable component. So, what’s the problem in that approach? I could list many of them, but let’s take the most important: 1) overflow of data on browser; 2) extensive data retrieving from database – your DBA won’t like It; 3) extensive memory consuming;
If you implement a list interface with this kind of approach, you’ll likely have a low performance system, depending on how much data your user browser might stop working for a while, if not crashes completely.
So, how can I improve this listing interface with datable using Entity Framework and get happy DBAs and users?
In this article we will focus on a simple pagination using ASP.NET MVC, Jquery Datables and Entity Framework, later on, I’ll show you how to create a generic component for extensive use in your project. That’s said, let’s get started.
Before we start coding, the most important thing about working with Jquery pagination is that It works with JSON, so every data transmitted from client to server and server to client will be in JSON format. First, we’ll create our web project on Visual Studio, I named mine as WebAppPagination. Hence I won’t host It on the Cloud, so I unchecked the option and checked the Web Application Option.
Before we start coding, I’d like to tell you that the focus on this article isn’t Architecture, so I won’t talk about design patterns and separation of concerns. Therefore, the context and repository are located on the web project, further on I’m planning to talk about design pattern and how to create a generic datatable for any functionality on your application.
First, I’ve created a folder called Data in my project, which contains Context, Repository and Entities.
As you can see, this folder has the Products DataContext, which is the Entity Framework context configuration and creation. In this class I’ve just configured how my Context will create my table telling It to set the Id property as Key and string properties should be varchar, intead nvarchar, this way you DBA won’t be Mad.
The IPagination Interface will define the pagination method should be assigned, It’s a simple interface for this article, but It has the role of defining a standard for pagination methods.
And now where the magic starts to happen, the ProductRepository (which should have something more than a method for listing) implements the IPagination Interface and get the data.
As you can see, this method is very simple where the most important feature is at the end, where we call the Skip and Take methods:
- Skip, as the name says, is used to tell the Entity Framework how many rows should be skipped when getting data from database. Basically your code will process the current page * rows per page.
- Take is the responsible to get the amount of rows each page shows to your user.
But, Why IQueryable instead IEnumerable? When you run the SQL Profiler trying to improve your database performance, you see the difference between these Interfaces. While IQueryable only executes commands on database after you decide to bring your results to memory (Convert to List, proceed an operation or show to your user), the IEnumerable interface works on memory, so if you try to paginate using IEnumerable instead of IQueryable, the EF will execute the very first query on database and get all the data for work on memory.
Our ProductController has only one View, which is the Index View, where we’ll work. The default, paremeterless Index is the method responsible to return the proper View containing the user interface.
The second method process the requests from the datable and returns the data in JSON format, which is formatted with Newtonsoft JsonConvert and serialized as string.
In the first portion we initialize the datatable get the Table by it’s class, then we set the defaults:
- Processing: specify whether sould show the processing message;
- serverSide: specify that our table will work with server side and not only in client, that’s a important property to set.
- Ajax: Here you configure your ajax setting, such as the url to process, the type (POST or GET). Also you can configure some other configurations, like additional filters if you have on your page and so on;
- Columns: The columns (properties) that will be retrieved from the Server.
- ColumnDefs: This a special portion because you can customize your columns, in this case we set how the actions buttons (Edit and Delete) will be presented.
- pagingType: How will be the paging, here we use full_numbers, but can be only next, previous among others.
The Table.On is a nice feature for datables, because you have some events to work with, here we use the draw event, which is triggered when the table finishes it’s draw of data on the screen. This event is very useful when you have configurations to apply after the draw, like components initialization or buttons events. If you look closely you can see that we set the click event for Edit and Delete buttons.
The getData function works for verification because if you already have a DataTable attached to your table and try to reload data by attaching again your script crashes. I coded this function to show an useful way to reload data when you Delete, Create or apply some custom filter to your data, if you perform any of these operations, just call the getData function.
So, let’s see if all this stuff really work.
As you can see, all the data was successfully retrieved and the table correctly manage It’s pagination on user interface. Before we go, let’s take a look on what’s going on between the client and server.
At the right side you can see all the paremeters sent to the server by the DataTable and which you can work with, order parameters, search and so on. Now, what’s coming back from the server? As you can see we have a JSON object with some basic information and the data collection found on database.
Well, that’s it, with jquery datatables is very easy to work with pagination using ajax and therefore, transmitting little amount of data between server and client, making it light and fast.