Reputation: 229342
What's the proper way of parameterizing an order by clause in ADO.NET ?
Sometimes there's a need to order by 2 columns, while the default is ordering on just 1 column, and sometimes you'll just want to change ASC to DEC. Is it considered ok to just use string concatenating in such cases (provided the input doesn't come from the user directly, but just a lookup of more or less hardcoded values in the code)
Upvotes: 6
Views: 3364
Reputation: 11
Sample:
SELECT 1 AS Num, '2015-06-22' AS Datex INTO Arya
INSERT INTO Arya
SELECT 2, '2015-08-17' UNION SELECT 3, '2015-07-14'
Arya table:
Num Datex
-----------------
1, 2015-06-22
2, 2015-08-17
3, 2015-07-14
Now, Parametrize sorting(Base on Datex Field) in a SELECTION ....
SELECT Num, Date1 FROM ARYA, (SELECT -1 as e union Select 1 as e) a
WHERE a.e=1 --(1) For ASC sort --(OR a.e=-1) For Desc Sort
ORDER BY DATEDIFF(DAY, '2000-01-01', Arya.Datex)*a.e
Result: ASC Sort IF 1
1, 2015-06-22
3, 2015-07-14
2, 2015-08-17
Result: ASC Sort IF -1
2, 2015-08-17
3, 2015-07-14
1, 2015-06-22
Upvotes: 0
Reputation: 15849
Try like this:
SELECT ...
ORDER BY
CASE WHEN @OrderBy = 'Option1' THEN SomeField END,
CASE WHEN @OrderBy = 'Option1' THEN SomeOtherField END DESC,
CASE WHEN @OrderBy = 'Option2' THEN Field75 END,
...
The idea is that each CASE statement will evaluate to NULL if the WHEN doesn't match. So if you put Option2, then you get a constant value for the first two options.
So using this, you can easily have some options that let you sort by several fields, or descending, or whatever you want.
Rob
Upvotes: 6
Reputation: 10013
If it is not that much data, I would just have:
DataTable dt = ....
DataView dv = new DataView(dt);
dv.Sort = "LastName DESC, FistName";
and then vary the last line based on whatever.
Upvotes: 0
Reputation: 180958
The SQL Injection purists will tell you that string concatenation is never permissible, because there always exists the possibility that another programmer may extend the program and expose the SQL statement to the outside world.
However, if the values are hardcoded (i.e. as a constant), and will never see the outside world, then yes, it is perfectly OK to concatenate it.
Upvotes: 3
Reputation: 432742
There are pure T-SQL solutions that do not use dynamic SQL.
Some answers here: Dynamic order direction. The accepted answer and my answer demonstrate the 2 approaches. Perhaps SQL Server specific though.
Upvotes: 0
Reputation: 8088
AS long as a user is not allowed input through text or can possibly access the variable through the url, I don't see any negative reason in using string concatenation. Unless like the guy below says, the program may be extended by a different user that isn't so "injection conscious".
Upvotes: 0