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"]); ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.