O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

2.13. Executing SQL Server User-Defined Scalar Functions

Problem

Your SQL Server 2000 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 sample code, as shown in Example 2-16, uses a single SQL Server function:

ExtendedPrice

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

Example 2-16. SQL Server function: ExtendedPrice

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

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

The sample code defines a SQL statement that uses the ExtendedPrice user-defined function. The statement is used by a DataAdapter to fill a DataTable with all records from the Order Details table in Northwind together with the extended price calculation for each record. The default view of the table is bound to a data grid on the form.

The C# code is shown in Example 2-17.

Example 2-17. File: ScalarFunctionForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // . . . String sqlText = "SELECT *, " + "dbo.ExtendedPrice(UnitPrice, Quantity, Discount) ExtendedPrice " + "FROM [Order Details]"; // Create DataAdapter and fill the table. SqlDataAdapter da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_ConnectString"]); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required