CLR项目出错 [英] Error in CLR Project

问题描述

你好。

我在clr项目中遇到问题。

i在C＃.net 3.0中创建一个clr项目（代码如下）。

 public partial class UserDefinedFunctions 
 {
 [Microsoft.SqlServer.Server.SqlFunction] 
 public static SqlString CLR_GET_SCHEMA（string tablename，int i）
 {
 / /将您的代码放在这里
 List< string> strlist = new List< string>（）; 
 SqlConnection connectionstring = new System.Data.SqlClient.SqlConnection（server = localhost; database = S7; Integrated Security = true）; 
 SqlCommand selectcommand = new System.Data.SqlClient.SqlCommand（）; 
 selectcommand.Connection = connectionstring; 
 selectcommand.CommandText =USP_SCHEMA; 
 selectcommand.CommandType = CommandType.StoredProcedure; 
 selectcommand.Parameters.Add（@ TABLENAME，SqlDbType.VarChar，50）; 
 selectcommand.Parameters [@ TABLENAME]。Value = tablename; 
 connectionstring.Open（）; 
 SqlDataReader reader = selectcommand.ExecuteReader（）; 
 while（reader.Read（））
 {
 strlist.Add（Convert.ToString（reader [0]））; 
} 
 return strlist [i]; 
 
} 
}; 

当我部署项目时项目已成功构建

但是当我在管理工作室中使用该功能时，我有以下错误。

Quote：

消息6522，级别16，状态2，行2

在执行用户定义的例程或聚合CLR_GET_SCHEMA期间发生.NET Framework错误：

System.Security .SecurityException：请求类型''System.Data.SqlClient.SqlClientPermission，System.Data，Version = 2.0.0.0，Culture = neutral，PublicKeyToken = b77a5c561934e089''的权限失败。

系统。 Security.SecurityException：

在System.Security.CodeAccessSecurityEngine.Check（Object demand，StackCrawlMark& stackMark，Boolean isPermSet）

at System.Security.PermissionSet.Demand（）<在System.Data.SqlClient.SqlConnection的System.Data.Common.DbConnectionOptions.DemandPermission（）

处获得
。 PermissionDemand（）

在System.Data.SqlClient.SqlConnectionFactory.PermissionDemand（DbConnection outerConnection）

在System.Data.ProviderBase.DbConnectionClosed.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）

在System.Data.SqlClient.SqlConnection.Open（）

在UserDefinedFunctions.CLR_GET_SCHEMA（String tablename，Int32 i）

我存储的程序

 USE AdventureWorks 
 GO 
 ALTER PROC USP_SCHEMA 
 @TABLENAME VARCHAR（50）
 AS 
 BEGIN 
 SELECT TABLE_SCHEMA 
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_NAME = @ TABLENAME 
 END 
 GO 
 EXEC USP_SCHEMA''PRODUCT''

请帮帮我

解决方案

在这里，关于解决类似问题的讨论应该有所帮助：请求获得类型的许可 [ ^ ]

Hi guys.
I have a problem in a clr projects.
i create a clr project in C#.net 3.0(following code).

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString CLR_GET_SCHEMA(string tablename,int i)
    {
        // Put your code here
        List<string> strlist=new List<string>();
        SqlConnection connectionstring = new System.Data.SqlClient.SqlConnection("server=localhost;database=S7;Integrated Security=true");
        SqlCommand selectcommand = new System.Data.SqlClient.SqlCommand();
        selectcommand.Connection = connectionstring;
        selectcommand.CommandText = "USP_SCHEMA";
        selectcommand.CommandType = CommandType.StoredProcedure;
        selectcommand.Parameters.Add("@TABLENAME", SqlDbType.VarChar, 50);
        selectcommand.Parameters["@TABLENAME"].Value = tablename;
        connectionstring.Open();
        SqlDataReader reader=selectcommand.ExecuteReader() ;
        while (reader.Read())
        {
            strlist.Add(Convert.ToString(reader[0]));
        }
        return strlist[i];

    }
};

When i deploy my project The project have successful build
but when i use the function in management studio i have the following error.

Quote:

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_GET_SCHEMA":
System.Security.SecurityException: Request for the permission of type ''System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at UserDefinedFunctions.CLR_GET_SCHEMA(String tablename, Int32 i)

My stored prodedure

USE AdventureWorks
GO
ALTER PROC USP_SCHEMA
   @TABLENAME VARCHAR(50)
AS
BEGIN
   SELECT TABLE_SCHEMA 
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME=@TABLENAME
END   
GO 
EXEC USP_SCHEMA ''PRODUCT''

please help me

解决方案

Here, following discussion on similar issue resolved should help: Request for the permission of type [^]

这篇关于CLR项目出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！