Zenophyr

I wish to replace my foreign key values in the datagridview with actual name values.

I am using this code:

Windows Form:

Code Block

_OnlineDataSource = SessionManager.Instance.GetAllSessions();

OnlineGridView.AutoGenerateColumns = false;

OnlineGridView.DataSource = _OnlineDataSource;

OnlineGridView.DataMember = "Session";

DataGridViewColumn gridViewColumn = new DataGridViewTextBoxColumn();

gridViewColumn.DataPropertyName = "SessionID";

gridViewColumn.HeaderText = "Session ID";

gridViewColumn.Width = 200;

OnlineGridView.Columns.Add(gridViewColumn);

DataGridViewColumn gridViewColumn = new DataGridViewTextBoxColumn();

gridViewColumn.DataPropertyName = "ProjectName";

gridViewColumn.HeaderText = "Project Name";

gridViewColumn.Width = 200;

OnlineGridView.Columns.Add(gridViewColumn);

Business Class (GetAllSession function):

Code Block

SQLManager.SQLQuery = "SELECT Session.SessionID, Session.SessionTypeID, Session.ProjectID, " +

"Session.Date, Session.Agenda, Session.LastModifiedDate, Session.ConcurrencyCheck " +

"FROM Session;"; ;

sessionDataSet.Tables.Add(SQLManager.ReadData("Session"));

//GetAllProjects provides the ProjectID, ProjectName etc.

sessionDataSet.Tables.Add(ProjectManager.Instance.GetAllProjects().Copy());

DataRelation SessionProjectRelation = new DataRelation("SessionProjectRelation",

sessionDataSet.Tables["Session"].Columns["ProjectID"],

sessionDataSet.Tables["Project"].Columns["ProjectID"], true);

sessionDataSet.Relations.Add(SessionProjectRelation);

The ProjectName column stays empty Tongue Tied



Re: Windows Forms Data Controls and Databinding DataGridView DataTable Foreign Key replacements

Zhi-Xin Ye - MSFT

In fact, there're two DataTables in your DataSet, since you had set the DataMember to be "Session", the DataGridView can only retrieve data from the Seesion table, which results in the empty ProductName column, the Relationship offers no help in this scenario, one possible solution for you would be to use a merged table, this can be done by the DataTable.Merge() method, however, while doing this, you have to make sure the column which links these two tables should be the primary key of the child table. I write the following sample for your information

Code Block

private void Form2_Load(object sender, EventArgs e)

{

DataTable dtSession = new DataTable("Session");

dtSession.Columns.Add("SeesionID", typeof(int));

dtSession.Columns.Add("ProjectID", typeof(int));

dtSession.Columns.Add("name");

for (int j = 0; j < 5; j++)

{

dtSession.Rows.Add(j, j, "s" + j.ToString());

}

DataTable dtProject = new DataTable("Project");

dtProject.Columns.Add("ProjectID",typeof(int));

dtProject.Columns.Add("ProjectName");

//set primary key

dtProject.Constraints.Add("PrimaryKey", dtProject.Columns["ProjectID"], true);

for (int j = 0; j < 5; j++)

{

dtProject.Rows.Add(j, "Project" + j.ToString());

}

DataTable dtMerged = new DataTable("Merged");

dtMerged = dtSession.Copy();

dtMerged.Merge(dtProject);

this.dataGridView1.AutoGenerateColumns = false;

this.dataGridView1.DataSource = dtMerged;//use the merged table as source

DataGridViewTextBoxColumn c1 = new DataGridViewTextBoxColumn();

c1.HeaderText = "SessionID";

c1.DataPropertyName = "SeesionID";

this.dataGridView1.Columns.Add(c1);

DataGridViewTextBoxColumn c2 = new DataGridViewTextBoxColumn();

c2.HeaderText = "ProjectName";

c2.DataPropertyName = "ProjectName";

this.dataGridView1.Columns.Add(c2);

}