Using autonumber int PK together with TableAdapter - Concurrency error.

[Ola Fjelddahl]

hejdig.

Have anybody had any success with implementing TableAdapters together with
tables with an int PK that is set by the database?

The problem is that when the row is inserted into the dataset's datatable,
it is set to a value automagically. When the row then is persisted into the
database's table it is set to another value. This new value is not
communicated to the dataset (at least I havn't found it)

I have come up with threee solutions so far:
1) Exchange the int PK in favour of a GUID PK and create it in the
application.
2) Loop through the datatable when updating and figure out how to handle
each row by itself.
3) Always start with inserting the new row and figure out how to find the
"last row inserted".

BTW: The database is a SQLServerCE3 for a PocketPC2005 but I am interested
also in solutions for SQLServer2k.

TIA

/OF

 

[Jim Rand]

Step 1) In your XSD for each table, set AutoIncrement to true, AutoIncrementSeed to -1, AutoIncrementStep to -1 and cascade the primary keys in the relationships.

Step 2) An example INSERT statement is: INSERT INTO Customer (CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE (CustomerID = @@IDENTITY)

The CustomerID is issued by the database as an autoincrement. TS is a timestamp used for concurrency checking.

The UPDATE statement looks like: UPDATE Customer SET CustomerOldID = @CustomerOldID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND ((CAST(TS AS INT)) = @Original_TS); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE (CustomerID = @CustomerID)

This deals with the concurrency issue and gets the new timestamp back from the database.

Step 3) Assuming your'e using the dataset GetChanges() method, short circuit the adapter with:
private void sqlDACustomer_RowUpdated(object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e)

{

if (e.StatementType == StatementType.Insert) e.Status = UpdateStatus.SkipCurrentRow;

}

This solves newly inserted rows from being duplicate.