|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Filtering Parent table on child recordsGood day,
I have a DataSet with 2 tables, Parent & Child, that are connected by a DataRelation. I would like to filter the Parent table to display only those records which have a record in the Child table that satisfies a certain condition. Any suggestions? Cheers, Helen Helen,
Normally you have to get the childrows from the parent and than to loop to those. http://msdn2.microsoft.com/en-US/library/system.data.datarow.getchildrows.aspx I hope this helps, Cor Show quote "Helen Warn" <HelenW***@discussions.microsoft.com> schreef in bericht news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... > Good day, > > I have a DataSet with 2 tables, Parent & Child, that are connected by a > DataRelation. I would like to filter the Parent table to display only > those > records which have a record in the Child table that satisfies a certain > condition. > > Any suggestions? > > Cheers, > > Helen > -- > Helen Warn, PhD > Agile Software Inc. > www.agile-soft.com Like Cor mentions, GetChildRows is your best bet. You can also use a
DataView and reset the RowFilter property which is sometimes a little cleaner syntactically but less performant. Overall I'd use Cor's approach Show quote "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... > Good day, > > I have a DataSet with 2 tables, Parent & Child, that are connected by a > DataRelation. I would like to filter the Parent table to display only > those > records which have a record in the Child table that satisfies a certain > condition. > > Any suggestions? > > Cheers, > > Helen > -- > Helen Warn, PhD > Agile Software Inc. > www.agile-soft.com The trouble is that you have to first call GetChildRows() on each parent row,
which I was hoping to avoid. In my case I think it will be faster to filter the child table, then walk through the results to get the parent row, building a DataView on them. I was hoping there was a more SQL-like way, to do it all in one step. Thanks for your responses. Show quote "W.G. Ryan [MVP]" wrote: > Like Cor mentions, GetChildRows is your best bet. You can also use a > DataView and reset the RowFilter property which is sometimes a little > cleaner syntactically but less performant. Overall I'd use Cor's approach > "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message > news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... > > Good day, > > > > I have a DataSet with 2 tables, Parent & Child, that are connected by a > > DataRelation. I would like to filter the Parent table to display only > > those > > records which have a record in the Child table that satisfies a certain > > condition. > > > > Any suggestions? > > > > Cheers, > > > > Helen > > -- > > Helen Warn, PhD > > Agile Software Inc. > > www.agile-soft.com > > > Helen,
There are mostly for profesionals no one step solutions, just because they don't perform as we wish. (Don't forget that SQL was created as a simple plain language for endusers). But there will be Linq in future which should cover your question. Cor Show quote "Helen Warn" <HelenW***@discussions.microsoft.com> schreef in bericht news:5C13DB8E-7298-4225-8E1A-D1D9BADF16DC@microsoft.com... > The trouble is that you have to first call GetChildRows() on each parent > row, > which I was hoping to avoid. > > In my case I think it will be faster to filter the child table, then walk > through the results to get the parent row, building a DataView on them. > > I was hoping there was a more SQL-like way, to do it all in one step. > > Thanks for your responses. > -- > Helen Warn, PhD > Agile Software Inc. > www.agile-soft.com > > > "W.G. Ryan [MVP]" wrote: > >> Like Cor mentions, GetChildRows is your best bet. You can also use a >> DataView and reset the RowFilter property which is sometimes a little >> cleaner syntactically but less performant. Overall I'd use Cor's approach >> "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message >> news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... >> > Good day, >> > >> > I have a DataSet with 2 tables, Parent & Child, that are connected by a >> > DataRelation. I would like to filter the Parent table to display only >> > those >> > records which have a record in the Child table that satisfies a certain >> > condition. >> > >> > Any suggestions? >> > >> > Cheers, >> > >> > Helen >> > -- >> > Helen Warn, PhD >> > Agile Software Inc. >> > www.agile-soft.com >> >> >> Hi Cor,
I find from the docs that you can use only aggregate functions on the Child in the RowFilter property of the parent, and these do not allow a condition. I was going to make a suggestion that a future version of ADO.NET expand this functionality, but find that it has already been done. Until then ... Laters Helen Show quote "Cor Ligthert [MVP]" wrote: > Helen, > > There are mostly for profesionals no one step solutions, just because they > don't perform as we wish. > (Don't forget that SQL was created as a simple plain language for endusers). > > But there will be Linq in future which should cover your question. > > Cor > > "Helen Warn" <HelenW***@discussions.microsoft.com> schreef in bericht > news:5C13DB8E-7298-4225-8E1A-D1D9BADF16DC@microsoft.com... > > The trouble is that you have to first call GetChildRows() on each parent > > row, > > which I was hoping to avoid. > > > > In my case I think it will be faster to filter the child table, then walk > > through the results to get the parent row, building a DataView on them. > > > > I was hoping there was a more SQL-like way, to do it all in one step. > > > > Thanks for your responses. > > -- > > Helen Warn, PhD > > Agile Software Inc. > > www.agile-soft.com > > > > > > "W.G. Ryan [MVP]" wrote: > > > >> Like Cor mentions, GetChildRows is your best bet. You can also use a > >> DataView and reset the RowFilter property which is sometimes a little > >> cleaner syntactically but less performant. Overall I'd use Cor's approach > >> "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message > >> news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... > >> > Good day, > >> > > >> > I have a DataSet with 2 tables, Parent & Child, that are connected by a > >> > DataRelation. I would like to filter the Parent table to display only > >> > those > >> > records which have a record in the Child table that satisfies a certain > >> > condition. > >> > > >> > Any suggestions? > >> > > >> > Cheers, > >> > > >> > Helen > >> > -- > >> > Helen Warn, PhD > >> > Agile Software Inc. > >> > www.agile-soft.com > >> > >> > >> > > > Helen:
I'm not sure you are correct if I understand you correctly. YOu can aggregate any field you want irrespective of child or parent and you can also put conditions on it. However since you mention the documentation, we may be talking about a different situation. DataTable.Compute can do what you mention http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute.aspx Show quote "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message news:F4DD5A18-848E-477E-BCB6-4ED526F60D11@microsoft.com... > Hi Cor, > > I find from the docs that you can use only aggregate functions on the > Child > in the RowFilter property of the parent, and these do not allow a > condition. > > I was going to make a suggestion that a future version of ADO.NET expand > this functionality, but find that it has already been done. Until then ... > > Laters > > Helen > -- > Helen Warn, PhD > Agile Software Inc. > www.agile-soft.com > > > "Cor Ligthert [MVP]" wrote: > >> Helen, >> >> There are mostly for profesionals no one step solutions, just because >> they >> don't perform as we wish. >> (Don't forget that SQL was created as a simple plain language for >> endusers). >> >> But there will be Linq in future which should cover your question. >> >> Cor >> >> "Helen Warn" <HelenW***@discussions.microsoft.com> schreef in bericht >> news:5C13DB8E-7298-4225-8E1A-D1D9BADF16DC@microsoft.com... >> > The trouble is that you have to first call GetChildRows() on each >> > parent >> > row, >> > which I was hoping to avoid. >> > >> > In my case I think it will be faster to filter the child table, then >> > walk >> > through the results to get the parent row, building a DataView on them. >> > >> > I was hoping there was a more SQL-like way, to do it all in one step. >> > >> > Thanks for your responses. >> > -- >> > Helen Warn, PhD >> > Agile Software Inc. >> > www.agile-soft.com >> > >> > >> > "W.G. Ryan [MVP]" wrote: >> > >> >> Like Cor mentions, GetChildRows is your best bet. You can also use a >> >> DataView and reset the RowFilter property which is sometimes a little >> >> cleaner syntactically but less performant. Overall I'd use Cor's >> >> approach >> >> "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message >> >> news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... >> >> > Good day, >> >> > >> >> > I have a DataSet with 2 tables, Parent & Child, that are connected >> >> > by a >> >> > DataRelation. I would like to filter the Parent table to display >> >> > only >> >> > those >> >> > records which have a record in the Child table that satisfies a >> >> > certain >> >> > condition. >> >> > >> >> > Any suggestions? >> >> > >> >> > Cheers, >> >> > >> >> > Helen >> >> > -- >> >> > Helen Warn, PhD >> >> > Agile Software Inc. >> >> > www.agile-soft.com >> >> >> >> >> >> >> >> >> As far as I can see, you run into exactly the same problem. A filter
condition that references a Child column must be an aggregate function, and the argument to the aggregate function can only be a column name, not an expression using the column name. cheers, Helen Show quote "W.G. Ryan [MVP]" <WilliamRyan@nospam.gmail.com> wrote in message news:%23$zYJuu8GHA.4568@TK2MSFTNGP02.phx.gbl... > Helen: > > I'm not sure you are correct if I understand you correctly. YOu can > aggregate any field you want irrespective of child or parent and you can > also put conditions on it. However since you mention the documentation, > we may be talking about a different situation. DataTable.Compute can do > what you mention > http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute.aspx > > > "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message > news:F4DD5A18-848E-477E-BCB6-4ED526F60D11@microsoft.com... >> Hi Cor, >> >> I find from the docs that you can use only aggregate functions on the >> Child >> in the RowFilter property of the parent, and these do not allow a >> condition. >> >> I was going to make a suggestion that a future version of ADO.NET expand >> this functionality, but find that it has already been done. Until then >> ... >> >> Laters >> >> Helen >> -- >> Helen Warn, PhD >> Agile Software Inc. >> www.agile-soft.com >> >> >> "Cor Ligthert [MVP]" wrote: >> >>> Helen, >>> >>> There are mostly for profesionals no one step solutions, just because >>> they >>> don't perform as we wish. >>> (Don't forget that SQL was created as a simple plain language for >>> endusers). >>> >>> But there will be Linq in future which should cover your question. >>> >>> Cor >>> >>> "Helen Warn" <HelenW***@discussions.microsoft.com> schreef in bericht >>> news:5C13DB8E-7298-4225-8E1A-D1D9BADF16DC@microsoft.com... >>> > The trouble is that you have to first call GetChildRows() on each >>> > parent >>> > row, >>> > which I was hoping to avoid. >>> > >>> > In my case I think it will be faster to filter the child table, then >>> > walk >>> > through the results to get the parent row, building a DataView on >>> > them. >>> > >>> > I was hoping there was a more SQL-like way, to do it all in one step. >>> > >>> > Thanks for your responses. >>> > -- >>> > Helen Warn, PhD >>> > Agile Software Inc. >>> > www.agile-soft.com >>> > >>> > >>> > "W.G. Ryan [MVP]" wrote: >>> > >>> >> Like Cor mentions, GetChildRows is your best bet. You can also use a >>> >> DataView and reset the RowFilter property which is sometimes a little >>> >> cleaner syntactically but less performant. Overall I'd use Cor's >>> >> approach >>> >> "Helen Warn" <HelenW***@discussions.microsoft.com> wrote in message >>> >> news:5321A0B6-2AFE-4753-8699-A1DD79BF1877@microsoft.com... >>> >> > Good day, >>> >> > >>> >> > I have a DataSet with 2 tables, Parent & Child, that are connected >>> >> > by a >>> >> > DataRelation. I would like to filter the Parent table to display >>> >> > only >>> >> > those >>> >> > records which have a record in the Child table that satisfies a >>> >> > certain >>> >> > condition. >>> >> > >>> >> > Any suggestions? >>> >> > >>> >> > Cheers, >>> >> > >>> >> > Helen >>> >> > -- >>> >> > Helen Warn, PhD >>> >> > Agile Software Inc. >>> >> > www.agile-soft.com >>> >> >>> >> >>> >> >>> >>> >>> > > I ran into the same problem and because I wondered why an aggregate shouldn't be appropriate for the given probelm, I tried it out successfully.
If I didn't get the problem wrong, that should be one (!) solution: I have a checkbox "Hide rows with no matches" on a Form with two data grids; a relation exists. The event handler looks like this: this.Cursor = Cursors.WaitCursor; if (cbHideXrows.Checked) { string Filter = "Count(Child(" + _ds.Relations[0].RelationName + ")." + _ds.Relations[0].ParentColumns[0].ColumnName + ") > 0"; dvLeft.RowFilter = Filter; } else { dvLeft.RowFilter = ""; } /////////////////////////////////////// dvLeft is the view behind the left DataGrid. It can be determined with the following code, assuming that the datasource of leftGrid is already set to _ds.Tables[0] /////////////////////////////////////// CurrencyManager cmLeft = (CurrencyManager)leftGrid.BindingContext[_ds.Tables[0]]; dvLeft = (DataView)cmLeft.List; /////////////////////////////////////// Any comments appreciated. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com  |
|||||||||||||||||||||||
Other interesting topics