Building C User-Defined Functions

Building C# User-Defined Functions

A function in SQL Server is just that: a function that can be invoked from within any T-SQL statement that will return a value. SQL Server has many built-in functions for string concatenation, mathematics, data type conversion, and much more. With the integration of the CLR into SQL Server 2005, you can now write your own user-defined functions in C#.

In many circumstances, you need to perform some bit of logic on a column or a couple of columns in order to provide a calculated column. What often happens in that situation is that either the logic is too difficult to represent in T-SQL or the calculation is just too slow, so developers leave the columns unaltered, and then perform the calculated column on the client side in C#. Now you can place that complex logic on the server, and gain a performance boost from not having to perform the calculations on the client.

To illustrate this, without getting bogged down in a complex calculation, the next example will create a C# user-defined function that takes a customer's first name, last name, and middle initial as parameters, and returns a string containing the appropriate full-name display.

Add a new user-defined function to your project by right-clicking the project, highlighting Add, and then clicking User-Defined Function. You'll be presented with an empty stub that looks similar to the empty stored procedure stub from the preceding section. However, this method starts off with a return type (functions cannot be void) and is decorated with the Microsoft.SqlServer.Server.SqlFunction attribute. The code in Listing 21.1 shows this name-formatting function.

Listing 21.1. C# User-Defined Function

using System; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString NameDisplay(     SqlString firstName,     SqlString lastName,     SqlString middleInitial) {     // we have access to the StringBuilder, a much better     // concatenator than string addition     StringBuilder sb = new StringBuilder();     sb.AppendFormat("{0}, {1} {2}",         lastName, firstName, middleInitial);     return new SqlString(sb.ToString()); } }; 

The important thing to note here isn't how easy it is to format names. The important thing to note is that you can pass any SQL data type (even the new XML data type) to your own C# function, perform some processing on it, and then return a value that can then be used in a SELECT statement by any code, whether it's someone running a query directly on the server or whether it is within your own C# client application.

If you modify the Test.sql script to contain the following UDF test code

select dbo.NameDisplay(c.FirstName, c.LastName, c.MiddleInitial) as FullName FROM Customers c 

You will see the following output (assuming you have a Customers table defined):

FullName ------------------------ Doe, John Q Doe, Jane R Smith, Bob E  Smith, Rob V   Smith, Lob L Customer, Jethro M Customer, Ringo R No rows affected. (7 row(s) returned) 

If you have been developing applications based on SQL Server for a long time, you can probably think of several uses for C# user-defined functions that can save you the time and headache of writing them in T-SQL, and also create potential performance improvements.