因需要在函数中执行动态sql,sqlserver函数不支持,所以使用clr函数来实现,clr函数需要调用sqlserver函数,执行时异常提示“此语句已尝试访问其访问受程序集限制的数据。”,但用 CLR 写的存储过程执行没有问题, 请指教?测试实例如下:
1、被CLR函数调用的sqlserver函数:Create Function [dbo].[Fun_getdate1]()
returns varchar(50)
as
Begin
declare @datetime varchar(50)
set @datetime = convert(varchar(50),getdate(),121)
return @datetime
End2、CLR函数:using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString F_test1()
{ String returnvalue = "";
try
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{ using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
String sql = "select dbo.Fun_getdate1()";
cmd.CommandText = sql;
returnvalue = (String)cmd.ExecuteScalar() ; --执行时转到异常:此语句已尝试访问其访问受程序集限制的数据。
}
} }
catch (Exception ex)
{
returnvalue = ex.Message.ToString();
}
return returnvalue ;
}
};
1、被CLR函数调用的sqlserver函数:Create Function [dbo].[Fun_getdate1]()
returns varchar(50)
as
Begin
declare @datetime varchar(50)
set @datetime = convert(varchar(50),getdate(),121)
return @datetime
End2、CLR函数:using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString F_test1()
{ String returnvalue = "";
try
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{ using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
String sql = "select dbo.Fun_getdate1()";
cmd.CommandText = sql;
returnvalue = (String)cmd.ExecuteScalar() ; --执行时转到异常:此语句已尝试访问其访问受程序集限制的数据。
}
} }
catch (Exception ex)
{
returnvalue = ex.Message.ToString();
}
return returnvalue ;
}
};
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]