Wednesday, 5 June 2013

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”
http://webcodeexpert.blogspot.in/

  • 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 ObjectByVal 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

No comments:

Post a Comment