C#.NET - How to use OUTPUT clause in Stored Procedures to retrieve new identity values from SQL Server

SQL Server 2005 introduced the OUTPUT clause in DML statements.
We can use this in an INSERT ... SELECT statement to capture all the inserted identity values.

1. Create an Employee table

 CREATE TABLE [dbo].[tbl_employee]
  (
     [serial]      [INT] IDENTITY(1, 1) NOT NULL,
     [empname]     [VARCHAR](50) NULL,
     [empsal]      [VARCHAR](50) NULL,
     [emplocation] [VARCHAR](50) NULL
  )     

2. Create a stored procedure to get the identity value

 CREATE PROCEDURE [dbo].[Temp]
 AS
  BEGIN
      -- Declare table variable to hold results
      DECLARE @Temp TABLE
        (
           serial INT
        )

      -- Insert into table with IDENTITY column      
      INSERT INTO [Temp].[dbo].[tbl_employee]
                  ([empname],
                   [empsal],
                   [emplocation])
      -- Use OUTPUT clause to retrieve new value 
      output      inserted.serial
      INTO @Temp(serial)
      VALUES      ('emp1',
                   '15000',
                   'Hyderabad')

      --Select the identity value
      SELECT serial
      FROM   @Temp
  END     

3. Open web.config file and set connection string as follows

<connectionStrings>
   <add name="SiteSqlServer" connectionString="Data Source=ADMIN-PC;Initial Catalog=Temp;User ID=sa;Password=sql@121"
      providerName="System.Data.SqlClient" />
</connectionStrings>

4. Open code behind file and add following namespaces

using System.Data.SqlClient;
using System.Collections.Generic;

5. Write the following method to retrieve data from Stored Procedure.

public static object UsingTheOutputClauseExecuteCommandAndRetriveNewIdentity(string storedProcedureName, List<SqlParameter> sqlParameters)
    {
        string outputParameterValue = string.Empty;
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (sqlParameters.Count > 0)
        command.Parameters.AddRange(sqlParameters.ToArray());        
        using (connection)
        {
            connection.Open();
            object num = command.ExecuteScalar();           
            connection.Close();
        }
        return outputParameterValue;
    }
    

6. Finally use the following code to retrieve new identity values from SQL Server.

List<SqlParameter> sqlparams = new List<SqlParameter>();
object str = UsingTheOutputClauseExecuteCommandAndRetriveNewIdentity("Temp",sqlparams);