In this tutorial, I will show you how to output parameter with SCOPE_IDENTITY using stored procedure in C#. The following example will outline on how to create a new user and return the User ID as output parameter.
private int NewUser(string firstName, string lastName, string email){ string connectionString = "This will be your connection string"; int userID = 0; using (SqlConnection objConnection = new SqlConnection(connectionString)) { SqlCommand objCommand = new SqlCommand("NewUser", objConnection); objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add(new SqlParameter("@FirstName", firstName)); objCommand.Parameters.Add(new SqlParameter("@LastName", lastName)); objCommand.Parameters.Add(new SqlParameter("@Email", email)); SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; objCommand.Parameters.Add(returnValue); objConnection.Open(); objCommand.ExecuteNonQuery(); userID = int.Parse(returnValue.Value.ToString()); objConnection.Close(); objConnection.Dispose(); objCommand.Dispose(); } return userID; }
This is the sample of SQL stored procedure. Note, you will need to modify it according to your needs.
CREATE PROCEDURE [dbo].[NewUser] @FirstName NVARCHAR(30), @LastName NVARCHAR(30), @Email NVARCHAR(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO Users( FirstName , LastName , Email )VALUES( @FirstName , @LastName , @Email ) RETURN SCOPE_IDENTITY() END