时间戳

首页 > 解决方案 > SQL Server 2008 上的 ServiceStack ORMLite 分页

sql-server - SQL Server 2008 上的 ServiceStack ORMLite 分页

问题描述

我有一个现有的 SQL Server 2008 数据库，其中包含许多视图、存储过程和函数。

我希望能够SELECT从这些 SQL 函数之一获取数据并限制它在分页场景中返回的行数。

我尝试使用.Selectwith.Skip和.Take如下：

        public IEnumerable<Product> CallSqlFunction_dbo_Search_Products_View(int clientId,
            string environmentCode,
            int sessionId)
        {
            IEnumerable<Product> results;

            using (var db = _dbConnectionFactory.Open())
            {
                results = db.Select<Product>(@"
                    SELECT
                        * 
                    FROM 
                        [dbo].[Search_Products_View]
                        (
                            @pClientID, 
                            @pEnvironmentCode,
                            @pSessionId
                        )", new
                    {
                        pClientID = clientId,
                        pEnvironmentCode = environmentCode,
                        pSessionId = sessionId
                    })
                    .Skip(0)
                    .Take(1000);

                db.Close();
            }

            return results;
        }

这将生成以下 SQL，该 SQL 在 SQL Server 上执行。

exec sp_executesql N'
                    SELECT
                        * 
                    FROM 
                        [dbo].[Search_Products_View]
                        (
                            @pClientID, 
                            @pEnvironmentCode,
                            @pSessionId
                        )',N'@pClientID int,@pEnvironmentCode varchar(8000),@pSessionId int',@pClientID=0,@pEnvironmentCode='LIVE',@pSessionId=12345

这意味着此查询返回 134,000 个产品，而不是我期望的 1000 个产品的第一页。一旦 SQL Server 返回 134,000 行，就会在 API 服务器上进行分页。

是否可以使用 ORMLite 以便我可以在查询中生成类似于以下内容的分页：

exec sp_executesql N'
SELECT
    [t1].*
FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY [t0].[ProductId], [t0].[ProductName])
    FROM
        [dbo].[Search_Products_View](@pClientId, @pEnvironmentCode, @pSessionId) AS [t0]
    WHERE
        (LOWER([t0].[ProductStatus]) = @pProductStatus1) OR (LOWER([t0].[ProductStatus]) = @pProductStatus2) OR (LOWER([t0].[ProductStatus]) = @pProductStatus3)
    ) AS [t1]
WHERE 
    [t1].[ROW_NUMBER] BETWEEN @pPageNumber + 1 AND @pPageNumber + @pNumberOfRowsPerPage
ORDER BY [t1].[ROW_NUMBER]',
N'@pClientId decimal(9,0),@pEnvironmentCode char(3),@pSessionId decimal(9,0),@pProductStatus1 varchar(8000),@pProductStatus2 varchar(8000),@pProductStatus3 varchar(8000),@pPageNumber int,@pNumberOfRowsPerPage int',
@pClientId=0,@pEnvironmentCode='LIVE',@pSessionId=12345,@pProductStatus1='1',@pProductStatus2='2',@pProductStatus3='3',@pPageNumber=0,@pNumberOfRowsPerPage=1000

标签： sql-serverormlite-servicestack

解决方案

OrmLite 将使用 <= SQL Server 2008 中的窗口函数 hack 进行类型化查询，但不适用于自定义 SQL。您需要将整个 SQL（包括窗口函数）包含到自定义 SQL 中。

如果您经常这样做，我建议将 Windowing Function SQL Template 包装在扩展方法中，以便您可以轻松地在自定义查询中使用它，例如：

results = db.Select<Product>(@"
    SELECT
        * 
    FROM 
        [dbo].[Search_Products_View]
        (
            @pClientID, 
            @pEnvironmentCode,
            @pSessionId
        )"
    .InWindowingPage(0,1000), new
    {
        pClientID = clientId,
        pEnvironmentCode = environmentCode,
        pSessionId = sessionId
    })

如果你想使用 DB 参数作为偏移量，你需要一些耦合来使用传统的参数名称：

results = db.Select<Product>(@"
    SELECT
        * 
    FROM 
        [dbo].[Search_Products_View]
        (
            @pClientID, 
            @pEnvironmentCode,
            @pSessionId
        )"
    .InWindowingPage(), new
    {
        pClientID = clientId,
        pEnvironmentCode = environmentCode,
        pSessionId = sessionId,
        pPageNumber = 0,
        pNumberOfRowsPerPage = 100
    })

推荐阅读