Published on: 16 March, 2014

How to output parameter with stored procedure in ASP.Net C#?

Tumblr Digg Google Plus

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

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.