MS SQL function not returning value to C#

Question

I've got the following MS SQL function:

CREATE FUNCTION [dbo].[fn_NumApplications] ()
RETURNS int
AS
BEGIN
DECLARE @numRecords int = 0
SELECT @numRecords = COUNT(A.id) FROM Applications A
RETURN @numRecords
END

and the following C# code to call the function:

using (SqlConnection conn = new SqlConnection(mydbconnstring))
{
    using (SqlCommand cmd = new SqlCommand("dbo.fn_numApplications", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        //execute
        conn.Open();
        string retVal = cmd.ExecuteScalar().ToString();
        conn.Close();
    }
}

When the function gets to cmd.ExecuteScalar(), I am getting a NullReferenceException.

I have tried calling the function with, and without "dbo.". I have also tried calling it using cmd.ExecuteReader() without success.

What's going wrong here?

Not only do you need the parenthesis and the SELECT keyword, but if you have a case sensitive collation this will throw an exception because the name of the function does not have a capital N in your c# code. Of course I would suggest making this into an inline table valued function instead. — Sean Lange, Jul 08 '16 at 20:05
You're missing the SELECT statement in front of the dbo.MyFunction. You should run the code in SSMS first......to learn the syntax — granadaCoder, Jul 08 '16 at 20:26

score 0 · Answer 1 · answered Jul 08 '16 at 20:29

0

Try this:

SqlCommand cmd = new SqlCommand("Select dbo.fn_numApplications()", conn)

answered Jul 08 '16 at 20:29

Navid K

90
1
6

MS SQL function not returning value to C#

1 Answers1