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;
}
}
}
ex:::
alter PROCEDURE [dbo].[Students_sp]@FirstName nvarchar(200),
@lastname nvarchar(200),
@Email nvarchar(100)=null,
@ERROR VARCHAR(100)=null out
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM tbl_Students WHERE FirstName=@FirstName) -- To Check UserName is exits or not
BEGIN
INSERT INTO tbl_Students(
FirstName,
lastname,
)
VALUES
(
@FirstName,
@lastname,
)
SET @ERROR=(select top 1 (Studentid) from tbl_Students order by Studentid desc)
select @ERROR
END
else
begin
set @ERROR='Error'
select @ERROR
end
end
SET NOCOUNT ON
SELECT * FROM @table
SELECT @@rowcount
SET NOCOUNT OFF
exec Students_sp 'Vivek01','aa','sss'
sp_helptext Students_sp
No comments:
Post a Comment