Database Search Solution
(New Version) Search Control
Free ASP.NET Controls
ASP.NET Media Player Control
Flash Video Player Control
ASP.NET Telecommute Jobs
Free IP Location Lookup
Test .Net Regular Expressions
CSS/Table/DIV Page Layouts
Article Sites Master List
.NET Windows Forms
General .NET Framework
Accepting Credit Cards
SQL Queries For Paging In ASP.NET
Data paging is common task for ASP.NET developers. It could be very confusing if you place hundreds, thousands or even millions of records on single page. Also, your web page will load slowly and increase bandwidth. Common solution when you work with large amount of data is to provide data paging.
Simply, you show only 10, 20 or 50 records per page and provide buttons for navigation to the rest of data if user wants to see more. Some web controls, like GridView includes paging feature simple or customized. To use simple paging you only need to set AllowPaging property to true. Then, you can change some additional properties like PagerSettings.Position, PagerSettings.Mode and PageSize to suite your web application specific requirements. This paging will work fine if you don't have large tables and don't have many visitors.
Scalable Data Paging
But, if you have large table (e.g. more than a hundred pages) or you have a lot of concurrent web site visitors or you have shared web hosting with not so fast connection to Internet, shared memory etc., you should consider more efficient data paging. You need to return only one page instead to bind to complete record set and save web server resources on that way. There are different ways to achieve this, depending of your needs and type of database used.
Common questions could be:
- Do you need sorting?
- Do you need filtering (using of WHERE clause) or you can do it on server side by using a view?
- Do you need to show total number of pages or you just want to list pages forward?
Paging with dynamic SQL
Dynamic SQL. data paging is divided in two queries:
1. Find total number of records
2. Return records from selected page
You can avoid first query to get faster execution. In that case you will not have an information about how much pages you have. Your Next button on pager will simply return next 20 or 30 records , depending of page size. After last page, the record set will be empty and you can show some message to the user or show last page again but now without Next button.
Finding total number of pages
To find total number of records you can use a simple one code line like this:
[ C# ]
string SQLQuery = "SELECT COUNT(*) FROM MyTable";
[ VB.NET ]
Dim SQLQuery As String = "SELECT COUNT(*) FROM MyTable"
After you execute this query and get the number of records, you can get number of pages with this code snippet:
[ C# ]
Number of records we get from previous query
[ VB.NET ]
of records we get from previous query
How to return only records from specific page
The problem with paging is that it is hard to use one universal solution, because ANSI SQL has not standard way to limit the number of rows returned from table. There are some complex ways to do this by using JOIN or MAX keywords, often limited on some way. Fortunately, database systems like MS SQL Server, Oracle, MySQL or DB2 provide additional set of keywords that make data paging easier.
Dynamic SQL to return only selected page on SQL Server
You can get selected page with plain T-SQL by using TOP keyword and three nested SQL queries. For example, to return fifth page with 10 rows per page from Customers table ordered by column CompanyName ascending, we need SQL query like this:
SELECT * FROM
This query works fine for all pages except the last page because last page is usually smaller and contains the rest of dividing records count and page size. I created a .NET function that automates this idea and returns rows that belongs to selected page and meet the WHERE clause conditions. This function works with all versions of SQL Server.
[ C# ]
[ VB.NET ]
So, for example if we use sample Northwind database and we need to return fifth page with 10 rows per page from Customers table ordered by column CompanyName, we'll need the code:
getPageOnSqlServer("Customers", 10, 5, 10, 91, "*", "CompanyName");
Note that you must select a column for Order By clause. In this case that is "CompanyName".
Paging with row_number() function on SQL Server 2005
Things become simpler with SQL Server 2005. There is a new row_number() function which is very useful for data paging.
For example, to get third page with 10 rows per page, from table Customers, ordered by some column "CompanyName", you can use this T-SQL:
SELECT * FROM (SELECT ROW_NUMBER() OVER
So, things are much simpler with SQL Server 2005. Of course, you can add additional conditions to WHERE clause or even sort page to some other condition in outside query. According to this, it is easy now to create .NET function that automate this task:
[ C# ]
[ VB.NET ]
Security issues and recommendations in ASP.NET data paging
I must mention that this tutorial does not cover paging with stored procedures. That is very important part and will be the subject of the next tutorial. However, in both cases (dynamic sql or stored procedures) you need to devote attention to user inputs because it can be potentially dangerous for your web application. You can check every user input or use sql parameters. Using of parameters will make your queries secure and in some scenarios faster.
Building of complex WHERE clauses with various search conditions can be hardest part if you need to provide database search to your users. Because of that we developed specialized solution Search Control that makes building of WHERE clauses fun and easy. Search Control supports different search types, like all words, any word, exact phrase or boolean search expressions, works with different databases syntax, take care of security issues and much more.
comments powered by Disqus