Using "OUT" Parameter With Stored Procedure In ASP.NET

Rohatash Kumar
Dec 15, 2017
221.7k
0
2
Article

Introduction

This article introduces you to creating stored procedures for SQL Server 2008 and executing the stored procedure from C# code. Some stored procedures return values through parameters. When a parameter in a SQL statement or stored procedure is declared as out, the value of the parameter is returned back to the caller. First, we create a database table; after that create a stored procedure with an out parameter for insertiing records in the table.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserName, Email and Country. The table looks as below.

Creating a Stored Procedure with Out parameter

Now create a stored procedure with an out parameter to insert data into the table. We create an error out parameter.

USE[Rohatash]
GO
/****** Object: StoredProcedure [dbo].[spuserdetail] Script Date: 01/25/2012 01:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE[dbo].[spuserdetail]
@UserName varchar(50),
@Password varchar(50),
@Email varchar(50),
@Country varchar(50),
@ERROR VARCHAR(100) OUT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM UserDetail WHERE UserName = @UserName) // To Check UserName is exits or not
BEGIN
INSERT INTO UserDetail(UserName, [Password], Email, Country)
VALUES(@UserName, @Password, @Email, @Country)
SET @ERROR = @UserName + ' Registered Successfully'
END
ELSE
BEGIN
SET @ERROR = @UserName + ' Already Exists'
END
END

In the above stored procedure, error is the out parameter and other are the input parameter. In this stored procedure we check UserName; if the UserName exists in the table then it will return the message as an Output Parameter.

SET @ERROR=@UserName + ' Already Exists'

If the UserName does not exist in the table then it will return the message as an Output Parameter.

SET @ERROR=@UserName+' Registered Successfully'

Executing the stored procedure from C# code

In order to demonstrate the process of executing a stored procedure from a C#, create a new web application project in Visual Studio 2010. Add using statements above the namespace declaration to enable the use of non-fully qualified references to other namespace types.

Now add the following namespace.

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

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Now I need to display that output parameter message during user registration in ASP.NET. How to get that output parameter returned by a SQL query. For a sample design your aspx page might be like this:

aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication117.WebForm1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div> UserName:
<asp:TextBox ID="UserNameTextBox" runat="server"></asp:TextBox> <br /> <br /> Password:
<asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Confirm Password:
<asp:TextBox ID="ConfirmPasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Email:
<asp:TextBox ID="EmailTextBox" runat="server"></asp:TextBox> <br /> <br /> Country:
<asp:TextBox ID="CountryTextBox" runat="server"></asp:TextBox> <br /> <br />
<asp:Button ID="SaveButton" runat="server" Text="Save" onclick="SaveButton_Click" /> <span style="color:Red; font-weight :bold"> <asp:Label ID="lblErrorMsg" runat="server"></asp:Label></span> </div>
</form>
</body>
</html>

To get output parameters in ASP.NET we need to write statements like this.

cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
message = (string)cmd.Parameters["@ERROR"].Value;

In Codebehind write the following code in the SaveButton_Click like this.

Codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication117 {
public partial class WebForm1: System.Web.UI.Page {
private string message = string.Empty;
protected void Page_Load(object sender, EventArgs e) {}
protected void SaveButton_Click(object sender, EventArgs e) {
if (PasswordTextBox.Text == ConfirmPasswordTextBox.Text) {
string UserName = UserNameTextBox.Text;
string Password = PasswordTextBox.Text;
string ConfirmPassword = ConfirmPasswordTextBox.Text;
string Email = EmailTextBox.Text;
string Country = CountryTextBox.Text;
SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
con.Open();
SqlCommand cmd = new SqlCommand("spuserdetail", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
message = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
} else {
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");
}
lblErrorMsg.Text = message;
}
}
}

Now run the application and test it.

Now insert the data and click on the Save Button.

SET @ERROR=@UserName+' Registered Successfully'

The above message shows UserName with a message which is defined in the stored procedure as an output parameter. If we enter the same UserName again than it will display the following message as an output parameter.

SET @ERROR=@UserName + ' Already Exists'