Currency-Typ und Konvertierungsfunktion

Artikel
05/07/2013

In diesem Beispiel wird ein benutzerdefinierter Currency-Datentyp mithilfe von C# definiert. Dieser benutzerdefinierte Datentyp kapselt sowohl einen Betrag als auch ein Gebietsschema ein, um die richtige Wiedergabe des Betrags als Währungswert in diesem Gebietsschema zu bestimmen. Dieses Beispiel stellt außerdem eine Währungsumrechnungsfunktion bereit, die eine Instanz des benutzerdefinierten Currency-Typs zurückgibt. Wenn die AdventureWorks-Datenbank über einen Umrechnungskurs von US-Dollar (USD) in die Währung der angegebenen Kultur verfügt, gibt die Umrechnungsfunktion einen benutzerdefinierten Currency-Datentyp mit dem konvertierten Kurs und einer Kultur zurück, die der angeforderten Kultur entspricht. Andernfalls wird ein benutzerdefinierter Currency-Datentyp mit dem ursprünglichen Betrag zurückgegeben, d. h. ein USD-Betrag mit der Kultur en-us. Das Beispiel veranschaulicht auch, wie Sie mithilfe von Transact-SQL CLR (Common Language Runtime)-Methoden und Assemblys registrieren und die Registrierung von CLR-Methoden und Assemblys aufheben können.

Vorsicht
Die in diesem Beispiel verwendeten Wechselkurse sind frei erfunden und sollten nicht für tatsächliche finanzielle Transaktionen verwendet werden.

Voraussetzungen

Zum Erstellen und Ausführen dieses Projekts muss die folgende Software installiert sein:

SQL Server oder SQL Server Express. SQL Server Express erhalten Sie kostenlos auf der Website mit der Dokumentation und den Beispielen für SQL Server Express.
Die AdventureWorks-Datenbank, die auf der SQL Server Developer-Website zur Verfügung gestellt wird.
.NET Framework SDK 2.0 oder höher oder Microsoft Visual Studio 2005 oder höher. Das .NET Framework SDK ist kostenlos erhältlich.
Außerdem müssen die folgenden Bedingungen erfüllt sein:
In der von Ihnen verwendeten SQL Server-Instanz muss die CLR-Integration aktiviert sein.
Führen Sie zum Aktivieren der CLR-Integration die folgenden Schritte aus:

Aktivieren der CLR-Integration
- Führen Sie die folgenden Transact-SQL-Befehle aus:
sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

Hinweis

Um CLR zu aktivieren, benötigen Sie die ALTER SETTINGS-Serverberechtigung, die Mitglieder der festen Serverrollen sysadmin und serveradmin implizit erhalten.
Die AdventureWorks-Datenbank muss in der von Ihnen verwendeten SQL Server-Instanz installiert sein.
Falls Sie kein Administrator für die verwendete SQL Server-Instanz sind, muss Ihnen ein Administrator die CreateAssembly -Berechtigung erteilen, damit Sie die Installation ausführen können.

Hinweis
Um CLR zu aktivieren, benötigen Sie die ALTER SETTINGS-Serverberechtigung, die Mitglieder der festen Serverrollen sysadmin und serveradmin implizit erhalten.

Erstellen des Beispiels

Verwenden Sie die folgenden Anweisungen, um das Beispiel zu erstellen und auszuführen:

Wechseln Sie zu einer Visual Studio- oder .NET Framework-Eingabeaufforderung.
Erstellen Sie ggf. ein Verzeichnis für das Beispiel. Für dieses Beispiel wird C:\MySample verwendet.
Erstellen Sie in c:\MySample die Datei Currency.cs, und kopieren Sie den C#-Beispielcode (unten) in die Datei.
Kompilieren Sie an der Eingabeaufforderung den Beispielcode, indem Sie Folgendes ausführen:
- 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
Kopieren Sie den Transact-SQL-Installationscode in eine Datei, und speichern Sie sie als Install.sql im Beispielverzeichnis.
Wenn das Beispiel in einem anderen Verzeichnis als C:\MySample\ installiert ist, bearbeiten Sie die Datei Install.sql wie gezeigt, damit sie auf diesen Speicherort zeigt.
Stellen Sie die Assembly und die gespeicherte Prozedur bereit, indem Sie die folgende Anweisung ausführen:
- sqlcmd -E -I -i install.sql
Kopieren Sie das Transact-SQL-Testbefehlsskript in eine Datei, und speichern Sie diese als test.sql im Beispielverzeichnis.
Führen Sie das Testskript mit dem folgenden Befehl aus:
- sqlcmd -E -I -i test.sql
Kopieren Sie das Transact-SQL-Bereinigungsskript in eine Datei, und speichern Sie diese als cleanup.sql im Beispielverzeichnis.
Führen Sie das Skript mit dem folgenden Befehl aus:
- sqlcmd -E -I -i cleanup.sql

Beispielcode

Die Codelistings für dieses Beispiel lauten wie folgt.

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));
            }
        }
    }

Dies ist das Transact-SQL-Installationsskript (Install.sql), das die Assembly bereitstellt und die gespeicherte Prozedur in der Datenbank erstellt.

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;

Dies ist die Datei test.sql, die das Beispiel durch das Ausführen der Funktionen testet.

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();

Im folgenden Transact-SQL-Code werden die Assembly, der Typ und die Funktionen aus der Datenbank entfernt.

USE AdventureWorks
GO

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

Siehe auch

Konzepte

Verwendungsszenarien und Beispiele für Common Language Runtime (CLR)-Integration

Freigeben über