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"]);
DataTable dt = new DataTable( );
da.Fill(dt);

// Bind the default view for the table to the grid.
resultDataGrid.DataSource = dt;

Discussion

A user-defined scalar function is a SQL routine that accepts one or more scalar input parameters and returns a single value. A user-defined scalar function is invoked from a query or executed like a stored procedure using an EXECUTE statement.

You can invoke scalar functions where scalar expressions can be used. To invoke a scalar function, use the following syntax:

[databaseName.]ownerName.functionName([argument1][, . . . ])

In the solution code, the ExtendedPrice function is called as shown by:

dbo.ExtendedPrice(UnitPrice, Quantity, Discount)

This calculates the extended price for each row in the Order Details table based on the UnitPrice, Quantity, and Discount values. The result is returned as a result set column named ExtendedPrice.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax

show all menu



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net