In Oracle, when you execute an INSERT statement, you can use a trigger to assign some values, for example, to generate ID using a sequence.
The RETURNING clause in the INSERT statement allows you to return the assigned values to the application.
Assume there is the following table and trigger in Oracle:
Oracle:
CREATE TABLE teams ( id NUMBER(7) NOT NULL, name VARCHAR2(70) NOT NULL ); // Sequence to generate IDs CREATE SEQUENCE teams_seq START WITH 31; // Trigger to assign ID CREATE OR REPLACE TRIGGER teams_id_tr BEFORE INSERT ON teams FOR EACH ROW BEGIN SELECT teams_seq.nextval INTO :new.id FROM dual; END; /
The following C# example demonstrates how to obtain the ID value assigned in the trigger:
C# for Oracle:
using System; using System.Data; using System.Data.OracleClient; class Program { static void Main(string[] args) { OracleConnection con = new OracleConnection("Data Source=orcl;User Id=scott; Password=tiger;"); // Open a database connection con.Open(); OracleCommand cmd = new OracleCommand(); // INSERT statement with RETURNING clause to get the generated ID cmd.CommandText = "INSERT INTO teams (name) VALUES ('West Ham United') RETURNING id INTO :id"; cmd.Connection = con; cmd.Parameters.Add(new OracleParameter { ParameterName = ":id", OracleType = OracleType.Number, Direction = ParameterDirection.Output }); // Execute INSERT statement cmd.ExecuteNonQuery(); // Output ID Console.WriteLine("ID: {0}", cmd.Parameters[":id"].Value.ToString()); } }
In SQL Server, you can use the OUTPUT clause in a INSERT statement to return the assigned ID.
Assume there is the following table and identity column in SQL Server:
SQL Server:
CREATE TABLE teams ( id INT NOT NULL IDENTITY(31, 1), name VARCHAR(70) NOT NULL );
The following C# example demonstrates how to obtain the assigned identity value using the OUTPUT clause of INSERT statement in SQL Server:
C# for SQL Server:
using System; using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { SqlConnection con = new SqlConnection("Server=localhost;Database=Test;Trusted_Connection=True;"); // Open database connection con.Open(); SqlCommand cmd = new SqlCommand(); // INSERT statement with OUTPUT clause to get the generated ID cmd.CommandText = "INSERT INTO teams (name) OUTPUT inserted.id VALUES ('West Ham United')"; cmd.Connection = con; int newId = (int)cmd.ExecuteScalar(); // Output ID Console.WriteLine("ID: {0}", newId); } }
Note that we used cmd.ExecuteScalar() in SQL Server instead of cmd.ExecuteNonQuery() that was used in Oracle. Also in SQL Server we do not need the output parameter.
SQLines offers services and tools to help you migrate Oracle databases and applications to Microsoft SQL Server. For more information, please contact us at support@sqlines.com.
Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - January 2013.