How to populate dropdownlist based on a DataTable

I have the following code which populates a ASP.net label with values retrieved from a SP and a CachedTable:

public void UpdateDropDownList()
{
    string strQuery = "";

    using (SqlConnection scCon = new SqlConnection(connString))
    {
        using (SqlCommand scCmd = new SqlCommand("cation", scCon))
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            DataTable dt = new DataTable();
            scCmd.CommandType = CommandType.StoredProcedure;

            scCmd.Parameters.Add("@ation", SqlDbType.VarChar).Value = cation.SelectedItem.Value;

            sda.SelectCommand = scCmd;
            sda.Fill(dt);

            var distinctValues = dt.AsEnumerable()
                   .Select(row => row.Field<string>("Specialty"))
                   .Distinct();
            Label1.Text = "All Specialties<br/>";
            Label1.Text += string.Join("<br />", distinctValues); //Displays all specialties related to the location
            var k = distinctValues.ToArray();
            strQuery = "(";
            for (int y = 0; y < k.Length; y++)
            {
                if (y == 0)
                {
                    strQuery += @"[Specialty] = '" + k[y] + "'";
                }
                else
                {
                    strQuery += @" OR [Specialty] = '" + k[y] + "'";
                }
            }
            strQuery += @") AND ([Location] = '" + Location.SelectedItem.Value + "')";
            DataTable cacheTable2 = HttpContext.Current.Cache["cachedtable"] as DataTable; //first cached table
            DataTable filteredData2 = cacheTable2.Select(strQuery).CopyToDataTable<DataRow>();

            var distinctValues2 = filteredData2.AsEnumerable()
                   .Select(row => row.Field<string>("Name"))
                   .Distinct();
            Label1.Text += "All Providers<br/>";
            Label1.Text += string.Join("<br />", distinctValues2); //Displays all providers related to the location

            strQuery = "([Specialty] = 'All Specialties'";
            for (int y = 0; y < k.Length; y++)
            {
                strQuery += @" OR [Specialty] = '" + k[y] + "'";
            }
            strQuery += ")";
            DataTable cacheTable3 = HttpContext.Current.Cache["cachedtable2"] as DataTable; //different cached table
            DataTable filteredData3 = cacheTable3.Select(strQuery).CopyToDataTable<DataRow>();

            var distinctValues3 = filteredData3.AsEnumerable()
                   .Select(row => row.Field<string>("Topic"))
                   .Distinct();
            Label1.Text += "All Topics<br/>";
            Label1.Text += string.Join("<br />", distinctValues3); //Displays all topics related to the specialty of that location

            Session.Add("DTTableLocation", dt);
        }
    }
}

I have four dropdownlist:

<asp:DropDownList ID="Location" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Location_SelectedIndexChanged"></asp:DropDownList>
<asp:DropDownList ID="Specialty" runat="server" AutoPostBack="true"></asp:DropDownList>
<asp:DropDownList ID="Name" runat="server" AutoPostBack="true"></asp:DropDownList>
<asp:DropDownList ID="Topic" runat="server" AutoPostBack="true"></asp:DropDownList>

The above C# code is executed for the Location_SelectedIndexChanged.

How can I replace the following:

Label1.Text = "All Specialties<br/>";
Label1.Text += string.Join("<br />", distinctValues); //Displays all specialties related to the location

with populating the Specialty dropdownlist (The DataField and the DataValue is the value).

Label1.Text += "All Providers<br/>";
Label1.Text += string.Join("<br />", distinctValues2); //Displays all providers related to the location

with populating the Name dropdownlist (The DataField and the DataValue is the value).

Label1.Text += "All Topics<br/>";
Label1.Text += string.Join("<br />", distinctValues3); //Displays all topics related to the specialty of that location

with populating the Topic dropdownlist (The DataField and the DataValue is the value).

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Specialty.DataSource = distinctValues
Specialty.DataBind()

You may need to adjust the DataTextField and DataValueField properties, but I can’t tell what those should be.

Do the same for all three dropdownlists.

I would also divide this code into separate functions. Maybe GetSpecialties which retrieves all the specialties from the database. GetProviders which builds the query and returns the providers from the cached table. And GetTopics which builds that query and returns the Topics.

That would make the main function much shorter and easier to understand. As it is right now it is very hard to read.

var distinctSpecialties = GetSpecialties();
Specialty.DataSource = distinctSpecialties;
Specialty.DataBind();

var distinctProviders = GetProviders(distinctSpecialties);
Name.DataSource = distinctProviders;
Name.DataBind();

var distinctTopics = GetTopics(distinctSpecialties);
Topic.DataSource = distinctTopics;
Topic.DataBind();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0