如何在数据库项目中使用SqlFunctionAttribute生成SQL以自行部署函数？ Visual studio失败了

Background:

Visual studio fails at deploying a database project. It tries to drop functions that are already referenced (e.g. in a check constraint), rather than just adding the new ones and updating the existing ones, so the deployment always fails.

Visual Studio无法部署数据库项目。它尝试删除已经引用的函数(例如,在检查约束中),而不是仅添加新函数并更新现有函数,因此部署总是失败。

As a result, I'm writing my own code to update the assembly and add/update any functions.

因此,我正在编写自己的代码来更新程序集并添加/更新任何函数。

I assume the compiler/deployer uses reflection and properties of the SqlFunction attribute, so I'm also using reflection to gather a MethodInfo list of the static methods that have the SqlFunction attribute.

我假设编译器/部署者使用SqlFunction属性的反射和属性,所以我也使用反射来收集具有SqlFunction属性的静态方法的MethodInfo列表。

Question/Task:

I need to know how to translate the SqlFunctionAttribute's properties (e.g. IsDeterministic, DataAccess, Name, IsPrecise, etc.) and the method signature of the function into an appropriate T-SQL "CREATE FUNCTION" statement.

我需要知道如何将SqlFunctionAttribute的属性(例如IsDeterministic,DataAccess,Name,IsPrecise等)和函数的方法签名转换为适当的T-SQL“CREATE FUNCTION”语句。

Existing information I've already found to be not helpful:

我发现的现有信息没有帮助:

The documentation for 'create function' is confusing and incomplete. Towards the bottom it finally mentions some of the SqlFunction properties like IsDeterministic, but it talks about them like they're C# properties, not T-SQL parameters, so I have no idea how to use them in a create function statement.

“创建功能”的文档令人困惑且不完整。接下来,它最终提到了一些像IsDeterministic这样的SqlFunction属性,但是它们就像是C#属性而不是T-SQL参数一样,所以我不知道如何在create function语句中使用它们。

//CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

I would expect the clr_function_option parameter to handle things like IsDeterministic, but it's not listed as an option.

我希望clr_function_option参数能够处理像IsDeterministic这样的东西,但它没有作为选项列出。

Meanwhile, in documentation for IBM DB2, I see statements like the following, which the MSDN documentation has nothing remotely similar:

同时,在IBM DB2的文档中,我看到如下语句,MSDN文档没有任何远程类似的语句:

  CREATE FUNCTION countUp(INTEGER) 
  RETURNS INTEGER
  LANGUAGE CLR
  PARAMETER STYLE SQL
  SCRATCHPAD 10
  FINAL CALL
  NO SQL
  FENCED
  THREADSAFE
  NOT DETERMINISTIC
  EXECUTION CONTORL SAFE
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp' ;

1 个解决方案

#1

I solved this problem eventually, after realizing that SQL Server, possessing the assembly itself, has access to the SqlFunctionAttribute's property values. In that case, there is no need to (and is no syntax for) specifying such properties in the "CREATE FUNCTION" T-SQL statement.

在意识到拥有程序集本身的SQL Server可以访问SqlFunctionAttribute的属性值之后,我最终解决了这个问题。在这种情况下,不需要(并且没有语法)在“CREATE FUNCTION”T-SQL语句中指定这些属性。

I built a utility to automatically enumerate and deploy functions and check-constraints which works like this:

我构建了一个实用程序来自动枚举和部署函数和检查约束,其工作方式如下:

It enumerates all deployable static methods in classes that I specify, by searching for functions having an SqlFunction attribute using reflection. It also runs a query to enumerate all existing scalar and table-valued assembly functions already deployed. It then merges these lists into a single list, where each function either exists or does not exist. There is a button to update the assembly, and a button to toggle each function's existence, making adding/removing/updating functions a BREEZE.

它通过使用反射搜索具有SqlFunction属性的函数,枚举我指定的类中的所有可部署静态方法。它还运行查询以枚举已部署的所有现有标量和表值程序集函数。然后,它将这些列表合并到一个列表中,其中每个函数都存在或不存在。有一个按钮来更新组件,还有一个按钮来切换每个功能的存在,使添加/删除/更新功能成为BREEZE。
In addition, I added a second tab to the interface to create/enable/check/disable check-constraints. I created a new attribute called SqlFunctionCheck, which can be applied multiple times to a function. The attribute has a "Table" property and a "Field" property, which helps the utility build the check constraints. It aggregates all the checks by table, create a check for each table named "CK_" + table_name, and creates a constraint expression joining all method calls with "and" passing the associated field name for that table to the function. It displays the generated constraint expression as well as the constraint expression queried from sql server if the constraint already exists.

另外,我在界面中添加了第二个选项卡来创建/启用/检查/禁用检查约束。我创建了一个名为SqlFunctionCheck的新属性,可以多次应用于函数。该属性具有“Table”属性和“Field”属性,这有助于实用程序构建检查约束。它按表聚合所有检查,为名为“CK_”+ table_name的每个表创建一个检查,并创建一个约束表达式,用“和”将所有方法调用连接到该函数。它显示生成的约束表达式以及从sql server查询的约束表达式(如果约束已存在)。

IMO, this is the ultimate solution for using SQL CLR Integration to enforce data type constraints in management studio, SQL queries in your code, and the data used throughout the code itself from a centralized data (constraint) classes.

IMO,这是使用SQL CLR集成在管理工作室中强制执行数据类型约束,在代码中执行SQL查询以及从集中数据(约束)类在整个代码本身中使用的数据的最终解决方案。

Interface Screenshots and Relevant Function Headers http://i41.tinypic.com/5ydrop.png

界面截图和相关功能标题http://i41.tinypic.com/5ydrop.png

#1