Just recently I ran into a problem with what seems to be a deficiency in the Oracle Database. When trying to return an auto-generated key as is done in Microsoft's SQL database, it seems Oracle for whatever reason didn't add this capability, the key couldn't be passed back to the.Net call. Apr 24, 2007 Identity Columns - An identity is a common 'auto generated' primary key to use in a SQL Server database these days. An identity is simply an integer value that 'auto increments' as each new row is added to your table. You can specify when it should start and how it should increment when you add the column to your table. Statement.executeUpdate(sql-statement, Statement.RETURNGENERATEDKEYS); The following forms are valid only if the data source supports SELECT FROM INSERT statements. Sql-statement can be a single-row INSERT statement or a multiple-row INSERT statement. With the first form, you specify the names of the columns for which you want automatically. Return auto generated key in table output fails on SQL Server 2005: 2011/03/29 12:15:06 - Table output.0 - ERROR (version 4.1.0-GA, build 14380 from 2010-11-09 17.25.17 by buildguy): Unable to release database transaction savepoint.
-->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.
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
.
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 . |
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.
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.
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.
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.
Vmware fusion 8.5 for mac. 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.
|