ASP.NET GridView 정렬 및 페이징 Optimize

OptmizedPagingAndSorting_Src.zip

http://www.codeproject.com/KB/aspnet/Optimized_Paging_And_Sort.aspx

GridView 와 이를 가지고 페이징 처리 하는 부분을 기술한 것이다.




[테이블 구조]

Create PROCEDURE spGetAllEmployee

	(

	@startIndex		int,

	@pageSize		int,

	@sortBy		nvarchar(30),

	@totalEmployees	int OUTPUT		

	)

AS

	SET NOCOUNT ON 



 DECLARE

    @sqlStatement nvarchar(max),    

    @upperBound int



  IF @startIndex  < 1 SET @startIndex = 1

  IF @pageSize < 1 SET @pageSize = 1

  

  SET @upperBound = @startIndex + @pageSize

 



 Select @totalEmployees=Count(*) From Employee

  

  SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary

                FROM (

                      SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *

                      FROM    Employee

                     ) AS E

                WHERE  rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND

                       rowNumber <  ' + CONVERT(varchar(9), @upperBound)

  exec (@sqlStatement)

쿼리를 보면 동적쿼리로 구현되어 있고, SQL 2005 의 함수인 ROW_NUMBER() 를 사용했습니다.

그리고 페이징 처리로 인해 데이터와 함께 COUNT 까지 반환받도록 되어 있습니다.

public List<EmployeeInfo> GetAllEmployee(int startIndex, int pageSize, string sortBy,ref int totalEmployees)        {
            IDbConnection connection=null ;
            IDbCommand selectCommand=null ;
            List<EmployeeInfo> employeeInfoList;
            DataSet employeeDS = new DataSet();
            IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();
            try
            {
                using (connection = DataObjectFactory.CreateConnectionObject())
                {
                    using (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connection, "spGetAllEmployee"))
                    {

                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@startIndex", SqlDbType.Int, startIndex));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@pageSize", SqlDbType.Int, pageSize));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@sortBy", SqlDbType.NVarChar, 30, sortBy));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@totalEmployees", SqlDbType.Int, 0));
                        ((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        dataAdapter.SelectCommand = selectCommand;

                        dataAdapter.Fill(employeeDS);

                        totalEmployees = Convert.ToInt32(((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Value);
                    }
                }
            }
            catch (SqlException ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                DALException mineException = new DALException();
                mineException.ConnectingString = connection.ConnectionString;
                mineException.StoredProcedureName = selectCommand.CommandText;
                mineException.Source = "GetAllEmployee";
                throw mineException;
               
            }

            employeeInfoList =  ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);

            return employeeInfoList;
          
        }

코드는 위의 저장프로시저를 호출하는 부분을 기술한것 이며, DataSet 으로 받아서 employee 객체에 할당한 부분이다. 굳이 dataAdapter 를 사용할 필요없이 속도를 좀 더 개선하기 위해서 datareader 를 사용하는게 더 좋지 않을까 생각이 든다.

총페이지 카운터는 여기서 ref totalEmployees 를 사용하여 값을 반환받도록 처리되어 있습니다.

<asp:GridView ID="GridView1" DataKeyNames="EmployeeID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" GridLines="None" AllowSorting="True" PageSize="5" >
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
            <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="true" SortExpression="EmployeeID" />
            <asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode" SortExpression="EmployeeCode" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
        </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees" EnablePaging="true"
        TypeName="EmployeeData" StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize" SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >
    </asp:ObjectDataSource>

주의깊게 볼 부분은 붉은색으로 칠해놓은 코드 부분입니다. TypeName, StartRowIndexParameterName, MaximunRowsParameterName, SortParameterName 그리고 카운트를 담당하는 SelectCountMethod ...

public class EmployeeData
{

    private static int employeesCount;
    public EmployeeData()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<EmployeeInfo> GetAllEmployees(int startIndex, int pageSize, string sortBy)
    {
        EmployeeBLL objEmployeeBLL = new EmployeeBLL();
        List<EmployeeInfo> result;
        int totalEmployees=0;

        if (string.IsNullOrEmpty(sortBy))
            sortBy = "EmployeeID";

        result = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy, ref totalEmployees);
        employeesCount = totalEmployees;
        return result;

    }

    public static int GetTotalEmployeesCount()
    {
        return employeesCount;
    }


}

이 부분이 webform 의 gridview 에서 호출한 클래스의 함수들입니다. 해석하는데 그리 어렵지 않을겁니다.


이... 코드가 마음에 드시면 해당 codeproject 가서 별 5개 주시길....