Get next autoincrement value for datatable column?

Question

I have a datatable with an AutoIncrement column. How can I get the next increment (-1,-2,-3,...) for the column without adding a new row?

Answer 1

since the auto increment value is always the largest value in that column, you can just get it using max(column name) +1.

Answer 2

You can use the MAX function in the Select method against DataTables.

Something like (syntax might not be completely correct):

DataRow row = dt.Select("MAX(id)")[0];
int nextId = row["id"] + 1;

MSDN has a page with all the expressions you can use. DataColumn..::.Expression Property

Answer 3

I have a datatable with an AutoIncrement column.

Do you have a datatable with an AI column, or a SQL database with an AI column? If it's the SQL db that has the column, you cannot get the next value because your DataTable is disconnected. Otherwise you have to wait until you do an insert and use @@IDENTITY or a similar function.

Answer 4

If it fits in to your design, you can always create the new row (datatable.newrow) and it will already have assigned the nextId. It's now wasted but the row isn't committed to the table until you AcceptChanges.

Answer 5

If you're using SQL Server, you could do:

SELECT cast(last_value as integer)+cast(increment_value as integer) 
FROM sys.identity_columns 
WHERE object_id=(SELECT id FROM sys.sysobjects WHERE name='PACLogErros')
AND name='ERRCod'

Answer 6

The best way is to use the @@IDENTITY as Andrew mentioned because we cannot infer the algorithm in which SQL calculates the next identity and if you think about multiple people updating the database the identity value which you calculated will not be the same when you try to save the row to the database