How to create LookUp fields in DataGridView?

Question

Hello.

In my DataGridView I'am displaying a buch of columns from one table. In this table I have a column which points to item in another table. As you may already guessed, I want to display in the grid in one column some text value from the second table instead of and ItemID. I could not find a right example on the net how to do this.

Lets assume that I have two tables in databes:

Table Users:

UserID UserName UserWorkplaceID
  1     Martin        1
  2     John          1
  3     Susannah      2
  4     Jack          3

Table Workplaces:

WorkplaceID WorkplaceName
     1        "Factory"
     2        "Grocery"
     3        "Airport"

I have one untyped dataset dsUsers, one binding source bsUsers, and two DataAdapters for filling dataset (daUsers, daWorkplaces).

Code which I am performing:

daUsers.Fill(dsUsers);
daWorkplaces.Fill(dsUsers);
bsUsers.DataSource = dsUsers.Tables[0];
dgvUsers.DataSource = bsUsers;

At this point I see in my dgvUsers three columns, UserID, UserName and UserWorkplaceID. However, instead of UserWorkplaceID and values 1,2,3 I would like to see "Factory", "Grocery" and so on...

So I've added another column to dgvUsers called "WorkplaceName" and in my code I am trying to bind it to the newly created relation:

dsUsers.Relations.Add("UsersWorkplaces", dsUsers.Tables[1].Columns["WorkplaceID"], dsUsers.Tables[0].Columns["UserWorkplaceID"]);

WorkplaceName.DataPropertyName = "UsersWorkplaces.WorkplaceName";

Unfortunately that doesn't work. Relation is created without errors but fields in this column are empty after running the program.

What I am doing wrong?

I would like to also ask about an example with LookUp combobox in DataGridView which allow me to change the UserWorkplaceID but instead of numeric value it will show a tex value which is under WorkplaceName.

Thanks for your time.

Answer 1

You could make SQL do the job instead. Use a join to return a table with Workplace names instead of IDs, output that table into a dataset and use it instead.

eg.

SELECT A.UserID, A.UserName, B.WorkplaceID
   FROM Users A 
   JOIN Workplaces B ON A.UserWorkplaceID = B.WorkplaceID

Then use its output to fill dsUsers.

Answer 2

I don't know if you can do exactly what you want, which seems to be binding the DataGridView to two different DataTable instances simulataneously. I don't think the DataGridView class supports that -- or if it does it's a ninja-style move I haven't seen.

Per MSDN, your best bet is probably using the CellFormatting event on the DataGridView and check for when the cell being formatted is in the lookup column, then you could substitute your value from the other table. Use an unbound column for the WorkplaceName column, hide the UserWorkplaceID column and then implement the CellFormatting event handle to look up the value in the row, e.g.:

private void dgv_CellFormatting(object sender, 
    DataGridViewCellFormattingEventArgs e)
{
    if (dgv.Columns[e.ColumnIndex].Name.Equals("WorkplaceName")
    {
        // Use helper method to get the string from lookup table
        e.Value = GetWorkplaceNameLookupValue(
            dataGridViewScanDetails.Rows[e.RowIndex].Cells["UserWorkplaceID"].Value);
    }
}

If you've got a lot of rows visible, this might impact performance but is probably a decent way to get it working.

If this doesn't appeal to you, maybe use the DataTable.Merge() method to merge your lookup table into your main table. A quick glance at one of my ADO.NET books suggests this should work, although I have not tried it. But I'm not sure if this is too close to the idea suggested previously which you shot down.

As for your second question about the lookup combobox, you should really post it in a separate question so it gets proper attention.