A simple way to add a 'View All' sort option to your DataSource in Asp.Net

Just use a little SQL trick to simplify and add usability to your DataSource

Often times I have integer fields in my database that serve as categories. These categories are very helpful when sorting for the users, butit is sometimes tough to write a simple, one-line query to output ALL of them; I figured an extremely simple way to do this. This may be a little hard to explain, but it is very useful.

For this example, I am going to use a LINQDataSource

<asp:LinqDataSource ID="ldsCases" runat="server" ContextTypeName="dbDataContext" TableName="Cases" Where="Category == @Category">

   <WhereParameters>

     <asp:SessionParameter Name="Category" SessionField="Cat" Type="Int32" />

   </WhereParameters>

</asp:LinqDataSource>


This is using a Session variable to work with the DataSource which will be set the the integer value that corresponds to the category I want to find. Now this will work just fine if we are trying to just look at a single category, but what if we want to look at all of them? It is really quite simple, now, just add one more OR (||) statement to our SQL and set the Session variable to 0 any time we want to call everything:

<asp:LinqDataSource ID="ldsCases" runat="server" ContextTypeName="dbDataContext" TableName="Cases" Where="(Category == @Category) || ((Category * @Category) == 0)">

   <WhereParameters>

     <asp:SessionParameter Name="Category" SessionField="Cat" Type="Int32" />

   </WhereParameters>

</asp:LinqDataSource>

Comments are closed