Let’s say you are using Dot Net to interact with your SQL database. You want to sort the data being returned from your database. But, you want to keep the load in your middle tier, with the intention to reduce the load on your database server. What options are available to you at this point?
There are many different ways to talk to SQL Server in Dot Net. They are all based on a Dot Net communication protocol such as ODBC, or ADO.Net using the System.Data.SqlClient. Irrespective of your communication protocol, data is returned to your code in the form of an System.DataTable, or, if more than one table, it is returned in a System.DataSet (Note: you can have a dataset with only one table).
The DataTable class has both a filter and sort Method built in. Check out the DataTable.Select() method. It allows you to provide two strings as parameters to the select method. The first parameter is a filter, and the second is a sort. The filter text looks very much like the WHERE clause for an SQL Statement, without the word WHERE. In like manner, the sort looks like the ORDER BY clause of an SQL Statement without the key phrase ORDER BY.
Sorting in this manner returns an array of System.Data.DataRow, filtered, and sorted according to your requirements. Depending on the size of your set, this can work for you.
Another more attractive, more modern, method is to use Linq in the form of Linq To SQL or Entity Framework. I find entity framework more preferable because it works with Dot Net Classes. All the scaffolding to gather data from the database is built into Entity Framework. I simply get a set. Then, with my set, I can sort using the Linq .OrderBy or .OrderByDescending methods. What is nice about using Linq is that it will pass the sort through to SQL Server if that is the better place to perform the work.
If I am using Data Transfer Objects (DTO) which consist of simple classes containing only properties, that may be easily transformed to JSON objects for transport in a RESTful service, I can combine the code to generate my DTO and sort the data into a single Linq statement.
Both these options take the load off your database server and move it to another tier. This is advantageous in those environments where your application may need to scale out immediately, or in the future. Your database does not scale out easily. But, your consumers are more readily extended. So, put the load there. Using tools like Linq, it is a lot easier to sort on the client, especially using different sort criteria.
Cheers,
Ben