What pkey's to use with ADO.NET

Traditionally autoincrement columns are used as pkeys. This causes a small problem with DataSet's as you have to use stored procs to get the generated number back (as well as ensuring you set the seed and increment correctly to avoid duplicates). So, Microsoft best practice recommends the use of GUID columns. But these are massively flawed as well, as described later.

The result of this extensive (2 days) investigation is to stick to autoincrement, and setup the dataset as :

'when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet generates negative numbers for auto-incremented values that do not conflict with the positive auto-increment values generated by the data source.'

However, this will not solve the problem of how to ensure your local dataset has the correct pkeys after insert. That is a problem that can be solved by:

'Change the insert command text to include a select command as well, and make sure the insert command's UpdatedRowSource is set to UpdateRowSource.FirstReturnedRecord.

"INSERT INTO eg_tab(name, addr) VALUES (@name, " +
"@addr); SELECT id, name, addr" +
" FROM eg_tab WHERE (id =@@IDENTITY)";

If you're using SQL Server 7.0 or earlier, use @@IDENTITY instead of SCOPE_IDENTITY(). For more information on the difference between these features, see SQL Server Books OnLine.'

Don't use GUID's because:

.Net best practice is to use GUID's, but there are loads of reasons not to: in particular the fact that they may not actually be globally unique. Read on....

They are not unique!

Older Guid's were based on time and ethernet card address (MAC) which was built into the card hardware. But, cheaper cards seemed to report duplicate MAC's so this was ditched. GUID's are now based on random numbers and because they are so large there is a persumption that they won't duplicate. Which is obvious rubbish from Microsoft. No real system which requires uniqueness can simply presume things will be unique.

Result is that GUID's are not suitable for use anywhere, in my opinion. Others have other reasons for hating GUID's, which are described below.

Don't use GUID's no1

src: http://www.cplus-zone.com/dbzone/Article/10167/1954

GUID's are bad because:

GUID values are up to four times larger than the alternatives (Access' AutoNumber and SQL Server's Identity columns are Long Integer datatypes), so GUIDs require more overhead.
GUID values take longer to search and sort. Consequently, a GUID primary key suffers a performance penalty that cascades throughout the database.
The number of characters in and the awkward composition of the actual GUID values themselves make them hard to remember or work with directly, although neither is really necessary when used as a primary key value.

Don't use GUID's no2

src: http://www.winnetmag.com/Articles/Index.cfm?ArticleID=8434&Action=Comments

Using a GUID as a primary key is a bad idea. When using a GUID you are basically using a really long string. The performance hit you take just for executing a join statements is unacceptable as opposed to using an INT data type.

Don't use GUID's no3

src: http://support.microsoft.com/default.aspx?scid=KB;en-us;q241371

SQL Server 7.0 or later provides a uniqueidentifier data type that stores 16-byte binary values, which operate as Globally Unique Identification Numbers (Uniqueidentifier or GUIDs). We do not recommend use of the SQL Server 7.0 (or later) uniqueidentifier data type columns when you work with OLAP Services. Several problems have been observed in the behavior of the OLAP Manager when dealing with Uniqueidentifier (GUID) data type columns. These problems are: etc.etc.

The autoincrement side of things!

I attach all the comments I found that were useful. But the best is the first, which basically says DON'T USE AUTOINCREMENTING COLUMNS with ADO.NET.

And this from MSDN ADO.NET best practices:

Avoiding Auto-Increment Value Conflicts
Like most data sources, the DataSet enables you to identify columns that automatically increment their value when new rows are added. When using auto-increment columns in a DataSet, with auto-increment columns from a data source, avoid conflicts between the local numbering of rows added to the DataSet and rows added to the data source.

For example, consider a table with an auto-incrementing primary key column of CustomerID. Two new rows of customer information are added to the table and receive auto-incremented CustomerID values of 1 and 2. Then, only the second customer row is passed to the Update method of the DataAdapter, the newly added row receives an auto-incremented CustomerID value of 1 at the data source, which does not match the value 2, in the DataSet. When the DataAdapter fills the second row in the table with the returned value, a constraint violation occurs because the first customer row already has a CustomerID of 1.

To avoid this behavior, it is recommended that, when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet generates negative numbers for auto-incremented values that do not conflict with the positive auto-increment values generated by the data source. Another option is to use columns of type Guid instead of auto-incrementing columns. The algorithm that generates Guid values should never generate the same Guid in the DataSet as is generated by the data source.

If your auto-incremented column is used simply as a unique value, and does not have any meaning, consider using Guids instead of auto-incrementing columns. They are unique and avoid the extra work necessary to work with auto-incremented columns.

I found this: (from David Sceppa)

The CommandBuilder will not generate the logic required to fetch your new auto-increment values. However, it's easy to add
this logic yourself. If the CommandBuilder generates an InsertCommand with a CommandText of:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (@CustomerID, @EmployeeID, @OrderDate)

change the text to:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (@CustomerID, @EmployeeID, @OrderDate);
SELECT SCOPE_IDENTITY() AS OrderID

and make sure that the InsertCommand's UpdatedRowSource is set to UpdateRowSource.FirstReturnedRecord. This property setting tells the Command to look for a row returned by the query and to assign the data from that row to your DataRow object.

If you're using SQL Server 7.0 or earlier, use @@IDENTITY instead of SCOPE_IDENTITY(). For more information on the difference between these features, see SQL Server Books OnLine.

And this from Eric.

You can make this work as follows:

Suppose your dbtable is:

create table t (
id int identity primary key,
value varchar(20) not null
);

You may assign any value you like to a new row's ID column, I found that increment and seed may both be 1. This works even if you have a key contraint defined in your typed DataSet. The work is done in the DataAdapter and the associated IDbCommands.

Here is how you configure your DataAdapter Commands
A) Don't make changes to or specify ID yourself, when talking to the database:
- Configure your Insert command:
INSERT into t (value) values (@value);
SELECT id, value from t where id = @@identity;
/* if your DB doesn't support @@identity, enclose this command in a
transaction (if not already in an implicit one) and do SELECT max(id) newid
from t to obtain the same value @@identity does */
- Configure your Update command:
UPDATE t set value = @value where id = @Original_ID;
SELECT id, value from t where id = @Original_ID;
B) Make sure you update the TableMappings for the commands (including
Input/Output value).
C) Configure your DataAdapter's Insert (and Update) commands to use
Updatedrowsource=FirstReturnedRecord (preferably, but never UpdateRowSource.None). This will ensure any value the database assigns to
fields will be assigned to fields in your DataSet.

This way, the ID value in your DataSet is always in-sync with your database and you can add multiple rows. Eventually, when you still get errors, you may try increment -1, seed -1 as well, but with MSDE, 1 resp 1 works fine (even on already-populated data with minimum ID > 1 i.e. first inserted record ever was deleted in the past).

AND this:

"The OrderID column in the Northwind's Orders table is auto-incremented and to avoid update errors when inserting new rows in the table we must set the AutoIncrementStep AutoIncrementSeed properties to -1 and the AutoIncrement property to true in Orders DataTable"