Cascading ComboBoxes within a Telerik GridView based on a SQL Server table

[Daniel]

Hi,

I have a problem, that I cannot solve. I have a Grid View (see attachment) based on a SQL Table. Now have replaced some colums with a comboBox.

The comboBoxes receive the data from the following table.

| ID  |  Country |  Plant  |  Area  |  Machine |
------------------------------------------------
|  1  |   DE     |   MUC   |  BA    |   A      |
|  2  |   DE     |   MUC   |  BA    |   A      |
|  3  |   AT     |   VIE     |  BE    |    1      |
|  4  |   AT     |   VIE     |  BE    |    2      |

F.e. The column "Country" in my GridView has as ItemSource a grouping of the column Country. Now I want to update the comboBox "Plant" depending on the value of the ComboBox "Country" meaning that when "DE" is chosen, it should only show "MUC" as a possible entry. How can I do that? Here is my code.

Thanks.

 public partial class MainWindow : Window
 {
     private SqlConnection connection;
     private string dbName = string.Empty;
     
     public MainWindow()
     {
         InitializeComponent();
         LoadData();
     }

     private void LoadData()
     {

         try
         {
             string connectionString = "Data Source=localhost;Initial Catalog=Test_DB;Integrated Security=True";
             using (SqlConnection connection = new SqlConnection(connectionString))
             {
                 connection.Open();
                 string query = "SELECT * FROM dbo.MachineAreas";
                 SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                 DataTable dataTable = new DataTable();
                 adapter.Fill(dataTable);

                 gridMachineAreas.ItemsSource = dataTable.DefaultView;
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show("Error: " + ex.Message);
         }
     }

     private void GridMachineAreas_AutoGeneratingColumn(object sender, GridViewAutoGeneratingColumnEventArgs e)
     {            

         switch ((e.Column as GridViewDataColumn).DataMemberBinding.Path.Path)
         {
             case "ID":
                 var newColumn1 = new GridViewDataColumn();
                 newColumn1.CopyPropertiesFrom(e.Column);
                 newColumn1.Header = "ID";
                 newColumn1.Width = 60;
                 e.Column = newColumn1;
                 break;

             case "Country":
                 var newColumn2 = new GridViewComboBoxColumn();
                 newColumn2.CopyPropertiesFrom(e.Column);
                 newColumn2.Header = "Country";
                 newColumn2.Width = 60;
                 newColumn2.UniqueName = "Country";
                 newColumn2.IsComboBoxEditable = false;
                 newColumn2.ItemsSource = MakeCountryCollection();                    
                 
                 e.Column = newColumn2;
                 break;
             case "Plant":
                 var newColumn3 = new GridViewComboBoxColumn();
                 newColumn3.CopyPropertiesFrom(e.Column);
                 newColumn3.Header = "Plant";
                 newColumn3.Width = 60;                    
                 e.Column = newColumn3;
                 break;
             case "Area":
                 var newColumn4 = new GridViewComboBoxColumn();
                 newColumn4.CopyPropertiesFrom(e.Column);
                 newColumn4.Header = "Area";
                 newColumn4.Width = 60;
                 e.Column = newColumn4;
                 break;
             case "Machine":
                 var newColumn5 = new GridViewComboBoxColumn();
                 newColumn5.CopyPropertiesFrom(e.Column);
                 newColumn5.Header = "Machine";
                 newColumn5.Width = 60;
                 e.Column = newColumn5;
                 break;
         }

     }

     private List<string> MakeCountryCollection()
     {
         List<string> countryCollection = new List<string>();
         string query = "SELECT Country FROM dbo.MachineAreas Group By Country";
         
         try
         {
             string connectionString = "Data Source=localhost;Initial Catalog=Test_DB;Integrated Security=True";
             using (SqlConnection connection = new SqlConnection(connectionString))
             {
                 connection.Open();


                 using (SqlCommand command = new SqlCommand(query, connection))
                 {
                     using (SqlDataReader reader = command.ExecuteReader())
                     {


                         while (reader.Read())
                         {
                             string value = reader.GetString(0);
                             countryCollection.Add(value);
                         }
                         
                     }
                 }
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show("Error: " + ex.Message);
         }
         return countryCollection;
     }
 }

[Dimitar]

Hi Daniel,

You can use the PreparedCellForEdit event and set the data source of the editor: 

private void RadGridView1_PreparedCellForEdit(object sender, GridViewPreparingCellForEditEventArgs e)
{
    var col = (e.Column as GridViewComboBoxColumn);
    if (col != null && col.DataMemberBinding.Path.Path == "Plant")
    {
        var value = (e.Row.DataContext as DataRow)["Country"].ToString();  

        var editor = e.EditingElement as RadComboBox;

        if (value == "DE")
        {
            editor.ItemsSource = new List<string> { "Item 1" };
        }

        if (value == "AT")
        {
            editor.ItemsSource = new List<string> { "Item 2" };
        }
    }
}

We have another example of this in our SDK repo: xaml-sdk/GridView/CascadingComboboxColumns at master.

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik