Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

copy datarow vb.net strongly typed Dataset autogen new primary keys and then add

Posted on 2006-12-01
18
Medium Priority
?
5,644 Views
Last Modified: 2008-10-18
Hi there,

I am using strongly typed datatables. I  have a datatable filled from a dataAdapter. I want to copy some of the rows in the table, add them as new rows to the same the data table, so the same details for all columns except the primary key (which should autiogenerate for insert) and explicity set a foreign key. Then post this back to the database, so under the hood only the newly added rows will get inserted to the DB. I just need an easy way to copy existing rows and do the add as then I should be able to just call Update and RowState.Added rows will get inserted right?

Any help, thanks
0
Comment
Question by:craigdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 6

Expert Comment

by:riyazthad
ID: 18054261
I hope you are using strongly typed dataset and TableAdapter for updating

Then try this

'TempDataSet dataset

        Dim newTable As TempDataSet.tblDataTable = CType( _
        TempDataSet.tbl.GetChanges(Data.DataRowState.Added), TempDataSet.tbDataTable)

        If newTable IsNot Nothing Then
            Me.tblTableAdapter.Update(newTable)
        End If

        Me.TempDataSet.AcceptChanges()
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18057411
We really need to know what version of VB.NET you are using.  

2005 has a method .SetAdded.  Using that, you can import the rows you want to copy into a clone of your existing datatable, make the modifications you want, and then call .SetAdded.  That will mark them as new rows so that the datadapter/tableadapter will use the InsertCommand in passing them into the database.

Pre-2005, that method does not exist.  Which means you have to copy the values you want, cell by cell (or using ItemArray), from existing rows into new rows.  Otherwise "copy and alter" on a row by row basis leaves the .Rowstate flag set as modified, which means that the dataadapter/tableadapter will try to use the UpdateCommand on them.

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18067142
VB.net 2005, riyazthad you don't address the issue, I know how to call update, I need to know how to add the new edited rows for my scenario outlined above.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:craigdev
ID: 18067154
Sancler, SetAdded I didn't know about and sounds good., however how does it handle the primary key, do I just set the new rows primary key to "nothing" and the insert will take care of the rest doing the Update method? because this key will already exist.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18067284
I haven't tried this.  So one of us will have to experiment ;-)

My guess would be that you do not need to do anything.  This is because, where an (automatically generated) Insert statement is going to a database table that has an autogenerated primary key, it does not reference the primary key column.  Ad these rows will be "new", it is the Insert statement that will be used.

If you are not using an automatically generated statement (or you are, but it doesn't work in the way I would expect as outlined above) then I would next try a manually generated (or manually amended) Insert statement that did not reference the primary key column.

Or, as you suggest, the primary key column entries could be set to null (or even the column be totally removed from the copy table).

As I say, some experimentation will be necessary.  Although I can (and will, if necessary) do it I would have to cobble dummy data together to do so.  You have the data and you know all the other details of your setup.  So can I suggest you suck it and see?  If that doesn't sort it, come back describing what you've tried and what the problems were and I'll put something together to test it out myself.

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18068070
This seems to working ok, also is there a way to do a SetAdded for a whole table or do I need to For each row and set the property?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18068098
Only row by row, so far as I know.  As it is a method of a DataRow, rather than of a DataRow collection, I don't see how it could be otherwise.

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18068167
Cool thanks, also my table structure is like this: Parent, Child, GrandChild so there is a nested relationship.

So far from my parent row I have been calling GetStronglyTypedChildTableRows() which gets me all the rows of data related to this parent row in the child table. Then I want to get all related rows in the Grandchild table, at the moment I do For each row in the result of GetStronglyTypedChildTableRows() I call GetStronglyTypedGrandChildTableRows() and then I have all the data.

Is there a way to get this 2 level GrandChild nested data without looping through all the Child rows? I just noticed on the XSD designer that these is a checkbox on a relation if you click edit relation call "Nested Relation" does anyone know what ticking this does? would it enable me to get from a parent row, all Child rows and in turn relations of that childs rows in one fowl swoop?

Thanks heaps
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18068388
I haven't tried it, so I don't know for certain, but I would be very surprised.  Two reasons.  First, the .Nested property is, so far as I know, concerned with XML representation of data, not with its manipulation within apps.  See this

   http://msdn2.microsoft.com/en-us/library/7sfkwf9s(vs.80).aspx

Second, thinking of it in terms of objects and their methods, into what could the result of the operation you want be returned?  What you are getting with each call to get the next level down is an array of datarows.  If you want to do two levels of calls at the same time, the result would have to be an array of datarows for the first (child) level each coupled with  an array of (different types of) datarows for the second (grandchild) level.  I can't think of any object in VB.NET that would accommodate that mixture as one element in the returned array.

So I think your current For ... Each approach is the right one.

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18068638
Thanks man, awesome. One last Q and you get the points. How the heck do I use transactions and DataTableAdapters in conjuction, been reading google and it doesn't seem to play ball, I call 7 tableadapters in succession and need it to be transactional. I do a connection.BeginTransaction but I get his error when I try and call Update:

"ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized."

Which basically means the Select, Update, Insert, Delete commands of my SqlDataAdapter and not linked to the Transaction I deduce from my googling. Any ideas?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18069548
It's not easy.  But here are two references

   http://blah.winsmarts.com/2006/06/18/the-definitive-tableadapters--transactions-blog-post.aspx

   http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

In both, the sample code is in C#.  I doubt that would really hinder understanding of the articles.  But if you want to use either approach and need the C# code translated to VB.NET, try this site

   http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18070423
Just found out you can't call SetAdded on a modified row, get this error:

"SetAdded and SetModified can only be called on DataRows with Unchanged DataRowState."
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18070674
So ...

You import your rows to the copy table

You make your alterations

You call .AcceptChanges on the copy table

That sets all the rowstates to Unchanged

THEN you cycle through the rows to .SetAdded

Roger

0
 
LVL 1

Author Comment

by:craigdev
ID: 18075530
Thanks Roger, you seem to really know your stuff. I am kinda new to using Strongly Typed datasets and am still having some trouble.

Ok this approach works fine when dealing with one datatable, working in memory and then doing an update.

I call SetAdded for the all the parent rows, this keeps the same PK_IDs in the datatable but under the hood on update the insert method gets called, inserts into the DB and creates new unique PK_IDs automatically. This is all working fine and I am happy with that.

However I if I have two related tables in a Parent Child relationship in a dataset. I do this for the first table fine, but the second table for each row I need to set it's FK_ID to the parent tables newly created ID, I don't have this information yet as I haven't called an update. How do I do this, I have no temporary parent ID I can link to?

I could do this by updating parent, filling the dataset again from the DB, getting the new parent IDs and then setting the Child's FK_IDs to these new parents each time but it seems a pretty inefficient way to do this. I am taking the whole wrong approach to this problem?

I have a requirement to copy a Campaign in my appication. The campaign is a single row in the Campaign table, it has multiple related rows in 5 child tables and 1 of these child tables has a further 2 nested tables. I need to create a new campaign, duplicate all information and get it all pointing back to the new campaign via setting direct child tables FK_Campaign to the new campaign record and setting the nested grandchild tables FK_Child to the respective child table UID. I hope you follow what I am saying here.

Really appreciate all your help. I want to avoid having to do addRow and enter all parameters for all columns (ommits the UID) because there are a stack of columns in each table. Hard coding this would be a mission.

Thanks
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18075711
Given the expanded scenario, it sounds to me as though you may want to change your focus from copying rows (which was what the original question was about) to working with the existing dataset.  I assume (a) that the existing dataset contains all the datatables that are relevant to cover the various levels of nested data that you are interested in and (b) you could fill those tables with some filter/s to bring over just those records (at whatever level of nesting) that relate to the Campaign you want to copy.

Remembering that the ADO.NET model is disconnected, this means that you can alter the records in the dataset that you now have in any way that you want (and that the coding facilities make possible) without the database "knowing" that you are in fact fiddling with "existing" records.  As you have seen, applying .SetAdded to existing, unmodified data (or data to which .AcceptChanges has been applied after modifications) cons the tableadapter into thinking that the record is New, so it Inserts it, and a new autonumber primary key is generated at the database end when it does so.  So far, you've done that only at the "parent" level.  But my belief is that you should be able to cascade that down through the nested relationships.  I stress that YOU should be able to cascade it down.  I am not suggesting that you should try to use any of the automatic cascading features.

So, in a bit more detail, I envisage bringing over the parent table including only those rows which relate to the relevant Campaign, the child table/s containing only those rows that relate to the rows in the parent table, the grandchildren table/s containing only those rows that relate to the rows in the child table/s.  Then you apply the technique already discussed to the parent table.  Make any alterations, .AcceptChanges, .SetAdded and .Update.  You then empty that table and refill it (so you can get the new PKs) with a filter that just brings over the newly added rows.  You then use the information from that to do the same process on the child table/s.  And then on the grandchildren table/s.

That's all a bit glib.  You may, for instance, have to (temporarily) disable constraints on the dataset or, when you start altering parent rows you may get objections from child rows (or vice versa).  You may have to store original PKs from parent rows so that, when the new PKs come back, you know what has to be altered to what in the child rows.  And so on.

But, overall, it seems to me a simpler approach than trying to think in terms of copying rows.

In the light of the above, perhaps this is not relevant.  But I don't really share your view "I want to avoid having to do addRow and enter all parameters for all columns (ommits the UID) because there are a stack of columns in each table. Hard coding this would be a mission."  The coding should be similar no matter how many columns there are.  You could either use

      For i As Integer = 0 To myTable.Columns.Count - 1
          newRow(i) = oldRow(i)
      Next

or just

      newRow.ItemArray = oldRow.ItemArray

Roger
0
 
LVL 1

Author Comment

by:craigdev
ID: 18075789
Thanks Sancler, the above approach is how I am doing it, Having to add new row to the parent table, update, fill again to get new parent ID. Add rows to children (change FK with new), add rows, update, fill again to get new ids.

newRow.ItemArray = oldRow.ItemArray

works fine for this approach, provided there does not exist a row with the same primary key for ItemArray(0) I just set ItemArray(0) = nothing and it works fine.

If I need a completely dissconnected solution calling update only at the end I need to use the StronglyTypedTable.AddStronglyTypedRow(param1, param2, ....)  for all tables in the dataset and temp IDs would be created which I could reference . Also if I have a row (has relation to a child table), is there a way to call AddChildRow or equivalent which would auto link this new child row to it's parent via temp pk_id to fk_id in the new child row?


I wanted a solution that didn't invole make changes to parent, update, fill to get new ids, repeat for child tables. Where I could just work disconnected and call update at the end rather than after each table. I suppose I have to do this as I need to get the newids, I thought there might be a solution where I could get temp IDs all the way along, keep working disconnected and then update only at the end.

Thanks
0
 
LVL 1

Author Comment

by:craigdev
ID: 18075804
Also this is really slow, takes about 13 minutes to insert about 120,000 new records. Is there a more efficent way to do this, perhaps with SqlClient.SqlBulkCopy or something else (haven't used this before).

Thanks
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 18076325
1) there seems to be some confusion.  If you are using the approach that I outlined in my previous post, you would never need to use ItemArray (or anything else) to copy a datarow.  The whole point of it was that, right at the start, your dataset would contain exactly the rows (subject to the modifications you wished to make to PKs and FKs - and any other fields that the change of Campaign required) that you wished to add as new rows to the various tables in the database.  No copying of rows of any sort would be required within the app.  You would just alter the existing rows as necessary.  It was a set of rows that related to oldCampaign, in your app you alter it so that it relates to newCampaign.  You then save it to the database with Insert so they are new rows in that.  So it may look like a "copy" of the original set of rows so far as the database is concerned but, so far as your app is concerned, no copying is involved.

2) if you want to use a completely disconnected approach you can.  Just check what number the database will use for the next "new" row in any given table and assign that as the PK for rows as you deal with them, incrementing them each time, and as the FKs for those rows' children.  It may be a bit quicker.  But I would personally use the save and refill method.  The complications if - for any reason - a FK was used for a child record which did not, in the event, turn out to be the PK that you had expected to be allocated by the database's autonumber feature to the parent row could be horrendous.

3) in view of my comment at 1 above, I cannot see how an AddChildRow method would be relevant.  All relevant child rows would already exist, all you need to do is alter the FK to match that of the PK that has been changed in the parent row.  But I do not know of any such method inherent in VB.NET.  If you want one, you could code your own.

4) as to the use of alternatives such as SqlBulkCopy, I'm afraid I cannot help you.  The internals of datahandling are virtually the same WITHIN VB.NET whatever the database's type.  But when it comes to the VB.NET - database interface, such expertise as I have in is OleDb, not SQL.

Roger
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

715 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