using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

    //保存当前组当前值
    private static System.Collections.Generic.Dictionary<string, SqlString> _listValue = new System.Collections.Generic.Dictionary<string, SqlString>();
    //保存当前组
    private static System.Collections.Generic.Dictionary<string, string> _listGroup  = new System.Collections.Generic.Dictionary<string, string>();

    /// <summary>
    /// 获取当前组上条记录数值
    /// </summary>
    /// <param name="key">并发键</param>
    /// <param name="currentGroup">当前组</param>
    /// <param name="currentValue">当前组当前值</param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetPrevMemberValue(SqlString key,SqlString currentGroup,SqlString currentValue)
    {
        if (key.IsNull || currentGroup.IsNull) return SqlString.Null;

       
        try
        {
            SqlString prevMemberValue = _listValue[key.Value];

            //组变更
            if (_listGroup[key.Value] != currentGroup.Value)
            {
                prevMemberValue = SqlString.Null;
                _listGroup[key.Value] = currentGroup.Value;
            }
            //值变更
            _listValue[key.Value] = currentValue;

            return prevMemberValue;
        }
        catch
        {
            return SqlString.Null;
        }
    }
    /// <summary>
    /// 初始化并发键
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean InitKey(SqlString key)
    {
        try
        {
            _listValue.Add(key.Value, SqlString.Null);
            _listGroup.Add(key.Value, string.Empty);
            return true;
        }
        catch
        {
            return false;
        }
    }
    /// <summary>
    /// 释放并发键
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean DisposeKey(SqlString key)
    {
        try
        {
            _listValue.Remove(key.Value);
            _listGroup.Remove(key.Value);
            return true;
        }
        catch
        {
            return false;
        }
    }
};

CREATE ASSEMBLY TestForSQLCLR FROM 'E:\sqlclrdata\TestFun.dll' WITH PERMISSION_SET = UnSAFE;
--
go
--获取上个维度
CREATE FUNCTION dbo.xfn_GetPrevMemberValue  
(  
    @key nvarchar(255),
    @initByDim nvarchar(255),
    @currentValue nvarchar(255)
)    
RETURNS nvarchar(255)
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].GetPrevMemberValue
go

--初始化并发键
CREATE FUNCTION dbo.xfn_initKey
(  
    @key nvarchar(255)
)    
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].InitKey
go
--清理并发键
CREATE FUNCTION dbo.xfn_disposeKey  
(  
    @key nvarchar(255)
)    
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFu