Showing data from related tables in single datagridview

Question

I have some tables in the db:

Items             Manufacturers       Categories      Cities      Regions
==============    ================    ============    ========    ==========
ItemId            ManufacturerId      CategoryId      CityId      RegionId
ManufacturerId    CityId              NameCategory    RegionId    NameRegion
CategoryId        NameManufacturer                    NameCity
NameItem
Weight

I am displaying the list of the items in DataGridView using this code:

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from Items", connectionString);

SqlCommandBuilder cmdBldr = new SqlCommandBuilder(da);
da.Fill(ds, "Items");
dataGridView1.DataSource = ds.Tables[0];

I also have button that save the changes in the DataGridView with this code:

da.Update(ds, "Items");

I want to replace two columns in datagridview - ManufacturerId and CategoryId with the NameManufacturer and NameCategory from related tables. So those two columns should be ComboBox with all possible names from related tables - to be able to change the Category or Manufacturer to other and save it using da.Update().

I also need to add three combobox filters for the datagridview: Category, City and Region, that will filter the displayed items in the datagridview by selected values in those filters.

I can't use wizard creator, I have to do it all in the code. If you could give me some ideas related to any part of this, will be great.

Answer 1

ComboBox Column

For each column that you want to be combo box, you should create an instance of DataGridViewComboBoxColumn and set these properties and add it to Columns of grid:

• DataSource: list of items that you want to show in combo box
• DataPropertyName: Name of a property of data source of grid, that you want to combobox bind to it.
• DisplayMember: Name of a column of data source of combo box to show in combo box
• ValueMember: Name of a column of data source of combo box to use its value when you select an item from combo box.

For example here is the code for CategoryId column:

var categoryAdapter= new SqlDataAdapter("SELECT * FROM Categories", connectionString);
var categoryTable= new DataTable();
categoryAdapter.Fill(categoryTable);

var categoryComboBoxColumn=new DataGridViewComboBoxColumn();
categoryComboBoxColumn.Name="categoryComboBoxColumn";
categoryComboBoxColumn.HeaderText="Category";
categoryComboBoxColumn.DataSource = categoryTable;
categoryComboBoxColumn.DataPropertyName = "CategoryId";
categoryComboBoxColumn.DisplayMember= "NameCategory";
categoryComboBoxColumn.DisplayMember= "CategoryId";
this.dataGridView1.Columns.Add(categoryComboBoxColumn);

Filter

To filter the grid you can assign an expression to DefaultView.RowFilter of your main data table.

For example to filter based on CategoryId, you can create a System.Windows.Forms.ComboBox control and name it for example categoryComboBox and bind it to categories to show list of categories and set its DisplayMember to NameCategory and its ValueMember to CategoryId in that combo box and then:

var table = ((DataTable)dataGridView1.DataSource);
table.DefaultView.RowFilter = 
    string.Format("CategoryId = {0}", categoryComboBox.SelectedValue);

You can reset the filter by setting it to null or empty.

Also you can create expression with and / or:

var criterias = new List<string>();
if(categoryComboBox.SelectedIndex > 0)
    criterias.Add(string.Format("CategoryId = {0}", categoryComboBox.SelectedValue);

if(cityComboBox.SelectedIndex > 0)
    criterias.Add(string.Format("CityId = {0}", cityComboBox.SelectedValue);

var table = ((DataTable)dataGridView1.DataSource);
table.DefaultView.RowFilter = string.Join(" And " , criterias);