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

• Download demo project - 19.3 Kb
• Download Sql.Net from SourceForge

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.

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.

   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.

   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:

//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.