PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Copy Folder Tree From The Database Table & Paste It Into The Destination Folder In Same Table Using C#

I have only one database, and only one table named WorkingDirectory.

I want to copy folder structure from source (RootFolder1) folder and paste it into destination (RootFolder2) folder in the same table using C#.

Below parameter passed from the UI side or swagger.

[
    {
        "copySourceFolderId": 1,
        "pasteDestinationFolderId": 5
    }
]

WorkingDirectory table structure:

|   Id  |   ParentId   |   Name          |
|   1   |   null       |   RootFolder1   |
|   2   |   1          |   SubFolder1    |
|   3   |   1          |   SubFolder2    |
|   4   |   2          |   SubSubFolder1 |
|   5   |   null       |   RootFolder2   |

Step 1: Get the Nth level of tree data structure from the table based on copySourceFolderId = 1.

Getting below result from the query and storing the result into "workingDirectoryList" object.

|   Id  |   ParentId   |   Name          |
|   1   |   null       |   RootFolder1   |
|   2   |   1          |   SubFolder1    |
|   3   |   1          |   SubFolder2    |
|   4   |   2          |   SubSubFolder1 |

with recursive subordinate as 
(
    select "Id" ,"Name" ,"ParentId" from ol."WorkingDirectory" w  where "Id" = 1  
    union 
    select wd2."Id" ,wd2."Name" ,wd2."ParentId" from ol."WorkingDirectory" wd2  
    inner join subordinate s on s."Id"= wd2."ParentId"
)
select s."Id",s."Name",s."ParentId"
from subordinate s
group by s."Id",s."Name",s."ParentId"
order by s."Id" asc


List<WorkingDirectoryModel> workingDirectoryList = new List<WorkingDirectoryModel>();

public class WorkingDirectoryModel
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Name { get; set; }
}

Step 2: One by one, add or insert records in the "WorkingDirectory" table from "workingDirectoryList" object using C#.

foreach (var item in workingDirectoryList)
{
    // What logic should written here?
}

Step 3: output of WorkingDirectory table after copy RootFolder1 and paste it into RootFolder2:

|   Id  |   ParentId   |   Name          |
|   1   |   null       |   RootFolder1   |
|   2   |   1          |   SubFolder1    |
|   3   |   1          |   SubFolder2    |
|   4   |   2          |   SubSubFolder1 |
|   5   |   null       |   RootFolder2   |
|   6   |   5          |   RootFolder1   |
|   7   |   6          |   SubFolder1    |
|   8   |   6          |   SubFolder2    |
|   9   |   7          |   SubSubFolder1 |
Tags:

2

Answers


  1. 0

    As per my understanding, you need a parent-child relationship,

    Please check this query I prepared in SQL.

    DECLARE @WorkingDirectory TABLE
(
    Id int IDENTITY(1,1),
    Name NVARCHAR(MAX),
    ParentId INT NULL
)

INSERT INTO @WorkingDirectory
VALUES('RootFolder',NULL),
('SubFolder1',1),
('SubFolder2 ',1),
('SubSubFolder1 ',2),
('RootFolder2',NULL),
('RootFolder1 ',5),
('SubFolder1 ',6),
('SubFolder2 ',6),
('SubSubFolder1',7)


;with subordinate as 
(
    select Id ,Name ,ParentId from @WorkingDirectory w  where ParentId IS NULL 
    union all
    select wd2.Id ,wd2.Name ,wd2.ParentId from @WorkingDirectory wd2  
    inner join subordinate s on s.Id= wd2.ParentId
)
select s.Id,s.Name,s.ParentId
from subordinate s
order by s.Id asc

    OutPut

    enter image description here

    Do you want to get data based on the query?
    So you have to add a where condition in the CTE.

    "copySourceFolderId": 1

--SQL QUERY
select s.Id,s.Name,s.ParentId
from subordinate s
WHERE (id = 1 OR ParentId=1)
order by s.Id asc
    Login or Signup to reply.
  2. 0

    If I understand your objective then this can be accomplished in a single query. A couple notes first:

    • Postgres allows DML within a CTE.
    • Postgres allows multiple CTEs to be generated.
    • DML operations are valid as the main query and can use any/all the generated CTEs.

    With these then you get what you want with:

    with recursive
     new_root(new_root_id) as
         (insert into WorkingDirectory(name) 
               values ('Root_folder2')
            returning (id) 
         )
    , old_parents(id,name,parentid) as     -- recursive part begins here
         (select * 
            from WorkingDirectory 
           where id = 1
          union all 
           select w.*
             from WorkingDirectory w
             join old_parents      op 
               on op.id = w.parentid
          ) 
insert into WorkingDirectory(name,parentid)
       select op.name, coalesce(op.parentid,0) + nr.new_root_id
         from old_parents   op
         cross join new_root nr;

    The new_root CTE gets the new row with Root_Folder2 and returns its id. This is later used to generate the copied hierarchy revised ParentId. The old_parents just builds an image of the desired hierarchy to be copied. Finally the main query (Insert DML) generates the copy while recalculating the ParientId. (see demo here).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search