在C#中调用SQL定义的函数 [英] Calling SQL Defined function in C#
本文介绍了在C#中调用SQL定义的函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在TSQL写了这个标量函数:
创建功能TCupom(@cupom INT)
浮动收益
为
开始
申报@Tcu浮动;
从alteraca2选择@Tcu = SUM(总),其中pedido = @cupom
如果(@tcu为null)
组@tcu = 0;
返回@tcu;
端
我要调用这个函数在我的C#代码。这是我到目前为止有:
公共无效TotalCupom(INT cupom)
{
浮动SAIDA;
SqlDataAdapter的DA2 =新的SqlDataAdapter();如果
(conex1.State == ConnectionState.Closed)
{
conex1.Open();
}
的SqlCommand Totalf =新的SqlCommand(Tcupom,conex1);
的SqlParameter代码1 =新的SqlParameter(@码,SqlDbType.Int);
code1.Value = cupom;
Totalf.CommandType = CommandType.StoredProcedure;
= SAIDA Totalf.ExecuteScalar();
返回SAIDA;
}
解决方案
您不能只是调用函数名,你需要编写一个内嵌的SQL语句,这使得使用UDF的:
的SqlCommand Totalf =新的SqlCommand( 选择dbo.Tcupom(@code),conex1);
和删除的CommandType
,这ISN 。T A存储过程,它是一个用户定义的函数
在所有的:
公共无效TotalCupom(INT cupom)
{
浮动SAIDA;
SqlDataAdapter的DA2 =新的SqlDataAdapter();
如果(conex1.State == ConnectionState.Closed)
{conex1.Open();}
的SqlCommand Totalf =新的SqlCommand(SELECT dbo.Tcupom(@code),conex1) ;
的SqlParameter代码1 =新的SqlParameter(@码,SqlDbType.Int);
code1.Value = cupom;
= SAIDA Totalf.ExecuteScalar();
返回SAIDA;
}
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
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;
}
解决方案
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);
And remove the CommandType
, this isn't a Stored Procedure, its a User Defined Function.
In all:
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屋!
查看全文