Tuesday, December 15, 2009

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

Wednesday, December 9, 2009

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'