与C#中的string.Format等效的CLR函数 [英] A CLR function equivalent to string.Format in C#

问题描述

我正在寻找一个CLR函数，该函数将与C#中的String.Format做相同的事情.例如，我想通过CLR函数执行以下操作:

I was looking for a CLR function that will do the same thing as String.Format in C#. As an example, I would like to do the following through a CLR function:

String.Format("My name is {0}, and I live in {1}",myName, cityName)

我希望能够在第一个参数之后传递可变数量的参数，这将与在第一个参数中为此CLR函数指定的占位符一样多.任何人对于这种CLR函数的C#代码有什么想法?

I would like to be able to pass variable number of parameters after the first parameter, which would be as many place holders are specified in the first parameter to this CLR function. Anyone has any idea on what would be the C# code for such a CLR function?

推荐答案

我正在寻找相同的东西.我遇到了这两个站点: http://www.fotia.co.uk/fotia/Blog/2009/05/indispensable-sql-clr-functions.html & http://stringformat-in-sql.blogspot.com/.

I'm looking for the same thing. I came across these two sites: http://www.fotia.co.uk/fotia/Blog/2009/05/indispensable-sql-clr-functions.html & http://stringformat-in-sql.blogspot.com/.

第一个使用CLR函数，另一个使用存储过程.

The first uses a CLR function, the other uses a stored procedure.

在第一篇文章中，作者说UDF参数不是可选的，因此您不能拥有可变数量的参数(至少是他的操作方式).他只是使用所需的数字或参数创建了一个不同的函数.

In the first post, the writer says that UDF parameters aren't optional so you can't have variable number of params (at least the way he is doing it). He just creates a different function with the number or args he will need.

[SqlFunction(DataAccess = DataAccessKind.None)]
public static SqlString FormatString1(SqlString format, object arg0, object arg1)
{
   return format.IsNull ? SqlString.Null : 
       string.Format(format.Value, SqlTypeToNetType(arg0, arg1));
}

这就是我为我解决此问题的方式.

使用第二篇文章中的存储过程，我创建了此函数以使用数据透视表为我设置文本格式.我们的数据库是用表 dbo.[Rules] 设置的，该表具有列 [Description] 的列，该列具有需要格式化的字符串，例如:"NET INCOME MARGINAL($ {0}低于$ {1})".然后，我们有一个1-many表 dbo.[RuleParameters] ，其中每个需要替换的值都有一行. dbo.[SortOrder] 列指定参数进入的顺序.此函数适用于AT MOST 4参数，这是我们所拥有的最多的参数.

EDIT : This is how I solved this problem for me.

Using the stored procedure from the second article, I created this function to format the text for me using a pivot table. Our database is setup with a table dbo.[Rules] that has a column [Description] that has a string that needs to be formatted such as: "NET INCOME MARGINAL (${0} TO BELOW ${1})". We then have a 1-many table dbo.[RuleParameters] with a row for each value that needs to be substituted. The column dbo.[SortOrder] specifies which order the arguments go in. This function will work for AT MOST 4 arguments which is the most that we have.

CREATE FUNCTION [dbo].[GetRuleDescriptionWithParameters]
(
    @Code VARCHAR(3)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(400)

    SELECT 
        @Result =
            dbo.FormatString([Description], 
                ISNULL([1], '') + ',' + 
                ISNULL([2], '') + ',' + 
                ISNULL([3], '') + ',' + 
                ISNULL([4], '')
                )
    FROM
    (
        SELECT 
        r.[Description]
        ,rp.Value
        ,rp.SortOrder

        FROM 
            [Rules] r
        LEFT OUTER JOIN [RuleParameters] rp
            ON r.Id = rp.RuleId

        WHERE r.Code = @Code

    ) AS SourceTable
    PIVOT
    (
        MAX(Value)
        FOR SortOrder IN ([1], [2], [3], [4])
    ) AS PivotTable;


    RETURN @Result
END

EDIT#2 :添加更多示例

格式化字符串函数:

CREATE FUNCTION [dbo].[FormatString]
(
    @Format NVARCHAR(4000) ,
    @Parameters NVARCHAR(4000)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Message NVARCHAR(400),
@Delimiter CHAR(1)
DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Paramter VARCHAR(1000) )
SELECT @Message = @Format, @Delimiter = ','
;WITH CTE (StartPos, EndPos) AS
(
    SELECT 1, CHARINDEX(@Delimiter, @Parameters)
    UNION ALL
    SELECT EndPos + (LEN(@Delimiter)), CHARINDEX(@Delimiter,@Parameters, EndPos + (LEN(@Delimiter)))
FROM CTE
WHERE EndPos > 0
)
INSERT INTO @ParamTable ( Paramter )
SELECT
[ID] = SUBSTRING ( @Parameters, StartPos, CASE WHENEndPos > 0 THEN EndPos - StartPos ELSE 4000 END )
FROM CTE
UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}',     Paramter )
RETURN @Message
END
GO

这是我调用存储过程的方式:

Here is how I call the stored procedure:

SELECT r.[Id]
        ,[Code]
        ,[Description]
        ,dbo.GetRuleDescriptionWithParameters([Code])
        ,rp.[Value]
        ,rp.SortOrder
FROM [Rules] r
INNER JOIN [RuleParameters] rp
    ON r.Id = rp.RuleId

现在，我可以像这样调用该函数，以使所有格式对我来说都很好！这是结果的示例用法:

Now I can just call the function like this to get everything formatted nicely for me! Here is sample usage with the results:

Id  Code    Description                     (No column name)          Value     SortOrder
1   1       NOT THE MINIMUM AGE             NOT THE MINIMUM AGE       18        1
3   8       NET INCOME (BELOW ${0})      NET INCOME (BELOW $400)   400    1
4   9       NET (${0} TO BELOW ${1})          NET ($400 TO BELOW $600)  400         1
4   9       NET (${0} TO BELOW ${1})          NET ($400 TO BELOW $600)  600         2

通常，在调用此函数时，我不会加入[RuleParameters]表，但在这种情况下，我这样做是为了让您可以看到一个数据集中前后的数据.

Normally when calling this, I wouldn’t join on the [RuleParameters] table, but I did in this case so that you can see the data before and after in one data set.

这篇关于与C#中的string.Format等效的CLR函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！