Change SQL select command operator in WHERE part at runtime

Question

I am trying to implement user rights management in a project with C# and asp.net. What I want to do is simply showing the relevant pages to the user according to the login name. I wrote lots of SQL select commands.

Is there a way to change the WHERE part only in a SQL select command? I mean for example when a user clicks on a button

...WHERE REGION='IC' becomes ...WHERE REGION<>'IC'

Or can you please advise any other practices to implement such a scenario?

Thanks in advance

Edit: My select commands

SELECT * FROM (SELECT Firma,BOLGE,SUM(KDV_MATRAHI) AS TUTAR from SATISLAR_T WHERE DAHIL=0 AND REGION=REGION  GROUP BY Firma,BOLGE UNION SELECT Firma,CH_YETKI_KODU AS BOLGE, SUM(KDV_MATRAHI)  AS TUTAR
FROM LNX_STD_6_016_01_SLSINVOICES WHERE MALZEME_OZEL_KODU<>'DİĞER GLR'   AND REGION=REGION GROUP BY REGION, Firma) AS BOLGE
  PIVOT
(
SUM(TUTAR)
    FOR Firma IN ([008] ,[009] ,[010] ,[011], [012], [013], [014] ,[015],[016])
)AS pvt

After user clicks on the button:

SELECT * FROM (SELECT Firma,BOLGE,SUM(KDV_MATRAHI) AS TUTAR from SATISLAR_T WHERE DAHIL=0 AND REGION='IC'  GROUP BY Firma,BOLGE UNION SELECT Firma,CH_YETKI_KODU AS BOLGE, SUM(KDV_MATRAHI)  AS TUTAR
FROM LNX_STD_6_016_01_SLSINVOICES WHERE MALZEME_OZEL_KODU<>'DİĞER GLR'   AND REGION<>'IC' GROUP BY CH_YETKI_KODU, Firma) AS BOLGE
  PIVOT
(
SUM(TUTAR)
    FOR Firma IN ([008] ,[009] ,[010] ,[011], [012], [013], [014] ,[015],[016])
)AS pvt

Of course it's possible to change the WHERE part. What do you mean with "at runtime". Please show us your code and your selects (give a useful example of what you want to do). — Seb, Dec 08 '16 at 09:22
By "at runtime" I mean when a user clicks a button, the code will run inside the click handler. I added select into the original question. I am not using stored procedures. — Orkun Antmen, Dec 08 '16 at 09:46
This is actually about giving users different views according to their rights. — Orkun Antmen, Dec 08 '16 at 10:42
So build your query on the side where you're building shown above. What's the problem you are facing? It's all about concatenating strings. — Ivan Starostin, Dec 08 '16 at 13:58
I dont want to write queries for every other user. I just want to change the WHERE part of the query based on the user right. I mean when a specific user logs in who has the right to see the customer from "IC" region the first query should run. There could be lots of different regions or lots of other filter parameters. — Orkun Antmen, Dec 09 '16 at 08:14
I don't understand what are your objections. You want to change WHERE clause - do change it. You are working with ad-hocs. Do change your ad-hoc "based on user permissions". Each one of them has his own app instance, doesn't he? — Ivan Starostin, Dec 09 '16 at 09:30

score 1 · Answer 1 · answered Dec 08 '16 at 09:25

1

A quick and dirty way is to pass a parameter

where 
  ((@Param=1 and REGION = 'IC')
    or
  (@Param=2 and REGION <> 'IC'))

answered Dec 08 '16 at 09:25

JohnHC

10,935
1
24
40

I like this idea, perhaps to be slightly more efficient you could have two different procedures/views one that is Not Equal and one that is Equal. if the op is using procedures and there's more logic than just a Where clause prehaps put that into a view and then 2 procedures – Luke Franklin Dec 08 '16 at 09:49

Dmitry Bychenko · Answer 2 · 2016-12-08T12:55:16.143

1

I suggest formatting when forming SQL:

// depending on condition 
// we put either REGION='IC' or REGION<>'IC' instead of {0} place holder
string sql = string.Format( 
    // Make slq readable and maintainable, use @ verbatim strings
  @"select ...
     where {0} 
     ...", condition ? "REGION='IC'" : "REGION<>'IC'"); 

using (var command = new SqlCommand(connection)) {
  command.CommandText = sql;
  ...
}

In case of C# 6.0, string interpotaion is an alternative:

string sql =  
  $@"select ...
      where {(condition ? "REGION='IC'" : "REGION<>'IC'")} 
      ...";

edited Dec 08 '16 at 12:55

answered Dec 08 '16 at 09:29

Dmitry Bychenko

180,369
20
160
215

Well this solution seem to solve this problem, I did not understand how to use it at first look but I will try. Thanks. – Orkun Antmen Dec 08 '16 at 09:50

score 0 · Answer 3 · answered Dec 08 '16 at 09:24

0

The obvious way to implement that functionality would be to have the two different queries as functions and call the appropriate ones depending on the state of the button. No clever code or tricks required.

answered Dec 08 '16 at 09:24

Luiz Carlos Ciafrino Neto

86
1
6

Actually I have many SQL commands and lots of users so that will mean maybe writing double or triple queries again. – Orkun Antmen Dec 08 '16 at 09:47
I understand your concern. The reply below by Dmitry is a good enough solution then, however bear in mind that maintenance of such code could turn into a problem later down the road. – Luiz Carlos Ciafrino Neto Dec 08 '16 at 09:50
So can you recommend any other way for this? Just tell me few words for me to google it. – Orkun Antmen Dec 08 '16 at 09:56

Change SQL select command operator in WHERE part at runtime

3 Answers3