使用VSTS创建SQL Server的functions (User-Defined Function)

时间:2022-08-25 12:56:18

没想到,头一次使用VSTS创建SQL Server的function,就遇到个大的问题,VSTS(2008)默认的function是不支持对数据库的访问,只能够对传入的几个参数进行各种操作。如:
 使用VSTS创建SQL Server的functions (User-Defined Function)

如果在其中对数据库操作,打开一个SqlConnection,则会出问题,提示:

A .NET Framework error occurred during execution of user defined routine or aggregate 'Your_Function':
System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

根据错误提示,我们得知(我是google了好久才得知的),需要做DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read 这一标记,即不能只写[Microsoft.SqlServer.Server.SqlFunction] (此处为系统默认), 如果要对数据库操作,要写成 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)], 如:

使用VSTS创建SQL Server的functions (User-Defined Function)