How to return Output parameter from Stored Procedure in ASP.Net using C#

How to return Output parameter from Stored Procedure in ASP.Net using C# :

In this article I will explain how to use and return value from Stored Procedure using Output Parameter in ASP.Net.
 For this article I a Table named Fruits is used which contains FruitId and FruitName columns. The name of the Fruit is fetched using Output Parameter in SQL Server Stored Procedure in ASP.Net

CREATE PROCEDURE [dbo].[GetFruitName]

      @FruitId INT,

      @FruitName VARCHAR(30) OUTPUT

AS

BEGIN

      SET NOCOUNT ON;

     

      SELECT @FruitName = FruitName

      FROM Fruits

      WHERE FruitId = @FruitId

END

ASPX Code:

Enter FruitId:

<asp:TextBox ID="txtFruitId" runat="server" />

<asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />

<br />

<br />

<asp:Label ID="lblFruitName" runat="server" />

C#:

 
protected void Submit(object sender, EventArgs e)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetFruitName", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
            cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
            cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            lblFruitName.Text = "Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString();
        }
    }
}