Updating an existing AUTOINCREMENT column value also resets the AUTOINCREMENT sequence. You can retrieve the most recent automatically generated AUTOINCREMENT value with the LASTINSERTID SQL function or the mysqlinsertid C API function. These functions are connection-specific, so their return values are not affected by another. Knowing the primary key value allows you to locate the row that contains it. Relational database engines, such as SQL Server, Oracle, and Microsoft Access/Jet support the creation of automatically incrementing columns that can be designated as primary keys. These values are generated by the server as rows are added to a table. Turns out these don't get generated unless, in your mapping, you had specified a primary key. Similarly, a SubmitChanges on a LINQ to SQL object will not actually be saved if the mapping has no primary key specified (this makes sense, since it needs to know what object it is updating and what criteria is used to load it from the DB). Aug 27, 2007 Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the.NET Framework 3.5 release, and which enables you to model relational databases using.NET classes. How to get primary key value (auto-generated keys) from inserted queries using JDBC? Description: When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically.

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

Which returns:

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers. For example:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

Updating an existing AUTO_INCREMENT column value also resets the AUTO_INCREMENT sequence.

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255. See Section 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:

For information about AUTO_INCREMENT usage specific to InnoDB, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

• For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

  Which returns:

  In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

• If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

More information about AUTO_INCREMENT is available here:

• How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.20, “CREATE TABLE Statement”, and Section 13.1.9, “ALTER TABLE Statement”.

• How AUTO_INCREMENT behaves depending on the NO_AUTO_VALUE_ON_ZERO SQL mode: Section 5.1.11, “Server SQL Modes”.

• How to use the LAST_INSERT_ID() function to find the row that contains the most recent AUTO_INCREMENT value: Section 12.15, “Information Functions”.

• Setting the AUTO_INCREMENT value to be used: Section 5.1.8, “Server System Variables”.

• AUTO_INCREMENT and replication: Section 17.5.1.1, “Replication and AUTO_INCREMENT”.

• Server-system variables related to AUTO_INCREMENT (auto_increment_increment and auto_increment_offset) that can be used for replication: Section 5.1.8, “Server System Variables”.

-->

A primary key in a relational database is a column or combination of columns that always contain unique values. Knowing the primary key value allows you to locate the row that contains it. Relational database engines, such as SQL Server, Oracle, and Microsoft Access/Jet support the creation of automatically incrementing columns that can be designated as primary keys. These values are generated by the server as rows are added to a table. In SQL Server, you set the identity property of a column, in Oracle you create a Sequence, and in Microsoft Access you create an AutoNumber column.

A DataColumn can also be used to generate automatically incrementing values by setting the AutoIncrement property to true. However, you might end up with duplicate values in separate instances of a DataTable, if multiple client applications are independently generating automatically incrementing values. Having the server generate automatically incrementing values eliminates potential conflicts by allowing each user to retrieve the generated value for each inserted row.

During a call to the Update method of a DataAdapter, the database can send data back to your ADO.NET application as output parameters or as the first returned record of the result set of a SELECT statement executed in the same batch as the INSERT statement. ADO.NET can retrieve these values and update the corresponding columns in the DataRow being updated.

Linq To Sql Auto Generated Primary Key Access

Some database engines, such as the Microsoft Access Jet database engine, do not support output parameters and cannot process multiple statements in a single batch. When working with the Jet database engine, you can retrieve the new AutoNumber value generated for an inserted row by executing a separate SELECT command in an event handler for the RowUpdated event of the DataAdapter.

Note

An alternative to using an auto incrementing value is to use the NewGuid method of a Guid object to generate a GUID, or globally unique identifier, on the client computer that can be copied to the server as each new row is inserted. The NewGuid method generates a 16-byte binary value that is created using an algorithm that provides a high probability that no value will be duplicated. In a SQL Server database, a GUID is stored in a uniqueidentifier column which SQL Server can automatically generate using the Transact-SQL NEWID() function. Using a GUID as a primary key can adversely affect performance. SQL Server provides support for the NEWSEQUENTIALID() function, which generates a sequential GUID that is not guaranteed to be globally unique but that can be indexed more efficiently.

Retrieving SQL Server Identity Column Values

When working with Microsoft SQL Server, you can create a stored procedure with an output parameter to return the identity value for an inserted row. The following table describes the three Transact-SQL functions in SQL Server that can be used to retrieve identity column values.

Function	Description
SCOPE_IDENTITY	Returns the last identity value within the current execution scope. SCOPE_IDENTITY is recommended for most scenarios.
@@IDENTITY	Contains the last identity value generated in any table in the current session. @@IDENTITY can be affected by triggers and may not return the identity value that you expect.
IDENT_CURRENT	Returns the last identity value generated for a specific table in any session and any scope.

The following stored procedure demonstrates how to insert a row into the Categories table and use an output parameter to return the new identity value generated by the Transact-SQL SCOPE_IDENTITY() function.

The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. The CommandType property of the InsertCommand must be set to StoredProcedure. The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. When the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

If your insert command executes a batch that includes both an INSERT statement and a SELECT statement that returns the new identity value, then you can retrieve the new value by setting the UpdatedRowSource property of the insert command to UpdateRowSource.FirstReturnedRecord.

Merging New Identity Values

A common scenario is to call the GetChanges method of a DataTable to create a copy that contains only changed rows, and to use the new copy when calling the Update method of a DataAdapter. This is especially useful when you need to marshal the changed rows to a separate component that performs the update. Following the update, the copy can contain new identity values that must then be merged back into the original DataTable. The new identity values are likely to be different from the original values in the DataTable. To accomplish the merge, the original values of the AutoIncrement columns in the copy must be preserved, in order to be able to locate and update existing rows in the original DataTable, rather than appending new rows containing the new identity values. However, by default those original values are lost after a call to the Update method of a DataAdapter, because AcceptChanges is implicitly called for each updated DataRow.

There are two ways to preserve the original values of a DataColumn in a DataRow during a DataAdapter update:

• The first method of preserving the original values is to set the AcceptChangesDuringUpdate property of the DataAdapter to false. This affects every DataRow in the DataTable being updated. For more information and a code example, see AcceptChangesDuringUpdate.

• The second method is to write code in the RowUpdated event handler of the DataAdapter to set the Status to SkipCurrentRow. The DataRow is updated but the original value of each DataColumn is preserved. This method enables you to preserve the original values for some rows and not for others. For example, your code can preserve the original values for added rows and not for edited or deleted rows by first checking the StatementType and then setting Status to SkipCurrentRow only for rows with a StatementType of Insert.

When either of these methods is used to preserve original values in a DataRow during a DataAdapter update, ADO.NET performs a series of actions to set the current values of the DataRow to new values returned by output parameters or by the first returned row of a result set, while still preserving the original value in each DataColumn. First, the AcceptChanges method of the DataRow is called to preserve the current values as original values, and then the new values are assigned. Following these actions, DataRows that had their RowState property set to Added will have their RowState property set to Modified, which may be unexpected.

How the command results are applied to each DataRow being updated is determined by the UpdatedRowSource property of each DbCommand. This property is set to a value from the UpdateRowSource enumeration.

The following table describes how the UpdateRowSource enumeration values affect the RowState property of updated rows.

Member name	Description
Both	AcceptChanges is called and both output parameter values and/or the values in the first row of any returned result set are placed in the DataRow being updated. If there are no values to apply, the RowState will be Unchanged.
FirstReturnedRecord	If a row was returned, AcceptChanges is called and the row is mapped to the changed row in the DataTable, setting the RowState to Modified. If no row is returned, then AcceptChanges is not called and the RowState remains Added.
None	Any returned parameters or rows are ignored. There is no call to AcceptChanges and the RowState remains Added.
OutputParameters	AcceptChanges is called and any output parameters are mapped to the changed row in the DataTable, setting the RowState to Modified. If there are no output parameters, the RowState will be Unchanged.

Example

This example demonstrates extracting changed rows from a DataTable and using a SqlDataAdapter to update the data source and retrieve a new identity column value. The InsertCommand executes two Transact-SQL statements; the first one is the INSERT statement, and the second one is a SELECT statement that uses the SCOPE_IDENTITY function to retrieve the identity value.

The UpdatedRowSource property of the insert command is set to UpdateRowSource.FirstReturnedRow and the MissingSchemaAction property of the DataAdapter is set to MissingSchemaAction.AddWithKey. The DataTable is filled and the code adds a new row to the DataTable. The changed rows are then extracted into a new DataTable, which is passed to the DataAdapter, which then updates the server.

The OnRowUpdated event handler checks the StatementType of the SqlRowUpdatedEventArgs to determine if the row is an insert. If it is, then the Status property is set to SkipCurrentRow. The row is updated, but the original values in the row are preserved. In the main body of the procedure, the Merge method is called to merge the new identity value into the original DataTable, and finally AcceptChanges is called.

Retrieving Microsoft Access Autonumber Values

Sql Server Linq

This section includes a sample that shows how to retrieve Autonumber values from a Jet 4.0 database. The Jet database engine does not support the execution of multiple statements in a batch or the use of output parameters, so it is not possible to use either of these techniques to return the new Autonumber value assigned to an inserted row. However, you can add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

Linq To Sql Tutorial

Example

Instead of adding schema information using MissingSchemaAction.AddWithKey, this example configures a DataTable with the correct schema prior to calling the OleDbDataAdapter to fill the DataTable. In this case, the CategoryID column is configured to decrement the value assigned each inserted row starting from zero, by setting AutoIncrement to true, AutoIncrementSeed to 0, and AutoIncrementStep to -1. Obdwiz professional add on key_generator. The code then adds two new rows and uses GetChanges to add the changed rows to a new DataTable that is passed to the Update method.

The RowUpdated event handler uses the same open OleDbConnection as the Update statement of the OleDbDataAdapter. It checks the StatementType of the OleDbRowUpdatedEventArgs for inserted rows. For each inserted row a new OleDbCommand is created to execute the SELECT @@IDENTITY statement on the connection, returning the new Autonumber value, which is placed in the CategoryID column of the DataRow. The Status property is then set to UpdateStatus.SkipCurrentRow to suppress the hidden call to AcceptChanges. In the main body of the procedure, the Merge method is called to merge the two DataTable objects, and finally AcceptChanges is called.

Retrieving Identity Values

We often set the column as identity when the values in the column must be unique. And sometimes we need the identity value of new data. This sample demonstrates how to retrieve identity values:

• Creates a stored procedure to insert data and return an identity value.

• Executes a command to insert the new data and display the result.

• Uses SqlDataAdapter to insert new data and display the result.

Before you compile and run the sample, you must create the sample database, using the following script:

The code listing follows:

Tip

The code listing refers to an Access database file called MySchool.mdb. You can download MySchool.mdb (as part of the full C# or Visual Basic sample project) from code.msdn.microsoft.com.

See also