正如Steve正确指出的那样,这里的根本错误是没有正确添加参数。然而,作为一种通用的代码错误避免技巧,您可能想尝试像dapper这样的工具,这样更难出错。例如:
我已经在SQL 中编写了这个函数
alter function TVprest (@emitente int, @mes int, @ano int)
returns float
as
begin
declare @tcu float;
select @tcu = sum(cast(vtprest as money))
from ctrc
where emitente = @emitente and MONTH (EMISSAODATA ) = @mes
and YEAR (EMISSAODATA)=@ano and status = 'A'
if (@tcu is null)
set @tcu = 0;
return @tcu
end
尝试用以下代码调用C#中的相同函数:
public double TVprest (int emitente, int mess, int anno)
{
double saida;
SqlConnection abre1 = Tconex.GetConnection();
SqlDataAdapter da3 = new SqlDataAdapter();
if (abre1.State == ConnectionState.Closed) { abre1.Open(); }
SqlParameter emit = new SqlParameter("@emitente", SqlDbType.Int);
emit.Value = emitente;
SqlParameter mes = new SqlParameter("@mes", SqlDbType.Int);
mes.Value = mess;
SqlParameter ano = new SqlParameter("@ano", SqlDbType.Int);
ano.Value = ano;
SqlCommand TotalF = new SqlCommand("SELECT dbo.Tcupom(@emitente,@mes,@ano),", abre1);
TotalF.CommandType = CommandType.Text;
saida = Convert.ToDouble(TotalF.ExecuteScalar());
return saida;
}
当运行时,我得到这个错误:
无法将参数值从SqlParameter转换为Int32
怎么了?使用以下参数调用函数:
double Tvprest = impx.TVprest(504, 5, 2013);
lblVtprest.Text = Tvprest.ToString();
您尚未将参数添加到命令中
SqlCommand TotalF = new SqlCommand("SELECT dbo.Tcupom(@emitente,@mes,@ano),", abre1);
TotalF.Parameters.Add(emit);
TotalF.Parameters.Add(mes);
TotalF.Parameters.Add(ano);
saida = Convert.ToDouble(TotalF.ExecuteScalar());
然而,我认为你在问题中遗漏了一些解释。您有一个名为TVprest的函数,但您调用的是SELECT dbo.Tcupom
。不清楚Tcupom
是什么
return abre1.Query<double>("SELECT dbo.Tcupom(@emitente,@mess,@anno)",
new { emitente, mess, anno }).Single();
这在这里做了所有,但它做得很好,而且很容易阅读。它甚至适用于更复杂的类型,即
string region = ...
var customers = connection.Query<Customer>(
"select * from Customers where Region = @region",
new { region }).ToList();