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;
}
We can execute a function in C# using a SqlCommand object and passing a SQL defined function in a SELECT SQL query.
Yes you can call a function inside a function.
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; }
...
try
{
if (connectionState != ConnectionState.Open)
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "udfName";
cmd.CommandType = CommandType.StoredProcedure;
foreach (var cmdParam in sqlParams)
{
cmd.Parameters.Add(cmdParam);
}
var retValParam = new SqlParameter("RetVal", SqlDbType.Int)
{
//Set this property as return value
Direction = ParameterDirection.ReturnValue
};
cmd.Parameters.Add(retValParam);
cmd.ExecuteScalar();
retVal = retValParam.Value;
}
}
finally
{
if (connectionState == ConnectionState.Open)
conn.Close();
}
...
I wanted to call a SQL Function in the same way that I call Stored Procedures which is by using DeriveParameters and then setting the parameter values. It turns out that this works with Scalar Functions out of the box and you can use ExecuteNonQuery and read the RETURN_VALUE. Please see sample code below:
public int GetLookupCodeFromShortCode(short tableType, string shortCode)
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
{
conn.Open();
using (var cmd = new SqlCommand("dbo.fnGetLookupCodeFromShortCode", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@sintTableType"].Value = tableType;
cmd.Parameters["@vchrShortCode"].Value = shortCode;
cmd.Parameters["@chrLanguage"].Value = "en";
cmd.Parameters["@chrCountry"].Value = "en";
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["@RETURN_VALUE"].Value;
}
}
}
The Scalar Function code looks like this:
CREATE FUNCTION [dbo].[fnGetLookupCodeFromShortCode]( @sintTableType SMALLINT, @vchrShortCode VARCHAR(5), @chrLanguage CHAR(2), @chrCountry CHAR(2))
RETURNS INT
AS
BEGIN
DECLARE @intLookupCode INT
SELECT @intLookupCode = LV.intLookupCode
FROM
tblLookupValueDesc LVD
INNER JOIN tblLookupValue LV ON LV.sintLookupTableType = LVD.sintLookupTableType AND LV.intTableKey = LVD.intTableKey
WHERE
LVD.sintLookupTableType = @sintTableType
AND LVD.vchrShortCode = @vchrShortCode
AND LVD.chrCountry = @chrCountry
AND LVD.chrLanguage = @chrLanguage
RETURN @intLookupCode
END
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With