Filter & Sort DataTable using LINQ

You need to add reference to System.Data.DataSetExtensions to be able to use AsEnumerable() on a datatable.

Dim dt As New DataTable

dt.Load(myDataReader)

'get name of distinct root taxonomies

Dim root_names As IEnumerable(Of String) = From myRow In dt.AsEnumerable() _

Select myRow.Field(Of String)("root_name") Distinct

'get a list of the root taxonomy objects based on the name

Dim list As New List(Of Taxon.Taxon)

For Each root_name As String In root_names

Dim t As Taxon.Taxon = Taxon.TaxonDAL.Instance.GetOnName(root_name)

If t IsNot Nothing Then

list.Add(t)

End If

Next

'sort the root taxons by Product_DisplayOrder

list.Sort(Function(p1, p2) p1.Product_DisplayOrder.CompareTo(p2.Product_DisplayOrder))

For Each t As Taxon.Taxon In list

Dim currentRootName As String = t.name

'get the children of the current root taxon

Dim drRows As IEnumerable(Of DataRow) = From myRow In dt.AsEnumerable() _

Where myRow.Field(Of String)("root_name") = currentRootName _

Order By myRow.Field(Of Double)("Product_DisplayOrder") Ascending _

Select myRow

For Each rv As DataRow In drRows

Dim ThisTaxonId As String = rv("taxon_id")

Next

Next

Reference:

LINQ query on a DataTable

While loop in sql

Here is how to do while loop in sql:

--script to copy UserOrganisationId record from User table to User_Organisation

Declare @UserID as int

Declare @MaxUserID as int

Declare @OrgID int

select @UserID = MIN(intuserid) from [user]

select @MaxUserID = MAX(intuserid) from [user]

WHILE (@UserID <= @MaxUserID)

BEGIN

if exists(Select * from [user] where intuserid=@UserID)

begin

select @UserID=intuserid, @OrgID=UserOrganisationId

from [user] where intuserid = @UserID

if @orgID is not null and @orgID > 0

begin

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

VALUES (@UserID, @OrgID, 1)

end

end

set @UserID=@UserID+1

END

-- alternative script

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

SELECT intUserID, UserOrganisationId, 1

FROM [User]

WHERE (UserOrganisationId IS NOT NULL) AND (UserOrganisationId > 0)

Get comma seperated string

This is how you can get comma seperated string:

Public Function GetUserOrganisationIds(ByVal userID As Integer) As String

Dim dbCon As New SqlConnection(ConnectionString)

dbCon.Open()

Dim cmd As New SqlCommand("GetUserOrganisationIds", dbCon)

cmd.CommandType = CommandType.StoredProcedure

Try

Dim dr As SqlDataReader = cmd.ExecuteReader

Dim orgList As New List(Of String)

While dr.Read

orgList.Add(dr("OrganisationID").ToString)

End While

If orgList.Count > 0 Then

Return String.Join(",", orgList.ToArray())

Else

Return ""

End If

Catch ex As Exception

Throw (ex)

Finally

dbCon.Close()

End Try

End Function

Here is how to do it in sql:

--declare holder var

DECLARE @list VARCHAR(8000)

--build comma separated list

SELECT @list = COALESCE(@list + ',', '') + CAST(DZName AS VARCHAR(5) )

FROM ClubDZlocation where ClubId = 201

--show results

SELECT @list AS 'list'