DataBinding with a DataGridView C#

Question

I have a Database table that I want to display in a DataGridView. However, it has several foreign keys and I don't want to show the integer that represents the other Table to the user.

I have this DataTable with a column userId I have another DataTable with a column id, and a column username

I want the DataGridView to show the username instead of userId where the userId = id.

How can I do this? I also have to do this on several columns in the same table to be displayed.

edit: windows forms, not web forms.

Answer 1

You can create a Stored Procedure that make the requiered joins to get the names of the user, or whatever field you need, and mapp it to a class that have this info and the bind the datagridView to a List<YourClass>

Answer 2

Here are a couple options to consider...

1 - If you have access to the database, have a view or stored procedure created that returns the "denormalized" data. You can then bind your GridView directly to this and everything is done.

2 - Create template columns for those fields that you need to retrieve. Then in the RowDatabound event for the GridView, you can programmatically call out and get the appropriate lookup information and display that in the column specified. Your code would look something like this:

protected void FormatGridView(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)
{
   if (e.Row.RowType == DataControlRowType.DataRow) 
   {
      e.Row.Cells[1].Text = GetUserId(Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "userid"));
   }
}

Answer 3

How are you populating the DataTable? You can specify custom SQL with the joins and appropriate columns to the .SelectCommand of a DataAdapter or specify the custom SQL in the .CommandText of a xxxCommand object.

Eg

myAdapter.SelectCommand = "Select a.column1, a.column2, b.username from tablea a inner join tableb b on b.userid = a.userId"

Or

myCommand.CommandType = Text;
myCommand.CommandText = ""Select a.column1, a.column2, b.username from tablea a inner join tableb b on b.userid = a.userId";

Or as has been mentioned, you could create a view or storedproc in your database and call that.

Answer 4

Are you using Linq?
Because if you are using Linq, you can set the DataPropertyName of the column to "User.UserName" provided you've set up the proper association in the designer.

Answer 5

You can bind a DataGridView to a DataSet, which can contain one or more data tables. The first data table could be populated with a SELECT statement which contains the userid column. The second data table could contain the usernames and id's. Then add a relation to the dataset's Relations collection which maps the userid of the first table to the id of the second table. Using the dataset, create a new default view manager which shows only the username and not the userid. Set the DataGridView's datasource to the dataset's DefaultViewManager.