通貨型と変換関数

[アーティクル]
05/07/2013

この例では、C# を使用して Currency ユーザー定義データ型を定義します。このユーザー定義データ型は、価格とカルチャをカプセル化し、そのカルチャの通貨の値で正しい価格を表示する方法を決定するために役立ちます。この例ではさらに、Currency ユーザー定義データ型のインスタンスを返す通貨換算関数を提供します。 U.S. ドル (USD) を指定のカルチャに関連付けられている通貨に換算するための換算レートが AdventureWorks データベースに含まれている場合、通貨換算関数は、換算済みレートを格納した Currency ユーザー定義データ型と、要求されたカルチャに一致するカルチャを返します。換算レートがデータベースに含まれていない場合は、USD で表される元の価格を格納した Currency ユーザー定義データ型と、en-us カルチャを返します。この例は、Transact-SQL を使用した共通言語ランタイム (CLR) メソッドとアセンブリの登録解除と登録の方法も示しています。

注意
このサンプルで使用する換算レートは架空のものです。実際の財務トランザクションには使用しないでください。

必要条件

このプロジェクトを作成して実行するには、次のソフトウェアがインストールされている必要があります。

SQL Server または SQL Server Express。 SQL Server Express は、SQL Server Express ドキュメントとサンプルの Web サイトから無償で入手できます。
SQL Server デベロッパー Web サイトから入手できる AdventureWorks データベース。
.NET Framework SDK 2.0 以降または Microsoft Visual Studio 2005 以降。 .NET Framework SDK は無償で入手できます。
また、次の条件を満たしている必要があります。
使用している SQL Server インスタンスで CLR 統合が有効になっている必要があります。

CLR 統合を有効にするには、次の手順に従います。

CLR 統合の有効化

以下の Transact-SQL コマンドを実行します。

sp_configure 'clr enabled', 1

RECONFIGURE

注
CLR 統合を有効にするには、サーバーレベルの ALTER SETTINGS 権限が必要です。この権限は、sysadmin 固定サーバーロールおよび serveradmin 固定サーバーロールのメンバーには暗黙に許可されています。

使用している SQL Server インスタンスに AdventureWorks データベースがインストールされている必要があります。
使用している SQL Server インスタンスの管理者でない場合、インストールを完了するには、権限が管理者から許可されている必要があります。

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

Visual Studio または .NET Framework のコマンドプロンプトを開きます。
必要な場合は、サンプル用のディレクトリを作成します。この例では C:\MySample を使用します。
c:\MySample で、Currency.cs を作成し、C# サンプルコード (下記) をこのファイルにコピーします。
次のコマンドをコマンドプロンプトで実行して、サンプルコードをコンパイルします。
- Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library Currency.cs
Transact-SQL インストールコードをファイルにコピーし、Install.sql としてサンプルディレクトリに保存します。
サンプルが C:\MySample\ 以外のディレクトリにインストールされている場合は、その場所を示すように、ファイル Install.sql を編集します。
次のコマンドを実行して、アセンブリとストアドプロシージャを配置します。
- sqlcmd -E -I -i install.sql
Transact-SQL テストコマンドスクリプトをファイルにコピーし、test.sql としてサンプルディレクトリに保存します。
次のコマンドを使用してテストスクリプトを実行します。
- sqlcmd -E -I -i test.sql
Transact-SQL クリーンアップスクリプトをファイルにコピーし、cleanup.sql としてサンプルディレクトリに保存します。
次のコマンドを使用してこのスクリプトを実行します。
- sqlcmd -E -I -i cleanup.sql

サンプルコード

このサンプルのコードリストを次に示します。

using System;
using System.Globalization;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.IO;
using System.Data.SqlClient;

    /// <summary>
    ///Defines a class for handing particular amounts of money in a 
    ///particular culture's monetary system.  This class is exposed as 
    ///a SQL Server UDT.
/// 
///Note that we are implementing IComparable to affect comparison behavior 
///only within the CLR.  This does not affect how SQL Server will compare the
///     the types.  How SQL Server will compare the type is determined by the Write 
///method on IBinarySerialize.
    /// </summary>
[Serializable]
    [SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 32)]
    public struct Currency : INullable, IComparable, IBinarySerialize
    {
        const string nullMarker = "\0\0\0\0\0\0\0\0\0\0";
        const int cultureNameMaxSize = 10;

        private string cultureName;//Who issued the money (en-us, for example)

        private CultureInfo culture;//The object which represents cultureName

        private decimal currencyValue;//The amount of money

        // Public properties for private fields
        public CultureInfo Culture
        {
            get
            {
                //A culture name is required.  If not present the entire object is considered null.
                if (cultureName == null) return null;

                //If we've got a cached copy of the culture return it.
                if (culture != null) return culture;

                //Otherwise, set the cache and return the culture for the culture name specified.
                culture = CultureInfo.CreateSpecificCulture(cultureName);
                return culture;
            }
        }

        // Public property for the private field.
        public decimal CurrencyValue
        {
            get
            {
                return currencyValue;
            }
        }

        // Constructors for when we have the culture or the name of the culture

        public Currency(CultureInfo culture, decimal currencyValue)
        {
if (culture == null) throw new ArgumentNullException("culture");
            this.cultureName = culture.Name;
            this.culture = culture;
            this.currencyValue = currencyValue;
        }

        public Currency(string cultureName, decimal currencyValue)
        {
            this.cultureName = cultureName;
            this.culture = null;
            this.currencyValue = currencyValue;
        }

        //Return the string representation for the currency, including the currency symbol.
        [SqlMethod(IsDeterministic = true,
            IsPrecise = true, DataAccess = DataAccessKind.None,
            SystemDataAccess = SystemDataAccessKind.None)]
        public override string ToString()
        {
            if (this.Culture == null) return "null";

            return String.Format(this.Culture, "{0:c}", currencyValue);
        }

        //The entire value of the currency is considered null if the culture name is null
        public bool IsNull
        {
            get
            {
                return cultureName == null;
            }
        }

        //The no-argument constructor makes a null currency.
        public static Currency Null
        {
            get
            {
                Currency h = new Currency((String)null, 0);

                return h;
            }
        }

        //Be sure to set the current UI culture before using this method! Even better, provide the culture
        //specifically (for the method after this one).
        [SqlMethod(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
        public static Currency Parse(SqlString sqlString)
        {
            return ParseWithCulture(sqlString, CultureInfo.CurrentUICulture);
        }

        public static Currency ParseWithCulture(SqlString sqlString, CultureInfo culture)
        {
            if (sqlString.IsNull 
|| (string.Compare(sqlString.Value, "null", true, CultureInfo.CurrentUICulture) == 0))
                return Currency.Null;

            int digitPos = -1;
            string stringValue = sqlString.Value;

            while (digitPos < stringValue.Length 
                && !Char.IsDigit(stringValue, ++digitPos))
            {
            }

            if (digitPos < stringValue.Length)
                return new Currency(culture, decimal.Parse(
                    stringValue.Substring(digitPos), culture));

            return Currency.Null;
        }

        public override int GetHashCode()
        {
            if (this.IsNull)
                return 0;

            return this.ToString().GetHashCode();
        }

//Note: This only affects the behavior of CLR, not SQL Server.  Comparisions
//for SQL Server will be determined by the Write method below.

public int CompareTo(object obj)
        {
            if (obj == null)
                return 1; //by definition

            if (obj == null || !(obj is Currency))
                throw new ArgumentException(
                    "the argument to compare is not a Currency");

            Currency c = (Currency)obj;

            if (this.IsNull)
            {
                if (c.IsNull)
                    return 0;

                return -1;
            }

            if (c.IsNull)
                return 1;

            string thisCultureName = this.Culture.Name;
            string otherCultureName = c.Culture.Name;
            if (!thisCultureName.Equals(otherCultureName))
                return thisCultureName.CompareTo(otherCultureName);
            return this.CurrencyValue.CompareTo(c.CurrencyValue);
        }
        // IBinarySerialize methods
        // The binary layout is as follow:
        //    Bytes 0 - 19:Culture name, padded to the right with null characters, UTF-16 encoded
        //    Bytes 20+:Decimal value of money
        // If the culture name is empty, the currency is null.
       
        public void Write(System.IO.BinaryWriter w)
        {
if (w == null) throw new ArgumentNullException("w");
            if (this.IsNull)
            {
                w.Write(nullMarker);
                w.Write((decimal)0);
                return;
            }

            if (cultureName.Length > cultureNameMaxSize)
            {
                throw new ApplicationException(string.Format(
                    CultureInfo.InvariantCulture, 
                    "{0} is an invalid culture name for currency as it is too long.", 
                    cultureNameMaxSize));
            }

            String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
            for (int i = 0; i < cultureNameMaxSize; i++)
            {
                w.Write(paddedName[i]);
            }

            // Normalize decimal value to two places
            currencyValue = Decimal.Floor(currencyValue * 100) / 100;
            w.Write(currencyValue);
        }
        public void Read(System.IO.BinaryReader r)
        {
            char[] name = r.ReadChars(cultureNameMaxSize);
            int stringEnd = Array.IndexOf(name, '\0');

            if (stringEnd == 0)
            {
                cultureName = null;
                return;
            }

            cultureName = new String(name, 0, stringEnd);
            currencyValue = r.ReadDecimal();
        }
    }


    /// <summary>
    /// This class is used to compute the value of US money a given region.
    /// </summary>
    public sealed class CurrencyConverter
    {
        // Classes with only static members should not be instantiable
        private CurrencyConverter()
        {
        }

        private static readonly CultureInfo USCulture = CultureInfo.CreateSpecificCulture("en-us");

        /// <summary>
        ///Computes the value of a certain amount of money in the USA in a different region.
        /// </summary>
        /// <param name="fromAmount">The quantity of money</param>
        /// <param name="toCultureName">A culture which is a member of the region of interest</param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
        public static Currency ConvertCurrency(SqlMoney fromAmount, SqlString toCultureName, SqlDateTime when)
        {
            CultureInfo toCulture = CultureInfo.CreateSpecificCulture(toCultureName.Value);

            if (toCulture.Equals(USCulture))
            {
                Currency c = new Currency(USCulture, (decimal)fromAmount);
                return c;
            }

            String toCurrencyCode = new RegionInfo(toCulture.LCID).ISOCurrencySymbol;

            // Find the rate closest to the specified date

            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {

                SqlCommand command = conn.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "usp_LookupConversionRate";

                SqlParameter onDateParameter
                    = new SqlParameter("@OnDate", SqlDbType.DateTime);
                onDateParameter.Value = when;
                command.Parameters.Add(onDateParameter);

                SqlParameter toCurrencyCodeParameter
                    = new SqlParameter("@ToCurrencyCode", SqlDbType.NChar, 3);
                toCurrencyCodeParameter.Value = toCurrencyCode;
                command.Parameters.Add(toCurrencyCodeParameter);

                SqlParameter resultParameter
                    = new SqlParameter("@Result", SqlDbType.Decimal);
                resultParameter.Precision = 10;
                resultParameter.Scale = 4;
                resultParameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(resultParameter);

                conn.Open();
                command.ExecuteNonQuery();

                decimal conversionFactor;

                if (resultParameter.Value is decimal)
                {
                    conversionFactor = (decimal)(resultParameter.Value);
                }
                else
                {
                    conversionFactor = 1.0M;
                    toCulture = USCulture;
                }

                return new Currency(toCulture, ((decimal)fromAmount * conversionFactor));
            }
        }
    }

次の Transact-SQL インストールスクリプト (Install.sql) は、アセンブリを展開し、データベースにストアドプロシージャを作成します。

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO

IF EXISTS (SELECT * FROM sys.types WHERE [name] = N'Currency') 
DROP TYPE Currency;
GO

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Currency')
DROP ASSEMBLY Currency;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
DROP FUNCTION ConvertCurrency;
GO

-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
DECLARE @SamplesPath nvarchar(1024)
set @SamplesPath = 'C:\MySample\'
CREATE ASSEMBLY Currency 
FROM @SamplesPath + 'Currency.dll'
with permission_set = safe;

USE AdventureWorks
GO



CREATE TYPE Currency EXTERNAL NAME [Currency].[Currency];
GO

CREATE FUNCTION ConvertCurrency
(
@fromAmount AS money,
@toCultureName AS nvarchar(10),
@when as DateTime
)
RETURNS Currency
AS EXTERNAL NAME [Currency].[CurrencyConverter].ConvertCurrency;
GO

CREATE PROCEDURE usp_LookupConversionRate
(
@OnDate datetime,
@ToCurrencyCode nchar(3),
@Result decimal(10,4) OUTPUT
)
AS
BEGIN
--It is not permitted to perform certain side-effects in functions, and
--SET NOCOUNT is one of them.  Since this sproc is called from 
--the ConvertCurrency CLR UDF, we must not do that side-effect or
--there will be an error at runtime.
--SET NOCOUNT ON

SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate 
WHERE CurrencyRateDate <= @OnDate AND FromCurrencyCode = N'USD' 
AND ToCurrencyCode = @ToCurrencyCode 
ORDER BY CurrencyRateDate DESC);

IF (@Result IS NULL)
SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate 
WHERE CurrencyRateDate > @OnDate AND FromCurrencyCode = N'USD' 
AND ToCurrencyCode = @ToCurrencyCode
ORDER BY CurrencyRateDate ASC);
END;

次の test.sql は、関数を実行してサンプルをテストします。

use AdventureWorks
GO

DECLARE @TwoBitsEuro Currency;
SELECT @TwoBitsEuro = dbo.ConvertCurrency(CAST('.25' as money), 'FR-FR', GetDate());
PRINT '$0.25 in USD is equivalent to ' + @TwoBitsEuro.ToString();

次の Transact-SQL は、アセンブリ、型、および関数をデータベースから削除します。

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO

次の方法で共有

通貨型と変換関数

必要条件

CLR 統合の有効化

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

サンプルコード

関連項目

概念

その他のリソース

次の方法で共有

通貨型と変換関数

必要条件

CLR 統合の有効化

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

サンプル コード

関連項目

概念

その他のリソース

サンプルコード