Call SQL function with C#

Question

I write this function in SQL:

   ALTER FUNCTION Fn_CheckBill
(
@image AS image,
@number AS nvarchar(50),
@date AS nchar(10)
)
RETURNS bit
AS
BEGIN
DECLARE @flag bit;
IF EXISTS ( SELECT * 
            FROM tblBill 
            WHERE ((cast([Image] as varbinary(max)) = cast(@image as varbinary(max))) AND (Number = @number) AND ([Date] = @date)) )
            BEGIN
                SET @flag = 0
            END
ELSE
    BEGIN
        SET @flag = 1
    END

RETURN @flag
END

And write this code in my C# source code:

int flag;
    try
    {
        objCommand = new SqlCommand("SELECT Fn_CheckBill(@image,@date,@number) AS int");
        objCommand.CommandType = CommandType.Text;

        objCommand.Parameters.AddWithValue("image", image);
        objCommand.Parameters.AddWithValue("number", number);
        objCommand.Parameters.AddWithValue("date", _Date);

        using (objConnection = new SqlConnection(connenctString))
        {

            objConnection.Open();
            objCommand.Connection = objConnection;
            flag = int.Parse(objCommand.ExecuteScalar().ToString());
        }

        if (flag == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch
    {
        return false;
    }

But It throw this exception when executed: 'Fn_CheckBill' is not a recognized function name.

Please help me to solve this problem :(

Are you sure you connected the right database in your connection string? — Soner Gönül
– Soner Gönül, Commented Mar 5, 2015 at 14:57
ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here — marc_s
– marc_s, Commented Mar 5, 2015 at 15:01
Scalar functions are not a great choice from a performance perspective. You could certainly streamline this one into a single query instead of the if else logic here. Far worse however is the usage of the image datatype. That datatype has been deprecated for a decade and you should avoid using it. — Sean Lange
– Sean Lange, Commented Mar 5, 2015 at 15:01

Tab Alleman · Accepted Answer · 2015-03-05 14:58:08Z

3

You need to supply the schema in any SQL function call

objCommand = new SqlCommand("SELECT dbo.Fn_CheckBill(@image,@date,@number) AS int");

answered Mar 5, 2015 at 14:58

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean Lange · Accepted Answer · 2015-03-05 15:06:31Z

0

I would consider rewriting this as an inline table valued function. Something like this:

create FUNCTION Fn_CheckBill
(
    @image AS varbinary(max),
    @number AS nvarchar(50),
    @date AS nchar(10)
)
RETURNS table
AS
RETURN
    SELECT CAST(count(*) as bit) as RowFound
    FROM tblBill 
    WHERE [Image] = @image
        AND Number = @number
        AND [Date] = @date

answered Mar 5, 2015 at 15:06

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Call SQL function with C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related