Call Stored Procedure with Output Parameter in Business Class using C# and VB.Net in ASP.Net

Hi SUJAYS,

Please refer below sample.

SQL

CREATE PROCEDURE GetCustomerName
      @CustomerId INT,
      @Name VARCHAR(30) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT @Name = Name
      FROM Customers
      WHERE CustomerId = @CustomerId
END

HTML

Enter CustomerId:
<asp:TextBox ID="txtId" runat="server" />
<asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />
<br />
<br />
<asp:Label ID="lblName" runat="server" />

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

BEL.cs

public class BEL
{
    private int _Id;
    public int id
    {
        get
        {
            return _Id;
        }
        set
        {
            _Id = value;
        }
    }
    private string _Name;
    public string Name
    {
        get
        {
            return _Name;
        }
        set
        {
            _Name = value;
        }
    }
}

DAL.cs

public class DAL
{
    public string Data(BEL enities)
    {
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("GetCustomerName", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerId", enities.id);
                cmd.Parameters.Add("@Name", SqlDbType.VarChar, 30);
                cmd.Parameters["@Name"].Direction = ParameterDirection.Output;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                string name = "Name is : <b>" + cmd.Parameters["@Name"].Value.ToString() + "</b>";
                return name;
            }
        }
    }
}

BLL.cs

public class BLL
{
    public string GetData(BEL enities)
    {
        DAL dal = new DAL();
        return dal.Data(enities);
    }
}

CS.aspx.cs

protected void Submit(object sender, EventArgs e)
{
    BEL entity = new BEL();
    entity.id = Convert.ToInt32(txtId.Text);
    BLL bll = new BLL();
    lblName.Text = bll.GetData(entity);
}

VB.Net

BELVB.vb

Public Class BELVB
    Private _Id As Integer

    Public Property id As Integer
        Get
            Return _Id
        End Get
        Set(ByVal value As Integer)
            _Id = value
        End Set
    End Property

    Private _Name As String

    Public Property Name As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
End Class

DALVB.vb

Public Class DALVB
    Public Function Data(ByVal enities As BELVB) As String
        Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constring)
            Using cmd As SqlCommand = New SqlCommand("GetCustomerName", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@CustomerId", enities.id)
                cmd.Parameters.Add("@Name", SqlDbType.VarChar, 30)
                cmd.Parameters("@Name").Direction = ParameterDirection.Output
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
                Dim name As String = "Name is : <b>" & cmd.Parameters("@Name").Value.ToString() & "</b>"
                Return name
            End Using
        End Using
    End Function
End Class

BLLVB.vb

Public Class BLLVB
    Public Function GetData(ByVal enities As BELVB) As String
        Dim dal As DALVB = New DALVB()
        Return dal.Data(enities)
    End Function
End Class

VB.aspx.vb

Protected Sub Submit(ByVal sender As Object, ByVal e As EventArgs)
    Dim entity As BELVB = New BELVB()
    entity.id = Convert.ToInt32(txtId.Text)
    Dim bll As BLLVB = New BLLVB()
    lblName.Text = bll.GetData(entity)
End Sub

Screenshot