最近的项目中碰到一个问题，简单描述如下：
商家在发布产品信息的时候，存储的价格包括数字和单位两个字段。在产品汇总时，要以列表形式显示，列表要可以根据列名排序。
在按价格排序的时候碰到这样的问题：不同的单位之间是有汇率的，不是单纯的按照数字大小来排序。而且需求是，显示列表的时候价格单位要统一，具体显示由用户选择。而且还要能显示某一类产品的价格的最大值和最小值以进行对比。这样就需要根据汇率动态转换成统一单位再进行排序。汇率转化的soap api：http://xurrency.com/api.wsdl 。
首先想到的解决方式是：将所有数据读出来，根据汇率转换，得到新的列表，再绑定。但这样无法解决最大最小值的问题。
查找了一番资料，发现SQLSERVER CLR编程可以搞定这样的问题。用法就是在SQLSERVER里面调用由c#或者c++等语言编写的assembly。初步编写的程序集如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using CurrencyConverter.com.xurrency;
namespace CurrencyConverter
{
public class CurrencyConverter
{
public static string GetCurrency(string amout, string @base)
{
if (amout == null || @base == null)
 {
return "0";
}
            if (@base.ToLower() == "usd")
            {
                return amout;
            }
                string returnValue = "0";
       try
       {
                xurrency currency = new xurrency();
                Pair[] rates = currency.getValues("usd");
                foreach (Pair pair in rates)
                {
                    if (pair.Id == @base.ToLower())
                    {
                        returnValue = (float.Parse(amout) / pair.Value).ToString();
                        break;
                    }
                }
            }
            catch(Exception)
            {
                //
            }
            return returnValue;
        }
    }
}
com.xurrency是引用的ws的命名空间。这样只要传入一个价格数字和一个价格单位就能转换成对应的美元数字。当然这里用try catch只是为了不必要的远程读取数据判断是否是价格单位。
光这样肯定是不行的，还要进行一定的设置，右击-》属性-》生成-》生成序列化程序集，设置为开，因为这里引用了ws，所以要生成相应的序列化程序集。生成，就可以在项目bin/debug/目录下看到两个dll文件。 CurrencyConverter.dll，CurrencyConverter.XmlSerializers.dll 。将这两个程序集添加到SQLSERVER中。操作很简单，有图形操作界面，要注意的是CurrencyConverter.dll的安全级别要设置为 unsafe。当然这些操作之前，要先启用SQLSERVER的CLR集成。这可以在SQLSERVER功能外围配置里面操作，也可以SQL语句操作：
exec sp_configure 'clr enabled', 1
reconfigure
go
USE [database name]
GO
EXEC   sp_changedbowner 'sa'
ALTER   DATABASE   [database name]  SET   TRUSTWORTHY   ON;
完成这些之后，就可以添加SQL函数了：
CREATE FUNCTION [dbo].[F_GetCurrencyByUSD](@amout [nvarchar](10), @base [nvarchar](10))
RETURNS [nvarchar](10) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [CurrencyConverter(程序集名称)].[CurrencyConverter.CurrencyConverter(命名空间+类名)].[GetCurrency(方法名)]
有了这个函数之后就可以使用了(下面是一个实际项目中的存储过程，有点长)：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:        Jimmy
-- Create date:     2009 8 10
-- Description:        Get all dive courses by organization_id. Can search and sort.
-- =============================================
CREATE PROCEDURE [dbo].[P_GetDiveCourseBySearch]
(
    @pageSize INT = 10,
    @pageNum INT = 1,
    @skipSize INT = 0,
    @orgID INT = -1,
    @orderKey NVARCHAR(10) = '',
    @orderBy NVARCHAR(50) = ''
)
AS
BEGIN
    DECLARE @startSize int
    DECLARE @endSize int
    IF @pageSize<=0
        BEGIN
            SET @pageSize=5
            SET @endSize=5
        END

    IF @pageNum <=1--first page
        BEGIN
            SET @startSize=1
            SET @endSize=@pageSize
        END
    ELSE--not first page
        BEGIN
            SET @startSize=(@pageNum-1)*@pageSize+1
            SET @endSize=@startSize+@pageSize-1
        END  

    IF @skipSize >0
        BEGIN
            SET @startSize=@startSize+@skipSize
            SET @endSize=@startSize+@pageSize-1
        END

    DECLARE @str NVARCHAR(2000)
    DECLARE @temp NVARCHAR(500)
   
    SET @temp = ''
   
    IF @orderKey = ''
        BEGIN
            SET @orderKey = ' ASC '
        END
   
    IF @orderBy = ''
        BEGIN
            SET @orderBy = ' Organization_Name '
        END
   
    SET @temp = ' ORDER BY ' + @orderBy + ' ' + @orderKey
   
   

    SET @str = '

    SELECT
        *
    FROM
    (
        SELECT
            ROW_NUMBER() OVER (' + @temp + ') ROWNUMBER,
            *
        FROM
        (

            SELECT
                o.Organization_ID,
                c.Certification_ID,
                o.Organization_Name,
                c.Certification_Name,
                ISNULL(t1.CountNum, 0) CountNum,
                ISNULL(t1.maxPrice, 0) maxPrice,
                ISNULL(t1.minPrice, 0) minPrice,
                t1.TC_OrganizationID,
                t1.TC_CertificationID
            FROM
                dbo.T_SBSOrganization o
                INNER JOIN dbo.T_SBSOrgCertRelate ocr on ocr.OCR_OrganizationID = o.Organization_ID
                INNER JOIN dbo.T_SBSCertification c on ocr.OCR_CertificationID = c.Certification_ID
                LEFT JOIN
                (
                    SELECT
                        stc.TC_OrganizationID TC_OrganizationID,
                        stc.TC_CertificationID TC_CertificationID,
                        COUNT(*) CountNum,
                        MAX(dbo.F_GetCurrencyByUSD(stc.TC_PriceNum, stc.TC_PriceUnit)) maxPrice,
                        MIN(dbo.F_GetCurrencyByUSD(stc.TC_priceNum, stc.TC_PriceUnit)) minPrice
                    FROM dbo.T_SBSSupplierTripCourse stc
                        LEFT JOIN dbo.T_SBSOrganization o ON stc.TC_OrganizationID = o.Organization_ID
                        LEFT JOIN dbo.T_SBSCertification c ON stc.TC_CertificationID = c.Certification_ID
                    GROUP BY
                            stc.TC_OrganizationID,
                            stc.TC_CertificationID
                    ) AS t1  ON t1.TC_OrganizationID = o.Organization_ID AND t1.TC_CertificationID = c.Certification_ID
                WHERE '
               
                IF @orgID = -1
                    BEGIN
                        SET @str = @str + ' 1 = 1 '
                    END
                ELSE
                    BEGIN
                        SET @str = @str + 'o.Organization_ID = ' + CAST(@orgID AS NVARCHAR(6))
                    END

                SET @str = @str + '
                    ) AS t2
                ) AS t3
                WHERE
                    t3.ROWNUMBER BETWEEN ' + CAST(@startSize AS NVARCHAR(6)) + ' AND ' + CAST(@endSize AS NVARCHAR(6))

    EXEC(@str)
    PRINT(@str)


    DECLARE @AllCertCount INT   
    DECLARE @CertCountByOrg INT
    SET @AllCertCount =
    (
        SELECT
            COUNT(*)
        FROM
            dbo.T_SBSOrganization o
            INNER JOIN dbo.T_SBSOrgCertRelate ocr on ocr.OCR_OrganizationID = o.Organization_ID
            INNER JOIN dbo.T_SBSCertification c on ocr.OCR_CertificationID = c.Certification_ID
    )

    SET @CertCountByOrg =
    (
        SELECT
            COUNT(*)
        FROM
            dbo.T_SBSOrganization o
            INNER JOIN dbo.T_SBSOrgCertRelate ocr on ocr.OCR_OrganizationID = o.Organization_ID
            INNER JOIN dbo.T_SBSCertification c on ocr.OCR_CertificationID = c.Certification_ID
        WHERE o.Organization_ID = @orgID
    )

    SELECT @AllCertCount, @CertCountByOrg

END
 
其中
MAX(dbo.F_GetCurrencyByUSD(stc.TC_PriceNum, stc.TC_PriceUnit)) maxPrice,
MIN(dbo.F_GetCurrencyByUSD(stc.TC_priceNum, stc.TC_PriceUnit)) minPrice
这两列就是用到了function的最大最小值了。
这样就解决了价格大小排序和取得价格的最大最小值的问题。本来以为事情就这么解决了，执行一下存储过程之后，我发现我错了...光这个存储过程就差不多执行了2 min，原因是调用ws花去太多时间。实在没法让人接受。
改进的方法首先想到的就是缓存，将汇率缓存起来，在程序集中再添加一个方法来决定缓存过期与否，然后在执行SQL语句的时候去触发。还是觉得这样不行。
于是就想到第二种方法：建一个表，在表中将各种货币对美元的汇率存起来，然后写一个触发器或者windows服务定期(比如一天)触发来更新这个表，这样达到汇率的相对实时更新。
CREATE TABLE [dbo].[T_SBSSupplierCoursePriceConverse2USD](
    [CPC_ID] [int] IDENTITY(1,1) NOT NULL,
    [CPC_Code] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CPC_CurrencyRate] [float] NOT NULL,
 CONSTRAINT [PK_T_SBSSupplierCoursePriceConverse2USD] PRIMARY KEY CLUSTERED
(
    [CPC_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
先执行一次插入操作，将各种货币对美元的汇率存储到这个表。然后以后就只需要做更新操作。
windows服务方法：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SNET;
using CurrencyRecord.com.xurrency;
using SBS;
namespace CurrencyRecord
{
public class Program
{
    public static void Main(string[] args)
    {
       DataAccess.BaseDAO.CONN_STRING_NON_DTC = ConnectionString;
       Pair[] pairs = GetCurrency2USDRate();
            foreach (Pair pair in pairs)
            {
                SBSSupplierCoursePriceConverse2USDVO objCPC = new SBSSupplierCoursePriceConverse2USDVO();
                objCPC.CPC_Code = pair.Id;
                objCPC.SetAfieldInput("CPC_Code");
                objCPC.CPC_CurrencyRate = pair.Value;
                DataAccess.BaseDAO.Update(objCPC, DataAccess.SqlType.ByInputPropertyAnd);
            }
        }

        public static string ConnectionString
        {
            Get
            {
                return System.Configuration.ConfigurationSettings.AppSettings["SQLConnectionString"];
            }
        }

        public static Pair[] GetCurrency2USDRate()
        {
            xurrency currency = new xurrency();
            return currency.getValues("usd");
        }
}
}
这其实是一个Console项目，然后由一个服务管理器定期去执行这个exe来达到定期更新的效果。
修改数据库方法F_GetCurrencyByUSD
ALTER FUNCTION [dbo].[F_GetCurrencyByUSD]
(
    @amount FLOAT,
    @code VARCHAR(5)
)
RETURNS FLOAT
AS
BEGIN

    IF @amount = 0 OR @amount IS NULL
        RETURN NULL
    DECLARE @rate FLOAT   
    SET @rate =
    (
        SELECT
            CPC_CurrencyRate
        FROM
            dbo.T_SBSSupplierCoursePriceConverse2USD
        WHERE
            CPC_Code = LOWER(@code)
    )
   
    RETURN @amount / @rate

END
之前的存储过程也不用改了，执行一下，花的时间基本上可以忽略不计...这可是天差地别啊。
就这样，总算把这个问题搞定了
SQLSERVER CLR编程的确可以解决很多比较难解决的问题，但如果要建立在严重损失性能的基础上话，还是不要用吧。：）