DataRelation

  • Thread starter Thread starter Justin Lazanowski
  • Start date Start date
J

Justin Lazanowski

I have some data that is disassociated, I am trying to put an ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a table. Is it
possible to do this?

Thanks,

Justin
 
Yes, if they are of the same type. A datarelation is the perfect way to
pull it off... are you familiar w/ it? If not I can point you to some
examples (although Googling on DataRelation is probably the best way to go).
 
Here is a psuedo-code that shows how to relate a a field in table A to
another field in table A (self referencing). The datatypes need to be the
same.

// Relate a table's managerID to the employeeID
ds.Relations.Add("EmpMgr", ds.Tables["Employee"].Columns
["EmployeeID"], ds.Tables["Employee"].Columns["ManagerID"]);



// John Papa
// http://codebetter.com/blogs/john.papa
 
Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also have
Table[2] that has a rectype, I can add a row ID if I need to the problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual data so
for components it would run a Stored Procedure off a SQL database. The
problem is that I have data that really has no relation. So I added the
rectype Components. The problem of course is that Table[1] has more that one
field called components so there is no unique relation. RowID could be used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on two
fields instead of one. Joining on RowID and RecType would give me a unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make sure the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not trying
to make more work for myself.

Thanks for you help!

Justin
 
Look @ the constructor of DataRelation. I think it can take an array of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Justin Lazanowski said:
Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also have
Table[2] that has a rectype, I can add a row ID if I need to the problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual data so
for components it would run a Stored Procedure off a SQL database. The
problem is that I have data that really has no relation. So I added the
rectype Components. The problem of course is that Table[1] has more that one
field called components so there is no unique relation. RowID could be used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on two
fields instead of one. Joining on RowID and RecType would give me a unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make sure the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not trying
to make more work for myself.

Thanks for you help!

Justin
W.G. Ryan eMVP said:
Yes, if they are of the same type. A datarelation is the perfect way to
pull it off... are you familiar w/ it? If not I can point you to some
examples (although Googling on DataRelation is probably the best way to
go).
Click to expand...
Click to expand...
 
That looks right. The constructor take System.Data.Datacolumn[] parent
columns.

So then my question would be how do I pass in the parent and child columns?

Forgive me I am very very new at this.

Thanks,
Justin

Sahil Malik said:
Look @ the constructor of DataRelation. I think it can take an array of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Justin Lazanowski said:
Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle
just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also have
Table[2] that has a rectype, I can add a row ID if I need to the problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual data
so
for components it would run a Stored Procedure off a SQL database. The
problem is that I have data that really has no relation. So I added the
rectype Components. The problem of course is that Table[1] has more that one
field called components so there is no unique relation. RowID could be used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on two
fields instead of one. Joining on RowID and RecType would give me a
unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make sure
the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not trying
to make more work for myself.

Thanks for you help!

Justin
W.G. Ryan eMVP said:
Yes, if they are of the same type. A datarelation is the perfect way
to
pull it off... are you familiar w/ it? If not I can point you to some
examples (although Googling on DataRelation is probably the best way to
go).

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com


I have some data that is disassociated, I am trying to put an ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a table.
Is
it
possible to do this?

Thanks,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Just create a new DataRelation and pass in an array of the relevant
datacolumns to the constructor.
Then go to the dataset and do
DataSet.Relations.Add(therelationyoucreatedabove).

Thats all you have to do :). If the current data conforms with the newly
added relation - it will get added and future data will be enforced.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Justin Lazanowski said:
That looks right. The constructor take System.Data.Datacolumn[] parent
columns.

So then my question would be how do I pass in the parent and child columns?

Forgive me I am very very new at this.

Thanks,
Justin

Sahil Malik said:
Look @ the constructor of DataRelation. I think it can take an array of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Justin Lazanowski said:
Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle
just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also have
Table[2] that has a rectype, I can add a row ID if I need to the problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual data
so
for components it would run a Stored Procedure off a SQL database. The
problem is that I have data that really has no relation. So I added the
rectype Components. The problem of course is that Table[1] has more
Click to expand...
that
one
field called components so there is no unique relation. RowID could be used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on two
fields instead of one. Joining on RowID and RecType would give me a
unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make sure
the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not trying
to make more work for myself.

Thanks for you help!

Justin
Yes, if they are of the same type. A datarelation is the perfect way
to
pull it off... are you familiar w/ it? If not I can point you to some
examples (although Googling on DataRelation is probably the best way to
go).

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com


I have some data that is disassociated, I am trying to put an ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a table.
Is
it
possible to do this?

Thanks,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Ok this works!

Thank you very much, however I am still experiencing one small problem

I am trying to relate Table 1 to Table 2 and Table 3

If I add the data relation twice, the data only seems to bind on whatever
relation is first.

Here is some example code

dsPayCheck1.Relations.Add("Componenets",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[2].Columns["pkey"],
dsPayCheck1.Tables[2].Columns["rowid"]});

dsPayCheck1.Relations.Add("Adjustments",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[3].Columns["pkey"],
dsPayCheck1.Tables[3].Columns["rowid"]});



Any thoughts on this?





John Papa said:
Here is a sample that creates a relation on 2 columns ...

ds.Relations.Add("MyRelation",
new DataColumn[]{ds.Tables[0].Columns["x"], ds.Tables[0].Columns["y"]},
new DataColumn[]{ds.Tables[1].Columns["a"],
ds.Tables[0].Columns["b"]});


// John Papa
// http://codebetter.com/blogs/john.papa



Justin Lazanowski said:
That looks right. The constructor take System.Data.Datacolumn[] parent
columns.

So then my question would be how do I pass in the parent and child
columns?

Forgive me I am very very new at this.

Thanks,
Justin

Sahil Malik said:
Look @ the constructor of DataRelation. I think it can take an array of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle
just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also
have
Table[2] that has a rectype, I can add a row ID if I need to the
problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual
data
so
for components it would run a Stored Procedure off a SQL database. The
problem is that I have data that really has no relation. So I added
the
rectype Components. The problem of course is that Table[1] has more
that
one
field called components so there is no unique relation. RowID could be
used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on
two
fields instead of one. Joining on RowID and RecType would give me a
unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make sure
the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not
trying
to make more work for myself.

Thanks for you help!

Justin
Yes, if they are of the same type. A datarelation is the perfect
way
to
pull it off... are you familiar w/ it? If not I can point you to
some
examples (although Googling on DataRelation is probably the best way
to
go).

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com


I have some data that is disassociated, I am trying to put an
ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a
table.
Is
it
possible to do this?

Thanks,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Never mind, I just found out it's a limitation in the Grid that I am using.
The relationship is just fine.

Thanks Again
Justin
Justin Lazanowski said:
Ok this works!

Thank you very much, however I am still experiencing one small problem

I am trying to relate Table 1 to Table 2 and Table 3

If I add the data relation twice, the data only seems to bind on whatever
relation is first.

Here is some example code

dsPayCheck1.Relations.Add("Componenets",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[2].Columns["pkey"],
dsPayCheck1.Tables[2].Columns["rowid"]});

dsPayCheck1.Relations.Add("Adjustments",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[3].Columns["pkey"],
dsPayCheck1.Tables[3].Columns["rowid"]});



Any thoughts on this?





John Papa said:
Here is a sample that creates a relation on 2 columns ...

ds.Relations.Add("MyRelation",
new DataColumn[]{ds.Tables[0].Columns["x"],
ds.Tables[0].Columns["y"]},
new DataColumn[]{ds.Tables[1].Columns["a"],
ds.Tables[0].Columns["b"]});


// John Papa
// http://codebetter.com/blogs/john.papa



Justin Lazanowski said:
That looks right. The constructor take System.Data.Datacolumn[] parent
columns.

So then my question would be how do I pass in the parent and child
columns?

Forgive me I am very very new at this.

Thanks,
Justin

Look @ the constructor of DataRelation. I think it can take an array
of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle
just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also
have
Table[2] that has a rectype, I can add a row ID if I need to the
problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual
data
so
for components it would run a Stored Procedure off a SQL database.
The
problem is that I have data that really has no relation. So I added
the
rectype Components. The problem of course is that Table[1] has more
that
one
field called components so there is no unique relation. RowID could
be
used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on
two
fields instead of one. Joining on RowID and RecType would give me a
unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make
sure
the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not
trying
to make more work for myself.

Thanks for you help!

Justin
Yes, if they are of the same type. A datarelation is the perfect
way
to
pull it off... are you familiar w/ it? If not I can point you to
some
examples (although Googling on DataRelation is probably the best
way to
go).

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com


I have some data that is disassociated, I am trying to put an
ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a
table.
Is
it
possible to do this?

Thanks,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Cool .. glad it worked.


--

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Justin Lazanowski said:
Never mind, I just found out it's a limitation in the Grid that I am using.
The relationship is just fine.

Thanks Again
Justin
Justin Lazanowski said:
Ok this works!

Thank you very much, however I am still experiencing one small problem

I am trying to relate Table 1 to Table 2 and Table 3

If I add the data relation twice, the data only seems to bind on whatever
relation is first.

Here is some example code

dsPayCheck1.Relations.Add("Componenets",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[2].Columns["pkey"],
dsPayCheck1.Tables[2].Columns["rowid"]});

dsPayCheck1.Relations.Add("Adjustments",

new DataColumn[]{dsPayCheck1.Tables[1].Columns["rectype"],
dsPayCheck1.Tables[1].Columns["rowid"]},

new DataColumn[]{dsPayCheck1.Tables[3].Columns["pkey"],
dsPayCheck1.Tables[3].Columns["rowid"]});



Any thoughts on this?





John Papa said:
Here is a sample that creates a relation on 2 columns ...

ds.Relations.Add("MyRelation",
new DataColumn[]{ds.Tables[0].Columns["x"],
ds.Tables[0].Columns["y"]},
new DataColumn[]{ds.Tables[1].Columns["a"],
ds.Tables[0].Columns["b"]});


// John Papa
// http://codebetter.com/blogs/john.papa



:

That looks right. The constructor take System.Data.Datacolumn[] parent
columns.

So then my question would be how do I pass in the parent and child
columns?

Forgive me I am very very new at this.

Thanks,
Justin

Look @ the constructor of DataRelation. I think it can take an array
of
columns. Does that help?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/



Funny when I did Google this (before I posted to this group) the only
examples I could get are of single field relations which I can handle
just
fine

In my tables I have Table[1] that has rowid pkey and rectype. I also
have
Table[2] that has a rectype, I can add a row ID if I need to the
problem
that I have is that Table 1 has fields like this

Components
Adjustments
Stats

Table[1] has a parent Table[0] that are related on the row ID

It has a check date and amount

So we have Check1 [RowID 1]
Components [RowID 1]
Adjustments [RowID1]
Stats [RowID 1]
Check2 [RowID 2]
Components [RowID 2]
Adjustments [RowID 2]
Stats [RowID2]
ect...
Under each one of the items in Table[1] I need to place the actual
data
so
for components it would run a Stored Procedure off a SQL database.
The
problem is that I have data that really has no relation. So I added
the
rectype Components. The problem of course is that Table[1] has more
that
one
field called components so there is no unique relation. RowID could
be
used
but this is again non unique for the group because there are multiple
rowid's in Table[1]. This has lead me to the question of Joining on
two
fields instead of one. Joining on RowID and RecType would give me a
unique
value for each record.

One other note, this DataSet is not a strong typed DataSet. i.e. I am
creating that DS on demand.

Hopefully this wasn't to long winded for you, I just want to make
sure
the
problem is clear so that I can get the proper response.

Also, is this just a silly way to do what I am doing? I am really not
trying
to make more work for myself.

Thanks for you help!

Justin
Yes, if they are of the same type. A datarelation is the perfect
way
to
pull it off... are you familiar w/ it? If not I can point you to
some
examples (although Googling on DataRelation is probably the best
way to
go).

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com


I have some data that is disassociated, I am trying to put an
ad-hoc
relationship into it so that I can get it hierarchical.

What I need to do is create a relationship on two fields in a
table.
Is
it
possible to do this?

Thanks,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top