Srikanth Technologies

Retrieving return value of stored function from ADO.NET

A common doubt among beginners of stored functions and ADO.NET is how to access return value of stored function of SQL Server from ADO.NET. We will see a simple stored function called GetEmail, wh, which takes username and returns email address of the user. It returns null if username is not found. The following is the stored function in Sql Server. 
CREATE FUNCTION dbo.GetEmail(@uname varchar(10))TURNS varchar(50)
AS
BEGIN
  declare @email varchar(50)
	
  select  @email = email from users
  where uname = @uname;
 
  return @email;
END
Users table contains uname and email columns.

ASP.NET Page to call stored function

Let us create a simple ASP.NET page to take username from user and display email address. It uses ADO.NET to call stored function - GetEmail. It creates a parameter whose parameter direction is set to ReturnValue. Add it as one of the parameters to the Parameters collection of SqlCommand object. After the function is called, the parameter with the direction ReturnValue contains the return value of the function.

CallFunction.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CallFunction.aspx.cs" Inherits="CallFunction" %>

<!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 id="Head1" runat="server">
    <title>Calling Stored Function</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Calling Stored Function</h1>
    Enter username : 
        <asp:TextBox ID="txtUsername" runat="server" Width="230px"></asp:TextBox>
        <p />
        <asp:Button ID="btnGetEmail" runat="server" Text="Get Email" 
                onclick="btnGetEmail_Click" />
        <p />
        <asp:Label ID="lblEmail" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

CallFunction.aspx.cs


using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class callfunction : System.Web.UI.Page
{
    protected void btnGetEmail_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=msdb;Integrated Security=True");
        try {
            con.Open();
            SqlCommand cmd= new SqlCommand("getemail",con);
            cmd.CommandType = CommandType.StoredProcedure;
            
            // add parameter for return value
            SqlParameter rv = new SqlParameter("@returnvalue",SqlDbType.VarChar,50);
            rv.Direction = ParameterDirection.ReturnValue; 

            cmd.Parameters.Add(rv);
            
            // add input parameter
            cmd.Parameters.AddWithValue ("@uname", txtUsername.Text);

            cmd.ExecuteNonQuery ();

            // return value is in the parameter @returnvalue
            Object email = cmd.Parameters["@returnvalue"].Value;
            
            // If return value is not null then use value
            if (DBNull.Value != email)
                lblEmail.Text = email.ToString();
            else
                lblEmail.Text = "Sorry! User Not Found";
        }
        catch(Exception ex) {
            lblEmail.Text = ex.Message;
        }
        finally {
            con.Close();
        }
    }
}
Srikanth

Post Your Comment

Image
Enter the code given in the above image :
Enter Your Name :
Enter Your Email Address :
Enter Your Comment :

Comments
Posted By sindhu On 29-Jul-09 11:47:56 AM

Thank you sir


Posted By KishoreMullapudi On 05-Aug-09 03:51:12 PM

Thanks,It's vary nice.


Posted By Jitendra kumar On 07-Oct-09 04:10:21 PM

This is really very nice code for developer.


Posted By Kishore Kumar On 07-Jan-10 09:41:05 PM

Thank u sir,,i am one of your student at the time of august2009 batch..With your training i got placed in Heritage IT Solutions,,Hyderbad..Thank you sir,Thank you very much..


Posted By sureshkumar.g On 19-Jan-10 11:10:14 AM

hi

Thanks it will work well...


Posted By Girish On 26-Jan-10 04:51:24 PM

Sir, Please post Custom Paging in ASP.NET 2.0/3.5 with SQL Server 2005 using Grid View in your style (Paging for Large Amounts of Data).


Posted By Vijeesh Meethale Edathil On 08-Dec-11 02:42:25 PM

Thanks


Posted By Mohd. Moazzam On 14-Dec-11 08:31:31 PM

WOW.... Great Iam Impressed.Thank You GOOGLE


Posted By Brij Sharma On 30-Mar-12 07:22:19 PM

Thank You very much. I didn't know it was similar to calling stored procedure.


Posted By Chaitanya On 17-Mar-18 08:21:05 PM

Brilliant solution
You have showed how function
Can be invoked using stored procedures