conditioning inside a single select query

Question

4.00/5 (1 vote)

See more:

I am a little bit Confused for my HR Application I had three type of Users

1) Normal User(location based user), assigned type U

2) Administrator(company based), assigned type A

3) Management assigned type M

My problem is in forms DATAGRIDVIEW normal user(HR) should get the employee details of his location(branch) only while the Administrator should get the whole employee details the company company and the Management should get the whole emp details of all their group of companies

I have almost 56 forms where Ia m Calling select query to get data like

C#

SqlCommand cmd = new SqlCommand("Select * from EMPMASTERTBL" ,CON);
cmd.Parameters.AddWithValue("@Param1", empshiftdata.Empid);

BUT MY REQUIREMENT IS

For normal user

C#

(if program.usertype="n"){
     SqlCommand cmd = new SqlCommand("Select * from EMPMASTERTBL where branchlctn=@Param1",CON);
                    cmd.Parameters.AddWithValue("@Param1", program.lctnpk);
    
    }

for management

(if program.usertype="M"){
        SqlCommand cmd = new SqlCommand("Select * from EMPMASTERTBL",CON);
                       cmd.Parameters.AddWithValue("@Param1", "*");
       }

But it is not posible to call three different sql queries in each function
So Is there any idea to concat the two queries?
like

C#

SqlCommand cmd = new SqlCommand("Select * from EMPMASTERTBL where branchlctn=@Param1",CON);
                       cmd.Parameters.AddWithValue("@Param1", program.lctnpk);//for normal user
                        cmd.Parameters.AddWithValue("@Param1", "*");//for Management user

Posted 25-Sep-12 20:09pm

SREENATH GANGA

Updated 25-Sep-12 20:30pm

Kenneth Haugland

v3

Add a Solution

Comments

Anele Ngqandu 26-Sep-12 2:27am

does the management code work? because as far as i can see you have 2 sql requirements.other 1 needs a oparameter an the other 1 does not need parameter. so i dont think its posible,otherwise you will have wrong results

Tejas Vaishnav 26-Sep-12 3:12am

you need to write store procedure for that, and handle this different criteria for select query.

writing inline queries is a bad programming style, so please use store procedures instead of inline queries sp is also faster to execute.

SREENATH GANGA 26-Sep-12 4:04am

thsnks Iam a new bie and is not so good in Stored procedures so to reduce learning time i chose inline queries

Tejas Vaishnav 26-Sep-12 6:33am

OK, but try to learn proper thing it will helps you a lot in future.

Strange_Pirate 27-Sep-12 9:47am

Great Link

Strange_Pirate 27-Sep-12 9:46am

Is it Your Personal Profile ??

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Oshtri Deka · Accepted Answer · 2012-09-25T21:03:00

Id your database schema allows it, you should make a stored procedure which handles user's type and returns specific result-set.

SQL

CREATE PROCEDURE MyProcedure
(
@param1 typeofParam1 --this is BAD, parameters should have proper names
,@userType char
,@someotherParameter_If_Necessary someType)

AS
    BEGIN

        IF @userType = 'n'
            BEGIN
                 --SELECT query for N
            END
        ELSE IF @userType = 'a'
            BEGIN
                 --SELECT query for A
            END
        ELSE
            BEGIN
                 --SELECT query for M
            END

    END

Then you can call your new procedure from your code and simply supply it with correct parameters.

C#

SqlCommand cmd = new SqlCommand();
cmd.COnnection = CON;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "procedureName";
//parameters handling
//execution
//etc.

**Andrei Straut** · Accepted Answer · 2012-09-25T20:45:00

Setting aside that you seem to be binding parameters to queries that don't have any (as Anele pointed out in the comment above), you could concat the two queries in the same command using a Union. For instance:

C#

SqlCommand cmd = new SqlCommand(
    "SELECT 'U' AS type, field1, field2, field3 " +
        "FROM EMPMASTERTBL "
        "WHERE branchlctn=@Param1 " 
    "UNION " + // You can use UNION ALL if you also want duplicate rows displayed
    "SELECT 'M' as type,  field1, field2, field3 " + 
        "FROM EMPMASTERTBL " + 
        "WHERE empid=@Param2 "    
    ,CON);

The you bind the parameters normally:

C#

cmd.Parameters.AddWithValue("@Param1", program.lctnpk);
cmd.Parameters.AddWithValue("@Param2", empshiftdata.Empid);

In this way you can concat two or more queries in a single one.

Also, notice I've used a little hack, which I don't know if it works in SQL Server, but it does in Oracle and MySQL, so I'm assuming it will work in SQL Server too:

SQL

SELECT 'U' AS type

This is to give per each row an indication from which Select statement it came from.

Also, what you need to know and take care of: when you use Union / Union All, the fields that come from the Select statements must be of the same type, and there has to be the same number of fields queried in all Select statements.

Also, seeing that you are selecting fields from the same table but you may different Where conditions, instead of Union a simple In may work too:

SQL

"SELECT field1, field2, field3 FROM EMPMASTERTBL WHERE branchlctn IN (@Param1, @Param2, @Param3)"

Further Union reading here[^]

conditioning inside a single select query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0