在 C# 中调用 SQL 定义的函数 [英] Calling SQL Defined function in C#

问题描述

我已经用 TSQL 编写了这个标量函数:

I have written this scalar function in TSQL:

create function TCupom (@cupom int)
returns float
as
begin
    declare @Tcu float;

    select @Tcu = sum (total) from alteraca2 where pedido = @cupom 

    if (@tcu is  null)
        set @tcu = 0;

    return @tcu;
end

我想在我的 C# 代码中调用这个函数.这是我到目前为止所拥有的:

I want to call this function in my C# code. Here's what I have so far:

public void TotalCupom(int cupom)
{ 
    float SAIDA;           
    SqlDataAdapter da2 = new SqlDataAdapter();

    if (conex1.State == ConnectionState.Closed)
    { 
        conex1.Open();
    }

    SqlCommand Totalf = new SqlCommand("Tcupom", conex1);
    SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int);
    code1.Value = cupom ;
    Totalf.CommandType = CommandType.StoredProcedure ;
    SAIDA = Totalf.ExecuteScalar();

    return SAIDA;
}

推荐答案

你不能只调用函数名，你需要编写一个使用 UDF 的内联 SQL 语句:

You can't just call the function name, you will need to write an inline SQL statement which makes use of the UDF:

SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);

并删除CommandType，这不是存储过程，而是用户定义的函数.

And remove the CommandType, this isn't a Stored Procedure, its a User Defined Function.

总之:

public void TotalCupom(int cupom)
{ 
    float SAIDA;           
    SqlDataAdapter da2 = new SqlDataAdapter();
    if (conex1.State == ConnectionState.Closed)
    {
        conex1.Open();
    }
    SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);
    SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int);
    code1.Value = cupom;
    SAIDA = Totalf.ExecuteScalar();

    return SAIDA;
}

这篇关于在 C# 中调用 SQL 定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！