We get a requirement from a client where we need to make a grid. It starts out as a simple grid, but then the client calls for sorting, then paging, then in-grid filtering of the results. We've all had to code up a solution for this in the past... We didn't want to load all of the results into C# and then do the sorting, paging, and filtering, as this would be too much data to store in memory. So instead we write a sproc that handles this for us, like so:
if exists (select * from syscomments where id = object_id ('dbo.User_GetUsers'))
begin
drop proc dbo.User_GetUsers
end
go
create proc dbo.User_GetUsers
@FirstName varchar(50),
@LastName varchar(50),
@LoginName varchar(50),
@SortColumn varchar(50),
@SortDirection varchar(50),
@PageSize int,
@PageNumber int,
@TotalRecordCount int out
as
--create temp table to store results.
declare @Users table
(
RowNum int identity(1,1),
UserID int,
FirstName varchar(50),
LastName varchar(50),
LoginName varchar(50)
)
--insert into temp table the sorted and filtered results (before paging.)
insert into @Users
(
UserID,
FirstName,
LastName,
LoginName
)
select
UserID,
FirstName,
LastName,
LoginName
from
dbo.User (nolock)
--search filters
where
(len(@FirstName) = 0 or upper(FirstName) like '%' + upper(@FirstName) + '%')
and (len(@LastName) = 0 or upper(LastName) like '%' + upper(@LastName) + '%')
and (len(@LoginName) = 0 or upper(LoginName) like '%' + upper(@LoginName) + '%')
--sorting
order by
case when @SortColumn = 'FirstName' and @SortDirection = 'ASC' then FirstName end asc,
case when @SortColumn = 'FirstName' and @SortDirection = 'DESC' then FirstName end desc,
case when @SortColumn = 'LastName' and @SortDirection = 'ASC' then LastName end asc,
case when @SortColumn = 'LastName' and @SortDirection = 'DESC' then LastName end desc,
case when @SortColumn = 'LoginName' and @SortDirection = 'ASC' then LoginName end asc,
case when @SortColumn = 'LoginName' and @SortDirection = 'DESC' then LoginName end desc
--get total record count.
set @TotalRecordCount = (select count(1) from @Users)
--figure out where to page.
declare @StartRowNum int
set @StartRowNum = ((@PageNumber - 1) * @PageSize) + 1
declare @EndRowNum int
set @EndRowNum = @StartRowNum + @PageSize
--get paged results from temp table.
select *
from @Users
where RowNum >= @StartRowNum
and RowNum < @EndRowNum
order by RowNum asc
go
This code worked great in the past, but we're in the 3.5 era and the 4.0 era is fast approaching, we can do better!! In comes Linq to SQL to the rescue.
First we need to do a little prep work. We're going to create a UserSearch object, which will store our search parameters. Next, we're going to create a UserView object, which will declare what we want to return for each user. Do we need to do this? No, however it's a good practice to only return what you need, especially since this is an ajax grid (did I mention that??) and we're going to convert the results to JSON to pass to it. Lastly, we need to create an extension method that will help us with sorting later on. It is beyond the scope of the article how this extension method works (in other words I don't know how it works yet!)
public class UserSearch
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string LoginName { get; set; }
}
public class UserView
{
public int UserID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string LoginName { get; set; }
public int TotalRecordCount { get; set; }
}
public static class DynamicOrderBy
{
public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source,
string sortColumn, string sortDirection) where TEntity : class
{
string command = sortDirection == "ASC" ? "OrderBy" : "OrderByDescending";
var type = typeof(TEntity);
var property = type.GetProperty(sortColumn);
var parameter = Expression.Parameter(type, "p");
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExpression = Expression.Lambda(propertyAccess, parameter);
var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote(orderByExpression));
return source.Provider.CreateQuery<TEntity>(resultExpression);
}
}
Now that our prep work is done, it's time to write our method, which will handle sorting, paging, and filtering, and return the results as JSON for use in our ajax grid.
public string GetPageJSON(string sortColumn, string sortDirection, int pageNumber, int pageSize, UserSearch search)
{
var query = from u in db.User
select new UserView
{
UserID = u.UserID,
FirstName = u.FirstName,
LastName = u.LastName,
LoginName = u.LoginName
};
//searching.
if (search.FirstName.Length > 0) query = query.Where(u => u.FirstName.Contains(search.FirstName));
if (search.LastName.Length > 0) query = query.Where(u => u.LastName.Contains(search.LastName));
if (search.LoginName.Length > 0) query = query.Where(u => u.LoginName.Contains(search.LoginName));
//sorting.
query = query.OrderBy(sortColumn, sortDirection);
//get total record count.
int totalRecordCount = query.Count();
//paging.
query = query.Skip((pageNumber - 1) * pageSize).Take(pageSize);
//set total record count.
var list = query.ToList();
if (list.Count > 0)
{
list[0].TotalRecordCount = totalRecordCount;
}
//return json.
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(list);
}
Make sure you add a reference to System.Web.Extensions and then you can hit control + . on JavaScriptSerializer to get its namespace (System.Web.Script.Serialization). As you can see, we first declare what we want to return from our user table. Next we do our filtering with some simple where conditions. After that, we sort using our custom extension method. Next we get the total number of records so that we can let our pager know, then we get one page of data, using the nifty Linq to SQL Skip and Take methods. We then call .ToList() on our query, which for the first time, calls the database, runs the query, and returns the results. We then set our TotalRecordCount in our results and lastly serialize our results into JSON.
So that's it! Once the JSON has been passed into the javascript using whatever method you like, maybe a web service or ASP.NET Ajax Extensions pagemethods, you can then say:
var results = eval(json);
The results object will then contain the List of business objects, pretty nifty huh??