ADO.NET 3.5 Cookbook, 2nd Edition

3.25. Executing a SQL Server Scalar-Valued Function

Problem

Your SQL Server database includes a user-defined function that returns a scalar value. You want to retrieve the value from this function using ADO.NET.

Solution

Invoke the function as you would a query or stored procedure.

The solution uses a single SQL Server function, shown in Example 3-36:

ExtendedPrice

Calculates and returns the extended price for an order line item based on the unit price, quantity, and discount.

Example 3-36. SQL Server scalar-valued function: ExtendedPrice

CREATE FUNCTION ExtendedPrice
(
    @UnitPrice money,
    @Quantity smallint,
    @Discount real
)
RETURNS money
AS

BEGIN
    RETURN (@UnitPrice * (1 - @Discount)* @Quantity)
END

The solution defines a SQL statement that uses the ExtendedPrice user-defined scalar-valued function. The statement is used by a DataAdapter to fill a DataTable with the first five records from the Sales.SalesOrderDetail table in AdventureWorks together with the extended price for the record calculated using the scalar-valued function for each record. The value calculated by the scalar-valued function is the same as that calculated by the computed field LineTotal in the Sales.SalesOrderDetail table.

The C# code in Program.cs in the project ExecuteUserDefinedScalarValuedFunction is shown in Example 3-37.

Example 3-37. File: Program.cs for ExecuteUserDefinedScalarValuedFunction solution

using System; using System.Data; using System.Data.SqlClient; namespace ExecuteUserDefinedScalarValuedFunction { ...