How to bind a many-to-many relation in WinForms?

Question

I have the following DataSet:

The Product and Part tables can be edited using these DataGridViews:

When the user double-clicks a row in the Products grid, the following form opens:

The left column is supposed to list the parts associated with this product. The right column is supposed to list all the other parts. Using the << and >> buttons, the user should be able to choose which parts belong to the current product.

I have done something similar with a one-to-many relation and it worked perfectly. The code was as follows:

public partial class ProductPartsForm : Form
{
    private int _productID;
    private DataSet1 _data;

    public ProductPartsForm(DataSet1 data, DataRowView productRowView)
    {
        var productRow = (DataSet1.ProductRow)productRowView.Row;
        _productID = productRow.ID;
        _data = data;
        InitializeComponent();
        productBindingSource.DataSource = productRowView;
        assignedPartBindingSource.DataSource = productBindingSource;
        assignedPartBindingSource.DataMember = "FK_Product_Part";
        assignedPartsListBox.DisplayMember = "Name";
        unassignedPartBindingSource.DataSource = _data;
        unassignedPartBindingSource.DataMember = "Part";
        unassignedPartsListBox.DisplayMember = "Name";
        unassignedPartBindingSource.Filter = $"isnull(ProductID, 0) = 0";
    } 

    private void assignButton_Click(object sender, EventArgs e)
    {
        var partRowView = (DataRowView)unassignedPartBindingSource.Current;
        var partRow = (DataSet1.PartRow)partRowView.Row;
        var productRowView = (DataRowView)productBindingSource.Current;
        var productRow = (DataSet1.ProductRow)productRowView.Row;
        partRow.ProductRow = productRow;
        UpdateUI();
    }

    private void unassignButton_Click(object sender, EventArgs e)
    {
        var partRowView = (DataRowView)assignedPartBindingSource.Current;
        var partRow = (DataSet1.PartRow)partRowView.Row;
        partRow.SetProductIDNull();
        UpdateUI();
    }

    private void UpdateUI()
    {
        assignedPartsListBox.Refresh();
        unassignedPartsListBox.Refresh();
        assignButton.Enabled = unassignedPartsListBox.Items.Count > 0;
        unassignButton.Enabled = assignedPartsListBox.Items.Count > 0;
    }
}

With the many-to-many relation, there are two things I couldn't get to work:

• The left column doesn't show the names of the parts. It should display lowercase letters, like the right column; instead, it shows the string System.Data.DataRowView. I want to fix this using some sort of lookup, but I don't know how.

• When you press <<, the selected part stays on the right column instead of moving to the left column. If you try to press << again with the same part, you get the following error:

  System.Data.ConstraintException: 'Column 'ProductID, PartID' is constrained to be unique. Value '-4, -3' is already present.'

  (which is understandable). I think this can be fixed using a filter expression, but I'm not sure how to write it and how to update the right column automatically after every change.

Has anyone done something similar and can help point me in the right direction?

Answer 1

Here's what I finally came up with. The key function is UpdateFilters, which creates a list of part IDs assigned to the current product and then filters the two columns "manually" using the IN and NOT IN operators.

public partial class ProductPartsForm : Form
{
    private int _productID;
    private DataSet1 _data;

    public ProductPartsForm(DataSet1 data, DataRowView productRowView)
    {
        var productRow = (DataSet1.ProductRow)productRowView.Row;
        _productID = productRow.ID;
        _data = data;
        InitializeComponent();
        productBindingSource.DataSource = productRowView;
        assignedPartBindingSource.DataSource = _data;
        assignedPartBindingSource.DataMember = "Part";
        assignedPartsListBox.DisplayMember = "Name";
        unassignedPartBindingSource.DataSource = _data;
        unassignedPartBindingSource.DataMember = "Part";
        unassignedPartsListBox.DisplayMember = "Name";
    }

    private void ProductPartsForm_Load(object sender, EventArgs e)
    {
        UpdateFilters();
        UpdateUI();
    }

    private void assignButton_Click(object sender, EventArgs e)
    {
        var partRowView = (DataRowView)unassignedPartBindingSource.Current;
        var partRow = (DataSet1.PartRow)partRowView.Row;
        var productRowView = (DataRowView)productBindingSource.Current;
        var productRow = (DataSet1.ProductRow)productRowView.Row;
        _data.ProductPart.AddProductPartRow(productRow, partRow);
        UpdateFilters();
        UpdateUI();
    }

    private void unassignButton_Click(object sender, EventArgs e)
    {
        var partRowView = (DataRowView)assignedPartBindingSource.Current;
        var partRow = (DataSet1.PartRow)partRowView.Row;
        var productPartRow = _data.ProductPart
            .Single(pp => pp.ProductID == _productID && pp.PartID == partRow.ID);
        _data.ProductPart.RemoveProductPartRow(productPartRow);
        UpdateFilters();
        UpdateUI();
    }

    private void UpdateFilters()
    {
        var assignedIds = _data.ProductPart
            .Where(pp => pp.ProductID == _productID)
            .Select(pp => pp.PartID.ToString());
        if (assignedIds.Any())
        {
            assignedPartBindingSource.Filter = $"ID IN ({string.Join(",", assignedIds)})";
            unassignedPartBindingSource.Filter = $"ID NOT IN ({string.Join(",", assignedIds)})";
        }
        else
        {
            assignedPartBindingSource.Filter = "FALSE";
            unassignedPartBindingSource.RemoveFilter();
        }
    }

    private void UpdateUI()
    {
        assignedPartsListBox.Refresh();
        unassignedPartsListBox.Refresh();
        assignButton.Enabled = unassignedPartsListBox.Items.Count > 0;
        unassignButton.Enabled = assignedPartsListBox.Items.Count > 0;
    }
}