Currency User Defined Type and the ConvertCurrency User Defined Function

Description: The Currency user defined type is used to represent an amount of money in a specific culture's form of money. This class is used when the application needs to represent 12 Dollars, or 15 Euros, for example. Using a single object simplifies the code necessary to manage the correct formatting of the currency when it is displayed to users while giving the application access to the numerical quantities and culture objects when that it useful.

Implementation Notes:  The Currency user defined type is implemented in managed code which is installed in SQL Server. The ConvertCurrency user defined function creates instances of this user defined type when currency conversion is necessary between the standard currency used in the Adventure Works Cycles database tables (US Dollars) to a different currency requested by a user of the storefront. If the conversion is successful, the currency is expressed in the requested form of money. If there is not sufficient data to perform the conversion, the currency is expressed in US Dollars.

In the previous version of the storefront, code was scattered about the stored procedures which would determine the applicable currency conversion rate, convert the currency, and return the amount and currency culture information back to the caller as two separate paramaters. The middle tier would then potentially have to walk through a data set to ensure that the correct currency symbol would be displayed in the user interface. In this version of the storefront, the currency conversion is centralized into a single function, and the middle tier does not have to modify any values being returned by the data tier. This means that SqlDataReaders can be used for databinding in some cases instead of DataSets, which means a lower memory footprint on the server and better performance.

Using a managed user defined type for currency has simplified the code, simplified the interfaces between components, improved the reliability and maintability of the application, and improved the performance of the application.