How to call SQL Function in C#

Consider you have an SQL function in SQL Server called GetEmployeeSalary that takes an EmployeeID and returns the salary:

CREATE FUNCTION GetEmployeeSalary (@EmployeeID INT)
RETURNS DECIMAL
AS
BEGIN
    DECLARE @Salary DECIMAL
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID
    RETURN @Salary
END

You can call a SQL function by using ADO.NET.

For example:

public decimal CallSqlFunc(int EmployeeId)
{
    decimal salary = 0;
    using (var con = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]))
    {
        con.Open();
        var cmd = new SqlCommand("SELECT dbo.GetEmployeeSalary(@EmployeeId)", con);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        object result = cmd.ExecuteScalar();
        if (result != DBNull.Value) { salary = Convert.ToDecimal(result); }
        return salary;
    }
}

SqlConnection: This establishes a connection to your database using a connection string

SqlCommand: This is used to execute SQL queries.

ExecuteScalar() is used to execute the SQL function and retrieve a single value.

SqlParameter: This ensures safe parameterized queries, avoiding SQL injection.

This method demonstrates how to call an SQL function from C# using ADO.NET.