Solved

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

Posted on 2006-12-01
18
5,565 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
  • 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
 
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
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 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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

9 Experts available now in Live!

Get 1:1 Help Now