IMG-LOGO

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

andy - 16 Mar, 2014 4617 Views 0 Comment

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.

Related Articles

How to remove html tags from string in c#?

Sometimes you need to remove HTML tags from string to ensure there are no dangerous or malicious scripts especially when you want to store the string or data text into the database.