使用 Sql.Net 在 SQL Server 和 Oracle 中进行分页和排序

• 下载演示项目 - 19.3 Kb
• 从 SourceForge 下载 Sql.Net

引言

分页大型结果集的问题对于大多数应用程序开发人员来说都很熟悉，但仍然没有一个统一的通用解决方案。Jasmin Muharemovic 的优秀文章“ASP.NET 中的分页大型结果集”介绍了不同的分页技术。本文的目的是提出一种使用软件组件（而不是存储过程）生成有效 SQL 的、通用的、与数据库无关的分页和排序数据解决方案。

背景

问题所在

许多应用程序需要以网格形式显示数据，并按特定列排序。ASP.NET 的 DataGrid 类支持内存中的数据分页和排序，但要实现此功能，所有相关数据都必须检索并存储在页面的 ViewState 中，而在大多数情况下这是不切实际的。在这种情况下，可接受的解决方案是仅检索基于当前选定的排序列和当前页码所需的数据，但 SQL Server 和 Oracle 均未内置支持此类功能。

结论

静态 SQL 无法胜任，因此需要动态生成的 SQL。基本上，这意味着你不能编写一个带有单个 select 语句的存储过程，而是必须“即时”创建一个 SQL 语句，该语句将

• 根据当前排序条件对查询进行排序。
• 将结果集限制为所需数据页。
• 根据一个或多个条件筛选行（参数化查询）。

该解决方案必须是通用的，因此可以应用于你的应用程序将遇到的所有查询、排序要求和筛选条件。

解决方案

Sql.Net 包含封装 SQL 语句结构的类，允许开发人员在内存中创建 SQL 查询的表示形式，并为特定数据库（SQL Server、Oracle 或 MySql）生成它们。Sql.Net 可以自动创建生成分页数据的 SQL，同时仍然支持查询参数，因此执行参数化、分页、排序的查询就像执行存储过程一样简单。

使用 Sql.Net

使用 Sql.Net 生成和执行 SQL 语句是一个三步过程。

1. 使用 SelectQuery 类创建查询的内存表示。
2. 使用 SqlServerRenderer、OracleRenderer 或 MySqlRenderer 将 SelectQuery 实例呈现为 SQL 语句。
3. 使用标准的 ADO.NET 查询执行方法执行生成的语句。

在以下示例中，我们将假设一个数据库，其中包含“Orders”、“Products”和“Customers”表，并且“Orders”表以逻辑方式关联到“Products”和“Customers”。

示例 1

以下代码片段描述了一个简化场景，其中数据源是单个表，并且没有应用筛选器。

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;
}

那么，这里发生了什么？方法的第一个部分定义了一个简单的查询：select * from products order by <col_name> <dir>。第二部分呈现并执行了查询。正如你可能已经注意到的，执行了两个命令。第一个返回查询产生的总行数，第二个返回数据本身。

注意：呈现和执行代码对于所有示例都相同，因此以下代码片段将不包含它。

在大多数情况下，你的数据来自多个表。你有三种选项来指定数据源。

1. 在你的数据库中创建一个 VIEW，并将它的名称传递给 FromTerm.Table 方法。
2. 将 SQL 字符串传递给 FromTerm.SubQuery 方法。

   query.FromClause.BaseTable = 
     FromTerm.SubQuery("select productName, categoryName" + 
     " from products p inner join categories c " + 
     "on p.productId = p.categoryId");

3. 使用 Sql.Net 来构造你的查询。

   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));

参数化查询

参数化查询的需求显而易见。你很少显示数据库表中的所有数据；通常，数据会根据当前用户或上下文进行筛选。参数化查询的常见做法是将它们封装在参数化存储过程中，但不幸的是，这不适用于动态 SQL。幸运的是，SQL Server 和 Oracle（以及其他数据库引擎）都直接支持参数化查询，因此你可以像参数化存储过程一样参数化你的分页查询。

//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();

或者，你可以像这样直接将参数值注入 SQL 查询中

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

性能

使用 Sql.Net 进行分页时，可能会有三个性能顾虑。

创建了什么样的 SQL，它的性能如何？

目前，Sql.Net 使用 Asc-Desc 技术来生成分页结果。此技术在上述文章中有所解释，并在获取前几十页时提供可接受的性能。未来，Sql.Net 预计将支持更有效但有些限制的 RowCount 技术。

在存储过程中不会更快吗？

（以下信息对于 SQL Server 2000 是正确的，我没有足够的经验来说明 Oracle 的情况。）

简而言之，不会。存储过程在包含静态 SQL 时提供了一些性能优势，因为 SQL Server 会为它们准备一个执行计划，并为后续调用重用该计划。当存储过程不包含静态 SQL，而是包含一个连接字符串以生成动态 SQL 的脚本时，存储过程没有性能优势。此外，使用 ADO.NET 和 Sql.Net 正确操作，你可以获得与传统存储过程相似的性能。当 ADO.NET 执行参数化查询时，它会使用 sp_executesql 存储过程，该过程会尝试重用执行计划（请阅读 MSDN 上关于“使用 sp_executesql”的内容以获取更多信息）。为了使执行计划正常工作，对象名称必须完全限定。你可以使用 SelectQuery.TableSpace 属性来设置所有由 FromTerm 对象引用的表的表限定前缀。

呈现过程不会占用大量时间和资源吗？

呈现时间与执行时间相比微不足道。尽管如此，Sql.Net 的未来版本将提供常用查询的缓存，并提高某些呈现性能。

可扩展性

Sql.Net 是一个开源库，以可扩展性为设计理念。你可以轻松地添加对其他数据库的支持，使用替代的分页技术，或添加专有功能。

更多关于 Sql.Net

Sql.Net 并非仅限于分页，也不是专门为支持分页而开发的。它可以用于任何需要动态 SQL 或数据库独立性的目的。更多信息、用户指南和对象模型参考可以在 Sql.Net 网站或其 Source Forge 项目页面上找到。