- Return Auto Generated Key In Sql Excel
- Return Auto Generated Key In Sql Download
- Return Auto Generated Key In Sql Server
- Return Auto Generated Key In Sql Tutorial
- Return Auto Generated Key In Sql File
If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set. When you insert rows by executeUpdate or execute an INSERT statement or an INSERT within SELECT statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a. Records inserted. Auto-incremented values of the column ID generated by the current PreparedStatement object: 1 2 3 Retrieving auto-generated values (Statement object) Following JDBC program inserts 3 records into the Sales table (created above) using Statement, retrieves and displays the auto-incremented values generated by it. Mar 31, 2013 Here Mudassar Ahmed Khan has explained with an example and attached sample code, how to return the value of Identity (Auto Increment) Column value after record is inserted in SQL Server database using ADO.Net with C# and VB.Net. Spring JDBC FAQ: How do I retrieve the auto-generated database key for a serial field (also known as an autoincrement field in MySQL) after I perform a SQL INSERT using Spring JDBC? (I'm not phrasing that well, but by this question I mean the value of the primary key for the record I. This example shows how to retrieve auto generated primary key by the database (via an insert statement). Following method of JdbcTemplate takes KeyHolder argument which will contain the generated key on the successful insert execution.
-->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.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 theDataAdapter
tofalse
. This affects everyDataRow
in theDataTable
being updated. For more information and a code example, see AcceptChangesDuringUpdate. - The second method is to write code in the
RowUpdated
event handler of theDataAdapter
to set the Status to SkipCurrentRow. TheDataRow
is updated but the original value of eachDataColumn
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 aStatementType
ofInsert
.
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
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.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. 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:
Return Auto Generated Key In Sql Excel
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
Return Auto Generated Key In Sql Download
6.4 Retrieving AUTO_INCREMENT
Column Values through JDBC
Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported auto increment or identity columns. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the
Statement
interface, or issue the query SELECT LAST_INSERT_ID()
after issuing an INSERT
to a table that had an AUTO_INCREMENT
key. Using the MySQL-specific method call isn't portable, and issuing a SELECT
to get the AUTO_INCREMENT
key's value requires another round-trip to the database, which isn't as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTO_INCREMENT
values. First, we demonstrate the use of the new JDBC 3.0 method getGeneratedKeys()
which is now the preferred method to use if you need to retrieve AUTO_INCREMENT
keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standard SELECT LAST_INSERT_ID()
query. The final example shows how updatable result sets can retrieve the AUTO_INCREMENT
value when using the insertRow()
method. Example 6.8 Connector/J: Retrieving
AUTO_INCREMENT
column values using Statement.getGeneratedKeys()
Example 6.9 Connector/J: Retrieving
AUTO_INCREMENT
column values using SELECT LAST_INSERT_ID()
Return Auto Generated Key In Sql Server
Example 6.10 Connector/J: Retrieving
AUTO_INCREMENT
column values in Updatable ResultSets
Return Auto Generated Key In Sql Tutorial
Return Auto Generated Key In Sql File
Running the preceding example code should produce the following output:
At times, it can be tricky to use the
SELECT LAST_INSERT_ID()
query, as that function's value is scoped to a connection. So, if some other query happens on the same connection, the value is overwritten. On the other hand, the getGeneratedKeys()
method is scoped by the Statement
instance, so it can be used even if other queries happen on the same connection, but not on the same Statement
instance.