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.
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 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
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'
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'
No comments:
Post a Comment