CODEWITHSUNDEEP
c#databasedata-bindingdatagridview
Malfist
Malfist

Reputation: 31815

DataBinding with a DataGridView C#

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.

Upvotes: 0

Views: 7281

Answers (5)

aastle
aastle

Reputation:

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.

Upvotes: 0

Benjamin Autin
Benjamin Autin

Reputation: 4171

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.

Upvotes: 0

JamesSugrue
JamesSugrue

Reputation: 15011

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.

Upvotes: 2

Dillie-O
Dillie-O

Reputation: 29755

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"));
   }
}

Upvotes: 2

Eduardo Crimi
Eduardo Crimi

Reputation: 1601

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>

Upvotes: 1

Related Questions