스콧 미첼에 의해
이전 자습서에서는 웹 페이지에 데이터를 표시할 때 사용자 지정 페이징을 구현하는 방법을 알아보았습니다. 이 자습서에서는 사용자 지정 페이징 정렬에 대한 지원을 포함하도록 이전 예제를 확장하는 방법을 알아보세요.
소개
기본적인 페이징에 비해, 사용자 지정 페이징은 데이터를 페이징할 때 성능을 수십 배로 향상시킬 수 있습니다. 이는 많은 양의 데이터를 페이징할 때 사실상 기본적인 페이징 구현으로 선택되는 이유입니다. 그러나 사용자 지정 페이징 구현은 기본 페이징을 구현하는 것보다 더 중요합니다. 특히 혼합에 정렬을 추가할 때는 더욱 그렇습니다. 이 자습서에서는 정렬 및 사용자 지정 페이징에 대한 지원을 포함하도록 앞의 예제부터 확장합니다.
비고
이 자습서는 이전 자습서를 기반으로 하므로 시작하기 전에 이전 자습서의 웹 페이지(<asp:Content>)에서 EfficientPaging.aspx 요소 내의 선언적 구문을 복사하여 <asp:Content> 페이지의 SortParameter.aspx 요소 사이에 붙여넣으십시오. 한 ASP.NET 페이지의 기능을 다른 페이지로 복제하는 방법에 대한 자세한 내용은 편집 및 삽입 인터페이스에 유효성 검사 컨트롤 추가 자습서의 1단계를 참조하세요.
1단계: 사용자 지정 페이징 기술 다시 확인
사용자 지정 페이징이 제대로 작동하려면 행 인덱스 시작 및 최대 행 매개 변수를 고려할 때 특정 레코드 하위 집합을 효율적으로 가져올 수 있는 몇 가지 기술을 구현해야 합니다. 이 목표를 달성하는 데 사용할 수 있는 몇 가지 기술이 있습니다. 이전 자습서에서는 Microsoft SQL Server 2005의 새 ROW_NUMBER() 순위 함수를 사용하여 이 작업을 수행하는 방법을 살펴보았습니다. 즉, 순위 함수는 ROW_NUMBER() 지정된 정렬 순서로 순위가 지정된 쿼리에서 반환되는 각 행에 행 번호를 할당합니다. 그런 다음 번호가 매겨진 결과의 특정 섹션을 반환하여 적절한 레코드 하위 집합을 가져옵니다. 다음 쿼리에서는 이 기술을 사용하여 사전순 ProductName으로 정렬된 결과의 순위를 매겨 11에서 20까지 번호가 매겨진 제품을 반환하는 방법을 보여 줍니다.
SELECT ProductID, ProductName, ...
FROM
(SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
(ORDER BY ProductName) AS RowRank
FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20
이 기술은 특정 정렬 순서(ProductName 이 경우 사전순으로 정렬됨)를 사용하여 페이징하는 데 적합하지만 다른 정렬 식을 기준으로 정렬된 결과를 표시하기 위해 쿼리를 수정해야 합니다. 이상적으로는 위 쿼리를 절에서 매개 변수를 사용하도록 OVER 다음과 같이 재작성할 수 있습니다.
SELECT ProductID, ProductName, ...
FROM
(SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
(ORDER BY @sortExpression) AS RowRank
FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20
아쉽게도 매개 변수가 ORDER BY 있는 절은 허용되지 않습니다. 대신, @sortExpression 입력 매개 변수를 수락하는 저장 프로시저를 만들되 다음 해결 방법 중 하나를 사용해야 합니다.
- 사용할 수 있는 각 정렬 식에 대해 하드 코딩된 쿼리를 작성합니다. 그런 다음 T-SQL 문을 사용하여
IF/ELSE실행할 쿼리를 결정합니다. -
CASE구문을 사용하여ORDER BYn 입력 매개 변수에 따라 동적@sortExpressio식을 제공합니다. 자세한 내용은 T-SQLCASE문의 쿼리 결과를 동적으로 정렬하는 데 사용되는 섹션을 참조하세요. - 저장 프로시저에서 적절한 쿼리를 문자열로 만든 다음 시스템 저장 프로시저를 사용하여
sp_executesql동적 쿼리를 실행합니다.
이러한 각 해결 방법은 몇 가지 단점이 있습니다. 첫 번째 옵션은 가능한 각 정렬 식에 대한 쿼리를 만들어야 하기 때문에 다른 두 옵션만큼 유지 관리할 수 없습니다. 따라서 나중에 GridView에 정렬 가능한 새 필드를 추가하기로 결정한 경우 다시 돌아가서 저장 프로시저를 업데이트해야 합니다. 두 번째 방법은 문자열이 아닌 데이터베이스 열을 기준으로 정렬할 때 성능 문제를 발생시키는 몇 가지 미묘한 차이를 가지며 첫 번째 방법과 동일한 유지 관리 문제도 발생합니다. 또한 동적 SQL을 사용하는 세 번째 선택은 공격자가 선택한 입력 매개 변수 값을 전달하는 저장 프로시저를 실행할 수 있는 경우 SQL 삽입 공격에 대한 위험을 도입합니다.
이러한 접근 방식 중 어느 것도 완벽하지는 않지만 세 번째 옵션이 세 가지 중 최고라고 생각합니다. 동적 SQL을 사용하면 다른 두 가지가 사용하지 않는 수준의 유연성을 제공합니다. 또한 SQL 삽입 공격은 공격자가 선택한 입력 매개 변수를 전달하는 저장 프로시저를 실행할 수 있는 경우에만 악용될 수 있습니다. DAL은 매개 변수가 있는 쿼리를 사용하므로 ADO.NET 아키텍처를 통해 데이터베이스로 전송되는 매개 변수를 보호합니다. 즉, 공격자가 저장 프로시저를 직접 실행할 수 있는 경우에만 SQL 삽입 공격 취약성이 존재합니다.
이 기능을 구현하려면 Northwind 데이터베이스에 새 저장 프로시저를 만듭니다 GetProductsPagedAndSorted. 이 저장 프로시저는 세 가지 입력 매개 변수를 수용해야 합니다: 결과를 정렬하는 방법을 지정하고 @sortExpression 절 내 nvarchar(100 텍스트 바로 뒤에 직접 삽입되는 입력 매개 변수ORDER BY 형식OVER, 그리고 이전 자습서에서 조사한 저장 프로시저@startRowIndex에서 온 동일한 두 정수 입력 매개 변수인 @maximumRows와 GetProductsPaged입니다. 스크립트를 다음과 같이 사용하여 GetProductsPagedAndSorted 저장 프로시저를 만듭니다.
CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
@sortExpression nvarchar(100),
@startRowIndex int,
@maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
CategoryName, SupplierName
FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued,
c.CategoryName, s.CompanyName AS SupplierName,
ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
FROM Products AS p
INNER JOIN Categories AS c ON
c.CategoryID = p.CategoryID
INNER JOIN Suppliers AS s ON
s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
WHERE RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql
저장 프로시저는 매개 변수의 값 @sortExpression 이 지정되었는지 확인하여 시작합니다. 누락된 경우 결과는 .에 의해 ProductID순위가 매겨집니다. 다음으로 동적 SQL 쿼리가 생성됩니다. 여기서 동적 SQL 쿼리는 Products 테이블에서 모든 행을 검색하는 데 사용된 이전 쿼리와 약간 다릅니다. 이전 예제에서는 하위 쿼리를 사용하여 각 제품의 연결된 범주 및 공급업체 이름을 얻었습니다. 이 결정은 데이터 액세스 계층 만들기 자습서에서 다시 이루어졌으며 TableAdapter가 해당 쿼리에 대해 연결된 삽입, 업데이트 및 삭제 메서드를 자동으로 만들 수 없기 때문에 s를 사용하는 JOIN 대신 수행되었습니다. 그러나 저장 프로시저는 결과가 범주 또는 공급자 이름에 따라 정렬되도록 GetProductsPagedAndSorted의 값을 사용해야 합니다.
이 동적 쿼리는 정적 쿼리 부분과 , @sortExpression및 @startRowIndex 매개 변수를 @maximumRows연결하여 빌드됩니다.
@startRowIndex
@maximumRows 정수 매개 변수이므로 올바르게 연결하려면 nvarchars로 변환해야 합니다. 이 동적 SQL 쿼리가 생성되면 sp_executesql를 통해 실행됩니다.
잠시 시간을 내어 @sortExpression, @startRowIndex, 및 @maximumRows 매개 변수에 대해 서로 다른 값으로 이 저장 프로시저를 테스트하세요. 서버 탐색기에서 저장 프로시저 이름을 마우스 오른쪽 단추로 클릭하고 실행을 선택합니다. 그러면 입력 매개 변수를 입력할 수 있는 저장 프로시저 실행 대화 상자가 표시됩니다(그림 1 참조). 범주 이름을 기준으로 결과를 정렬하려면 매개 변수 값에 @sortExpression CategoryName을 사용하고 공급자의 회사 이름을 기준으로 정렬하려면 CompanyName을 사용합니다. 매개 변수 값을 제공한 후 확인을 클릭합니다. 결과는 출력 창에 표시됩니다. 그림 2는 내림차순으로 주문할 때 순위가 11~20인 제품을 반환할 때의 UnitPrice 결과를 보여 줍니다.
그림 1: 저장 프로시저의 세 가지 입력 매개 변수에 대해 다른 값 사용해 보기
그림 2: 저장 프로시저의 결과가 출력 창에 표시됩니다(전체 크기 이미지를 보려면 클릭).
비고
절에서 지정된 ORDER BY 열을 OVER 기준으로 결과의 순위를 지정할 때 SQL Server는 결과를 정렬해야 합니다. 열에 클러스터형 인덱스가 있는 경우 또는 결과 순서가 지정되거나 커버링 인덱스가 있는 경우 빠른 작업이지만, 그렇지 않으면 비용이 더 많이 들 수 있습니다. 충분히 큰 쿼리의 성능을 향상시키려면 결과가 정렬되는 열에 대해 비클러스터형 인덱스를 추가하는 것이 좋습니다. 자세한 내용은 SQL Server 2005의 순위 함수 및 성능을 참조하세요.
2단계: 데이터 액세스 및 비즈니스 논리 계층 보강
저장 프로시저를 GetProductsPagedAndSorted 만든 다음 단계는 애플리케이션 아키텍처를 통해 해당 저장 프로시저를 실행하는 방법을 제공하는 것입니다. 이는 DAL과 BLL 모두에 적절한 메서드를 추가하는 것을 수반합니다. DAL에 메서드를 추가하여 시작해 보겠습니다.
Northwind.xsd 형식화된 데이터 세트를 열고 마우스 오른쪽 단추로 클릭하고 ProductsTableAdapter상황에 맞는 메뉴에서 쿼리 추가 옵션을 선택합니다. 이전 자습서에서 했던 것처럼 이 경우 기존 저장 프로시저 GetProductsPagedAndSorted를 사용하도록 이 새 DAL 메서드를 구성하려고 합니다. 먼저 새 TableAdapter 메서드가 기존 저장 프로시저를 사용하도록 함을 나타냅니다.
그림 3: 기존 저장 프로시저 사용 선택
사용할 저장 프로시저를 지정하려면 다음 화면의 GetProductsPagedAndSorted 드롭다운 목록에서 저장 프로시저를 선택합니다.
그림 4: GetProductsPagedAndSorted 저장 프로시저 사용
이 저장 프로시저는 레코드 집합을 결과로 반환하므로 다음 화면에서 테이블 형식 데이터를 반환함을 나타냅니다.
그림 5: 저장 프로시저가 테이블 형식 데이터를 반환함을 나타냅니다.
마지막으로 DataTable 채우기 패턴과 DataTable 반환 패턴을 모두 사용하는 DAL 메서드를 만들고, 메서드의 이름을 각각 FillPagedAndSorted와 GetProductsPagedAndSorted로 지정합니다.
그림 6: 메서드 이름 선택
이제 DAL을 확장했으므로 BLL로 전환할 준비가 되었습니다.
ProductsBLL 클래스 파일을 열고 새 메서드를 추가합니다GetProductsPagedAndSorted. 이 메서드는 세 개의 입력 매개 변수sortExpressionstartRowIndex를 허용해야 하며 maximumRows 다음과 같이 DAL GetProductsPagedAndSorted 메서드를 호출하기만 하면 됩니다.
<System.ComponentModel.DataObjectMethodAttribute( _
System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
As Northwind.ProductsDataTable
Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function
3단계: SortExpression 매개 변수에 전달하도록 ObjectDataSource 구성
저장 프로시저를 활용하는 GetProductsPagedAndSorted 메서드를 포함하도록 DAL 및 BLL을 보강한 후에는 새 BLL 메서드를 사용하도록 페이지에서 ObjectDataSource SortParameter.aspx 를 구성하고 사용자가 결과를 정렬하도록 요청한 열에 따라 매개 변수를 전달하는 SortExpression 것만 남아 있습니다.
ObjectDataSource의 SelectMethod를 GetProductsPaged에서 GetProductsPagedAndSorted로 변경하여 시작합니다. 이 작업은 데이터 원본 구성 마법사, 속성 창 또는 선언적 구문을 통해 직접 수행할 수 있습니다. 다음으로 ObjectDataSource 속성SortParameterName에 대한 값을 제공해야 합니다. 이 속성을 설정하면 ObjectDataSource가 GridView의 SortExpression 속성을 SelectMethod.에 전달하려고 시도합니다. 특히 ObjectDataSource는 이름이 속성 값과 같은 입력 매개 변수를 SortParameterName 찾습니다. BLL 메서드에는 GetProductsPagedAndSorted 정렬 표현식 입력 매개 변수가 있으므로 sortExpression ObjectDataSource의 SortExpression 속성을 sortExpression으로 설정합니다.
이러한 두 가지 변경을 수행한 후 ObjectDataSource의 선언적 구문은 다음과 유사하게 표시됩니다.
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>
비고
이전 자습서와 마찬가지로 ObjectDataSource에 SelectParameters 컬렉션에 sortExpression, startRowIndex 또는 maximumRows 입력 매개 변수가 포함되지 않았는지 확인합니다.
GridView에서 정렬을 활성화하려면 GridView의 스마트 태그에 있는 정렬 사용 확인란을 선택하세요. 이렇게 하면 GridView의 AllowSorting 속성이 true로 설정되어 각 열의 헤더 텍스트가 LinkButton으로 렌더링됩니다. 최종 사용자가 LinkButtons 헤더 중 하나를 클릭하면 포스트백이 계속되고 다음 단계가 발생합니다.
- GridView는 헤더 링크를 클릭한 필드의
SortExpression값으로 업데이트합니다. - ObjectDataSource는 BLL의 메서드
GetProductsPagedAndSorted를 호출하며, GridView의 속성을 메서드의SortExpression입력 매개 변수 값으로 전달합니다. 이때 적절한sortExpression및startRowIndex입력 매개 변수 값도 함께 전달됩니다. - BLL은 DAL 메서드
GetProductsPagedAndSorted를 호출합니다. - DAL은
GetProductsPagedAndSorted저장 프로시저를 실행하여@sortExpression매개 변수와@startRowIndex,@maximumRows입력 매개 변수 값을 전달합니다. - 저장 프로시저는 BLL에 적절한 데이터 하위 집합을 반환하며, 이 하위 집합은 ObjectDataSource로 반환됩니다. 그러면 이 데이터가 GridView에 바인딩되고 HTML로 렌더링되고 최종 사용자에게 전송됩니다.
그림 7은 오름차순으로 UnitPrice 정렬된 결과의 첫 번째 페이지를 보여줍니다.
그림 7: 결과는 UnitPrice별로 정렬됩니다(전체 크기 이미지를 보려면 클릭).
현재 구현에서는 제품 이름, 범주 이름, 단위당 수량 및 단가를 기준으로 결과를 올바르게 정렬할 수 있지만 공급업체 이름으로 결과를 정렬하려고 하면 런타임 예외가 발생합니다(그림 8 참조).
그림 8: 다음 런타임 예외에서 공급업체 결과별로 결과를 정렬하려고 시도
이 예외는 GridView의 SortExpression BoundField가 SupplierName로 설정되어 있기 때문에 SupplierName 발생합니다. 그러나 Suppliers 테이블의 공급자 이름은 실제로 CompanyName라 불리며, 이 열 이름을 SupplierName라는 별칭으로 지정했습니다. 그러나 함수에서 사용하는 OVER 절은 ROW_NUMBER() 별칭을 사용할 수 없으며 실제 열 이름을 사용해야 합니다. 따라서 BoundField를 SupplierNameSortExpression SupplierName에서 CompanyName으로 변경합니다(그림 9 참조). 그림 10에서 보여 주듯이 이 변경 후에는 공급자가 결과를 정렬할 수 있습니다.
그림 9: SupplierName BoundField의 SortExpression을 CompanyName으로 변경
그림 10: 이제 공급업체별로 결과를 정렬할 수 있습니다(전체 크기 이미지를 보려면 클릭).
요약
이전 자습서에서 검사한 사용자 지정 페이징 구현에서는 디자인 타임에 결과를 정렬할 순서를 지정해야 했습니다. 즉, 구현한 사용자 지정 페이징 구현이 동시에 정렬 기능을 제공할 수 없다는 의미입니다. 이 자습서에서는 결과를 정렬할 수 있는 입력 매개 변수를 포함 @sortExpression 하도록 저장 프로시저를 첫 번째에서 확장하여 이러한 제한을 극복했습니다.
이 저장 프로시저를 만들고 DAL 및 BLL에서 새 메서드를 만든 후 GridView의 현재 SortExpression 속성을 BLL SelectMethod에 전달하도록 ObjectDataSource를 구성하여 정렬 및 사용자 지정 페이징을 모두 제공하는 GridView를 구현할 수 있었습니다.
행복한 프로그래밍!
작성자 정보
7개의 ASP/ASP.NET 책의 저자이자 4GuysFromRolla.com 창립자인 Scott Mitchell은 1998년부터 Microsoft 웹 기술을 연구해 왔습니다. Scott은 독립 컨설턴트, 트레이너 및 작가로 일합니다. 그의 최신 책은 샘스 자신에게 24시간 동안 ASP.NET 2.0을 가르치는 법입니다. 그에게 mitchell@4GuysFromRolla.com로 연락할 수 있습니다.
특별히 감사드립니다.
이 자습서 시리즈는 많은 유용한 검토자가 검토했습니다. 이 자습서의 수석 검토자는 Carlos Santos였습니다. 예정된 MSDN 문서를 검토하는 데 관심이 있으신가요? 그렇다면 mitchell@4GuysFromRolla.com으로 메시지를 보내 주세요.