Click here to Skip to main content
15,860,859 members
Articles / Database Development / SQL Server
Article

Paging and Sorting on SQL Server and Oracle using Sql.Net

Rate me:
Please Sign up or sign in to vote.
4.79/5 (10 votes)
21 Feb 20055 min read 93.4K   742   36   6
This article presents an alternative solution to paging large datasets using Open Source Sql.Net library.

Introduction

The issue of paging large resultsets is well familiar to most application developers and yet still no uniform generic solution exists. The excellent article "Paging of Large Resultsets in ASP.NET" by Jasmin Muharemovic presents different techniques for pagination. The purpose of this article is to suggest a generic, database independent solution for paging and sorting data in .NET using a software component (rather than a stored procedure) to generate effective SQL.

Background

The problem

Many applications need to present data in a grid sorted by a specific column. ASP.NET's DataGrid class supports paging and sorting of the data in memory, but for this to work, all the relevant data must be retrieved and stored in the page's ViewState, which is not practical in most cases. The acceptable solution in this case is to retrieve just the data you need based on the currently selected sort column and the current page number, but neither SQL Sever nor Oracle provide built in support for such functionality.

Conclusion

Static SQL won't do, so, dynamically generated SQL is required. Basically, it means that you can not write a stored procedure with a single select statement, instead you must create an SQL statement on the fly, which will:

  • Order the query according to the current sort condition.
  • Limit the result set to the required page of data.
  • Filter the rows based on one or more conditions (parameterized queries).

The solution must be generic so it can be applied on all the queries, sort requirements and filter conditions your application will encounter.

Solution

Sql.Net contains classes which encapsulate the structure of an SQL statement, allowing the developer to create in memory representations of SQL queries and render them for a specific database (SQL Server, Oracle or MySql). Sql.Net can automatically create SQL which produces paged data and yet still supports query parameters, so executing parameterized, paged, sorted queries is as easy as executing a stored procedure.

Using Sql.Net

Generating and executing SQL statements using Sql.Net is a three step process.

  1. Create an in memory representation of your query using SelectQuery class.
  2. Render the SelectQuery instance into the SQL statement using SqlServerRenderer, OracleRenderer or MySqlRenderer.
  3. Execute the resulting statement using standard ADO.NET query execution methods.

For the purpose of the following examples, we will assume a database with tables "Orders", "Products" and "Customers" where the table "Orders" relates to "Products" and "Customers" in a logical manner.

Example 1

The following code snippet describes a simplified scenario where the source of the data is a single table and no filter is applied.

C#
IDataReader AllProducts(int pageNum, int pageSize, string orderCol, bool asc)
{
  SelectQuery query = new SelectQuery();
  query.Columns.Add(new SelectColumn("*"));
  query.FromClause.BaseTable = FromTerm.Table("products");
  query.OrderByTerms.Add(new OrderByTerm(orderCol, 
    (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));

  SqlServerRenderer renderer = new SqlServerRenderer();
  string rowCountSql = renderer.RenderRowCount(query);
  int rowCount = 
    Convert.ToInt32(new SqlCommand(rowCountSql, connection).ExecuteScalar());
  string pageSql = renderer.RenderPage(pageNum, pageSize, rowCount, query);
  IDataReader data = new SqlCommand(pageSql, connection).ExecuteReader();
  return data;
}

So, what happens here? The first part of the method defines a simple query, select * from products order by <col_name> <dir>. The second part renders and executes the query. As you have probably noticed, there are two commands being executed. The first one returns the total number of rows that the query produces and the second one returns the data itself.

Note: the rendering and execution code is the same for all examples, so the following code snippets will exclude it.

In most cases, your data comes from more than one table. You have three options to specify the source of your data.

  1. Create a VIEW in your database and pass its name to FromTerm.Table method.
  2. Pass an SQL string to FromTerm.SubQuery method.
    C#
    query.FromClause.BaseTable = 
      FromTerm.SubQuery("select productName, categoryName" + 
      " from products p inner join categories c " + 
      "on p.productId = p.categoryId");
  3. Use Sql.Net to construct your query.
    C#
    FromTerm tProducts = FromTerm.Table("products");
    FromTerm tCategories = FromTerm.Table("categories");
    
    SelectQuery query = new SelectQuery();
    query.Columns.Add(new SelectColumn("productName"));
    query.Columns.Add(new SelectColumn("categoryName"));
    query.FromClause.BaseTable = tProducts;
    query.FromClause.Join(JoinType.Inner, tProducts, 
      tCategories, "categoryId", "categoryId");
    query.OrderByTerms.Add(new OrderByTerm(orderCol, 
      (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));

Parametric queries

The need to parameterize queries is obvious. You rarely display all the data in a database table; usually, the data is filtered according to the current user or context. Common practice to parameterize queries is encapsulating them in parameterized stored procedures, but unfortunately, it is not applicable to dynamic SQL. Luckily, both SQL Server and Oracle (as well as other database engines) support parameterized queries directly so you can parameterize your paged query as you would a stored procedure:

//Create the query
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("*"));
query.FromClause.BaseTable = FromTerm.Table("products");
query.OrderByTerms.Add(new OrderByTerm(orderCol, 
  (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));

//Set filter
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
  SqlExpression.Parameter("@param1"), 
  SqlExpression.Field("productId"), 
  CompareOperator.Equal));

//Prepare renderer and SqlCommand
SqlServerRenderer renderer = new SqlServerRenderer();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Parameters.Add("@param1", categoryId);

//Render & Execute
command.CommandText = renderer.RenderRowCount(query);
int rowCount = (int)command.ExecuteScalar();
command.CommandText = renderer.RenderPage(pageNum, 
                      pageSize, rowCount, query);
IDataReader data = command.ExecuteReader();

Alternatively, you can inject the parameter value directly into a SQL query like this:

C#
//Set filter
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
  SqlExpression.Number(categoryId), 
  SqlExpression.Field("productId"), CompareOperator.Equal));

Performance

There can be three performance concerns when using Sql.Net for paging.

What kind of SQL is created and how well it performs?

Currently, Sql.Net uses the Asc-Desc technique to produce paged results. This technique is explained in the article mentioned above and provides acceptable performance when fetching the first few dozens of pages. In the future, Sql.Net is expected to support the more effective but somewhat limiting RowCount technique.

Will it not work faster when in a stored procedure?

(The following information is correct regarding SQL Server 2000, I do not have enough experience to say the same about Oracle.)

In short, no. Stored procedures provide some performance benefits when they contain static SQL because SQL Server prepares an execution plan from them once and uses it for subsequent calls. When a stored procedure contains no static SQL but rather a script which concatenates strings to produce dynamic SQL, the stored procedure has no performance benefits. Moreover, using ADO.NET and Sql.Net correctly, you can achieve similar performance that you would with a classic stored procedure. When ADO.NET executes a parameterized query, it uses the sp_executesql stored procedure which attempts to reuse the execution plan (read "Using sp_executesql" on MSDN for more information). For the execution plan to work, object names must be fully qualified. You can use SelectQuery.TableSpace property to set the table qualification prefix for all tables referenced by FromTerm objects.

Doesn't the rendering process take a lot of time and resources?

Rendering times are insignificant compared to execution times. Nevertheless, future versions of Sql.Net will provide caching for frequently rendered queries and provide some rendering performance.

Extensibility

Sql.Net is an open source library built with extensibility in mind. You can easily add support for other databases, use an alternative paging technique, or add proprietary features.

More about Sql.Net

Sql.Net is not limited and was not developed specifically to support paging. It can be utilized for any purpose which requires dynamic SQL or database independence. More information, users guide, and object model reference can be found on Sql.Net web site or on its Source Forge project page.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:41
professionalKanasz Robert25-Sep-12 22:41 
GeneralMessage Closed Pin
4-Sep-08 17:10
mayurmv4-Sep-08 17:10 
GeneralError in the generation of the sql Pin
Sk8rider1-Jun-06 3:38
Sk8rider1-Jun-06 3:38 
GeneralRe: Error in the generation of the sql Pin
Sk8rider1-Jun-06 5:41
Sk8rider1-Jun-06 5:41 
QuestionWhat about security? Pin
John Colbert1-Aug-05 8:18
John Colbert1-Aug-05 8:18 
AnswerRe: What about security? Pin
Alon Catz19-Oct-05 9:04
Alon Catz19-Oct-05 9:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.