asp.net-mvc-3 – ASP.NET MVC3 WebGrid – 自定义,服务器端排序

您可以将服务器端排序数据传递给webgrid,以及有关记录数量的信息. http://msdn.microsoft.com/en-us/magazine/hh288075.aspx有一个有用的演练.我正在进行数据库级排序/过滤/分页,以最大限度地减少传递的数据量.我不认为我的网络服务器会爱我,如果我通过客户所有的70,000个对象,他们就可以看到第1页上的25个.这几乎是你需要的一切,除了包装你的IEnumerable集合的非常轻量级的视图模型一些额外的分页数据.

由于web网格只使用查询字符串变量来决定做什么,因此需要使用get方法表单.并且您需要以一种webgrid知道如何读取它的方式在querystring中包含sort字段/方向.所以你最终得到像localhost / example / admin / thing这样的网址？thingName = Hyatt& City =& State = TX& Country =& sort = city& sortdir = ASC

控制器：

public ActionResult Index(string thingName,string city,string state,string country,int page = 1)
            {
                const int pageSize = 25;                
                int totalRecords = 0; 
                IEnumerable<Thing> things = ThingModel.GetPagedSortedLocationsForCustomer(customerId,sort,sortdir,out totalRecords,pageSize,page,thingName,city,state,country);

                PagedThings viewModel = new PagedThings(pageSize,totalRecords,things);

                return View(viewModel);
            }

视图：

@model ExampleCompany.Area.ViewModels.PagedThings 

@{  



using (Html.BeginForm("Index","ThingaMaBob",System.Web.Mvc.FormMethod.Get))
{

    <label for="ThingName">ThingName</label>@Html.TextBox("ThingName","")
    <label for="City">City</label>@Html.TextBox("City","")
    <label for="State">State</label>@Html.TextBox("State","")
    <label for="Country">Country</label>@Html.TextBox("Country","")
    <input type="submit" value="Filter" />

    <br />
    var grid = new WebGrid(canPage: true,rowsPerPage: Model.PageSize,canSort: true);
    grid.Bind(Model.Things,rowCount: Model.TotalRows,autoSortAndPage: false);
    grid.Pager(WebGridPagerModes.All);

        @grid.GetHtml(htmlAttributes: new { id = "grid"},columns: grid.Columns(
            //ommitted
            grid.Column("thingName","Thing"),));

    Html.Hidden(grid.SortFieldName,grid.SortColumn);
    Html.Hidden(grid.SortDirectionFieldName,grid.SortDirection == SortDirection.Ascending ? "ASC" : "DESC");
}

模型：

public static IEnumerable<Thing> GetPagedSortedThingsForCustomer(int customerid,String sortby,String sorttype,out int totalRecords,int pageSize,int pageIndex,string thingName,string country)
        {
            var tmp = new List<Thing>();
            int total = 0;

            dynamic dr = OurDBUtility.ReturnDR("ExampleProc_GetThingsSortedPaged",ConnectionInfo.ExampleConnection,customerid,sortby,sorttype,pageIndex,country);
            {
                while (dr.Read())
                {
                    var tmpThing = new Thing();
                    tmpThing.LoadFromDr(dr);
                    tmp.Add(tmpThing);
                    if (total == 0)
                    {
                        total = (int)dr["TOTAL_THINGS"];
                    }
                }
            }
            totalRecords = total;

            return tmp;
        }

使用动态sql进行处理 – 是的,如果你愿意,你可以使用Linq-to-Sql或其他技术,但我老了：

CREATE PROCEDURE ExampleProc_GetThingsSortedPaged
      ( @CustomerId  int,@sortby    nvarchar(60),@sorttype   nvarchar(60),@pageSize int,@pageIndex int,@thingName nvarchar(255) = null,@city nvarchar(30) = null,@state nvarchar(30) = null,@country char(2) = null
      )
    as

        DECLARE @strSql      nvarchar(3000) 
        --calculate paging rows
        declare @startRow int,@endRow int
        --e.g. if you have a page size of 10,page 1 = 1 - 10,page 2 = 11 -20 
        set @startRow = ((@pageIndex - 1) * @pageSize) + 1
        set @endRow = @startRow + @pageSize - 1

        if @thingName = ''
            set @thingName = null
        if @city = ''
            set @city = null
        if @state = ''
            set @state = null
        if @country = ''
            set @country = null

        --return total for webgrid,accounting for filter
        declare @totalThings int
        select @totalThings = COUNT(*)
        from EXAMPLE_TABLE T with(nolock)
        where CUSTOMER_ID = @CustomerId
            AND (T.THING_NAME LIKE @thingName + '%' OR @thingName is null)              
            AND (T.CITY LIKE @city + '%' or @city is null) 
            AND (T.STATE LIKE @state + '%' or @state is null) 
            AND (T.COUNTRY = @country or @country is null)



        DECLARE @ParameterDefinition AS NVARCHAR(200)


        set @ParameterDefinition = '@totalThings int,@CustomerId INT,@startRow INT,@endRow INT,@thingName nvarchar(255),@city nvarchar(30),@state nvarchar(30),@country char(2)'

        --When we need to do dynamic sql it is better to use paramterization,but you cannot do (ORDER BY @sortBy).     
        SET @strSql = N'SELECT * from
                        (
                        select ROW_NUMBER() OVER (ORDER BY T.' + @sortby + ' ' + @sorttype + ') as Row,@totalThings  [TOTAL_THINGS],T.THING_ID,T.THING_NAME,T.ADDRESS,T.CITY,T.STATE,T.ZIP_CODE,T.COUNTRY 
                        FROM EXAMPLE_TABLE T
                        WHERE T.CUSTOMER_ID = @CustomerId 
                          AND (T.THING_NAME LIKE @thingName + ''%'' OR @thingName is null)              
                          AND (T.CITY LIKE @city + ''%'' or @city is null) 
                          AND (T.STATE LIKE @state + ''%'' or @state is null) 
                          AND (T.COUNTRY = @country or @country is null)
                           ) paged
                        where Row between @startRow and @endRow 
                        ORDER BY Row'

        --print @strSql
        EXECUTE sp_executesql @strSql,@ParameterDefinition,@totalThings,@CustomerId,@startRow,@endRow,@thingName,@city,@state,@country


    GO

Proc CTE：

CREATE PROCEDURE ExampleProc_GetThingsSortedPaged
  ( @CustomerID  int,@pageSize int = 25,@pageIndex int = 1,@city varchar(30) = null,@country char(2) = null
  )
as



declare @startRow int
declare @endRow int

SET @startRow = ((@pageIndex - 1) * @pageSize) + 1;
SET @endRow = @startRow + @pageSize - 1;

set @sortby = replace(LOWER(@sortby),'_','')
SET @sorttype = LOWER(@sorttype)

if @sorttype != 'asc' and @sorttype != 'desc'
begin
      set @sorttype = 'asc'
end

;with cte_things as (
      SELECT 
            CASE
                  WHEN @sortby ='country' AND @sorttype = 'asc'  then row_number() over (order by C.COUNTRY_NAME ASC)
                  WHEN @sortby ='country' AND @sorttype = 'desc' then row_number() over (order by C.COUNTRY_NAME DESC)

                  WHEN @sortby ='state' AND @sorttype = 'asc'  then row_number() over (order by STATE ASC) 
                  WHEN @sortby ='state' AND @sorttype = 'desc' then row_number() over (order by STATE DESC)

                  WHEN @sortby ='city' AND @sorttype = 'asc'  then row_number() over (order by CITY ASC)
                  WHEN @sortby ='city' AND @sorttype = 'desc' then row_number() over (order by CITY DESC)

                  WHEN @sortby ='thingname' AND @sorttype = 'desc' then row_number() over (order by THING_NAME DESC) 
                  ELSE row_number() over (order by THING_NAME ASC)
            END AS Row,T.THING_TYPE,T.COUNTRY_CODE,C.COUNTRY_NAME,T.PHONE_NUMBER,T.LATITUDE,T.LONGITUDE
            FROM EXAMPLE_TABLE L 
            join COUNTRIES C 
                  on C.COUNTRY_CODE = L.COUNTRY_CODE
            where
                  T.CUSTOMER_ID = @CustomerId 
                  and L.CITY = ISNULL(@city,CITY)
                  and L.STATE = ISNULL(@state,STATE)
                  and L.COUNTRY_CODE = ISNULL(@country,L.COUNTRY_CODE)
                  and L.THING_NAME = ISNULL(@thingName,THING_NAME)
),cte_total as (select COUNT(*) as TOTAL_THINGS from cte_things),cte_all as (select cte_things.*,cte_total.TOTAL_THINGS from cte_things cross join cte_total)

SELECT * FROM cte_all
where
      Row >= @startRow
      and Row <= @endRow  
ORDER BY Row



GO