C# .NET - Get returned value from Scalar Valued Function of SQl server in C#

Asked By Narendar Ch on 18-Apr-13 05:05 AM
Hi My Name is Narendar.
I'm trying to  increment the Invoice Number (Nvarchar) value and show the new invoice Number on webpage.
For this i have a Scalar Valued Function in SQL server returning the Incremented value.

My Function is :
CREATE
USE [Aadhya_Test]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_IncrementAlphaNumericString]    Script Date: 04/18/2013 11:37:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_IncrementAlphaNumericString]
(
  @InvID nvarchar(100)
RETURNS nvarchar(100) AS 
BEGIN
DECLARE   @NumericStringLen int, @LastAlphaPos int, @position int,
    @NewString nvarchar(100), @NumericString nvarchar(100),
    @MaxNumValue nvarchar(100),  @AlphaString nvarchar(100),
    @MaxStringValue nvarchar(100)
 
SET @position = 1
 
--Check to see if the given string is numeric.
IF ISNUMERIC(@InvID) <> 0
BEGIN
--The string is numeric so check to see if is at it's maximum numeric value.
  SET @MaxStringValue = REPLICATE('9',LEN(@InvID))
  IF @MaxStringValue = @InvID
  BEGIN
--The string is at it's maximum numeric value so add an alpha character.
  SET @NewString = 'A' + REPLICATE('0',LEN(@InvID)-1)
  END
  ELSE
  BEGIN
--The number isn't at it's maximum numeric value so increment the number and pad with the zero's if necessary.
  SET @NewString = RIGHT(REPLICATE('0',LEN(@InvID)) + CAST((CAST(@InvID as int) + 1) as nvarchar),LEN(@InvID))
  END
END
ELSE
BEGIN
--The string isn't numeric so find the position of the last alpha character
--by looping through the string character by character.
 WHILE @position <= LEN(@InvID)
 BEGIN
  IF ISNUMERIC(SUBSTRING(@InvID,@position,1)) = 0
  BEGIN
    SET @LastAlphaPos = @position
  END
 
  SET @position = @position + 1
 END
 
--Make sure the last alpha position is less than the length of the whole string.
 IF @LastAlphaPos < LEN(@InvID)
 BEGIN
  --Get the alpha portion of the string and change to uppercase characters so the acii range is correct.
  SET @AlphaString = UPPER(SUBSTRING(@InvID,1,@LastAlphaPos))
  --Get the numeric portion of the string.
  SET @NumericString = SUBSTRING(@InvID,@LastAlphaPos +1,LEN(@InvID))
  --Prepare a variable with the maximum numeric value to compare against the strings numeric value.
  SET @MaxNumValue = REPLICATE('9',LEN(SUBSTRING(@InvID,@LastAlphaPos+1,LEN(@InvID))))
  --Compare the numeric value of the string against the maximum numeric value .
  IF  @MaxNumValue = @NumericString
  BEGIN
    --The numeric value of the string has reached the maximum value so check to see if the position
    --of the last alpha character has reached the end of the string.
    IF @LastAlphaPos < (LEN(@InvID) -1)
    BEGIN
    --The alpha characters haven't reached the end of the string so add another alpha character.
      SET @NewString = @AlphaString + 'A' + REPLICATE('0',LEN(@NumericString)-1)
    END
    ELSE
    BEGIN
    --The alpha characters have reached the end of the string so check to see if the last alpha
    --character has reached it's maximum ascii value.
      IF ASCII(SUBSTRING(@InvID,@LastAlphaPos,1)) = 90
      BEGIN
      --The last alpha character has reached it's maximum ascii value so find the position of the
      --first alpha character that has reached it's maximum ascii value.
        DECLARE @pos int
        SET @pos = @LastAlphaPos
        WHILE ASCII(SUBSTRING(@AlphaString,@pos,1)) = 90
        BEGIN
          SET @pos = @pos - 1
        END
        --Check to see if the position of the first alpha character that has reached it's maximum
        --ascii value is the first character in the whole string.
        IF @pos > 1
        BEGIN  
        --The first alpha character that has reached it's maximum ascii value isn't the first in the
        --string so add the first alpha characters to the beginning of the new string and increment
        --those that have reached their maximum value.
          SET @NewString = LEFT(SUBSTRING(@AlphaString,1,@pos -1) + CHAR(ASCII(SUBSTRING(@AlphaString,@pos,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
        END
        ELSE
        BEGIN
        --The first alpha character that has reached it's maximum ascii value is the first character in
        --the string so increment it and pad the remainder of the string with zero's.
          SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@InvID,1,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
        END
      END
      ELSE
      BEGIN
        --The last alpha character hasn't reached it's maximum ascii value so check to see if it is the first
        --character in string.
        IF @LastAlphaPos > 1
        BEGIN
        --The last alpha character isn't the first in the string so add the first alpha characters to the
        --beginning of the new string and increment it and pad with zero's.
          SET @NewString = LEFT(SUBSTRING(@InvID,1,@LastAlphaPos -1) + CHAR(ASCII(SUBSTRING(@InvID,@LastAlphaPos,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
        END
        ELSE
        BEGIN
        --The last alpha character is the first in the string so increment it and pad the remainder of the string with zero's.
          SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@InvID,1,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
        END
      END
    END
  END
  ELSE
  BEGIN  
  --The numeric value of the string hasn't reached the maximum value so only increment the numeric portion of the string and pad with zero's if necessary.
    SET @NewString = SUBSTRING(@InvID,1,@LastAlphaPos) + RIGHT(REPLICATE('0',LEN(@NumericString)) + CAST((CAST(SUBSTRING(@InvID,@LastAlphaPos +1,LEN(@InvID)) as int)+1) as nvarchar), LEN(@NumericString))
  END
  END
END
 
RETURN @NewString
 
END

Output:
select dbo.fn_IncrementAlphaNumericString('2013-14/015') As ID
ID
2013-14/016

But I want to use this in C# Asp.net page
I want to send the value to the above function from asp.textbox and get the returned value to another textbox

For this I had written the Below Code.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
            <asp:TextBox runat="server" ID="txtInvNum"></asp:TextBox>
            <asp:ImageButton runat="server" src="images/AutoGen.png" ID="imgAutoGen" OnClick="imgAutoGen_Click"/>
             <asp:TextBox runat="server" ID="txtReturnValue"></asp:TextBox>
  </div>
  </form>
</body>
</html>

Code Behind:

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
public partial class Test : System.Web.UI.Page
{
  string dbconn;
  //string Result;
  protected void Page_Load(object sender, EventArgs e)
  {
    dbconn = System.Configuration.ConfigurationManager.ConnectionStrings["db_connection"].ConnectionString;
    SqlConnection con = new SqlConnection(dbconn);
    con.Open();
    if (!IsPostBack)
    {
      string strInvID = "SELECT TOP 1 InvID_New from Invoice_Amounts Order by InvID DESC ";
      SqlCommand cmdInvId = new SqlCommand(strInvID, con);
      cmdInvId.ExecuteNonQuery();
      SqlDataAdapter daInvID = new SqlDataAdapter(cmdInvId);
      DataTable dtInvId = new DataTable();
      daInvID.Fill(dtInvId);
      if (dtInvId.Rows.Count > 0)
      {
        txtInvNum.Text = dtInvId.Rows[0]["InvID_New"].ToString();
      }
      con.Close();
    }
  }
  protected void imgAutoGen_Click(object sender, ImageClickEventArgs e)
  {
    SqlConnection con = new SqlConnection(dbconn);
    try
    {
      con.Open();
 
      SqlCommand Cmd = new SqlCommand();
      SqlParameter Parameter = new SqlParameter();
      List<SqlParameter> Parameters = new List<SqlParameter>();
 
      Cmd.Connection = con;
 
      Cmd.CommandText = "select dbo.fn_IncrementAlphaNumericString(@InvID)";
      Cmd.CommandType = CommandType.Text;
 
      Parameter = new SqlParameter("@NewString", SqlDbType.NVarChar);
       Parameter.Direction = ParameterDirection.ReturnValue;
      Parameters.Add(Parameter);
 
      Parameter = new SqlParameter("@InvID", SqlDbType.NVarChar);
      Parameter.Direction = ParameterDirection.Input;
      Parameter.Value = txtInvNum.Text;
      Parameters.Add(Parameter);
 
      foreach (SqlParameter functionParameter in Parameters.ToArray())
        Cmd.Parameters.Add(functionParameter);
 
      Cmd.ExecuteNonQuery();
 
      string returnValue = Cmd.Parameters["@NewString"].Value.ToString();
      txtReturnValue.Text = returnValue.ToString();
 
      con.Close();
    }
    catch { }
    finally
    {
      if (con.State == ConnectionState.Open)
        con.Close();
    }
  }
}


But Im getting 0 as output.

Can anybody pl tell me what is the problem in the code, why i am unable to get the output.
pl help me..

Thanks..  
Robbe Morris replied to Narendar Ch on 18-Apr-13 08:09 AM
I do not make direct calls to UDFs in my apps so I could be wrong here.  I'm pretty sure you'll want to use a SqlDataReader or SqlDataAdapter to read the result like you would any other query.  By using the "SELECT", you are running a query and not running the UDF in the same way you would execute a stored procedure.

You could try removing the SELECT and see if the sql provider will see if the same way it does with a stored procedure.  I doubt if it works though.