Recursive lookup needed. DB vs. C# GUI

I currently have a program in which I want to list ‘support tickets’ in a grid.

The tickets are coming from a SQL DB out of a ticketing system.

A ticket has an activity, example:

enter image description here

I have a fully functional design but have a problem to list the following:

What I want to do in my program is to list the parent tickets, with their child tickets as children (details). However, with the added complexity, i’m obliged to do this recursively.

Example:

enter image description here

My questions were:

  • In this case, is the recursive lookup to be done on DB level? Or application level?

  • AM i obliged to achieve this, to loop through all the tickets PER TICKET?

  • How is performance affected by these look ups?

I did some research and tried to get the tickets alone but haven’t achieved this. I tried it on the level of the DB using:

SELECT ticketID,
FROM dbo.Ticket
UNION ALL

SELECT a.ticketID, 
FROM dbo.Ticket a 
INNER JOIN dbo.ticket s ON a.ticketID = s.ticketID 

SELECT * FROM Ticket 
where ticketID = 1

While I was testing this, i had the idea that maybe I’m doing this wrong, since maybe, the recursive search is better to be done in my application level (in which i would just need to do a simple select * from ticket in my DB.

Is this true? Would I be needing to do this on application level?

Answer

using a recursive query in SQL Server

Given

enter image description here

With TicketTree(tID,  pID)
As
(
    Select ticketID, parentID From Ticket Where ticketID = 1 
    UNION ALL
    Select ticketID, parentID from Ticket Inner join TicketTree R On parentID = R.tID   
)
select * from TicketTree

gives

enter image description here

or if you query root ticket 4

enter image description here

You can then join with your Activity or whatever you want to do, I just kept it to the minimum need for a recursive query

Leave a Reply

Your email address will not be published. Required fields are marked *