GetChanges, Merge - Returning autoincrement key

[Jim Rand]

The dataAdapter insert sql:

INSERT INTO Customer (CustomerOldID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerOldID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE (CustomerID = @@IDENTITY)

AutoIncrement: true
AutoIncrementSeed: -1
AutoIncrementStep: -1

This code results in duplicate rows appearing in the dataset after an insert:

DataSet dsChanges = dsNorthwind.GetChanges(); // Gets the row inserted
sqlDACustomer.Update(dsChanges, "Customer"); // Backend gets updated
dsNorthwind.Merge(dsChanges); // Both original inserted row with a CustomerID = -1 still exists and new real row
// with CustomerID = 104 appears resulting in two rows appearing in the grid
dsNorthwind.AcceptChanges();

------------------------------------------------------------------------------------------------

This code works:

sqlDACustomer.Update(dsNorthwind, "Customer"); // Backend gets updated
dsNorthwind.AcceptChanges(); // grid has only 1 row with the correct autoincrement key and timestamp.

--------------------------------------------------------------------------------------------------

dsNorthwind.WriteXml(@"C:\Tmp\fulldataset.xml",XmlWriteMode.DiffGram);
writes out <Customer diffgr:id="Customer93" msdata:rowOrder="91"....

dsChanges.WriteXml(@"C:\Tmp\changes.xml",XmlWriteMode.DiffGram);
writes out <Customer diffgr:id="Customer1" msdata:rowOrder="0"

--------------------------------------------------------------------------------------------------------
Deletes and updates work just fine. I'm only having problems with the inserts.
How do you use the GetChanges() method for inserts and have the autoincrement keys and timestamps make there way back to the dataset to the correct row?
Do I have to build some kind of a hash table and manually update the primary key and timestamp in the dataset?

What is the best approach?

Thanks.

 

[ng]

See below:

The dataAdapter insert sql:

INSERT INTO Customer (CustomerOldID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
VALUES (@CustomerOldID, @CompanyName, @ContactName, @ContactTitle,
@Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT
CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE (CustomerID =
@@IDENTITY)

AutoIncrement: true
AutoIncrementSeed: -1
AutoIncrementStep: -1

This code results in duplicate rows appearing in the dataset after an
insert:

Remove this line:

DataSet dsChanges = dsNorthwind.GetChanges(); // Gets the row inserted

Substitute this line:

sqlDACustomer.Update(dsChanges, "Customer"); // Backend gets updated

with this one:
sqlDACustomer.Update(dsNorthwind, "Customer");

And remove this line:

dsNorthwind.Merge(dsChanges); // Both original inserted row with a
CustomerID = -1 still exists and new real row
// with CustomerID =
104 appears resulting in two rows appearing in the grid

and go right to this one.

 

[Jim Rand]

Using the entire dsNorthwind dataset works which is fine for a 2-tier solution. I need to use the GetChanges() / Merge() approach to send only the updates across the wire for a 3-tier web services implementation. Based on your reply, I suspect that I have to cherrypick the returned autoincrement primary keys and timestamps and apply them to the dsNorthwind dataset in a loop WITHOUT using the Merge().

Is this correct?

By the way, the datasets will be streamed to XML, zipped, and passed as binary attachments using Web Services Extension (WSE). This should bypass bandwidth issues.

Thanks.

See below:

Jim Rand wrote:

The dataAdapter insert sql:

INSERT INTO Customer (CustomerOldID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerOldID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE (CustomerID = @@IDENTITY)

AutoIncrement: true
AutoIncrementSeed: -1
AutoIncrementStep: -1

This code results in duplicate rows appearing in the dataset after an insert:

Remove this line:

DataSet dsChanges = dsNorthwind.GetChanges(); // Gets the row inserted
Substitute this line:

sqlDACustomer.Update(dsChanges, "Customer"); // Backend gets updated
with this one:
sqlDACustomer.Update(dsNorthwind, "Customer");

And remove this line:

dsNorthwind.Merge(dsChanges); // Both original inserted row with a CustomerID = -1 still exists and new real row
// with CustomerID = 104 appears resulting in two rows appearing in the grid
and go right to this one.

dsNorthwind.AcceptChanges();

------------------------------------------------------------------------------------------------

This code works:

sqlDACustomer.Update(dsNorthwind, "Customer"); // Backend gets updated
dsNorthwind.AcceptChanges(); // grid has only 1 row with the correct autoincrement key and timestamp.

--------------------------------------------------------------------------------------------------

dsNorthwind.WriteXml(@"C:\Tmp\fulldataset.xml",XmlWriteMode.DiffGram);
writes out <Customer diffgr:id="Customer93" msdata:rowOrder="91"....

dsChanges.WriteXml(@"C:\Tmp\changes.xml",XmlWriteMode.DiffGram);
writes out <Customer diffgr:id="Customer1" msdata:rowOrder="0"

--------------------------------------------------------------------------------------------------------
Deletes and updates work just fine. I'm only having problems with the inserts.
How do you use the GetChanges() method for inserts and have the autoincrement keys and timestamps make there way back to the dataset to the correct row?
Do I have to build some kind of a hash table and manually update the primary key and timestamp in the dataset?

What is the best approach?

Thanks.

 

[cecil]

Jim,

You need to add this code to your DataAdapters RowUpdated event:
if(e.StatementType == StatementType.Insert)
e.Status = UpdateStatus.SkipCurrentRow;

As per:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q313540

I love the fact that the status is "By design"!!

Cecil Howell MCT, MCAD.NET
www.Ceciltech.com

 

[Jim Rand]

Very interesting!

Before the fix, the diffgram was coming back with a
diffgr:hasChanges="inserted" and the CustomerID of 106.

After adding the event code, the diffgram comes back with a:
diffgr:hasChanges="modified", the CustomerID of 106 ***PLUS*** a
diffgr:before with the original CustomerID of -1.

This is perfect!

Thank you.

Jim Rand