Wrestling to sort the ASP.NET GridView

It's supposed to be easy, and practically codeless to use, and it is--sometimes. When a GridView is to be populated with the same dataset every time the page loads. But I had a drop-down list where a condition had to be selected, and based on that, the grid then had to be populated. I made it to the point where I got the dropdown selecting, and the grid populating, but there were 2 problems: paging didn't work, and sorting didn't work. I decided to turn paging off, so sorting was my last remaining issue.

Umpteen useless web articles later, I resorted to the paper books stashed on my shelf at home. First stop was Murach's ASP.NET 2.0, which is alleged to be so good. But it held no love for me. Second stop was Dino Esposito's "Programming Microsoft ASP.NET 2.0: Core Reference"--and finally, I got the help I needed.

I'm blogging about this because a good book deserves real credit. Many mysteries were unraveled by the Esposito book, including that I needed to explicitly re-populate the GridView when its "Sorting" event fired. Esposito's directions were extremely explicit: use the "Sorting" event's GridViewArgEvents ("e" parameter) to find out the sort key, and write a special stored procedure that uses the sort key to ORDER the data differently. These last bits of information were the treasure that finally allowed me to get sorting to work.

I'm posting a copy of the rather odd-looking stored procedure that I ended up using below for your edification. The "@order_by" parameter names the column on which to sort, and the odd way of constructing the query from strings allows brackets to fit around any strange or keyword column names:

CREATE PROCEDURE [dbo].[lsp_NRSA_find_counts_and_person_by_naded] 
@naded_id int,
@order_by nvarchar(12)
AS

BEGIN

 IF @order_by = ''
 BEGIN
  SET @order_by = 'slide'
 END
 
 EXEC ('SELECT * ' + 
 'FROM vw_NRSA_cemaats_by_count_and_person ' +
 'WHERE naded = ' + @naded_id + 
 ' ORDER BY [' + @order_by + ']')

END