How to filter one dropdownlist based on another se

问题:

I have the following code which populates the Topic dropdownlist and saves it to a cached table:

bookingData2 = new DataTable();
DataTable DTable_List = new DataTable();
string connString = @"";
string query2 = @"Select * from [DB].dbo.[top]";// columng #1 = Specialty and column #2 = Topic
using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        SqlCommand cmd = new SqlCommand(query2, conn);
        SqlDataAdapter da = new SqlDataAdapter(query2, conn);
        da.Fill(bookingData2);

        HttpContext.Current.Cache["cachedtable2"] = bookingData2;

        bookingData2.DefaultView.Sort = "Topic ASC";

        Topic.DataSource = bookingData2.DefaultView.ToTable(true, "Topic"); // populate only with the Topic column
        Topic.DataTextField = "Topic";
        Topic.DataValueField = "Topic";
        Topic.DataBind();
        Topic.Items.Insert(0, new ListItem("All Topics", "All Topics"));

        da.Dispose();
    }
    catch (Exception ex)
    {
        string error = ex.Message;
    }
}

I have the following code which populates the Specialty dropdownlist and saves it to another cached table:

bookingData = new DataTable();
DataTable DTable_List = new DataTable();
string connString = @"";
string query = @"select * from [DB].dbo.[SP]";

using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        SqlCommand cmd = new SqlCommand(query, conn);
        SqlDataAdapter da = new SqlDataAdapter(query, conn);
        da.Fill(bookingData);

        bookingData.DefaultView.Sort = "Specialty ASC";

        Specialty.DataSource = bookingData.DefaultView.ToTable(true, "Specialty");
        Specialty.DataTextField = "Specialty";
        Specialty.DataValueField = "Specialty";
        Specialty.DataBind();
        Specialty.Items.Remove("All Specialties");
        Specialty.Items.Insert(0, new ListItem("All Specialties", "All Specialties"));

        da.Dispose();
    }
    catch (Exception ex)
    {
        string error = ex.Message;
    }
}

How can I code the Specialty dropdownlist index change to do the following and save it to a cache table for quick access:

protected void Specialty_SelectedIndexChanged(object sender, EventArgs e)
{
    //re-populate the Topic dropdownlist to display all the topics based on the following criteria:
        --> Where the Specialty column is either "All Specialties" OR "{specialty selected index value}"
}

回答1:

Save bookingData2 table in ViewState or Session (I won't recommend to use session though) if it's not too heavy. Otherwise, its better you cache it or query the database again to repopulate it.

Let's assume you save bookingData2 in ViewState as follows in Page_Load

ViewState["bookingData2"] = bookingData2; // This should be before the following line
Topic.DataSource = bookingData2.DefaultView.ToTable(true, "Topic");

Then in your SelectedIndexChanged event do something like this

protected void Specialty_SelectedIndexChanged(object sender, EventArgs e)
{
    //re-populate the Topic dropdownlist to display all the topics based on the following criteria:
    // Where the Specialty column is either "All Specialties" OR "{specialty selected index value}"
    DataTable bookingData2 = (DataTable)ViewState["bookingData2"];

    Topic.DataSource = bookingData2.Where(i => i.Specialty == "All Specialties" || i.Specialty == Specialty.SelectedValue).DefaultView.ToTable(true, "Topic"); // populate only with the Topic column
    Topic.DataTextField = "Topic";
    Topic.DataValueField = "Topic";
    Topic.DataBind();
    Topic.Items.Insert(0, new ListItem("All Topics", "All Topics"));

}

Update - With Cached object

Do following in Specialty_SelectedIndexChanged event instead of where we used ViewState before.

if (HttpRuntime.Current.Cache["cachedtable2"] != null)
{
    DataTable bookingData2 = HttpRuntime.Current.Cache["cachedtable2"] as DataTable;
    // Rest of the code
}

I haven't tried this code. Let me know if you find any issues.

回答2:

This is what solved it for me:

protected void Topic_SelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        if (Topic.SelectedIndex == 0)
        {
            string query = @"Specialty LIKE '%%'";

            DataTable cacheTable = HttpContext.Current.Cache["cachedtable"] as DataTable;
            DataTable filteredData = cacheTable.Select(query).CopyToDataTable<DataRow>();

            filteredData.DefaultView.Sort = "Specialty ASC";

            Specialty.DataSource = filteredData.DefaultView.ToTable(true, "Specialty");
            Specialty.DataTextField = "Specialty";
            Specialty.DataValueField = "Specialty";
            Specialty.DataBind();
        }
        else
        {
            string qpopulate = @"[Topic] = '" + Topic.SelectedItem.Value + "' or [Topic] = 'All Topics'"; //@"Select * from [DB].dbo.[table2] where [Specialty] = '" + Specialty.SelectedItem.Value + "' or [Specialty] = 'All Specialties'";
            DataTable cTable = HttpContext.Current.Cache["cachedtable2"] as DataTable;
            DataTable fData = cTable.Select(qpopulate).CopyToDataTable<DataRow>();

            if (fData.Rows.Count > 0)
            {
                fData.DefaultView.Sort = "Specialty ASC";

                Specialty.DataSource = fData.DefaultView.ToTable(true, "Specialty");
                Specialty.DataTextField = "Specialty";
                Specialty.DataValueField = "Specialty";
                Specialty.DataBind();
            }
            Specialty.Items.Insert(0, new ListItem("All Specialties", "All Specialties"));
        }
    }
    catch (Exception ce)
    {
        string error = ce.Message;
    }
}