Return data through Output Parameter in stored procedure in asp.net.

Introduction: 

 In this article I have explained with example How to use Output parameter in Stored Procedure to return value.
Description: 

Sometime we need to use Output parameter to return value. This example demonstrates the use of output parameter in the stored procedure with an example of user registration. User has to enter username, email id and password for registration. Username and Email id will be unique in this concept.

We will check duplicate username while entering data in this application. We return Status as output parameter which will show if data is already existed or not.

Implementation: Let's create a demo website to understand the concept.

Create a table as shown below and name it “Login_Table”

Note: Set Is_Identity=True to make the UserId  the primary key in the table.

Now create a Stored Procedure as:

 CREATE PROCEDURE User_Registration

(

@Username varchar(50),

@Password varchar(50),

@Status varchar(200) OUTPUT

)

AS

BEGIN

                                IF NOT EXISTS(SELECT * FROM  USER_REGISTRATION WHERE USERNAME=@Username)

                                                BEGIN

                                                                INSERT INTO Login_Table(USERNAME,PASSWORD)VALUES(@Username,@Password)

                                                                SET @Status = 'Username ' + @Username+ ' Registerd Successfully'

                                                END

                                ELSE

                                BEGIN

                                                SET @Status ='Email Id '+ @EmailId + ' Already Exits.Please select another.'

                                END

                END

Now create a conncetionstring in webconfig file:

(Please use your Datasource name and database name)

<connectionStrings>

                <add name="con" connectionString="Data Source=localhost;Initial   Catalog=test;Integrated Security=True"/>

</connectionStrings>

C#.Net Code

Now in the design page (.aspx) place two  text boxes for Username and password and also place a Button control as:

<table>

            <tr>

                <td>

                    Username</td>

                <td>

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

                </td>

            </tr>

            <tr>

                <td>

                    Password</td>

                <td>

                    <asp:TextBox ID="txtPwd" TextMode="Password" runat="server"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    &nbsp;</td>

                <td>

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

                    <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>

                </td>

            </tr>

        </table>

Asp.Net Code(c#):

First of all include these namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

protected void btnSubmit_Click(object sender, EventArgs e)

    {

        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString());

        try

        {

            if (con.State == ConnectionState.Closed)

            {

                con.Open();

            }

            SqlCommand cmd = new SqlCommand("User_Registration ", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim());

            cmd.Parameters.AddWithValue("@Password", txtPwd.Text.Trim());

            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 500);

            cmd.Parameters["@Status"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            txtUserName.Text = string.Empty;

            txtEmailId.Text = string.Empty;

            txtPwd.Text = string.Empty;

            lblStatus.Text =Convert.ToString(cmd.Parameters["@Status"].Value);          

        }

        catch (Exception ex)

        {

            lblStatus.Text = "ErrorOccured: " + ex.Message.ToString();

        }

        finally

        {

            con.Close();

        }      

      }

VB.Net Code

Import all these namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

    Dim con As NewSqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString.ToString())

        Try

            If con.State = ConnectionState.Closed Then

                con.Open()

            End If

            Dim cmd As New SqlCommand("User_Registration ", con)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim())

            cmd.Parameters.AddWithValue("@Password", txtPwd.Text.Trim())

            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 500)

            cmd.Parameters("@Status").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            txtUserName.Text = String.Empty

            txtEmailId.Text = String.Empty

            txtPwd.Text = String.Empty

            lblStatus.Text = Convert.ToString(cmd.Parameters("@Status").Value)

        Catch ex As Exception

            lblStatus.Text = "Error Occured: " & ex.Message.ToString()

        Finally

            con.Close()

        End Try

    End Sub