单个选择查询中的条件 [英] conditioning inside a single select query

问题描述

我对HR应用程序有些困惑，我遇到了三种类型的用户

1)普通用户(基于位置的用户)，分配类型为U

2)管理员(基于公司)，分配的类型为A

3)管理分配的类型M

我的问题在于，DATAGRIDVIEW格式的普通用户(HR)仅应获得其所在位置(分支)的员工详细信息，而管理员应获取公司公司的整个员工详细信息，而管理层应获取其所有公司集团的整个emp详细信息

我有将近56种表单，其中我可以调用选择查询来获取类似
的数据

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

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

但我的要求是

对于普通用户

BUT MY REQUIREMENT IS

For normal user

(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", "*");
       }

但是在每个函数中调用三个不同的sql查询是不可能的
那么，有什么想法可以同时处理这两个查询吗?
就像

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

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

推荐答案

您的数据库架构允许的ID，您应该创建一个存储过程来处理用户的类型并返回特定结果设置.

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

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.

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

您似乎将参数绑定到没有任何查询的对象(如Anele在上面的注释中指出的那样)，则可以使用Union在同一命令中合并这两个查询.例如:

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:

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:

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

这样，您可以在一个查询中合并两个或多个查询.

另外，请注意，我使用了一个小技巧，我不知道它是否可以在SQL Server中使用，但是它可以在Oracle和MySQL中使用，因此我假设它也可以在SQL Server中使用:

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:

SELECT 'U' AS type

这是给每行一个指示，指出它来自哪个Select语句.

另外，您需要了解并注意以下事项:使用Union/Union All时，来自Select语句的字段必须具有相同的类型，并且在所有Select语句中查询的字段数必须相同.

同样，看到您正在从同一张表中选择字段，但是您可能使用了不同的条件，而不是用简单的In合并也可以:

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:

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

进一步的联盟阅读此处 [

Further Union reading here[^]

这篇关于单个选择查询中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！