如何将下拉列表的选定值传递给查询以便它将返回过滤的记录？ [英] How Do I Pass The Selected Value Of Dropdownlist To The Query So That It Will Return Filtered Records?

问题描述

我需要根据DropDownList选择从数据库中过滤和显示Google Map Markers。为此我需要将选定的DropDownList值传递给查询。

我应该在下面的代码中做什么来完成上述任务？

i need to Filter and display Google Map Markers from database based on DropDownList selection. for that I need to pass the selected value of DropDownList to the query.
What should i do in the following code to do the above task?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace trial2
{
    public partial class explore : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DropDownList1.DataBind();

                ListItem liMainArea = new ListItem("Select", "-1");
                DropDownList1.Items.Insert(0, liMainArea);

                DropDownList2.DataBind();

                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.DataBind();
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);

 
                DropDownList2.Enabled = false;
                DropDownList3.Enabled = false;

                
                
               
            }
            if(!this.IsPostBack)
            {
                DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA]");
                rptMarkers.DataSource = dt;
                rptMarkers.DataBind();
            }
            

            }

        private DataTable GetData(string query)
        {
            string conString = ConfigurationManager.ConnectionStrings["gisConnectionString"].ConnectionString;
            SqlCommand cmd = new SqlCommand(query);
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;

                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DropDownList1.SelectedIndex == 0)
            {            
                DropDownList2.Enabled = false;
                DropDownList2.DataBind();

                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.Enabled = false;
                DropDownList3.DataBind();

                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);
                 
            }
            else
            {
                
               
                DropDownList2.Enabled = true;

                
                DropDownList2.DataBind();
                
                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.SelectedIndex = 0;
                DropDownList3.Enabled = false;
 
            }

        }

       

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DropDownList2.SelectedIndex == 0)
            {             
                DropDownList3.Enabled = false;

                DropDownList3.DataBind();
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);
            }
            else
            {
                DropDownList3.Enabled = true;
               
                
                DropDownList3.DataBind();
                
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);

            }
        }

        protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
        {

        }


    }

}

推荐答案

从不使用字符串连接将参数传递给SQL - 您将使代码容易受到 SQL注入 [ ^ ]。



相反，使用命令的参数集合 [< a href =https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx\"target =_ blanktitle =New Window > ^ ]。



例如：

Never use string concatenation to pass parameters to SQL - you'll leave your code vulnerable to SQL Injection[^].

Instead, pass the parameters using the command's Parameters collection[^].

For example:

private DataTable GetData(string query, params object[] queryParameters)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["gisConnectionString"].ConnectionString))
    using (SqlCommand cmd = con.CreateCommand())
    {
        if (queryParameters != null && queryParameters.Length != 0)
        {
            IFormatProvider provider = CultureInfo.InvariantCulture;
            for (int index = 0; index < queryParameters.Length; index++)
            {
                string name = "@P" + index;
                string placeholder = "{" + index + "}";
                query = query.Replace(placeholder, name);
                cmd.Parameters.AddWithValue(name, queryParameters[index] ?? DBNull.Value);
            }
        }
        
        cmd.CommandText = query;
        
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
    }
}

DataTable dt = GetData(
    "select [Name], [Latitude], [Longitude] from [MAIN AREA]"
    + " where ({0} Is Null Or [Name] = {0})"
    + " And ({1} Is Null Or [Latitude] = {1})"
    + " And ({2} Is Null Or [Longitude] = {2})",
    DropDownList1.SelectedValue,
    DropDownList2.SelectedValue,
    DropDownList3.SelectedValue);

我们只需要在DropDownList1的selectedIndexChange事件中添加以下代码，以便它返回过滤后的记录。

we just need to add the following code in the selectedIndexChange event of the DropDownList1 so that it will return the filtered records.

DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA] WHERE [Name] ='" + DropDownList1.SelectedItem.Text+"'");
            rptMarkers.DataSource = dt;
            rptMarkers.DataBind();

这是使用少量 if..else .. 和 StringBuilder -

替换此部分

Here is a solution using few if..else.. and StringBuilder -
Replace this section

DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA]");
                rptMarkers.DataSource = dt;
                rptMarkers.DataBind();

以下 -

with following-

StringBuilder query=new StringBuilder("select [Name], [Latitude], [Longitude] from [MAIN AREA] where 1=1");
if(DropDownList1.SelectedIndex>0)
{
   query.Append(" and [Name]="+DropDownList1.SelectedValue.ToString());
}
if(DropDownList2.SelectedIndex>0)
{
   query.Append(" and [Latitude]="+DropDownList2.SelectedValue.ToString());
}
if(DropDownList3.SelectedIndex>0)
{
   query.Append(" and [Longitude]="+DropDownList3.SelectedValue.ToString());
}
DataTable dt = this.GetData(query);
rptMarkers.DataSource = dt;
rptMarkers.DataBind();

希望，这有帮助！

如果我错过了请告诉我：）

Hope, it helps !
If I have missed something, please let me know :)

这篇关于如何将下拉列表的选定值传递给查询以便它将返回过滤的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！