Visual Studio Magazine - Execute User-Defined Functions From ADO.NET

Execute User-Defined Functions From ADO.NET
by Roman Rehak

Enterprise Application Lifecycle Management 2003

Technology Toolbox: SQL Server, ADO.NET

Level: Intermediate

For some reason, neither the ADO.NET documentation nor many of the available ADO.NET books sufficiently discuss the topic of executing SQL Server 2000 user-defined functions (UDFs) from ADO.NET. In the majority of cases, UDFs are executed from within T-SQL code, but there are occasions when you need to invoke a UDF from a client application. I'll show the options for executing both scalar and table-valued UDFs from ADO.NET code and provide recommendations for best practices. My ADO.NET code in this article uses the SQL Server .NET provider; you can modify the code easily to use the OLE DB .NET provider instead.

Scalar UDFs usually accept one or more parameters and return a single value. For my ADO.NET code examples, I created this scalar function in the Northwind database:

CREATE FUNCTION GetOrderTotal(@OrderID
int)
RETURNS money AS
BEGIN
RETURN
   (SELECT CAST(
   SUM(UnitPrice * Quantity *
   (1 - Discount)) AS money)
   FROM [Order Details]
   WHERE OrderID = @OrderID)
END

This function is of the scalar type, so the most obvious choice for many developers is to try using the ExecuteScalar() method of the SqlCommand object. The following ADO.NET code shows you how to set up a Command object and invoke the GetOrderTotal() function using ExecuteScalar(). The code here and elsewhere in the article assumes you have an opened connection (cn) to the Northwind database where you created the function:

Dim strSQL = _
   "SELECT dbo.GetOrderTotal(@OrderID)"
Dim cmd As SqlCommand = New _
   SqlCommand(strSQL, cn)
cmd.Parameters.Add(New _
   SqlParameter("@OrderID", _
   SqlDbType.Int)).Value = 10258
Dim Total As Double = _
   cmd.ExecuteScalar()

The SqlCommand object uses the default Text command type. The command text contains a single SELECT statement and a named parameter called @OrderID. The schema prefix, dbo in this case, is required for scalar UDFs. You could alternately hardcode parameter values into the command text instead of creating a SqlParameter object, but for many reasons it is not considered the best practice and it is recommended to create a parameterized query instead.

The code I just showed you works; however, it's not the fastest way to execute a scalar UDF. If you trace the call with SQL Server Profiler, you'll notice that the command gets executed on the server as dynamic SQL, using the sp_executesql stored procedure. Executing dynamic SQL is almost always slower than executing compiled SQL code. A faster way to execute a scalar UDF from ADO.NET is to call the function the same way you call a stored procedure. When you call a UDF as if it were a stored procedure, you set the command type to StoredProcedure and in addition to creating input parameters, you also define a return parameter:

Dim cmd As SqlCommand = New _
   SqlCommand("dbo.GetOrderTotal", cn)
cmd.CommandType = _
   CommandType.StoredProcedure
cmd.Parameters.Add(New _
   SqlParameter("@OrderID", _
   SqlDbType.Int)).Value = 10258
Dim ReturnValue As SqlParameter = New _
   SqlParameter("@Total", _
   SqlDbType.Money)
ReturnValue.Direction = _
   ParameterDirection.ReturnValue
cmd.Parameters.Add(ReturnValue)
cmd.ExecuteNonQuery()
MsgBox(ReturnValue.Value)

After you call the function with the SqlCommand.ExecuteNonQuery() method, ADO.NET populates the Value property of the return parameter with the value returned by the function. Based on my benchmarks, this technique seems to be about 25 percent faster than executing a UDF as a SQL query using the SqlCommand.ExecuteScalar() method.

Table-valued UDFs return a single table, so in many ways you can compare them to database views. Executing a table-valued UDF from ADO.NET is identical to executing a view or a single SELECT statement. Start by creating a table-valued UDF called fn_CustomerNamesInRegion; it is also available in SQL Server 2000 Books Online:

CREATE FUNCTION fn_CustomerNamesInRegion
(@RegionParameter nvarchar(30))
RETURNS table
AS
RETURN
   (SELECT CustomerID, CompanyName
   FROM Northwind.dbo.Customers
   WHERE Region = @RegionParameter)

Now you can call the function from ADO.NET with this code:

Dim strSQL = "SELECT * FROM " & _
   "fn_CustomerNamesInRegion" & _
   "(@RegionParameter)"
Dim cmd As SqlCommand = New _
   SqlCommand(strSQL, cn)
cmd.Parameters.Add(New _
   SqlParameter("@RegionParameter", _
   SqlDbType.NVarChar,30)).Value = "WA"
Dim da As SqlDataAdapter = New _
   SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
da.Fill(dt)

The code creates a parameterized query, calls the fn_ CustomerNamesInRegion function, and populates a DataTable object. Instead of populating a DataTable or a DataSet object, you can also create a DataReader object and execute the SqlCommand.ExecuteReader() method.

About the Author
Roman Rehak, MCSD, MCDBA, MCSA, is a senior developer at Competitive Computing, a Microsoft Gold Certified Partner for eCommerce in Colchester, Vt. He specializes in database and mobile application development using Microsoft technologies. He regularly authors magazine articles and speaks at SQL Server conferences in the United States and Canada. Reach him at .