How to retrieve @@identity auto incremented value using OUTPUT paramter through stored procedure and C#

Introduction

In this article, we will learn how to get the value from output parameter in C#  which is returned by stored procedure; also we will see how to get the newly inserted record’s Incremented PK value using output parameter. In this article we used @@Identity sql server variable which tracks the incremented value on the table.

The following example uses dbo.InsertEmployee stored procedure by passing require input parameters and a output paramter. This output parameter will returned the increamented PK to the caller.

This is one of the very good technique to insert the record in a table and retrieve the newly inserted record Pk Value using output parameter.

Following is the Employee table schema. Please note that Emp_Pk column is auto incremented so we don’t need to insert the value explicitly in that column.

Follwing is the stoerd procedure code.

Create PROCEDURE dbo.InsertEmployee
@FName varchar(50)
,@LName varchar(50)
,@Address varchar(50)
,@Emp_Pk INT output
AS
BEGIN
            INSERT INTO [dbo].[Employee]
                     ([FName]
                     ,[LName]
                     ,[Address])
             VALUES
                     (@FName
                     ,@LName
                     ,@Address)
      select @Emp_Pk =@@identity
END

Following is a employee class which has similar properties defined which we can used to insert the records.

public class Employee

    {

        public int EmpId { get; set; }

        public string Address { get; set; }

        public string FName { get; set; }

        public string LName { get; set; }

    }

Following is the code where we create the connection object and command object. Also we add the input paramters to the command object and finally we add the output paramter into the command object.

        private static void InsertEmployee()

        {

            Employee empObj = new Employee() { FName = "Dhirendra", LName = "Patil", Address = "Pune, India" };

            IDbConnection conn = new SqlConnection("ConnectionString");

            IDbCommand cmd = conn.CreateCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "dbo.InsertEmployee";

            IDbDataParameter param = cmd.CreateParameter();

            param.Direction = ParameterDirection.Input;

            param.ParameterName = "@FName";

            param.DbType = DbType.String;

            param.Value = empObj.FName;

            cmd.Parameters.Add(param);

            param = cmd.CreateParameter();

            param.Direction = ParameterDirection.Input;

            param.ParameterName = "@LName";

            param.DbType = DbType.String;

            param.Value = empObj.LName;

            cmd.Parameters.Add(param);

            param = cmd.CreateParameter();

            param.Direction = ParameterDirection.Input;

            param.ParameterName = "@Address";

            param.DbType = DbType.String;

            param.Value = empObj.EmpId;

            cmd.Parameters.Add(param);

            param = cmd.CreateParameter();

            param.Direction = ParameterDirection.Output;

            param.ParameterName = "@Emp_Pk";

            param.DbType = DbType.Int32;

            cmd.Parameters.Add(param);

            conn.Open();

            int i = cmd.ExecuteNonQuery();

            conn.Close();

            param = (IDbDataParameter)cmd.Parameters["@Emp_Pk"];

            Console.WriteLine(param.Value.ToString());

        }

If you see the above code you will realized that we can only access the output paramater & its value after closing the connection object.

Conclusion

Hope this article helps!  Thanks for reading and let me know your feedback, comments.