Copy the table data to the same table

Question

0.00/5 (No votes)

See more:

I have a 'Page' table with parent and child relations like..

PageId   Name ParentPageId
1         A        0
2         B        1
3         C        1
4         D        2
5         E        2
6         F        4

My requirement is, I want to copy of the above data based on the pageId.

For example if I pass PageId=1, all the relate rows should copy..

My desired output is like this..

PageId   Name ParentPageId
7          A        0
8          B        7
9          C        7
10         D        7
11         E        7
12         F        10

--------------------------------------------------------
For this I used recursion method..
my code is given below..

C#

private void CreatePageCopy(int pId)
{
    try
    {
        objpagebo.Action = "COPY";
        objpagebo.PageID = pId;

        if (dsChildPages != null)
        {
            if (dsChildPages.Tables[0].Rows.Count != 0)
            {
                objpagebo.PageName = dsChildPages.Tables[0].Rows[0]["PageName"].ToString();
            }
        }
        else
        {
            objpagebo.PageName = HfPageName.Value;
        }
        objpagebo.ActiveStatus = "Active";
        objpagebo.CreationDate = Convert.ToDateTime(System.DateTime.Now).ToString("dd/mm/yyyy");
        objpagebo.CreatedBy = Convert.ToString(oEmployeeBO.EmployeeId);

        objpagebo.ModifiedBy = Convert.ToString(oEmployeeBO.EmployeeId);

        objpagebo.ModificationDate = Convert.ToDateTime(System.DateTime.Now).ToString("dd/MM/yyyy");

        DataTable dtGetParentPageId = PageMasterBLL.GetParentPageid(objpagebo);
        if (dsChildPages != null)
        {
            if (dsChildPages.Tables[0].Rows.Count != 0)
            {
                objpagebo.ParentPageId = Convert.ToInt32(dsChildPages.Tables[1].Rows[0]["ParentId"]);
            }
        }
        else
        {
            objpagebo.ParentPageId = Convert.ToInt32(dtGetParentPageId.Rows[0]["ParentPageId"]);
        }

        objpagebo.PageDescription = "";

        dsChildPages = PageMasterBLL.InsertPageCopy(objpagebo);
        int PageIdForCopy = Convert.ToInt32(dsChildPages.Tables[1].Rows[0]["ParentId"]);

        if (dsChildPages.Tables[0].Rows.Count > 0 && dsChildPages != null)
        {
            foreach (DataRow drChildPages in dsChildPages.Tables[0].Rows)
            {
                CreatePageCopy(Convert.ToInt32(drChildPages["PageId"]));
            }
        }
    }
    catch (Exception Ex)
    {

        throw Ex;
    }
}

But this method will work only if a page have child pages with one level...next level ParentPageId is not comming correctely..because here I am taking Parent PageId as last Inserted row,.

Pls give me a solution..

Posted 10-May-13 2:05am

kanamala subin

Updated 10-May-13 2:13am

v3

Add a Solution

Comments

Yuriy Loginov 10-May-13 11:41am

your desired output seems a little off,
did you mean this instead?
PageId Name ParentPageId
7 A 0
8 B 7
9 C 7
10 D 8
11 E 8
12 F 10

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mojtaba Eng · Answer 1 · 2013-05-10T03:10:00

Combine Child Code and Parent Code :
your Record Code is : Level1-level2-level3-....-levelN
and each dash(-) show one level of generation. this means child code build from parent code + "-" + childNumber :
level 1 :
1
1-1
1-1-1
1-1-2
1-2
1-3
2
2-1
2-2
3
4
5

and using Like you can select child and parent of nodes

debkumar@codeproject · Answer 2 · 2013-05-11T01:31:00

Without knowing your hierarchy of paging, you could write a script to copy these rows. If you notice, you could see, for a particular Page, same difference is maintained between ParentPageId and PageId in original record and copied record. You could take advantage of that.

SQL

DECLARE @maxpageid INT
SELECT @maxpageid = MAX(PageId) FROM PageList
INSERT INTO PageList (PageId, Name, ParentPageId) SELECT (PageId + @maxpageid), Name,
CASE ParentPageId
    WHEN 0 THEN 0
    ELSE (ParentPageId + @maxpageid)
END
FROM PageList

This is not is correct approach. If this copying is one time and pageIds are +ve integer, then it would work fine. I would recommend to use recursive stored procedure/ sql function to do this.

Cheers,

Copy the table data to the same table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Copy the table data to the same table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us