Return Auto Generated Key In Sql Server
- Return Auto Generated Key In Sql Server Download
- Return Auto Generated Key In Sql Server Free
- Return Auto Generated Key In Sql Server Table
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.
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. Learn how to define an auto increment primary key in SQL Server. This data tutorial will explain basic table creation and information around using identity a. A safer answer may be just to have the application run another query once it returns from this one. As long as it's done on the back end, the performance penalty should be worth the simplicity of managing the development in groups of people, and it's closer in accordance to standards than some crazy feature with edge cases.
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
AcceptChangesDuringUpdateproperty of theDataAdaptertofalse. This affects everyDataRowin theDataTablebeing updated. For more information and a code example, see AcceptChangesDuringUpdate.The second method is to write code in the
RowUpdatedevent handler of theDataAdapterto set the Status to SkipCurrentRow. TheDataRowis updated but the original value of eachDataColumnis 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 aStatementTypeofInsert.
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/fifa-15-origin-key-generator-online.html. 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
Ms office 2016 online key generator. 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
Return Auto Generated Key In Sql Server Download
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:
Return Auto Generated Key In Sql Server Free
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.