Solved

SQL Copy table record and all related table records linked via relationships from VB.net or C#

Posted on 2006-11-30
25
673 Views
Last Modified: 2008-03-04
Hi there, I am working in VB.Net 2.0 Windows Forms with SQL 2000.

I have a requirement to copy a campaign in a our windows forms application. This involves copying the current campain record in the table plus all associate data inserting new copies of the data in all the related tables, making all new ids and records which all link back to the new campain.

Relationships back to the campaing table are all defined in SQL Server and there is currently 3 levels of tables nesting back to the campaign table via relationships.

What is the easiest and fastest way to do this?

I can use strongly typed data set and all the related tables on to it, and select the record for the campaign, but how do I select all related records in all the other tables and then copy them?

Thanks heaps, also I need this asap.

0
Comment
Question by:craigdev
  • 12
  • 7
  • 6
25 Comments
 
LVL 15

Expert Comment

by:ozymandias
ID: 18045770
Without knowing the structure of your database its hard to tell you exactly how to code this.
However, I would probably look at doing this in a SQL stored procedure and then just calling that from your .NET code.
Much simpler and more direct.
0
 
LVL 1

Author Comment

by:craigdev
ID: 18046530
How would I do this in SQL, you can give me an example for one found row in the parennt, multiple records matching that FK_Parent in a child table and multiple off this 2nd level child. I need to copy all existing data related to the Campain (root parent) and make one new entry at the parent and copies of all the related data referencing that parent down across nested tables.

Thanks
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18048336
OK.
Lets say I have

a table called Parent with ID, Name, Details.
a table called Child with ID, ParentID, Name, Details
a table called Grandchild1 with ID, ChildID, Name, Details
a table called Grandchild2 with ID, ChildID, Name, Details

Parent.ID is the FK to Child.ParentID
Child.ID is the FK to Grandchild1.ChildID and Grandchild1.ChildID.

If I duplicate a row in Parent I want to duplicate all the appropriate sub rows in Child, Grandchild1 and Grandchild2.

Here's the SQL SP :


=====================================================================================

CREATE PROCEDURE DuplicateParent
      @ParentID      integer -- the id of the parent we want to duplicate
AS
BEGIN

      BEGIN TRAN Duplicate -- start a transaction

      DECLARE @NEWID integer -- will hold the id of the newly created parent and be returned by the sp
      DECLARE @NEWCHILDID integer -- will hold each newly created child id

      INSERT INTO Parent ([Name], [Details]) SELECT Parent.[Name], Parent.[Details] FROM Parent WHERE ID = @ParentID -- duplicate the parent row

      IF (@@error <> 0)
      BEGIN
            ROLLBACK TRANSACTION MyName
            RETURN -1 -- returns -1 if there is an error
      END

      SELECT @NEWID = @@IDENTITY -- get the id of the new parent
      
      DECLARE @ChildID integer, @ChildName varchar(10), @CHildDetails varchar(10) -- create some variables to hold our data

      DECLARE child_cursor CURSOR FOR SELECT [ID], [Name], [Details] FROM Child WHERE ParentID = @ParentID -- grab each child of the parent into a cursor

      OPEN child_cursor  -- open the cursor

      FETCH NEXT FROM child_cursor
      INTO @ChildID, @ChildName, @ChildDetails -- fetch a cursor row into the variables

      WHILE @@FETCH_STATUS = 0 -- for each child row we can fetch...
      BEGIN
            INSERT INTO Child ([ParentID], [Name], [Details]) VALUES (@NEWID, @ChildName, @ChildDetails) -- create a new child assigned to the new parent

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION MyName
                  RETURN -1 -- returns -1 if there is an error
            END

            SELECT @NEWCHILDID = @@IDENTITY -- get the id of the new child

            INSERT INTO Grandchild1 ([ChildID], [Name], Details) SELECT @NEWCHILDID as ChildID, [Name], [Details] FROM Grandchild1 WHERE ChildID = @ChildID -- duplicate all the grandchildren in Grandchild1

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION MyName
                  RETURN -1 -- returns -1 if there is an error
            END

            INSERT INTO Grandchild2 ([ChildID], [Name], Details) SELECT @NEWCHILDID as ChildID, [Name], [Details] FROM Grandchild2 WHERE ChildID = @ChildID -- duplicate all the grandchildren in Grandchild2

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION MyName
                  RETURN -1 -- returns -1 if there is an error
            END

            FETCH NEXT FROM child_cursor
            INTO @ChildID, @ChildName, @ChildDetails -- fetch the next cursor row into the variables

      END
      
       CLOSE child_cursor
      DEALLOCATE child_cursor -- clean up
      
      
      COMMIT TRAN Duplicate -- commit

      RETURN @NEWID -- return our new parent id

END
GO

=====================================================================================

I can call this SP, pass it the ID of the Parent I want duplicated and it will pass abck the ID of the new duplicate parent or -1 if there was an error.
Also, if there is an error it will stop and roll back so you wont end up with a half-created duplicate.
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18073093
Here's a recursive function to handle the typed/untyped dataset relations.  
I coded it for single column, auto-increment primary key relationships:

    Private Sub CopyParentRowAndChildren(ByVal ParentRow As DataRow)
        Dim Table As DataTable = ParentRow.Table
        Dim NewRow As DataRow = Table.NewRow()
        For i As Integer = 0 To Table.Columns.Count - 1
            'TODO: Handle non-autoincrement unique columns.
            If Not Table.Columns(i).AutoIncrement Then
                NewRow(i) = ParentRow.Item(i)
            End If
        Next
        Table.Rows.Add(NewRow)
        For Each relation As DataRelation In Table.ChildRelations
            'Only copies child rows for relations with a SINGLE COLUMN primary key relationship.
            If relation.ParentColumns.Length = 1 AndAlso Table.PrimaryKey.Length = 1 AndAlso Table.PrimaryKey(0) Is relation.ParentColumns(0) Then
                For Each childRow As DataRow In ParentRow.GetChildRows(relation)
                    CopyParentRowAndChildren(childRow)
                Next
            End If
        Next
    End Sub
0
 
LVL 1

Author Comment

by:craigdev
ID: 18079978
ozymandias your sql sp works well I am trying to adapt it though, instead of Grandchild2, it is actually a child table of Grandchild1 so a GreatGrandChild. do I need a second cursor to process this now?

thanks
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080114
There's always more than one way to do something but off the top of my head, yes, you will need another cursor.
You could write this as separate SPs  that call each other in sequence.

0
 
LVL 1

Author Comment

by:craigdev
ID: 18080235
That's what I though, I am really struggling with how to write this as don't fully get how the cursor is working. How do I write the second cursor? Do I need to delcare it, close and deallocate each time around the cursor1 while loop?

Also I'll need the original not new @GrandChildID before entering the second cursor for the Insert .... into .... select .... WHERE GrandChildID = @GrandChildID

I not quite getting how to get this, I have this code after your Grandchild1 insert:

SELECT @NEWGrandChildID = @@IDENTITY
SELECT @OLDGrandChildID as UID_GrandChildID FROM Grandchild1 WHERE ChildID = @ChildID --- this line doesn't seem to work, am not sure where to put the new cursor

then go

INSERT INTO GreatGrandChild (GrandChildID, Details) SELECT @NEWGrandChildID as GrandChildID, Details FROM GreatGrandChild WHERE GrandChildID= @OLDGrandChildID

Thanks,
Klaus
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080873
Here are 4 SPs :

CREATE PROCEDURE DuplicateParent
      @ParentID      integer -- the id of the parent we want to duplicate
AS
BEGIN

      BEGIN TRAN DuplicateParent
      
      DECLARE @NEWID integer -- will hold the id of the newly created parent and be returned by the sp
      INSERT INTO Parent ([Name], [Details]) SELECT Parent.[Name], Parent.[Details] FROM Parent WHERE ID = @ParentID -- duplicte the parent row
      SELECT @NEWID = @@IDENTITY -- get the id of the new parent      
      
      DECLARE @SUCCESS integer
      
      EXEC @SUCCESS = DuplicateChildren @ParentID, @NEWID
      
      IF (@SUCCESS <> 0 OR @@error <> 0)
      BEGIN
            ROLLBACK TRANSACTION DuplicateParent
            RETURN -1
      END
      
      RETURN @NEWID -- return our new parent id

END
GO
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080875
CREATE PROCEDURE DuplicateChildren
      @OldParentID      integer, -- the id of the parent we want to duplicate
      @NewParentID      integer -- the id of the parent we just created
AS
BEGIN

      BEGIN TRAN DuplicateChildren
      
      DECLARE @OldChildID integer, @ChildName varchar(10), @CHildDetails varchar(10) -- create some variables to hold our data

      DECLARE child_cursor CURSOR FOR SELECT [ID], [Name], [Details] FROM Child WHERE ParentID = @OldParentID -- grab each child of the old parent into a cursor

      OPEN child_cursor  -- open the cursor

      FETCH NEXT FROM child_cursor
      INTO @OldChildID, @ChildName, @ChildDetails -- fetch a cursor row into the variables

      WHILE @@FETCH_STATUS = 0 -- for each child row we can fetch...
      BEGIN
            INSERT INTO Child ([ParentID], [Name], [Details]) VALUES (@NewParentID, @ChildName, @ChildDetails) -- create a new child assigned to the new parent

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION DuplicateChildren
                  RETURN -1 -- returns -1 if there is an error
            END
            
            DECLARE @NewChildID integer
            SELECT @NewChildID = @@IDENTITY -- get the id of the new child
      
            DECLARE @Success integer

            EXEC @Success = DuplicateGrandChild @OldChildID, @NewChildID
            
            IF (@SUCCESS <> 0 OR @@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION DuplicateChildren
                  RETURN -1
            END
            
            FETCH NEXT FROM child_cursor
            INTO @OldChildID, @ChildName, @ChildDetails -- fetch a cursor row into the variables


      END
      RETURN 0

END
GO
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080878
CREATE PROCEDURE DuplicateGrandChildren
      @OldChildID      integer, -- the id of the parent we want to duplicate
      @NewChildID      integer -- the id of the parent we just created
AS
BEGIN

      BEGIN TRAN DuplicateGrandChildren
      
      DECLARE @OldGrandChildID integer, @GrandChildName varchar(10), @GrandChildDetails varchar(10) -- create some variables to hold our data

      DECLARE grandchild_cursor CURSOR FOR SELECT [ID], [Name], [Details] FROM GrandChild1 WHERE ChildID = @OldChildID -- grab each child of the old parent into a cursor

      OPEN grandchild_cursor  -- open the cursor

      FETCH NEXT FROM grandchild_cursor
      INTO @OldGrandChildID, @GrandChildName, @GrandChildDetails -- fetch a cursor row into the variables

      WHILE @@FETCH_STATUS = 0 -- for each child row we can fetch...
      BEGIN
            INSERT INTO GrandChild1 ([ChildID], [Name], [Details]) VALUES (@NewChildID, @GrandChildName, @GrandChildDetails) -- create a new child assigned to the new parent

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION DuplicateGrandChildren
                  RETURN -1 -- returns -1 if there is an error
            END
            
            DECLARE @NewGrandChildID integer
            SELECT @NewGrandChildID = @@IDENTITY -- get the id of the new child
      
            DECLARE @Success integer

            EXEC @Success = DuplicateGreatGrandChild @OldGrandChildID, @NewGrandChildID
            
            IF (@SUCCESS <> 0 OR @@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION DuplicateGrandChildren
                  RETURN -1
            END

            FETCH NEXT FROM grandchild_cursor
            INTO @OldGrandChildID, @GrandChildName, @GrandChildDetails -- fetch a cursor row into the variables

      END
      RETURN 0

END
GO
0
 
LVL 15

Assisted Solution

by:ozymandias
ozymandias earned 250 total points
ID: 18080881
CREATE PROCEDURE DuplicateGreatGrandChildren
      @OldGrandChildID      integer, -- the id of the parent we want to duplicate
      @NewGrandChildID      integer -- the id of the parent we just created
AS
BEGIN

      BEGIN TRAN DuplicateGreatGrandChildren
      
      DECLARE @OldGreatGrandChildID integer, @GreatGrandChildName varchar(10), @GreatGrandChildDetails varchar(10) -- create some variables to hold our data

      DECLARE greatgrandchild_cursor CURSOR FOR SELECT [ID], [Name], [Details] FROM GreatGrandChild1 WHERE GrandChildID = @OldGrandChildID -- grab each child of the old parent into a cursor

      OPEN grandchild_cursor  -- open the cursor

      FETCH NEXT FROM grandchild_cursor
      INTO @OldGreatGrandChildID, @GreatGrandChildName, @GreatGrandChildDetails -- fetch a cursor row into the variables

      WHILE @@FETCH_STATUS = 0 -- for each child row we can fetch...
      BEGIN
            INSERT INTO GreatGrandChild1 ([GrandChildID], [Name], [Details]) VALUES (@NewGrandChildID, @GreatGrandChildName, @GreatGrandChildDetails) -- create a new child assigned to the new parent

            IF (@@error <> 0)
            BEGIN
                  ROLLBACK TRANSACTION DuplicateGreatGrandChildren
                  RETURN -1 -- returns -1 if there is an error
            END
            
            FETCH NEXT FROM grandchild_cursor
            INTO @OldGreatGrandChildID, @GreatGrandChildName, @GreatGrandChildDetails -- fetch a cursor row into the variables

      END
      RETURN 0

END
GO
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080886
You call the top one, DuplicateParent, which does its work and then calls DuplicateChildren which does its work and so on.
Each one has a transaction which is rolled back oif the one below fails.

I haven't had a chance to test this, but hopefully you can see where I am going :)

Cheers.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18080896
:(

But my solution is so easy.  Although you will get much better performance doing this on the database unless you want them available in the app interface.
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18080968
RobertRFreeman, your solution looks good but I have to admit I cannot see where new IDs get sorted out.
You create a new parent but where are you getting its new ID from  and where are you setting this new id as the parent id of the new child rows ?
When you duplicate the children where are they getting their IDs from and where are you setting the ID of the parent ?
I could just be being dense....
0
 
LVL 16

Accepted Solution

by:
RobertRFreeman earned 250 total points
ID: 18083026
This is based on having relations and autoincrementing key columns defined in your DataSet.
It uses the relations to find the children and uses the auto-incrementing key column to create new keys.

The key columns are automatically created with the tableadapter wizard in the VS DataSet designer.
You can also use the DataSet designer to drag and drop key columns onto child tables to create the relationships.
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18083255
So the new IDs are generated automatically in the dataset ?
How does it get these newly generateed IDs back into the underlying tables ?
Does it insert them using INSERT_ID or does it just assume that the tables will give the new rows the same IDs it has given them ?
What if there is an insert or update to one of the underlyoing tables between filling the dataset and calling the adapter update ?
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18083260
Sorry, I am not questioning the validity of your answer, it's just that if its correct then I'm really interested to know how it works.
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18083366
The dataset puts a temporary id in the table until an update is called on the adapter.  The adapter then performs the insert on the database and retrieves the auto-generated id back from the database.  It puts that id in the dataset record and the relationships by default will cascade the id changes to the child dataset tables (there is a setting on the relationship that controls this behaviour).

You do not need to wait till the update is performed on the parent table to insert or modify child records, since the temp ID and relations will track that.  However, it is important to do adapter updates on the parent tables first and then on the child tables.  Otherwise, you could end up with a constraint error or an orphaned child in the database.

If deletions are enabled, you should enable cascading deletes in the dataset and the database to automatically delete child records in the database and the dataset.
0
 
LVL 1

Author Comment

by:craigdev
ID: 18083422
Robert, it doesn't quite seem to work like that, in order to get the solution to work in datasets, at each point after I am done with one table I need to call update, fill again to get the new ids (round trip to db) then move onto a child table, repeat, move on to the grandchild table. So you are constantly going back and forward to the DB, you cannot work completely dissconnected.

This setting to cascade changes are you referring to on your dataset designer, r-click a relation choose "Both relation and foreign key constraint" set update rule to cascade, accept rule to cascade. I haven't been using this or the check box "Nested Relation", how would either of these options affect things? and be best implemented. At present I have a working solution using datasets with lots of round trips to the server, for about 150,000 inserts it takes 13minutes which is unaccceptable for the user. I am attempted to adapt ozymandias SQL solution at the moment, I am nearly there up to the greatgrandchild level and it seems like it will be much faster.

Thanks
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18083551
OK. I think I understand RobertRFreeman's approach now.
It's quite elegant if you can get all the plumbing in the underlying dataset to work and I have to admit I did not realise that it was possible to do this
The dataset must be doing an awful lot of work in the background.

However, if the amount of data being duplicated here is really on the scale craigdev suggests (150,000 inserts) then I would still have to recommend pushing the real leg work of this process back to the DB layer.
Loading 150,000 rows into a dataset, duplicating them and then writing them all back to the DB while maintaining and abstracted layer of referential integrity justs sounds scary :(
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18083629
Whenever you update or fill a child table, you should update or fill the associated parent records first.

In order to get that functionality, you must set accept rule = no cascade, update rule = cascade.
Update will apply id updates to the children.  Accept will call AcceptChanges on the child records when the parent record is updated.  This would prevent the children from being updated when you call the adapter for the child table.

The nested property is only for XML output formatting.

If you can do all the processing on the sql server, then you are better off doing it there as you will save the round-trips.  If you are doing 150,000 inserts, then you would be justified doing this on the server.

You can also increase performance by using stored procedures for your adapters rather than sql statements.
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18083666
Also note that you do not need to "fill again" after an update just to get the identity.  
The sqladapter will auto update the ID of any auto-incrementing key field via @@Identity.

You can also optionally set the adapter to refresh the rest of the fields as well, just in case you are doing some  processing on the DB or want the newest values.
0
 
LVL 1

Author Comment

by:craigdev
ID: 18083668
Thanks guys working on this now for an all Sql solution, I'll let you know how I get on over the next few hours. Really appreciate all the help.
0
 
LVL 1

Author Comment

by:craigdev
ID: 18083783
"Also note that you do not need to "fill again" after an update just to get the identity.  
The sqladapter will auto update the ID of any auto-incrementing key field via @@Identity.

You can also optionally set the adapter to refresh the rest of the fields as well, just in case you are doing some  processing on the DB or want the newest values."

How do I do this, do I need to change the default autogenerated update/insert commands to do Select @@Identity as there last line? or how do I access this information in .Net, I don't believ this is auto updating the ID for me. How to optionally set the adapter to refresh the rest of the fields?
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 18083922
There are 2 ways.

The best way is to use the adapter config wizard to generate stored procedures, which will include a return parameter for the primary key.
If you have custom stored procedures, create an output parameter and map it to the dataset key column in the command's tablerelations.

If using sql statements, you have an option of including a select statement after the insert to return any fields you want "refreshed", including the ID field.  You can have the wizard auto-generate this by selecting the "Refresh the data table" checkbox in the "Advanced" section.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using MS Code on my Mac 6 45
Convert string to date 3 40
Expando 4 34
Handle null when using linq in this line 1 20
Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now