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