RETURNING Clause in INSERT for C# - Oracle to SQL Server Migration

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()); 
      } 
  }

SQL Server OUTPUT Clause in INSERT Statement

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 Services and Tools

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.

You could leave a comment if you were logged in.