Post
Ask Question

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

Introduction
 
This article introduces you how to create stored procedures in SQL Server 2008 and how to execute stored procedures in C# from an ASP.NET Web application. 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. In this article and code sample, we will see how to execute a stored procedure and return a value via the OUT parameter.
 
First, we create a database table; after that create a stored procedure with an out parameter for inserting records in the table.
 
Creating a Table in SQL Server Database
 
Create a table named UserDetail with columns UserName, Email and Country. The table looks as below.
 
creating table in sql server
 
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.
  1. USE[Rohatash]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[spuserdetail]    Script Date: 01/25/2012 01:37:54 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE[dbo].[spuserdetail]  
  9. @UserName varchar(50),  
  10.     @Password varchar(50),  
  11.     @Email varchar(50),  
  12.     @Country varchar(50),  
  13.     @ERROR VARCHAR(100) OUT  
  14. AS  
  15. BEGIN  
  16. SET NOCOUNT ON;  
  17. IF NOT EXISTS(SELECT * FROM UserDetail WHERE UserName = @UserName) //  To Check UserName is exits or not  
  18. BEGIN  
  19. INSERT INTO UserDetail(UserName, [Password], Email, Country)  
  20. VALUES(@UserName, @Password, @Email, @Country)  
  21. SET @ERROR = @UserName + ' Registered Successfully'  
  22. END  
  23. ELSE  
  24. BEGIN  
  25. SET @ERROR = @UserName + ' Already Exists'  
  26. END  
  27. 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.
  1. SET @ERROR=@UserName + ' Already Exists'
If the UserName does not exist in the table then it will return the message as an Output Parameter.
  1. SET @ERROR=@UserName+' Registered Successfully'
Executing the stored procedure from C# code
 
In order to demonstrate the process of executing a stored procedure in 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 namespaces to your class:
  1. using System.Data.SqlClient;
  2. using System.Data;
Write connection string to connect to the database.
  1. string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";
Now, I  need to display that output parameter message during the user registration in ASP.NET. Let's see how we can get that output parameter returned by a SQL query. For sample purposes, the design of the aspx page looks like this:
 
aspx page
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication117.WebForm1" %>  
  2.     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  3.     <html xmlns="http://www.w3.org/1999/xhtml">  
  4.   
  5.     <head runat="server">  
  6.         <title></title>  
  7.     </head>  
  8.   
  9.     <body>  
  10.         <form id="form1" runat="server">  
  11.             <div> UserName:  
  12.                 <asp:TextBox ID="UserNameTextBox" runat="server"></asp:TextBox> <br /> <br /> Password:  
  13.                 <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Confirm Password:  
  14.                 <asp:TextBox ID="ConfirmPasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <br /> <br /> Email:  
  15.                 <asp:TextBox ID="EmailTextBox" runat="server"></asp:TextBox> <br /> <br /> Country:  
  16.                 <asp:TextBox ID="CountryTextBox" runat="server"></asp:TextBox> <br /> <br />  
  17.                 <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>  
  18.         </form>  
  19.     </body>  
  20.   
  21.     </html>  
To get output parameters in ASP.NET, we need to write the statement like this.
  1. cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);  
  2.         cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;  
  3.         message = (string)cmd.Parameters["@ERROR"].Value;  
In code behind, write the following code on the SaveButton_Click.
 
Code behind
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Data;  
  9. namespace WebApplication117 {  
  10.     public partial class WebForm1: System.Web.UI.Page {  
  11.         private string message = string.Empty;  
  12.         protected void Page_Load(object sender, EventArgs e) {}  
  13.         protected void SaveButton_Click(object sender, EventArgs e) {  
  14.             if (PasswordTextBox.Text == ConfirmPasswordTextBox.Text) {  
  15.                 string UserName = UserNameTextBox.Text;  
  16.                 string Password = PasswordTextBox.Text;  
  17.                 string ConfirmPassword = ConfirmPasswordTextBox.Text;  
  18.                 string Email = EmailTextBox.Text;  
  19.                 string Country = CountryTextBox.Text;  
  20.                 SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");  
  21.                 con.Open();  
  22.                 SqlCommand cmd = new SqlCommand("spuserdetail", con);  
  23.                 cmd.CommandType = CommandType.StoredProcedure;  
  24.                 cmd.Parameters.AddWithValue("@UserName", UserName);  
  25.                 cmd.Parameters.AddWithValue("@Password", Password);  
  26.                 cmd.Parameters.AddWithValue("@Email", Email);  
  27.                 cmd.Parameters.AddWithValue("@Country", Country);  
  28.                 cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);  
  29.                 cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;  
  30.                 cmd.ExecuteNonQuery();  
  31.                 message = (string) cmd.Parameters["@ERROR"].Value;  
  32.                 con.Close();  
  33.             } else {  
  34.                 Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");  
  35.             }  
  36.             lblErrorMsg.Text = message;  
  37.         }  
  38.     }  
  39. }  
Now run the application and test it.
 
OUT Parameter
 
Now insert the data and click on the Save Button.
 
SET @ERROR=@UserName+' Registered Successfully'
 
OUT Parameter
 
The above message shows a 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'
 
OUT Parameter
 
Some Helpful Resources


Similar Articles