Combobox items based on another combobox selected items data from SQL Server database

Question

I have two combo boxes where the first one has categories that I populate from a SQL Server database. The trick is having the second combo box show only the items from the DB that are associated with the chosen category from the first combo box.

Here my SQL code:

IF @ActionType = 'FetchDataCBOCity'  
BEGIN  
    SELECT DISTINCT ID_City, Name_City 
    FROM City
END

IF @ActionType = 'FetchDataCBOState'  
BEGIN  
    SELECT ID_State, Name_State 
    FROM State 
END

Here my C# code:

ConnectionTCP.CboFetchData(new List<string> { "FetchDataCBOCity", spName }, "ID_City", "Name_City", comboBox1);
ConnectionTCP.CboFetchData(new List<string> { "FetchDataCBOState", spName }, "ID_State", "Name_State", comboBox2);


 // CBO Fetch Data in db
        public static void CboFetchData( List<string> dataList, string valueMember, string displayMember, ComboBox cbo )
        {
            try
            {
                string phrase = "CBOFETCHDATA" + ">";
                foreach (var data in dataList)
                {
                    phrase += data + ">";
                }

                byte[] message = Encoding.ASCII.GetBytes(phrase.TrimEnd('>'));
                stream.Write(message, 0, message.Length);

                var buffer = getData(tcpClient);

                cbo.DataSource = DataFormatter.DeserializeData(buffer);
                cbo.ValueMember = valueMember;
                cbo.DisplayMember = displayMember;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error: " + e.Message, Client.nameApp, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Thanks

Answer 1

I guess, the easiest way will be to use DataSet with related DataTables but you can create data sources that are related

// Declare combo items
public class ParentInfo
{
    int Id {get; set;}
    string Display {get; set;}
}

public class ChildInfo
{
    int Id {get; set;}
    int ParentId {get; set;}
    string Display {get; set;}
}

// Load lists
List<ParentInfo> _parents;
List<ChildInfo> _children;
// . . . 
// Set parent combo, this one is not changing
cboParents.ValueMemeber = "Id";
cboParents.DisplayMemeber = "Display";
cboParents.DataSourse = _parents;

// . .  . . . 
// Change children for different parents
void OnParent_SelectedIndexChanged( . .  . .)
{
    if (cboParents.SelectedInfex = -1)
    {
        cboChildren.DataSource = null;
        return;
    }
    cboChildren.ValueMemeber = "Id";
    cboChildren.DisplayMemeber = "Display";
    ParentInfo currentP = (ParentInfo)cboParents.SelectedItem;
    cboChildren.DataSourse = _children.Where(c => c.ParentId == currentP.Id).ToList();

}