looking for more efficient way to concatinate values in a datatable

[camatulli]

i'm having an major performance issue with a concatination query i wrote against a datatable and was looking for a way to either speed up the query or a whole nother way of doing it which performs better... at present my app spends 2 min processing this one loop, while i can do the two loops individually in about 5 seconds. Here is the code: For Each dr As DataRow In Main.DS_New.Tables(RightTable).Select(filter, "location,type desc") Dim CATValues As String = Nothing If dr.RowState <> DataRowState.Deleted And dr.RowState <> DataRowState.Detached Then Select Case dr("name").ToString.ToLower Case "value" For Each DRsameLocation As DataRow In Main.DS_New.Tables(RightTable).Select(filter & " AND PK<>" & dr("PK") & " AND Location='" & dr("Location") & "' AND type='" & dr("type") & "' AND Name='" & dr("Name") & "'") CATValues += Chr(215) & DRsameLocation("loc") & Chr(214) & DRsameLocation("value") DRsameLocation.Delete() Next If CATValues = Nothing Then dr("value") = dr("loc") & Chr(214) & dr("value") Else dr("value") = dr("loc") & Chr(214) & dr("value") & CATValues dr("loc") = "MV" End If End Select End If Next

[Dave Kreskowiak]

This would probably be better done on the database side with a stored procedure. Look into the COALESCE sql function.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[camatulli]

unfortunatly since the data is in a datatable, and not in SQL, that is not an option.