The Final Guide

The Microsoft SQL Server Database provides an Identity value which is generated automatically when a record is added in such tables which contains IDENTITY. We can fetch the auto incremented value from MS-SQL Server database to our applications using built in function @@IDENTITY( ) (used in MS-Access) or SCOPE_IDENTITY( ) which is the recommended method for obtaining identity value on SQL Server. Scope_Identity( ) retrieves the last identity value created in the current scope which is a single module that can be a stored procedure, batch, function or trigger. We can implement the coding to retrieve the Scope_Identity in many ways.
1. Using batch command with two sql queries seperated with a semicolor, with ExecuteScalar( ) against the batch command to return single value.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES(@PRODUCT_NAME); SELECT SCOPE_IDENTITY()";
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open();
      using(SqlCommand Cmd = new SqlCommand())
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.Text;
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           intID = (int)Cmd.ExecuteScalar( );
      }
      dbCon.Close( );
}

2. We can use output parameter from a stored procedure and ExecuteNonQuery( ).
Stored Procedure declaration

CREATE PROCEDURE dbo.SP_PRODUCT_ADD
(@PRODUCT_NAME VARCHAR(250), @PRODUCT_ID INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES (@PRODUCT_NAME);
    SET @PRODUCT_ID = SCOPE_IDENTITY( );
END

Our Application Coding…

[C#]
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open( );
      using(SqlCommand Cmd = new SqlCommand( ))
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.StoredProcedure;
           Cmd.CommandText = "SP_PRODUCT_ADD";
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           SqlParameter parm1 = new SqlParameter( );
           param1.ParameterName = "@PRODUCT_ID";
           param1.SqlDbType = SqlDbType.BigInt;
           param1.Direction = ParameterDirection.Output;
           Cmd.Parameters.Add(param1);
           Cmd.ExecuteNonQuery( );
           intID = Convert.ToInt32(param1.Value);
      }
      dbCon.Close( );
}