在asp.net表比较值 [英] Compare value from tables in asp.net

问题描述

我是编辑我的codebehind文件，并在数据库中我有这4个表连接在一起：

I am editing my codebehind file, and in the database I have this 4 tables linked together:

Table :Compannies
--------------------
companyId
--------------------
companyName
--------------------

和

Table :UserCompany
--------------------
userId
--------------------
companyId
--------------------

和

Table :Users
--------------------
UserId
--------------------
Email
--------------------

和

Table :Choice
--------------------
Email
--------------------
Choice
--------------------

现在，我要检查所有的公司名称，如果选择相应=YES然后做动作。

Now, I want to check all the companyName if the Choice accordingly = "YES" then do the action.

string sqlIns = "SELECT companyName FROM Companies WHERE companyId IN
                  (SELECT companyId FROM UserCompany WHERE userId IN 
                   (SELECT UserId FROM Users WHERE Email IN 
                    (SELECT Email FROM Choice WHERE Choice='YES')"

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                    conn.Open();
try
{
    SqlCommand cmdIns = new SqlCommand(sqlIns, conn);    
    if (cmdIns.Parameters.compareValue("currentCompanyName", companyName))//how to compare?
    {
         //action here
    }
    else
    {
         //another action
    }

    cmdIns.ExecuteNonQuery();

    cmdIns.Parameters.Clear();
    cmdIns.Dispose();
    cmdIns = null;
}
catch (Exception ex)
{
      throw new Exception(ex.ToString(), ex);
}
finally
{
      conn.Close();
}

我不知道如何比较，从表中提取的价值，没有任何语法或逻辑错误在我的code？

I don't know how to compare the value that extract from the tables, and is there any grammar or logic error in my code?

推荐答案

尝试使用内加入代替嵌套查询：

Try using inner-join instead of nested queries:

下面是更改的查询：

SELECT cn.companyName from companies cn 
INNER JOIN UserCompany uc ON uc.companyid = cn.companyid 
INNER JOIN Users u ON u.userId = uc.userId
INNER JOIN Choice c ON c.Email = u.Email And c.Choice = @Choice
WHERE cn.CompanyName = @CompanyName;

举例code会是这样的：

Example code would be like that:

string commandText = @"SELECT cn.companyId, cn.companyName from companies cn 
                       INNER JOIN UserCompany uc ON uc.companyid = cn.companyid 
                       INNER JOIN Users u ON u.userId = uc.userId
                       INNER JOIN Choice c ON c.Email = u.Email And c.Choice = @Choice
                       WHERE cn.CompanyName = @CompanyName;";

string connectionString = ConfigurationSettings.AppSettings["connectionString"];

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);        

    command.Parameters.AddWithValue("@Choice", "YES");
    command.Parameters.AddWithValue("@CompanyName", "currentCompanyName");

    try
    {
        connection.Open();            

        datagridview.DataSource = command.ExecuteReader();
        datagridview.DataBind();

    }
    catch (Exception ex)
    {
       //handle exception
    }
}

这篇关于在asp.net表比较值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！