How to Call a UDF using ADO.NET

Let’s face it, most of us typically work with Stored Procedures with modest complexity using JOINS and such. Some of us embed Common Table Expressions (CTE) in the stored procs.

If you are already using Entity Framework 2.2, you would know that stored procedures containing multiple joins is not fully supported unless it’s a SQL View in which case you would have to use DbQuery type in your DbContext class.

But, what if you have a UDF (scalar-valued function) and want to use it with ADO.NET!

Let’s see how it’s done. For the purposes of demonstration, let’s assume you have two tables: Department and Employee. Department has a one-to-many relationship with Employee, i.e. A Department can have one or more employees. This is illustrated in the Entity Relationship Diagram (ERD) below:

Next, let’s write a simple User Defined Function (UDF) which accepts @DepartmentID as an input parameter and returns the Employee Name.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION udf_GetEmployeeByID
(
	-- Add the parameters for the function here
	@DepartmentId INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @EmployeeName NVARCHAR(50)

	-- Add the T-SQL statements to compute the return value here
	SELECT @EmployeeName = TRIM(FirstName) + ',' + TRIM(LastName)  
	FROM 
		Employee 
	WHERE 
		DepartmentID = (SELECT DepartmentID 
						FROM Department 
						WHERE DepartmentID = @DepartmentId)

	-- Return the result of the function
	RETURN @EmployeeName

END
GO

As a quick test, let’s test this function in SQL Management Studio. We can see when the function is invoked, it returns the Employee Name which is precisely what we want.

Now, let’s open Visual Studio and create a console application and write the following method. There are a couple of things that are worth noting.

• Since this is not a stored procedure, the command type is defined as “CommandType.Text”. If you change it to “CommandType.StoredProcedure”, the program will compile. However, it will return a null value.
• When newing up a Sql Command object, you must use the SELECT clause when declaring the function name and passing the required parameter to it i.e. “SELECT dbo.udf_GetEmployeeByID(@DepartmentId)”
• Don’t forget to bring in the System.Data.SqlClient namespace. You can use NuGet for this.
• Lastly, when invoking the ExecuteScalar() command, be sure to cast it to a string (in our case).

private static string GetEmployeeNameByID(int DepID)
        {
            string retVal = string.Empty;
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT dbo.udf_GetEmployeeByID(@DepartmentId)", connection))
                    {
                        connection.Open();
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@DepartmentID", DepID);
                        retVal = (string)cmd.ExecuteScalar();
                    }
                }
                catch (SqlException ex)
                {
                    ex.Message.ToString();
                }
                return retVal;
                
            }
        }

And now let’s put the code to test, by writing it out to a console.

static void Main(string[] args)
        {

            string name = GetEmployeeNameByID(1);
            Console.WriteLine(name);
            Console.ReadLine();
        }

Output:

Hope, you found this segment useful!