I have an ASP.NET page that builds a report, the user can filter the output of the report by choosing different options from multiple dropdown boxes and checkboxes. They can choose one of these options or all of them, or even some of them - there are 20+
different combinations.
I want to know what the best way to code this is? Do I create 20+ IF statements with each block containing a different SQLCommand based on the combination chosen by the user? I know a CASE statement would perhaps be better, but I just wanted to know if there
was a better way...
oblomov86 - That's what I am doing at the moment i.e. many different SQL Commands with parameterised variables within many IF statements. I just wondered if there was any other way to simplify the whole procedure. I think I'll go down the SELECT CASE road...
One suggestion I have is based upon the amount of total data that could be used for any or all of the 20 reports. If it is not too much overhead to bring back all possible data (again this is under the premise that the data is not absolutely huge)
used for reports and cache (if static enough) and then use LINQ to Objects to query the data.
So let's say all data is 1000 rows and multiple reports need to be ran off of it. Do you want 20 If statements, 20 different stored proces, dynamic SQL, etc? Using LINQ you could pull back all needed data and then query the data in .NET to generate and filter
on the report. I use this method often as it can drastically cut down on the number of custom stored procs or dynamically built queries needed when pulling back all data is a
reasonable option.
However if the total of all data is millions of records and will bring down the memory in IIS by trying to bring it back you will need another solution (or hybrid one - I would still use LINQ to Objects to filter for small variations in larger sets of data
that can provide for a variety of needs). In this case you may need to look into building out
Expression Trees to build out dynamic Linq queries. Have a look to the following for more information:
geezer
Member
25 Points
42 Posts
What's the best practice to query SQL Server with lots of option?
Jan 25, 2012 11:42 AM|LINK
Hi there,
I have an ASP.NET page that builds a report, the user can filter the output of the report by choosing different options from multiple dropdown boxes and checkboxes. They can choose one of these options or all of them, or even some of them - there are 20+ different combinations.
I want to know what the best way to code this is? Do I create 20+ IF statements with each block containing a different SQLCommand based on the combination chosen by the user? I know a CASE statement would perhaps be better, but I just wanted to know if there was a better way...
Any help or advice would be appreciated.
Ashutosh Shu...
Member
395 Points
116 Posts
Re: What's the best practice to query SQL Server with lots of option?
Jan 25, 2012 11:47 AM|LINK
Hi
Geezer
Its upto you choose which way handle this problem my point of view use Case
bcoz its work according to condition
Thanks
Ashutosh
oblomov86
Member
280 Points
76 Posts
Re: What's the best practice to query SQL Server with lots of option?
Jan 25, 2012 11:56 AM|LINK
Why you don't take variant with simple 'WHERE'?
select .......
where @is_cool = 1
and @is_fast = 0
and type_id = @type_id
....
geezer
Member
25 Points
42 Posts
Re: What's the best practice to query SQL Server with lots of option?
Jan 25, 2012 12:48 PM|LINK
oblomov86 - That's what I am doing at the moment i.e. many different SQL Commands with parameterised variables within many IF statements. I just wondered if there was any other way to simplify the whole procedure. I think I'll go down the SELECT CASE road...
Thanks for your help
atconway
All-Star
16846 Points
2756 Posts
Re: What's the best practice to query SQL Server with lots of option?
Jan 25, 2012 08:37 PM|LINK
One suggestion I have is based upon the amount of total data that could be used for any or all of the 20 reports. If it is not too much overhead to bring back all possible data (again this is under the premise that the data is not absolutely huge) used for reports and cache (if static enough) and then use LINQ to Objects to query the data.
So let's say all data is 1000 rows and multiple reports need to be ran off of it. Do you want 20 If statements, 20 different stored proces, dynamic SQL, etc? Using LINQ you could pull back all needed data and then query the data in .NET to generate and filter on the report. I use this method often as it can drastically cut down on the number of custom stored procs or dynamically built queries needed when pulling back all data is a reasonable option.
However if the total of all data is millions of records and will bring down the memory in IIS by trying to bring it back you will need another solution (or hybrid one - I would still use LINQ to Objects to filter for small variations in larger sets of data that can provide for a variety of needs). In this case you may need to look into building out Expression Trees to build out dynamic Linq queries. Have a look to the following for more information:
Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library):
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
Dynamic Query Sample:
http://msdn.microsoft.com/en-us/library/bb397982(v=vs.90).aspx
How to: Use Expression Trees to Build Dynamic Queries (C# and Visual Basic):
http://msdn.microsoft.com/en-us/library/bb882637.aspx
Hope this helps!