Search code examples
c#winformswhile-loopcomboboxdatareader

Nested While loop on DataReader.read escaping on second parent loop to fill a combo box C#

I have a TopCategory Table with ParentId field to itself and then the RootCategory with ParentId referring to TopCategory. Since Rootcategory is the only one linked to the Products table. I am trying to generate single combobox on add new product which shows nested list or list that includes Name of all parent and child categories. I am pretty much successful in getting the desired results, however my inside While Loop to get the top category is escaping after finishing its entire loop of the first parent While. Therefore causing only one item in the combobox.

Below is the function and pic of a desire result.

private void FillCBoxCategories()
    {
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            SqlCommand cmdGetRootCategories = new SqlCommand("select * from RootCategories", con);
            SqlCommand cmdGetTopCategories = new SqlCommand("select * from TopCategories", con);


            SqlDataReader rCatReader, tCatReader;
            try
            {
                con.Open();
                rCatReader = cmdGetRootCategories.ExecuteReader();
                tCatReader = cmdGetTopCategories.ExecuteReader();

                int rCatIdOrdinal = rCatReader.GetOrdinal("Id");
                int rCatNameOrdinal = rCatReader.GetOrdinal("RCatName");
                int rCatAbbreOrdinal = rCatReader.GetOrdinal("Abbre");
                int rCatParentIdOrdinal = rCatReader.GetOrdinal("TopCategoryId");

                int tCatIdOrdinal = tCatReader.GetOrdinal("Id");
                int tCatNameOrdinal = tCatReader.GetOrdinal("TCatName");
                int tCatAbbreOrdinal = tCatReader.GetOrdinal("Abbre");
                int tCatParentIdOrdinal = tCatReader.GetOrdinal("ParentCategoryId");

                while (rCatReader.Read())
                {
                    int rCatId = rCatReader.GetInt32(rCatIdOrdinal);
                    string rCatName = rCatReader.GetString(rCatNameOrdinal);
                    string rCatAbbre = rCatReader.GetString(rCatAbbreOrdinal);
                    int rCatParentId = rCatReader.GetInt32(rCatParentIdOrdinal);

                    while (tCatReader.Read())
                    {
                        string TopCatParent = "";
                        int tCatParentId = 0;
                        int tCatId = tCatReader.GetInt32(tCatIdOrdinal);
                        string tCatName = tCatReader.GetString(tCatNameOrdinal);
                        string tCatAbbre = rCatReader.GetString(tCatAbbreOrdinal);
                        if (tCatReader.IsDBNull(tCatParentIdOrdinal) ==false)
                        {
                            tCatParentId = tCatReader.GetInt32(tCatParentIdOrdinal);
                        }                         

                        if (tCatParentId > 0)
                        {
                            SqlCommand cmdGetParentTopCategories = new SqlCommand("select TCatName from TopCategories where Id='" + tCatParentId + "'", con);
                            TopCatParent = cmdGetParentTopCategories.ExecuteScalar().ToString() + " -- ";
                        }

                        if (tCatId == rCatParentId)
                        {
                            cbBoxProductCategory.Items.Add(TopCatParent + tCatName + " -- " + rCatName);
                            cbBoxProductCategory.ValueMember = rCatId.ToString();
                        }

                    }

                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Add Product Form Combobox Root cat data: line 95", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


    }

Desired Result

enter image description here

Result m getting

enter image description here

Solution

  • You should try to have a loop call a method as opposed to nested while loops...

    Try something like this.

    public void PartOne() {
while (rCatReader.Read())
            {
                int rCatId = rCatReader.GetInt32(rCatIdOrdinal);
                string rCatName = rCatReader.GetString(rCatNameOrdinal);
                string rCatAbbre = rCatReader.GetString(rCatAbbreOrdinal);
                int rCatParentId = rCatReader.GetInt32(rCatParentIdOrdinal);
        string data = rCatId + "," + rCatName+ "," + rCatAbbre+ "," + rCatParentId;
               PartTwo(data);

                }
}

public string PartTwo(string data) {
    while (tCatReader.Read())
                    {
            string source = data;
            string[] stringSeparators = new string[] {","};
                string[] result;
            result = source.Split(stringSeparators, 
                            StringSplitOptions.RemoveEmptyEntries);
                    string TopCatParent = "";
                    int tCatParentId = 0;
                    int tCatId = tCatReader.GetInt32(tCatIdOrdinal);
                    string tCatName = tCatReader.GetString(tCatNameOrdinal);
                    string tCatAbbre = rCatReader.GetString(tCatAbbreOrdinal);
                    if (tCatReader.IsDBNull(tCatParentIdOrdinal) ==false)
                    {
                        tCatParentId = tCatReader.GetInt32(tCatParentIdOrdinal);
                    }                         

                    if (tCatParentId > 0)
                    {
                        SqlCommand cmdGetParentTopCategories = new SqlCommand("select TCatName from TopCategories where Id='" + tCatParentId + "'", con);
                        TopCatParent = cmdGetParentTopCategories.ExecuteScalar().ToString() + " -- ";
                    }

                    if (tCatId == rCatParentId)
                    {
                        cbBoxProductCategory.Items.Add(TopCatParent + tCatName + " -- " + result[1]);
                        cbBoxProductCategory.ValueMember = result[0].ToString();
                    }

            }