DataRelation in DataSet and DataGrid

[Guest]

Hi,

[ BTW, I'm using C# in WindowsForms. ]

I'm using a DataSet and have a main table ( i.e. of users ), and sub-tables
which contain further information about the entries in the main table ( i.e.
group membership, etc.. ). These sub-tables contain 1-many type
relationships ( i.e. a user can belong to many groups ). I have set-up a
DataRelation so that these entries are linked and when they are displayed in
the DataGrid I get the DataRelation link which then displays the group
membership entries for that user in its own grid.

Is it possible to concatinate the group membership entries ( i.e. comma
separated group names ) into an extra column when the main table is shown in
the datagrid - I would really want this to be automatic through the
DataRelation, rather than have to calculate it separately - although any
solution would be appreciated !! ;-). I only want a single datagrid so cant
have a master-detail type display.

I've tried adding a DataColumn and using the expression proptery, but this
only seems to allow single values or number collation ( SUM, etc.. ), not
string collation.

Thanks in advance
RichS

For example, I would want to show the following in my datagrid:
User Name || Groups
Bill || Finance, Sales

If i had the following in my DataSet:

User Table:
User_ID || UserName
1 || Bill

Group Membership Table:
User_ID || Group_ID
1 || 1
1 || 2

Group Table:
Group_ID || Group_Name
1 || Sales
2 || Finance

 

[Guest]

Hi,

This can be done only by writing a stored procedure in SQL Server, which
would give the child table values as comma seperated string. The result of
the stored procedure can be loaded into the dataset. Concatenation of row
values is not possible with single expression in dataset.

Srimathi.

 

[Guest]

Thanks Srimathi - very quick reply!

My initial work-around was to iterate through every row in my Users table,
lookup the group membership in the relevant table, concatinate the entries
and the add this string to a manually added table in my dataset and then
create a DataRelation ( between the users table and my new table ) and then a
DataColumn in my users table using the expression property to reference this
DataRelation.

I can do the stored procedure no problem, but how do I link this from the
Users table into the extra column ? Do I have to iterate through all of the
rows and manually call the stored procedure for each user, or would I use the
Stored Procedure to generate the entire row data for the user table (
including comma separated string ) and then associate this with a datatable.

I was hoping to be able to use the dataset to only have to grab newly added
information ( rather than having to reload the entire data whenever I refresh
the locally stored data ) - would a stored procedure work in this way?

How would you do this ?

If it adds too much of a performance cost to either the client, client->sql
comms, or sql server itself, I will either have to come up with a different
way, or not do it at all.

The user table is going to be fairly large, and although the group
membership will be fairly static, new users could be added at any stage. So
I want to keep manual processing to a minimum.

Thanks,
Rich

Hi,

This can be done only by writing a stored procedure in SQL Server, which
would give the child table values as comma seperated string. The result of
the stored procedure can be loaded into the dataset. Concatenation of row
values is not possible with single expression in dataset.

Srimathi.

Hi,

[ BTW, I'm using C# in WindowsForms. ]

I'm using a DataSet and have a main table ( i.e. of users ), and sub-tables
which contain further information about the entries in the main table ( i.e.
group membership, etc.. ). These sub-tables contain 1-many type
relationships ( i.e. a user can belong to many groups ). I have set-up a
DataRelation so that these entries are linked and when they are displayed in
the DataGrid I get the DataRelation link which then displays the group
membership entries for that user in its own grid.

Is it possible to concatinate the group membership entries ( i.e. comma
separated group names ) into an extra column when the main table is shown in
the datagrid - I would really want this to be automatic through the
DataRelation, rather than have to calculate it separately - although any
solution would be appreciated !! ;-). I only want a single datagrid so cant
have a master-detail type display.

I've tried adding a DataColumn and using the expression proptery, but this
only seems to allow single values or number collation ( SUM, etc.. ), not
string collation.

Thanks in advance
RichS

For example, I would want to show the following in my datagrid:
User Name || Groups
Bill || Finance, Sales

If i had the following in my DataSet:

User Table:
User_ID || UserName
1 || Bill

Group Membership Table:
User_ID || Group_ID
1 || 1
1 || 2

Group Table:
Group_ID || Group_Name
1 || Sales
2 || Finance

 

[Guest]

Hi,

Yes, this is difficult one. But doing this process (getting child tables
values as comma seperated values) in SQL Server itself is better than trying
it in dataset which would cost much more, that is the reason i suggested the
stored procedure option. Currently I can give u a solution with User-defined
function. But the perfomance of the same has to be tested. i checked it with
only with very few data.

The solution is as given below:
select userid,username, dbo.fnRows2String(usermaster.userid)
from usermaster

Create Function fnRows2String(@UserId int)
returns varchar(1000)
as
begin
declare @Result varchar(1000)
select @Result=COALESCE(@Result + ',','') + group_name
from membership
inner join groupmaster
on groupmaster.groupid=membership.groupid
where userid=@UserId
return(@Result)
End

I create tables as is it mentioned in your first mail. based on that i have
provided a solution for you.

Srimathi.

Thanks Srimathi - very quick reply!

My initial work-around was to iterate through every row in my Users table,
lookup the group membership in the relevant table, concatinate the entries
and the add this string to a manually added table in my dataset and then
create a DataRelation ( between the users table and my new table ) and then a
DataColumn in my users table using the expression property to reference this
DataRelation.

I can do the stored procedure no problem, but how do I link this from the
Users table into the extra column ? Do I have to iterate through all of the
rows and manually call the stored procedure for each user, or would I use the
Stored Procedure to generate the entire row data for the user table (
including comma separated string ) and then associate this with a datatable.

I was hoping to be able to use the dataset to only have to grab newly added
information ( rather than having to reload the entire data whenever I refresh
the locally stored data ) - would a stored procedure work in this way?

How would you do this ?

If it adds too much of a performance cost to either the client, client->sql
comms, or sql server itself, I will either have to come up with a different
way, or not do it at all.

The user table is going to be fairly large, and although the group
membership will be fairly static, new users could be added at any stage. So
I want to keep manual processing to a minimum.

Thanks,
Rich

Hi,

This can be done only by writing a stored procedure in SQL Server, which
would give the child table values as comma seperated string. The result of
the stored procedure can be loaded into the dataset. Concatenation of row
values is not possible with single expression in dataset.

Srimathi.

Hi,

[ BTW, I'm using C# in WindowsForms. ]

I'm using a DataSet and have a main table ( i.e. of users ), and sub-tables
which contain further information about the entries in the main table ( i.e.
group membership, etc.. ). These sub-tables contain 1-many type
relationships ( i.e. a user can belong to many groups ). I have set-up a
DataRelation so that these entries are linked and when they are displayed in
the DataGrid I get the DataRelation link which then displays the group
membership entries for that user in its own grid.

Is it possible to concatinate the group membership entries ( i.e. comma
separated group names ) into an extra column when the main table is shown in
the datagrid - I would really want this to be automatic through the
DataRelation, rather than have to calculate it separately - although any
solution would be appreciated !! ;-). I only want a single datagrid so cant
have a master-detail type display.

I've tried adding a DataColumn and using the expression proptery, but this
only seems to allow single values or number collation ( SUM, etc.. ), not
string collation.

Thanks in advance
RichS

For example, I would want to show the following in my datagrid:
User Name || Groups
Bill || Finance, Sales

If i had the following in my DataSet:

User Table:
User_ID || UserName
1 || Bill

Group Membership Table:
User_ID || Group_ID
1 || 1
1 || 2

Group Table:
Group_ID || Group_Name
1 || Sales
2 || Finance

 

[Guest]

Brilliant, thanks for the help.

Rich.

Hi,

Yes, this is difficult one. But doing this process (getting child tables
values as comma seperated values) in SQL Server itself is better than trying
it in dataset which would cost much more, that is the reason i suggested the
stored procedure option. Currently I can give u a solution with User-defined
function. But the perfomance of the same has to be tested. i checked it with
only with very few data.

The solution is as given below:
select userid,username, dbo.fnRows2String(usermaster.userid)
from usermaster

Create Function fnRows2String(@UserId int)
returns varchar(1000)
as
begin
declare @Result varchar(1000)
select @Result=COALESCE(@Result + ',','') + group_name
from membership
inner join groupmaster
on groupmaster.groupid=membership.groupid
where userid=@UserId
return(@Result)
End

I create tables as is it mentioned in your first mail. based on that i have
provided a solution for you.

Srimathi.

Thanks Srimathi - very quick reply!

My initial work-around was to iterate through every row in my Users table,
lookup the group membership in the relevant table, concatinate the entries
and the add this string to a manually added table in my dataset and then
create a DataRelation ( between the users table and my new table ) and then a
DataColumn in my users table using the expression property to reference this
DataRelation.

I can do the stored procedure no problem, but how do I link this from the
Users table into the extra column ? Do I have to iterate through all of the
rows and manually call the stored procedure for each user, or would I use the
Stored Procedure to generate the entire row data for the user table (
including comma separated string ) and then associate this with a datatable.

I was hoping to be able to use the dataset to only have to grab newly added
information ( rather than having to reload the entire data whenever I refresh
the locally stored data ) - would a stored procedure work in this way?

How would you do this ?

If it adds too much of a performance cost to either the client, client->sql
comms, or sql server itself, I will either have to come up with a different
way, or not do it at all.

The user table is going to be fairly large, and although the group
membership will be fairly static, new users could be added at any stage. So
I want to keep manual processing to a minimum.

Thanks,
Rich

Hi,

This can be done only by writing a stored procedure in SQL Server, which
would give the child table values as comma seperated string. The result of
the stored procedure can be loaded into the dataset. Concatenation of row
values is not possible with single expression in dataset.

Srimathi.

:

Hi,

[ BTW, I'm using C# in WindowsForms. ]

I'm using a DataSet and have a main table ( i.e. of users ), and sub-tables
which contain further information about the entries in the main table ( i.e.
group membership, etc.. ). These sub-tables contain 1-many type
relationships ( i.e. a user can belong to many groups ). I have set-up a
DataRelation so that these entries are linked and when they are displayed in
the DataGrid I get the DataRelation link which then displays the group
membership entries for that user in its own grid.

Is it possible to concatinate the group membership entries ( i.e. comma
separated group names ) into an extra column when the main table is shown in
the datagrid - I would really want this to be automatic through the
DataRelation, rather than have to calculate it separately - although any
solution would be appreciated !! ;-). I only want a single datagrid so cant
have a master-detail type display.

I've tried adding a DataColumn and using the expression proptery, but this
only seems to allow single values or number collation ( SUM, etc.. ), not
string collation.

Thanks in advance
RichS

For example, I would want to show the following in my datagrid:
User Name || Groups
Bill || Finance, Sales

If i had the following in my DataSet:

User Table:
User_ID || UserName
1 || Bill

Group Membership Table:
User_ID || Group_ID
1 || 1
1 || 2

Group Table:
Group_ID || Group_Name
1 || Sales
2 || Finance