Duplicating DataRows with Constraints. Possible? 1

[CrashDome]

First, you are probably wondering why I want to 'duplicate' data in the first place
Well, it's actually necessary:

Currently, I am trying to find a nice efficient method to duplicate an order on our customer service order entry screen. The order entry program is connected to an MS SQL database and is written in C#.

Right now, our CSRs must re-enter by hand a new order that is identical to a previous order (Imagine a customer saying "Put in an order for the same product and quantity as last month"). This order table is linked to several child tables for both order and production purposes. Initially, I thought I needed to load in each row of every table that is linked to that order, create a new row for every existing row, and copy the data (minus the primary key) and retrieve the new primary key to use in the child tables. Very exhausting task.
Sounded like alot of work to me so I set out trying to figure out how to simply trick the program into changing the rowstate of every row to 'Added'. I could then simply change the order number , update the database (letting SQL assign new PKs), and voila! a duplicate set of rows in the database all constrained to each other yet seperate from the original order.
Unfortunately, I cannot do the above because no matter what I try, I cannot simply get the same rows only as 'Added'. What I do get is either A) Constraint exceptions from trying to change each individually or B) duplicate rows completely devoid of any relationship what-so-ever.

I hope I adequately explained my dilemma. I would post code, but I have yet to find any route of action to take other than the long hard path of rebuilding every row from the ground up (which I know would work).
"Why don't you just use that if you know it works?" you ask? Well, I figured this way I can apply the same methods to other applications and it would allow database changes without me having to re-compile this "copy order" form every time there is a new table added.

If anyone has ANY ideas. Please point me in a direction!

Thank You

 

[chiph]

If the data from your screen passes through an order object, why not write a .clone method?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[CrashDome]

I see the direction you are going. Unfortunately, alot of rows are handled directly through datagrids - not an object. I think the route I must take is the most difficult one.

Thanks for your response, though!

 

[CrashDome]

Ah HA!

It was right under my nose. Thank you for mentioning the Clone method. After looking at the Clone Method for a dataset, I devised an idea that so far is working on my test database. I created my own method called "CloneOrder" within the form object.

Within this method I do the following:
First, I clone the dataset of the order to give me a new one to work from. Then, I create a string array of the tables I need to "clone" data from. It was important that I keep them in the proper parent-child order.

			DataSet newDS = origDS.Clone();

			string[] jobTables = new string[10] {"jobColor",
														"jobSpec",
														"jobFoldSpec",
														"jobRun",
														"jobLot",
														"jobArt",
														"jobProcess",
														"jobProcessDetail",	
														"jobShipment",
														"jobShipItem"};

Second, I call another method to actually perform the duplication of the rows to the new dataset. (I didn't actually need to call another method in this example, but in my program I did)

			foreach (string table in jobTables)
			{
				CreateNewRows(table,origDS,newDS);
			}

This method uses the ItemArray property to add the new row to the second dataset.

		private void CreateNewRows(string table, DataSet origDS, DataSet newDS)
		{
			DataRowCollection origRows = origDS.Tables[table].Rows;
			foreach (DataRow origRow in origRows)
			{
				DataRow newRow = newDS.Tables[table].Rows.Add(origRow.ItemArray);
			}
		}

Once the program has returned from this method, I simply change the appropriate values in the new dataset to give it a new order number and such.

Then I call the Update() method on my DataAdapter referencing the second dataset and behold! a new order which is identical to the first order (except for the primary keys and order number).
Dispose the cloned dataset and everything is back to normal for the original order.

One thing to be cautious about is that the new rows in the second dataset have the exact same primary keys as the original rows but the Insert command will recognize that these rows are 'Added" and therefore need new PK values.
This requires that the dataset that is cloned to contain the same foreign key constraints as the database (and they must cascade any updates also).
The reason I needed to use a clone of the dataset is obviously due to constraints preventing a row that has identical PKs to be added. The reason for using the ItemArray property is because trying to add the row using Add(DataRow) method will cause an exception saying that the row already belongs to a table.

Thanks Chip, for the inspiration!