Tipo Currency e funzione di conversione
In questo esempio viene definito un tipo di dati Currency definito dall'utente mediante C#. Questo tipo di dati definito dall'utente incapsula un importo e impostazioni cultura che consentono di determinare il modo corretto per rappresentare l'importo come valuta del paese delle impostazioni cultura in oggetto. Nell'esempio viene inoltre fornita una funzione di conversione di valuta che restituisce un'istanza del tipo di dati Currency definito dall'utente. Se il database prevede un tasso di conversione da dollari US nella valuta associata alle impostazioni cultura specificata, la funzione di conversione restituisce un tipo di dati Currency definiti dall'utente con conversione e impostazioni cultura corrispondenti alle impostazioni cultura richieste. In caso contrario, viene restituito un tipo di dati Currency definito dall'utente con l'importo originale in dollari USA e con le impostazioni cultura en-us. Nell'esempio viene illustrato come registrare assembly e metodi CLR (Common Language Runtime) e annullarne la registrazione mediante Transact-SQL.
.gif "Nota di attenzione") Attenzione |
|---|
I tassi di cambio utilizzati nell'esempio sono fittizi e non devono essere utilizzati in transazioni finanziarie reali. |
Prerequisiti
Per creare ed eseguire questo progetto, è necessario installare il software seguente:
SQL Server o SQL Server Express. SQL Server Express è disponibile gratuitamente nel sito Web di documentazione ed esempi per SQL Server Express.
Database AdventureWorks, disponibile nel sito Web per sviluppatori di SQL Server.
.NET Framework SDK 2.0 o versione successiva oppure Microsoft Visual Studio 2005 o versione successiva. .NET Framework SDK è disponibile gratuitamente.
È necessario inoltre che siano soddisfatte le condizioni seguenti:
Per l'istanza di SQL Server utilizzata deve essere abilitata l'integrazione con CLR.
Per abilitare l'integrazione con CLR, effettuare le operazioni seguenti:
Abilitazione dell'integrazione con CLR
- Eseguire i comandi Transact-SQL seguenti:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
[!NOTA]
Per abilitare CLR, è necessario disporre dell'autorizzazione ALTER SETTINGS a livello di server, che viene assegnata implicitamente ai membri dei ruoli predefiniti del server sysadmin e serveradmin.
Il database AdventureWorks deve essere installato nell'istanza di SQL Server in uso.
Se non si dispone dei diritti di amministratore per l'istanza di SQL Server in uso, è necessario ricevere da un amministratore l'autorizzazione CreateAssembly per completare l'installazione.
Compilazione dell'esempio
Creare ed eseguire l'esempio tramite le istruzioni seguenti:
Aprire un prompt dei comandi di .NET Framework o Visual Studio.
Se necessario, creare una directory per l'esempio. Per questo esempio verrà utilizzata la directory C:\MySample.
In c:\MySample creare Currency.cs e copiare il codice C# di esempio, riportato di seguito, nel file.
Compilare il codice di esempio dal prompt della riga di comando eseguendo:
- 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
Copiare il codice di installazione Transact-SQL in un file e salvarlo come Install.sql nella directory dell'esempio.
Se l'esempio è installato in una directory diversa da C:\MySample\, modificare il file Install.sql come indicato, in modo che punti al percorso appropriato.
Distribuire l'assembly e la stored procedure eseguendo
- sqlcmd -E -I -i install.sql
Copiare lo script di comandi di test Transact-SQL in un file e salvarlo come test.sql nella directory dell'esempio.
Eseguire lo script di test con il comando seguente
- sqlcmd -E -I -i test.sql
Copiare lo script di pulizia Transact-SQL in un file e salvarlo come cleanup.sql nella directory dell'esempio.
Eseguire lo script con il comando seguente
- sqlcmd -E -I -i cleanup.sql
Codice di esempio
Di seguito sono illustrati i listati di codice per l'esempio.
C#
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));
}
}
}
Di seguito è illustrato lo script di installazione Transact-SQL (Install.sql) che consente la distribuzione dell'assembly e la creazione della stored procedure nel database.
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;
Di seguito è illustrato lo script test.sql che consente di testare l'esempio eseguendo le funzioni.
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();
Il codice Transact-SQL seguente consente di rimuovere l'assembly, il tipo e le funzioni dal database.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
Vedere anche
Concetti
Scenari di utilizzo ed esempi per l'integrazione con CLR (Common Language Runtime)