问题描述
有没有办法覆盖默认MVC3的WebGrid排序行为叫我控制器（这将执行服务器端排序并返回数据）时，排序是叫什么名字？
Is there a way to override default MVC3 WebGrid sorting behavior to call my controller (which will perform server side sorting and return the data) when sort is called?
感谢您的帮助！
推荐答案
您可以通过在服务器端排序的数据到的WebGrid，随着信息沿着多少记录有。 有一个有益的演练。我在做数据库级排序/过滤/分页，以尽量减少正在传递的数据量。我不认为我的web服务器会爱我，如果我通过了所有70000对象的客户刚刚这样他们就可以看到第25页1.你需要这是pretty所有事情上，但只是包装的非常轻巧视图模型您的IEnumerable集合一些额外的分页数据。
You can pass the server side sorted data to the webgrid, along with the info on how many records there are. http://msdn.microsoft.com/en-us/magazine/hh288075.aspx has a helpful walkthrough. I'm doing database level sorting/filtering/paging to minimize the amount of data being passed around. I don't think my webserver would love me if I passed all 70,000 objects a customer has just so they can see the 25 on page 1. This is pretty much everything you need, except the very lightweight view model that just wraps your IEnumerable collection with some extra paging data.
由于网络的网格只使用查询字符串瓦尔来决定做什么，你需要使用GET方法的形式。而且你需要在一个方法的WebGrid知道如何阅读查询字符串排序字段/方向。所以，你最终会像网址localhost/example/admin/thing?thingName=Hyatt&City=&State=TX&Country=&sort=city&sortdir=ASC
Since web grid just uses the query string vars to decide what to do you need to use a get method form. And you need to include the sort field/direction in querystring in a way webgrid knows how to read it. So you end up with urls like 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, page, 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 city, string state, string country)
{
var tmp = new List<Thing>();
int total = 0;
dynamic dr = OurDBUtility.ReturnDR("ExampleProc_GetThingsSortedPaged", ConnectionInfo.ExampleConnection, customerid, sortby, sorttype, pageSize, pageIndex, thingName, city, state, 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;
}
PROC动态SQL - 是的，你可以使用LINQ到SQL或其他技术，如果你想，但我的老同学：
Proc with dynamic sql - yes, you could use Linq-to-Sql or other techniques if you wanted to, but i'm old school:
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：
Proc with CTE:
CREATE PROCEDURE ExampleProc_GetThingsSortedPaged
( @CustomerID int
, @sortby nvarchar(60)
, @sorttype nvarchar(60)
, @pageSize int = 25
, @pageIndex int = 1
, @thingName nvarchar(255) = null
, @city varchar(30) = null
, @state nvarchar(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_ID, T.THING_NAME, T.THING_TYPE, T.ADDRESS, T.CITY, T.STATE
, T.ZIP_CODE, 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
这篇关于ASP.NET MVC3的WebGrid - 定制，服务器端排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！