Link to home
Start Free TrialLog in
Avatar of craigdev
craigdev

asked on

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

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
Avatar of riyazthad
riyazthad

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()
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
Avatar of craigdev

ASKER

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.
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.
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
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?
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
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
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
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?
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
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."
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

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
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
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
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
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial