我使用的是 CLR表 - 值函数中进行选择并返回一个复杂的数据库搜索，它使用许多变量的结果。

I'm using a CLR table-valued function to SELECT and return the results of a complex database search which uses many variables.

该文件显示您在一个类似的方式建立这样一个功能：

The documentation shows that you build such a function in a fashion similar to this:

public partial class UserDefinedFunctions
{
    private class ResultRow
    // This class holds a row which we want to return.
    {
        public SqlInt32 CustId;
        public SqlString Name;

        public ResultRow(SqlInt32 custId_, SqlString name_)
        {
            CustId = custId_;
            Name = name_;
        }
    }

    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "Test_FillRow",
        TableDefinition = "CustId int" +
                          "Name nvarchar(50)")]
    public static IEnumerable Test()
    // This function contains the actual logic.
    {
        ArrayList results = new ArrayList();

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();

            using (SqlCommand select = new SqlCommand(
                "SELECT TOP 100 custid, name FROM Customers",
                connection))
            {
                using (SqlDataReader reader = select.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        results.Add(new ResultRow(
                            reader.GetSqlInt32(0),  // CustId
                            reader.GetSqlString(1)  // Name
                        ));
                    }
                }
            }
        }
        return results;
    }

    public static void Test_FillRow(
        object resultsObj,
        out SqlInt32 custid,
        out SqlString name)
    // This function takes a row and tells SQL Server what variables we want to 
    // return from it and what types it contains.
    {
        ResultRow selectResults = (ResultRow)resultsObj;

        custid = selectResults.CustId;
        name = selectResults.Name;
    }
}

现在的问题是，这是相当反复。首先，你在SqlFunction块定义表。然后，当你在你返回的结果中添加或删除列，你必须确保你更新和

The problem is, it's rather repetitive. First you define the table in the SqlFunction block. Then as you add or remove columns in the results you're returning, you have to make sure that you update it and

我正在这样一个函数，它接受了二十参数，返回更多行，并包含这八个可能的地方犯错误。 八的。所有的错误都是微不足道的，容易固定，但它很容易使他们因为有这么多的地方，在code，我必须手动保持同步。

I'm working on such a function which takes over twenty arguments, returns even more rows, and contains these eight possible places to make mistakes. Eight. All the mistakes are trivial and easily fixed, but it's very easy to make them because there's so many places in the code which I have to manually keep in sync.

这是违反干的，但我不知道如何消除重复。有没有一种更简洁的方式来编写CLR表值函数？

This is a violation of DRY, but I don't know how to eliminate the repetition. Is there a more concise way to write CLR table-valued functions?

推荐答案

如果您替换对象[]，您可以使用reader.GetValues​​你ResultRow（对象[]），并消除了必须知道什么是在行，直到FillRow（ ），然后FillRow负责了解什么样的顺序领域是在原来的查询。

If you replace your ResultRow with object[], you can use reader.GetValues(object[]) and eliminate having to know what's in the rows until FillRow(), and then FillRow is responsible for knowing what order the fields were in in the original query.

这的确是一个折衷，你可以放弃具有强类型，一路过关斩将，以换取不必做的强类型一路过关斩将，但很难两者兼得： - ）

It's really a tradeoff, you can give up having strong typing all the way through, in exchange for not having to do strong typing all the way through, but it's hard to have it both ways :-)