How to return data through Output Parameter in stored procedure in asp.net?

How to return data through Output Parameter in stored procedure in asp.net?

Introduction:  In this article I have explained how to use Output parameter in Stored Procedure to return status.  This example demonstrate user registration. User has to enter username, email id and password. Username and Email id will be unique in this concept.

So whenever a same username or Email id is entered it prompts user to enter another username(if already exists in the database) or  password(if already exists in the database).  Checking username and email id for duplicity and providing status accordingly is handled through Stored Procedure.

• Create a table as shown below and name it “USER_REGISTRATION”

• Now create a Stored Procedure as:

 CREATE PROCEDURE USER_REGISTRATION_SP

(

@Username varchar(50),

@EmailId varchar(100),

@Password varchar(50),

@Status varchar(200) OUTPUT

)

AS

BEGIN

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

                BEGIN

                                IF NOT EXISTS(SELECT * FROM  USER_REGISTRATION WHEREEMAIL_ID=@EmailId)

                                                BEGIN

                                                                INSERT INTOUSER_REGISTRATION(USERNAME,EMAIL_ID,PASSWORD)VALUES(@Username,@EmailId,@Password)

                                                                SET @Status = 'Username ' + @Username+ ' with Email id ' +@EmailId +  ' Registerd Successfully'

                                                END

                                ELSE

                                BEGIN

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

                                END

                END

ELSE

                BEGIN

                                SET @Status ='Username ' + @Username+' Already Exits. Please select another.'

                END

END 

• Now add conectionstring  in web.config file as:

<connectionStrings>

                <add name="MyDbCon" connectionString="Data Source=LALIT;Initial   Catalog=MyDataBase;Integrated Security=True"/>

</connectionStrings>

Note:  Replace the Data Source name “LALIT” with your Data Source and Catalogue=”MyDataBase“  with your Database name.

C#.NET Code

• Now in the design file (.aspx) place three text boxes for Username, EmailId 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>

                    Email Id</td>

                <td>

                    <asp:TextBox ID="txtEmailId" 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>

• Now in the code behind file (.aspx.cs) write the Submit button code as:

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["MyDbCon"].ConnectionString.ToString());

        try

        {

            if (con.State == ConnectionState.Closed)

            {

                con.Open();

            }

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

            cmd.CommandType = CommandType.StoredProcedure;

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

            cmd.Parameters.AddWithValue("@EmailId", txtEmailId.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

• Now in the design file (.aspx) place three text boxes for Username, EmailId 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>

                    Email Id</td>

                <td>

                    <asp:TextBox ID="txtEmailId" 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" />

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

                </td>

            </tr>

        </table>

• Now in the code behind file (.aspx.vb) write the Submit button code as:


First of all include 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("MyDbCon").ConnectionString.ToString())
        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            Dim cmd As New SqlCommand("USER_REGISTRATION_SP", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim())
            cmd.Parameters.AddWithValue("@EmailId", txtEmailId.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