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

Dhirendra
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 12361 red flag
Rating: 5 out of 5  
 2 vote(s)

In this article, we will earn 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.

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.


Page copy protected against web site content infringement by Copyscape

About the Author

Dhirendra
Full Name: Dhirendra Patil
Member Level:
Member Status: Member
Member Since: 3/23/2010 2:39:20 PM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Samarmir on: 6/22/2012 | Points: 25
Thanks a lot for this.
I have been struggling with this.
I was not aware of this (you will realized that we can only access the output paramater & its value after closing the connection object), cleared out everything.

Again thanks.

Login to post response

Comment using Facebook(Author doesn't get notification)