Currency Type and Conversion Function
Applies To: SQL Server 2016 Preview
This example defines a Currency user-defined data type by using C#. This user-defined data type encapsulates both an amount and a culture that helps to determine the correct way to render the amount as a currency value in that culture. This example also provides a currency conversion function that returns an instance of the Currency user-defined data type. If the AdventureWorks database has a conversion rate from U.S. dollars (USD) to the currency that is associated with the specified culture, the conversion function returns a Currency user-defined data type with the converted rate and a culture that matches the culture requested. Otherwise, a Currency user-defined data type is returned with the original amount, which should be in USD, with the en-us culture. The example also demonstrates how to unregister and register common language runtime (CLR) methods and assemblies by using Transact-SQL.
Warning
The exchange rates used in this sample are fictitious and should not be used for actual financial transactions.
Prerequisites
To create and run this project the following the following software must be installed:
SQL Server or SQL Server Express. You can obtain SQL Server Express free of charge from the SQL Server Express Documentation and Samples Web site
The AdventureWorks database that is available at the SQL Server Developer Web site
.NET Framework SDK 2.0 or later or Microsoft Visual Studio 2005 or later. You can obtain .NET Framework SDK free of charge.
In addition, the following conditions must be met:
The SQL Server instance you are using must have CLR integration enabled.
In order to enable CLR integration, perform the following steps:
Enabling CLR Integration
- Execute the following Transact-SQL commands:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Note
To enable CLR, you must have ALTER SETTINGS server level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles.
The AdventureWorks database must be installed on the SQL Server instance you are using.
If you are not an administrator for the SQL Server instance you are using, you must have an administrator grant you CreateAssembly permission to complete the installation.
Building the Sample
Create and run the sample by using the following instructions:
Open a Visual Studio or .NET Framework command prompt.
If necessary, create a directory for your sample. For this example, we will use C:\MySample.
In c:\MySample, create Currency.cs and copy the C# sample code (below) into the file.
Compile the sample code from the command line prompt by executing:
- 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
Copy the Transact-SQL installation code into a file and save it as Install.sql in the sample directory.
If the sample is installed in a directory other then C:\MySample\, edit the file Install.sql as indicated to point to that location.
Deploy the assembly and stored procedure by executing
- sqlcmd -E -I -i install.sql
Copy Transact-SQL test command script into a file and save it as test.sql in the sample directory.
Execute the test script with the following command
- sqlcmd -E -I -i test.sql
Copy the Transact-SQL cleanup script into a file and save it as cleanup.sql in the sample directory.
Execute the script with the following command
- sqlcmd -E -I -i cleanup.sql
Sample Code
The following are the code listings for this sample.
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));
}
}
}
This is the Transact-SQL installation script (Install.sql), which deploys the assembly and creates the stored procedure in the 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;
This is test.sql, which tests the sample by executing the functions.
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();
The following Transact-SQL removes the assembly, type and functions from the database.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
See Also
Usage Scenarios and Examples for Common Language Runtime (CLR) Integration