Calling SQL Defined Function in C#

Calling SQL Defined function in C#

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;
}

Calling SQL Functions directly from C#

The calling scalar-valued function is absolutely correct solution.

C# call a SQL Server user defined function via Entity Framework

That's a scalar-valued User Defined Function. EF has never supported those directly. It's trivial to add a method to your DBContext to call a scalar-valued UDF. Something like:

public string GetFruit(int customerId)
{
    return Database.SqlQuery<string>("select dbo.fngetfruit(@customerId) fruit", new System.Data.SqlClient.SqlParameter("@customerID", customerId)).Single();
}

EF supports Table-Valued User Defined Functions since version 5 (which requires .NET 4x). Entity Framework Table-Valued Functions (TVFs) (EF5 onwards)

Call Scalar function in C#

I got your concern, as per your scenario you could try like below:

SQL Function:

CREATE FUNCTION CallFunctionFromCSharp (@EmpId INT)
RETURNS INT
AS
BEGIN
    DECLARE @EmpProjectNumber INT;

    SELECT @EmpProjectNumber =  (SELECT WsEmployeeNumber FROM WsEmployee WHERE WsEmployeeId =@EmpId)

    IF (@EmpProjectNumber IS  NULL)
        SET @EmpProjectNumber = 0;

    RETURN @EmpProjectNumber;
END

This function Return a number like below:

Call In C#

 using (var connection = new SqlConnection("Server=ServerName;Database=YourDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"))
                {
                    int employeeId = 1;
                    string projectNumber;
                    connection.Open();
                    var command = connection.CreateCommand();
                    command.CommandText = "SELECT dbo.CallFunctionFromCSharp(@EmpId) AS projectNumber";
                    command.Parameters.AddWithValue("@EmpId", employeeId);
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        projectNumber = reader["projectNumber"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
                      
                    }
                    reader.Close();
                    command.Dispose();
                    connection.Close();

                }

Note: You have two parameter so you can write like this: dbo.CallFunctionFromCSharp(@EmpId,@anotherParam) then pass the
parameter like this command.Parameters.AddWithValue("@anotherParam", anotherParamValue);

Output:

Note: Be double check the return type and parameter type if you encounter any exception.

Hope it would help you to achive your goal.

C# calling User Defined Scalar function in SQL Server that takes table type as its Parameter

Simply change:

sqlCommand.Parameters.AddWithValue("@Row", tableType);

to be:

sqlCommand.Parameters.Add(tableType);

since it is already a SqlParameter type. You would use AddWithValue to create the SqlParameter on-the-fly (though please see my note at the bottom about not using that deprecated method), but you have already created the SqlParameter and just need to add it to the collection.

And yes, removing the * FROM from the query was also necessary from a purely T-SQL syntax stand-point.

---

Side note: it is best to not use AddWithValue in any case, ever ;-):

• Difference between Parameters.Add and Parameters.AddWithValue
• Can we stop using AddWithValue() already?

Calling Functions in SqlCommand

If you mean, SQL Server user defined functions. Then, yes; you can use it normally like:

myCommand.CommandText = "SELECT fn_Yourfunctionname(@parameternames)";
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.Add(new SqlParameter("@parameternames", ...

The reason it works is because this is the way that functions are called in SQL Server directly.

How can I call a SQL function in C#?

Your SQL is a bit off, it should be:

  string query = string.Format("select * from dbo.Function1({0},{1});", int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString()),1);

You might want to use SqlParameter-objects to prevent sql injections:

  string query = "select * from dbo.Function1(@pa1,@par2);";
  cmd.Parameters.Add("@par1", SqlDbType.Int).Value = int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString());  
  cmd.Parameters.Add("@par2", SqlDbType.Int).Value = 1;

How to use SQL user defined functions in .NET?

It sounds like the right way in this case is to use the functionality of the entity framework to define a .NET function and map that to your UDF, but I think I see why you don't get the result you expect when you use ADO.NET to do it -- you're telling it you're calling a stored procedure, but you're really calling a function.

Try this:

public int GetUserIdByUsername(string username)
{
    EntityConnection connection = (EntityConnection)Connection;            
    DbCommand com = connection.StoreConnection.CreateCommand();
    com.CommandText = "select dbo.fn_GetUserId_Username(@Username)";
    com.CommandType = CommandType.Text;
    com.Parameters.Add(new SqlParameter("@Username", username));
    if (com.Connection.State == ConnectionState.Closed) com.Connection.Open();
    try
    {
        var result = com.ExecuteScalar(); // should properly get your value
        return (int)result;
    }
    catch (Exception e)
    {
        // either put some exception-handling code here or remove the catch 
        //   block and let the exception bubble out 
    }
}

Calling user defined functions in Entity Framework 4

I have finally worked it out :D For scalar functions you can append the FROM {1} clause.

bool result = FooContext.CreateQuery<bool>(
    "SELECT VALUE FooModel.Store.UserDefinedFunction(@someParameter) FROM {1}",
    new ObjectParameter("someParameter", someParameter)
).First();

This is definitely a case for using LINQ to SQL over EF.

---

Related Topics