How to pass value from a dropdownlist to SQL to get numerical information using C#?

Question

The following C# code is used to fill a dropdownlist with the description as you can see from the SQL statement. But, at the same time, I need to get the numerical values 2.0 and 2.5 based on the selection from the ddl:

• '2.5' for 'Open 2.5"
• '2' for 'Red. w/45° 2"

The idea of pulling data from SQL to fill a ddl is to leave open doors for future additions to more data in SQL that will be added automatically to the ddl. How can I pass the very selected value from the ddl to get then the numerical numbers from the same column Diameter in order to make the necessary calculations based on numbers 2 and 2.5 inches?

This query: SELECT Diameter FROM [tblNozzleDetail] where Diameter != 0 is the one that pulls the numerical values (2 and 2.5 inches), but the ddl must be filled with the descriptions + values as well (I know, I could do it hardcoded or with the properties behind the ddl).

This is the C# code:

public void GetFlowRestrictionDiametersValues()
{
    ModalPopupUpdate.Show();
    try
    {
        List<string> ListFlowRestrictionDiametersValues = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["someString"].ToString()))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(@"SELECT case when Diameter = '2.5' then 'Open 2.5""' when Diameter = '2' then 
                'Red. w/45° 2""' else [Stream Restriction] end as Diameter FROM [tblNozzleDetail] where Diameter != 0 ORDER BY DIAMETER", conn);

            {
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        diametersValues = reader[0].ToString();
                        ListFlowRestrictionDiametersValues.Add(diametersValues);
                    }
                }
                reader.Close();
            }
        }
        cbFlowRestrictionDiameterModal.Items.Clear();
        int counter = 1;
        cbFlowRestrictionDiameterModal.Items.Insert(0, new ListItem("(Select)", "0"));
        foreach (string item in ListFlowRestrictionDiametersValues)
        {
            cbFlowRestrictionDiameterModal.Items.Add(new ListItem(item, counter.ToString()));
            counter++;
        }
    }
    catch (SqlException ex)
    {
        //Log exception

    }
}

Any suggestion or correction with a nice attitude will be very much appreciated. Sorry to ask too much. I am new programmer. By the way, the database was created in that way. I do not have too many options.

Please see the following image:

Answer 1

Change you sql query to

SELECT 
   case 
      when Diameter = '2.5' then 'Open 2.5""'
      when Diameter = '2' then 'Red. w/45° 2""' 
      else [Stream Restriction] end as DisplayName,
   Diameter AS [Value]    
FROM [tblNozzleDetail] where Diameter != 0 ORDER BY DIAMETER

Bind (DisplayName,Value) to dropdownlist list item. And yourdropdowlist.SelectedValue would return value your need

Maybe like this

Create a class for dropdown list data

public class DropDownListClass
    {
        public string DisplayName { get; set; }
        public string Value { get; set; }
    }

And your method in code behind

public void GetFlowRestrictionDiametersValues()
    {
        ModalPopupUpdate.Show();
        try
        {
            List<DropDownListClass> ListFlowRestrictionDiametersValues = new List<DropDownListClass>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["someString"].ToString()))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT 
                                                   case 
                                                      when Diameter = '2.5' then 'Open 2.5'
                                                      when Diameter = '2' then 'Red. w/45° 2' 
                                                      else [Stream Restriction] end as DisplayName,
                                                   Diameter AS [Value]    
                                                 FROM [tblNozzleDetail] where Diameter != 0 ORDER BY DIAMETER", conn);

                {
                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            ListFlowRestrictionDiametersValues.Add(new DropDownListClass()
                            {
                                DisplayName = (reader["DisplayName"]).ToString(),
                                Value = (reader["Value"]).ToString()
                            });
                        }
                    }
                    reader.Close();
                }
            }
            cbFlowRestrictionDiameterModal.Items.Clear();
            cbFlowRestrictionDiameterModal.Items.Insert(0, new ListItem("(Select)", "0"));
            foreach (var item in ListFlowRestrictionDiametersValues)
            {
                cbFlowRestrictionDiameterModal.Items.Add(new ListItem(item.DisplayName, item.Value));
            }
        }
        catch (SqlException ex)
        {
            //Log exception

        }
    }

Answer 2

Thanks @TriV, I just did a correction and used:

                            ListFlowRestrictionDiametersValues.Add(new DropDownListClass()
                        {
                            DisplayName = (reader["DisplayName"]).ToString(),
                            Value = (reader["Value"]).ToString()
                        });

Thanks a lot! I did not have an idea of what to do. It gives me the right values: 2 and 2.5 plus the list down.