fntest.cs

< SQL Server2005程序设计>

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction()]
    public static SqlString fnHelloWorld()
    {
        return new SqlString("Hello World");
    }

    [SqlFunction()]
    public static SqlDecimal fnToCelsius(SqlInt16 Fahrenheit)
    {
        return new SqlDecimal((((Int16)Fahrenheit) - 32) / 1.8);
    }


    [SqlFunction( 
        FillRowMethodName="FillTickerRow", 
        TableDefinition="TickerSymbol nvarchar(5), Value decimal")]
    public static System.Collections.IEnumerable fnPortfolioTable(SqlString TickersPacked)
    {
        string[] TickerSymbols;
        object[] RowArr = new object[2];
        object[] CompoundArray = new object[3];
        char[] parms = new char[1];

        parms[0] = ';';
        TickerSymbols = TickersPacked.Value.Split(parms);

        RowArr[0] = TickerSymbols[0];
        RowArr[1] = 1;
        CompoundArray[0] = RowArr;

        RowArr = new object[2];
        RowArr[0] = TickerSymbols[1];
        RowArr[1] = 2;
        CompoundArray[1] = RowArr;

        RowArr = new object[2];
        RowArr[0] = TickerSymbols[2];
        RowArr[1] = 3;
        CompoundArray[2] = RowArr;

        return CompoundArray;
    }

    public static void FillTickerRow(object row, ref SqlString TickerSymbol, ref SqlDecimal Value)
    {
        object[] rowarr = (object[])row;
        TickerSymbol = new SqlString((string)rowarr[0]);
        Value = new SqlDecimal(decimal.Parse(rowarr[1].ToString()));
    }

};