Brilliant, thanks for the help.
Rich.
"Srimathi" wrote:
> 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.
>
>
>
> "RichS" wrote:
>
> > 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
> >
> > "Srimathi" wrote:
> >
> > > 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.
> > >
> > > "RichS" wrote:
> > >
> > > > 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
> > > >
|
Similar Threads
