使用c#下拉列表进行高级搜索

本文关键字:高级 搜索 下拉列表 使用 | 更新日期: 2023-09-27 17:50:53

我正在做一个预先搜索代码…我有6个下拉列表,用户可以从一个或所有下拉列表中选择一个或多个值,或者选择"-"值,表示没有选择值。我的代码正在工作,结果是所有值的联合…我怎样才能只找到相交点呢?

我的意思是,如果我从第一个下拉列表中选择(亚洲),从第二个下拉列表中选择(阿拉伯语),我的结果是所有亚洲国家和所有使用阿拉伯语的国家。我怎么能只有说阿拉伯语的亚洲国家>>相交呢?

if (!Class1.Search_Continent.Equals("-"))//DropDownList1.SelectedValue.ToString();
        {
            sunc.conn.Open();
            SqlCommand cmd1 = new SqlCommand("Select Country_name FROM Country WHERE Continent_name='" + DropDownList1.SelectedValue + "'", sunc.conn);
            SqlDataReader dr1;
            dr1 = cmd1.ExecuteReader();
            while (dr1.Read())
            {DropDownList9.Items.Add(dr1["Country_name"].ToString());}
            sunc.conn.Close();
            if (!Class1.Search_Country.Equals("-"))//DropDownList2.SelectedValue.ToString();
            {
                RemoveDuplicateItems(DropDownList9);
                sunc.conn.Open();
                SqlCommand cmd2 = new SqlCommand("Select Country_name FROM Country WHERE Country_name='" + DropDownList2.SelectedValue + "'", sunc.conn);
                SqlDataReader dr2;
                dr2 = cmd2.ExecuteReader();
                while (dr2.Read())
                {DropDownList9.Items.Add(dr2["Country_name"].ToString());}
                sunc.conn.Close();
                if (!Class1.Search_City.Equals("-"))//DropDownList3.SelectedValue.ToString();
                {
                    RemoveDuplicateItems(DropDownList9);
                    sunc.conn.Open();
                    SqlCommand cmd3 = new SqlCommand("Select Country_name FROM City WHERE City_name='" + DropDownList3.SelectedValue + "'", sunc.conn);
                    SqlDataReader dr3;
                    dr3 = cmd3.ExecuteReader();
                    while (dr3.Read())
                    {
                        DropDownList9.Items.Add(dr3["Country_name"].ToString());
                    }
                    //dr3.Close();
                    //conn3.Close();
                    sunc.conn.Close();
                    if (!Class1.Search_Religion.Equals("-"))//DropDownList4.SelectedValue.ToString();
                    {
                        RemoveDuplicateItems(DropDownList9);
                        //SqlConnection conn4 = new SqlConnection(@"Data Source=AK-PC'MSSQLSERVER1;Initial Catalog=DB;Integrated Security=True");
                        //conn4.Open();
                        sunc.conn.Open();
                        SqlCommand cmd4 = new SqlCommand("Select Country_name FROM Religion WHERE Religion_name='" + DropDownList4.SelectedValue + "'", sunc.conn);
                        SqlDataReader dr4;
                        dr4 = cmd4.ExecuteReader();
                        while (dr4.Read())
                        {
                            DropDownList9.Items.Add(dr4["Country_name"].ToString());
                        }
                        //dr4.Close();
                        //conn4.Close();
                        sunc.conn.Close();
                        if (!Class1.Search_Type.Equals("-"))//DropDownList5.SelectedValue.ToString();
                        {
                            RemoveDuplicateItems(DropDownList9);
                            //SqlConnection conn5 = new SqlConnection(@"Data Source=AK-PC'MSSQLSERVER1;Initial Catalog=DB;Integrated Security=True");
                            //conn5.Open();
                            sunc.conn.Open();
                            SqlCommand cmd5 = new SqlCommand("Select Country_name FROM Country WHERE Type_of_government='" + DropDownList5.SelectedValue + "'", sunc.conn);
                            SqlDataReader dr5;
                            dr5 = cmd5.ExecuteReader();
                            while (dr5.Read())
                            {
                                DropDownList9.Items.Add(dr5["Country_name"].ToString());
                            }
                            //dr5.Close();
                            //conn5.Close();
                            sunc.conn.Close();
                            if (!Class1.Search_Language.Equals("-"))//DropDownList6.SelectedValue.ToString();
                            {
                                RemoveDuplicateItems(DropDownList9);
                                //SqlConnection conn6 = new SqlConnection(@"Data Source=AK-PC'MSSQLSERVER1;Initial Catalog=DB;Integrated Security=True");
                                //conn6.Open();
                                sunc.conn.Open();
                                SqlCommand cmd6 = new SqlCommand("Select Country_name FROM Language WHERE Language_name='" + DropDownList6.SelectedValue + "'", sunc.conn);
                                SqlDataReader dr6;
                                dr6 = cmd6.ExecuteReader();
                                while (dr6.Read())
                                {
                                    DropDownList9.Items.Add(dr6["Country_name"].ToString());
                                }
                                //dr6.Close();
                                //conn6.Close();
                                sunc.conn.Close();
                                if (DropDownList1.SelectedValue.Equals("-") && DropDownList2.SelectedValue.Equals("-") &&
                                    DropDownList3.SelectedValue.Equals("-") && DropDownList4.SelectedValue.Equals("-") &&
                                    DropDownList5.SelectedValue.Equals("-") && DropDownList6.SelectedValue.Equals("-"))
                                {
                                    Button2.Enabled = false;
                                    Label1.Text = "you have to choose from the dropdown list";
                                }
                                else if (DropDownList9.SelectedValue.Equals("-"))
                                {
                                    Button2.Enabled = false;
                                    Label1.Text = "No result ";
                                }
                            }
                        }
                    }
                }
            }
        }

使用c#下拉列表进行高级搜索

我会修改你的代码,使它基于不同的选项创建一个查询,然后只返回该查询的结果。

例如:

string query = "Select Country_name FROM Country WHERE Continent_name='" + DropDownList1.SelectedValue + "'";
if (!Class1.Search_Country.Equals("-"))
   query+= " and Country_name='" + DropDownList2.SelectedValue + "'";
SqlCommand cmd1 = new SqlCommand(query, sunc.conn);

通常您希望在单个查询中执行此操作,例如:

SELECT Country_Name
FROM Country C
    INNER JOIN City CTY on (CTY.Country_Name = C.Country_Name)
    INNER JOIN Religion R on (R.Country_Name = C.Country_Name
WHERE ((@City ='') or (CTY.City_Name = @City))
  AND ((@Religion ='') or (R.Religion_Name = @Religion))
  AND ((@Government = '') or (C.Type_of_Government = @Government))

然后将@City、@Religion和@Government作为参数传递给查询。如果传入任何单独的参数,则WHERE子句将对其进行过滤;或者忽略该参数是否为空。

您必须像下面这样修改查询。

SqlCommand cmd1 = new SqlCommand("Select Country_name FROM Country WHERE (Continent_name='" + DropDownList1.SelectedValue + "' or Continent_name=Continent_name) AND (Country_name='" + DropDownList2.SelectedValue + "' OR Country_name=Country_name) AND (City_name='" + DropDownList3.SelectedValue + "' OR City_name=City_name) AND (Religion_name='" + DropDownList4.SelectedValue + "' OR Religion_name=Religion_name) AND (Type_of_government='" + DropDownList5.SelectedValue + "' OR Type_of_government=Type_of_government) AND (Language_name='" + DropDownList6.SelectedValue + "' OR Language_name=Language_name)", sunc.conn);

希望这有帮助!!