How to show all the rows in datagridview?

Question

 DataTable dt = db.getProductIdFromCategoriesId(categories_id);

 foreach (DataRow row in dt.Rows)
 {
     string products_id = row["products_id"].ToString();
     DataTable dt5 = db.FillDataGridfromTree(int.Parse(products_id));
     show_products.ItemsSource = dt5.DefaultView;
 }

this code show one by one rows in datagridview
but i want to show all the product rows having categories_id in datagridview in one go this is the function FillDataGridfromTree in databasecore class and its object is db

public DataTable FillDataGridfromTree(int product_Id)       
{            
        string CmdString = string.Empty;

        using (SqlCeConnection con = new SqlCeConnection(ConString))
        {
            CmdString = "SELECT products.product_id as ID, products.remote_products_id as Remote_ID, products_description.products_name as name,products.products_model as model,products.manufacturers_id as manufacturersId,products.products_image as Image,products.products_price as Price,products.products_weight as Weight,products.products_date_added as dateAdded,products.products_last_modified as lastModified,products.products_date_available as dateAvailable,products.products_status as status,products.products_tax_class_id as taxClass FROM products INNER JOIN products_description ON products.product_id=products_description.products_id where products_description.language_id=1 and products_description.products_id=" + product_Id;
            SqlCeCommand cmd = new SqlCeCommand(CmdString, con);
            SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
            DataTable dt = new DataTable("products");
            adapter.Fill(dt);
            //show_products.ItemsSource = dt.DefaultView;
            return dt;    
        }
}

this is the function through which i get product_id

public DataTable getProductIdFromCategoriesId(int categories_id) {

string CmdString = string.Empty;

        using (SqlCeConnection con = new SqlCeConnection(ConString))
        {
            CmdString = "SELECT products_id FROM products_to_categories where categories_id=" + categories_id;
            SqlCeCommand cmd = new SqlCeCommand(CmdString, con);
            DataTable dt = new DataTable();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
            adapter.Fill(dt);
            return dt;
        }
    }

how to show all the rows instead of one row in datagridview

Answer 1

CHANGED Try changing your foreach loop to:

DataTable dt = db.getProductIdFromCategoriesId(categories_id);

DataTable dt5 = new Datatable();

 foreach (DataRow row in dt.Rows)
 {
     string products_id = row["products_id"].ToString();
     dt5.Merge(db.FillDataGridfromTree(int.Parse(products_id)));
 }

show_products.ItemsSource = dt5.DefaultView;

Answer 2

You code is always going to display the last row for a category_id, this is because you're assigning an ItemsSource inside a loop. I've changed the top part to do what you looking for:

DataTable dt = db.getProductIdFromCategoriesId(categories_id);
List<DataRow> ProductList = new List<DataRow>();

foreach (DataRow row in dt.Rows)
{
   string products_id = row["products_id"].ToString();
   DataTable dt5 = db.FillDataGridfromTree(int.Parse(products_id));

  if(dt5.Rows.Count > 0)
  {
      ProductList.AddRange(dt5.Select().ToList());
  }
}

show_products.ItemsSource = ProductList.CopyToDataTable().DefaultView;