binding tables in many-to-many relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all. I'm having some (mis?)adventures in databinding a winforms app
and need some guidance.

My data model has 4 tables, let us call them Leases, Terms, LeaseTerms, and
LeaseTermTypes. The idea is that you can setup several definitions of Terms,
several definitions of Leases, and the two of them get related together in
LeaseTerms (standard intermediary tabling to avoid many-to-many). On
LeaseTerms is a foreign key to LeaseTermType (for clarity the create script
for these tables is at the bottom of the post)

On my WinForm app I have 2 comboboxes - one for LeaseTermType and the other
for Terms. What I want to do is for whatever LeaseTermType that has been
selected, I want to populate the 2nd combo box with all associated
Lease.LeaseTermName values (going through the LeaseTerms table). I'm
currently trying to do this using BindSoucres that bind to the relationships
defined in my dataset.

Now, this all currently works fine and dandy except that from the LeaseTerms
perspective, there's a 1-to-1 relationship from LeaseTerms to Leases. So
what's happening is that my 2nd combobox is only displaying 1 record rather
than a record for each of the records contained in the filtered LeaseTerms
table.

Is there any way to get all the Leases rather than just the 1 using the
built-in UI binding? I could write code to do all this, but ideally I'd like
to spend 15 seconds pointing and clicking rather than a few minutes hacking
code.

Any help and or guidance is appreciated.

Here's the script for our example DB.

/****** Object: Table [dbo].[Leases] Script Date: 10/23/2006 10:59:14
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Leases](
[LeaseID] [uniqueidentifier] NOT NULL,
[LeaseName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[LeaseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[LeaseTerms] Script Date: 10/23/2006
10:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LeaseTerms](
[LeaseTermID] [uniqueidentifier] NOT NULL,
[LeaseID] [uniqueidentifier] NULL,
[TermID] [uniqueidentifier] NULL,
[LeaseTermTypeID] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
[LeaseTermID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[LeaseTermTypes] Script Date: 10/23/2006
10:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeaseTermTypes](
[LeaseTermTypeID] [uniqueidentifier] NOT NULL,
[LeaseTermTypeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[LeaseTermTypeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Terms] Script Date: 10/23/2006 10:59:15
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Terms](
[TermID] [uniqueidentifier] NOT NULL,
[TermName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TermID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [DeleteMe]
GO
ALTER TABLE [dbo].[LeaseTerms] WITH CHECK ADD CONSTRAINT
[FK_LeaseTerms_Leases] FOREIGN KEY([LeaseID])
REFERENCES [dbo].[Leases] ([LeaseID])
GO
ALTER TABLE [dbo].[LeaseTerms] WITH CHECK ADD CONSTRAINT
[FK_LeaseTerms_LeaseTermTypes] FOREIGN KEY([LeaseTermTypeID])
REFERENCES [dbo].[LeaseTermTypes] ([LeaseTermTypeID])
GO
ALTER TABLE [dbo].[LeaseTerms] WITH CHECK ADD CONSTRAINT
[FK_LeaseTerms_Terms] FOREIGN KEY([TermID])
REFERENCES [dbo].[Terms] ([TermID])
 
Hi,

First of all, I¡¯d like to confirm my understanding of your issue.
According to your description, I understand that you want to know how to
get the related rows from many-to-many related tables.
If I misunderstood anything here, please don¡¯t hesitate to correct me.

Databinding is a very useful tool when you have two tables which are
related, but it¡¯s not powerful enough to relate three tables.

According to your scenario, you can use DataRelation. You can create one
DataSet which has three DataTables ( Leases, LeaseTerms and LeaseTermTypes).
Eeach DataTable has a relationship to another DataTable. Using
DataTable.getChildRows(), you can get related rows.
When ComboBox(LeaseTermType) has been selected, the following code snippet
can be used to populate the 2nd combo box with all associated leases.

DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
//"dr2" means the Relationship Betwen LeaseTermTypes and LeaseTerms
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
//"dr1" means the Relationship Betwen LeaseTermTypes and Leases
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";

I have done some sample code according to your scenario, you can try the
following code snippet if it is works on your machine.

private void init()
{
System.Data.SqlClient.SqlConnection scn=new
System.Data.SqlClient.SqlConnection("¡­¡­");
System.Data.SqlClient.SqlDataAdapter sda=new
System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand scd=new
System.Data.SqlClient.SqlCommand();
scd.Connection=scn;
sda.SelectCommand=scd;
scn.Open();
scd.CommandText="select * from Leases";
sda.Fill(ds,"Leases");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTerms";
sda.Fill(ds,"LeaseTerms");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTermTypes";
sda.Fill(ds,"LeaseTermTypes");
scn.Close();
ds.Relations.Add("dr1", ds.Tables["Leases"].Columns["LeaseID"],
ds.Tables["LeaseTerms"].Columns["LeaseID"]);
ds.Relations.Add("dr2",
ds.Tables["LeaseTermTypes"].Columns["LeaseTermTypeID"],ds.Tables["LeaseTerms
"].Columns["LeaseTermTypeID"]);
this.comboBox1.DisplayMember = "LeaseTermTypeName";
this.comboBox1.ValueMember = "LeaseTermTypeID";
this.comboBox1.DataSource=ds.Tables["LeaseTermTypes"];
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";
}

If anything is unclear, please don¡¯t hesitate to post in the newsgroup and
we will follow up.

Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Hi,

First of all, I¡¯d like to confirm my understanding of your issue.
According to your description, I understand that you want to know how to
get the related rows from many-to-many related tables.
If I misunderstood anything here, please don¡¯t hesitate to correct me.

Databinding is a very useful tool when you have two tables which are
related, but it¡¯s not powerful enough to relate three tables.

According to your scenario, you can use DataRelation. You can create one
DataSet which has three DataTables ( Leases, LeaseTerms and LeaseTermTypes).
Eeach DataTable has a relationship to another DataTable. Using
DataTable.getChildRows(), you can get related rows.
When ComboBox(LeaseTermType) has been selected, the following code snippet
can be used to populate the 2nd combo box with all associated leases.

DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
//"dr2" means the Relationship Betwen LeaseTermTypes and LeaseTerms
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
//"dr1" means the Relationship Betwen LeaseTermTypes and Leases
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";

I have done some sample code according to your scenario, you can try the
following code snippet if it is works on your machine.

private void init()
{
System.Data.SqlClient.SqlConnection scn=new
System.Data.SqlClient.SqlConnection("¡­¡­");
System.Data.SqlClient.SqlDataAdapter sda=new
System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand scd=new
System.Data.SqlClient.SqlCommand();
scd.Connection=scn;
sda.SelectCommand=scd;
scn.Open();
scd.CommandText="select * from Leases";
sda.Fill(ds,"Leases");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTerms";
sda.Fill(ds,"LeaseTerms");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTermTypes";
sda.Fill(ds,"LeaseTermTypes");
scn.Close();
ds.Relations.Add("dr1", ds.Tables["Leases"].Columns["LeaseID"],
ds.Tables["LeaseTerms"].Columns["LeaseID"]);
ds.Relations.Add("dr2",
ds.Tables["LeaseTermTypes"].Columns["LeaseTermTypeID"],ds.Tables["LeaseTerms
"].Columns["LeaseTermTypeID"]);
this.comboBox1.DisplayMember = "LeaseTermTypeName";
this.comboBox1.ValueMember = "LeaseTermTypeID";
this.comboBox1.DataSource=ds.Tables["LeaseTermTypes"];
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";
}

If anything is unclear, please don¡¯t hesitate to post in the newsgroup and
we will follow up.

Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Click to expand...
Wen Yuan,

Please check the language settings on your computer. The text you are sending
occasionally contains some non-English characters ( as in **don¡¯t** ).

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Hi Otis
Thanks for your reply.
I should do correct my post.
======================================
Hi,
First of all, I'd like to confirm my understanding of your issue.
According to your description, I understand that you want to know how to
get the related rows from many-to-many related tables.
If I misunderstood anything here, please don't hesitate to correct me.

Databinding is a very useful tool when you have two tables which are
related, but it's not powerful enough to relate three tables.

According to your scenario, you can use DataRelation. You can create one
DataSet which has three DataTables (Leases, LeaseTerms and LeaseTermTypes).
Eeach DataTable has a relationship to another DataTable. Using
DataTable.getChildRows(), you can get related rows.
When ComboBox(LeaseTermType) has been selected, the following code snippet
can be used to populate the 2nd combo box with all associated leases.

DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
//"dr2" means the Relationship Betwen LeaseTermTypes and LeaseTerms
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
//"dr1" means the Relationship Betwen LeaseTermTypes and Leases
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";

I have done some sample code according to your scenario, you can try the
following code snippet if it is works on your machine.

private void init()
{
System.Data.SqlClient.SqlConnection scn=new
System.Data.SqlClient.SqlConnection("...");
System.Data.SqlClient.SqlDataAdapter sda=new
System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand scd=new
System.Data.SqlClient.SqlCommand();
scd.Connection=scn;
sda.SelectCommand=scd;
scn.Open();
scd.CommandText="select * from Leases";
sda.Fill(ds,"Leases");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTerms";
sda.Fill(ds,"LeaseTerms");
scn.Close();
scn.Open();
scd.CommandText="select * from LeaseTermTypes";
sda.Fill(ds,"LeaseTermTypes");
scn.Close();
ds.Relations.Add("dr1", ds.Tables["Leases"].Columns["LeaseID"],
ds.Tables["LeaseTerms"].Columns["LeaseID"]);
ds.Relations.Add("dr2",
ds.Tables["LeaseTermTypes"].Columns["LeaseTermTypeID"],ds.Tables["LeaseTerms
"].Columns["LeaseTermTypeID"]);
this.comboBox1.DisplayMember = "LeaseTermTypeName";
this.comboBox1.ValueMember = "LeaseTermTypeID";
this.comboBox1.DataSource=ds.Tables["LeaseTermTypes"];
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
DataRow selectRow = ((DataRowView)this.comboBox1.SelectedItem).Row;
DataRow[] LeaseTermsDRs = selectRow.GetChildRows("dr2");
DataTable leasedt=ds.Tables["Leases"].Clone();
foreach (DataRow dr in LeaseTermsDRs)
{
leasedt.Rows.Add(dr.GetParentRow("dr1").ItemArray);
}
this.comboBox2.DataSource = leasedt;
this.comboBox2.DisplayMember = "LeaseName";
}
 
Hi

Just want to check if the issue has been resolved?
If it still persists, please don't hesitate to update here.
We'll go on to assist you on it. Thanks.

Sincerely,
Wen Yuan
 
Back
Top