Currency 類型及轉換函數
此範例會使用 C# 來定義 Currency 使用者定義資料類型。 這個使用者定義資料類型會封裝金額和文化特性，這樣做有助決定將金額轉譯成該文化特性中之貨幣值的正確方式。 此範例也會提供貨幣轉換函數，該函數會傳回 Currency 使用者定義資料類型的執行個體。 如果 AdventureWorks 資料庫具有從美元 (USD) 到與指定文化特性相關聯之貨幣的轉換比率，則轉換函數會傳回具有轉換比率和符合文化特性要求之文化特性的 Currency 使用者定義資料類型。 否則，會傳回具有使用 en-us 文化特性以 USD 計算之原始金額的 Currency 使用者定義資料類型。 此範例還會示範如何使用 Transact-SQL 取消註冊和註冊 Common Language Runtime (CLR) 方法與組件。
.gif "警告注意事項") 注意 |
|---|
在此範例中使用的匯率是虛構的，不可用於實際的財務交易。 |
必要條件
若要建立並執行這個專案，您必須安裝下列軟體：
SQL Server 或 SQL Server Express。 您可以從 SQL Server Express 文件集和範例網站免費取得 SQL Server Express。
您可以從 SQL Server 開發人員網站取得 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
GO
RECONFIGURE
GO
[!附註]
若要啟用 CLR 整合，您必須擁有 ALTER SETTINGS 伺服器層級權限，此權限是由系統管理員 (sysadmin) 和伺服器管理員 (serveradmin) 固定伺服器角色的成員以隱含方式持有。
AdventureWorks 資料庫必須安裝在您所使用的 SQL Server 執行個體上。
如果您不是正在使用之 SQL Server 執行個體的管理員，則必須讓管理員授與您 CreateAssembly 權限來完成安裝。
建立範例
使用下列指示來建立並執行範例：
開啟 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
範例程式碼
下面是此範例的程式碼清單。
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));
}
}
}
這是 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