Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
[ Team LiB ] Recipe 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. | Team LiB Recipe 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 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.
