How to 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 e.g. status in this article.  In previous article i explained How to Pass parameter to stored procedure using SqlDataAdapter and check login and 20 main differences between Stored procedures and Functions in Sql Server  and Delete all stored procedures from sql server database and Bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in Asp.net.

Description: This example demonstrate 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.

So whenever a same username or Email id is entered again then 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.

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

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

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

• 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 WHERE USERNAME=@Username)

                BEGIN

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

                                                BEGIN

                                                                INSERT INTO USER_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 in the web.con fig file create the  connection string to connect the asp.net website with the sql server  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 Catalog(i.e. Database name)=”MyDataBase“  with your Database name.

C#.Net Code

• Now in the design page (.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 = new SqlConnection(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 page (.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 import 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 New SqlConnection(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

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post