Incorporate SQL Server UDFs in your .NET applications

by Tony Patton | Oct 18, 2005 2:38:00 PM

Tags: .NET, Databases, Middleware, Tony Patton, Database interaction, User Defined Function, Microsoft SQL Server Transact-SQL, Microsoft .NET, Microsoft SQL Server, .NET Newsletter

Takeaway: User Defined Functions (UDFs) are a powerful feature that every developer who works with SQL Server needs to know. Learn how to incorporate UDFs in your next .NET project.

Database interaction is a standard feature of most .NET applications, with SQL Server as the popular choice for the data tier. Consequently, .NET developers often find themselves developing T-SQL code within their applications or stored procedures on the database server.

Developers often overlook a number of the features in the T-SQL language. For instance, when I used T-SQL's User Defined Function (UDF) feature in a recent application, I was surprised by the lack of understanding of this feature by other developers on the team. With that in mind, I want to take this opportunity to provide an overview of the UDF feature and explain how you can incorporate it in your .NET applications.

What is a UDF?

UDFs, which were introduced with SQL Server 2000, allow the creation of T-SQL functions. These functions may be embedded in other SQL statements, and they can return one of two data types: scalar values or a table. Scalar values are single values that correspond to T-SQL data types like varchar and int. Tables include a set or rows that include one or more column values. Just like stored procedures, they may accept parameters.

If you're using SQL Server Enterprise Manager, each database contains a UDFs item within the user interface. You may right-click on this item to create a new UDF by selecting New User Defined Function. Likewise, T-SQL code may be entered via the Query Analyzer client to create a new function. The latter method is used in this article.

UDFs may be invoked from a query like built-in functions. Also, they may be executed like stored procedures via the execute statement. Let's take a closer look at creating your own UDF. We begin with scalar values.

Scalar values

The following code sample demonstrates a scalar UDF that returns the date a database was created.

CREATE FUNCTION dbo.UDF_DatabaseCreationDate 

(@databaseNamesysname)

RETURNS datetime

AS

BEGIN

DECLARE @creationDatedatetime

SELECT @creationDate = crdate FROM master.dbo.sysdatabases

WHERE name = @databaseName

RETURN (@creationDate)

END

The following T-SQL statement may be used to execute this function. Notice that the name of the popular Northwind database is passed to the UDF:

SELECT dbo.UDF_DatabaseCreationDate ('Northwind')

It returns the following datetime value on my machine:

2000-08-06 01:41:00.310

Furthermore, a function could be used to return an employee's full name—the concatenation of first and last names—given the employee id. The following T-SQL creates this function in the Northwind database:

CREATE FUNCTION dbo.UDF_GetEmployeeName

(@employeeID As int)

RETURNS nvarchar(40)

BEGIN 

DECLARE @fullName As nvarchar(40)

SELECT @fullName = FirstName + ' ' + LastName

FROM Employees

WHERE EmployeeID = @employeeID

RETURN (@fullName)

END

This function could be utilized within an ASP.NET page to return an employee's name. The ASP.NET code in Listing A accepts an employee id number via a querystring variable. The result is that the employee's name is displayed on the page via the UDF call.

I load this ASP.NET page on my local machine using the following URL:

http://localhost/UDF1VB.aspx?empID=1

The name is displayed in the text box. Listing B contains the equivalent C# code. Let's move forward by working with UDFs that return tables.

Table values

UDFs may return results as tables in the following manner:

• Inline table-valued functions return the result of a single SELECT statement.
• Multi-statement table-valued functions return a table constructed via numerous T-SQL statements.

We could use our full name UDF inside a stored procedure that returns information on all employees in the Northwind employees table. The following stored procedure uses the UDF to construct its result set:

CREATE PROCEDURE dbo.ReturnAllEmployees
AS
BEGIN
SELECT dbo.GetEmployeeName(e.EmployeeID) As FullName,
Address, City, PostalCode
FROM Employees e
END

The UDF is called inline within another T-SQL statement. The neat aspect of this approach is the employee id from the current row is passed to the function, so the full name is easily constructed for each row returned by the stored procedure. The ASP.NET in Listing C displays the results of this stored procedure in a DataGrid control. Listing D contains the equivalent C#.

The code connects to SQL Server and calls the stored procedure. The stored procedure's result set is stored in a SQLDataReader object. It is used as the data source for the ASP.NET DataGrid control. The control's AutoGenerateColumns property is set so the columns are automatically created. The results are displayed in a simple table with the UDF's result in the first column.

UDFs add value to your .NET

While UDFs are a SQL Server feature rather than a .NET feature, every developer who works with SQL Server needs to know about UDFs. This valuable feature can be used to simplify stored procedures or provide simple functionality that may be easily utilized in your .NET code.

Print/View all Posts Comments on this article

The article shows a misuse of UDFsanovick@...  | 10/20/05

Is this misusing UDF's?Andrew Houghton  | 10/21/05

Yes, but how much dependsTony Hopkinson  | 10/21/05

Not working for mehbranyan  | 10/20/05

Function name changedbadhair@...  | 10/20/05

No, I'm not using the same functions as in the ...hbranyan  | 10/21/05