asp.net mvc 3 - ASP.NET MVC3 WebGrid: clasificación personalizada del lado del servidor

asp.net-mvc-3 sorting

Question

¿Hay alguna manera de anular el comportamiento predeterminado de clasificación de WebCrid de MVC3 para llamar a mi controlador (que realizará la clasificación del lado del servidor y devolver los datos) cuando se llama a ordenar?

¡Gracias por cualquier ayuda!

Answer 1

Puede pasar los datos clasificados del lado del servidor a la cuadrícula web, junto con la información sobre cuántos registros hay. http://msdn.microsoft.com/en-us/magazine/hh288075.aspx tiene un tutorial útil. Estoy haciendo la clasificación / filtrado / paginación a nivel de base de datos para minimizar la cantidad de datos que se pasan. No creo que mi servidor web me amaría si aprobaba los 70,000 objetos que un cliente tiene para poder ver los 25 en la página 1. Esto es prácticamente todo lo que necesita, excepto el modelo de vista muy liviano que simplemente envuelve su colección IEnumerable. con algunos datos de búsqueda adicionales.

Dado que la cuadrícula web solo utiliza los valores de cadena de consulta para decidir qué hacer, debe usar un formulario de método get. Y debe incluir el campo / dirección de ordenación en querystring de forma que webgrid sepa cómo leerlo. Así que terminas con urls como localhost / example / admin / thing? ThingName = Hyatt & City = & State = TX & Country = & sort = city & sortdir = ASC

Controlador:

    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);
            }

Ver:

@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");
}

Modelo:

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 con sql dinámico - sí, podrías usar Linq-to-Sql u otras técnicas si quisieras, pero yo soy de la vieja escuela:

 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 con 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