CLR有关问题，求解

CLR问题，求解
环境：VS2005，SQL Server 2005
要求：想写一个CLR存储过程，该过程读取数据库中的表，然后将相应的数据提交到远程Web地址（HTTP方式）
错误：

C# code

在执行用户定义例程或聚合 'sp_sync_data' 期间出现 .NET Framework 错误: System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException:    at StoredProcedures.SyncData()。

代码：

C# code

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.IO;using System.Net;using System.Text;using System.Collections;using System.Security.Permissions;public partial class StoredProcedures{    [Microsoft.SqlServer.Server.SqlProcedure]    public static void SyncData()    {        string conn_string = "context connection = true";        string uri = "{0}?o={1}&r={2}";        string sql = "select syncuri,oid,reservea from mydatatable";        SqlDataAdapter sda = new SqlDataAdapter(sql, conn_string);        try        {            DataTable dt = new DataTable();            sda.Fill(dt);            for (int i = 0; i < dt.Rows.Count; i++)            {                string syncuri = dt.Rows[i]["syncuri"].ToString();                string oid = dt.Rows[i]["oid"].ToString();                string reservea = dt.Rows[i]["reservea"].ToString();                string syncresult = GetHttpRequest(string.Format(uri, syncuri, oid, reservea));                if (syncresult == "1")                {                    //etc                }            }        }        catch (Exception ex)        {            throw ex;        }    }    private static string GetHttpRequest(string uri)    {        #region 这样也不成，晕         WebPermission oWebPermision = new WebPermission(PermissionState.None);        oWebPermision.AddPermission(NetworkAccess.Connect, uri);        oWebPermision.AddPermission(NetworkAccess.Accept, uri);        oWebPermision.Demand();        #endregion        string strResult = string.Empty;        WebRequest oRequest = null;        WebResponse oResponse = null;        StreamReader oStreamReader = null;        try        {            oRequest = WebRequest.Create(uri);            oRequest.ContentType = "application/x-www-form-urlencoded";            oRequest.Method = "GET";            oResponse = oRequest.GetResponse();            oStreamReader = new StreamReader(oResponse.GetResponseStream(), Encoding.Default);            strResult = oStreamReader.ReadToEnd();            oStreamReader.Close();            oStreamReader = null;            oResponse.Close();            oResponse = null;        }        catch(Exception ex)        {            throw ex;        }        return strResult;    }};

我加了上面的WebPermission后，也没有解决问题，以前没有搞过这方面的东西，搞过的朋友怎么解决呀？还望不吝赐教。

[解决办法]
太長了，看了頭暈
[解决办法]
跟CLR么关系,Request的有几个属性还是要设一下的,楼主参考下post和get的代码吧

Post方式:

public string Post(string url, string content, string referer)
{
HttpWebRequest request = (HttpWebRequest) WebRequest.Create(new Uri(url));
request.UserAgent = this.reqUserAgent;
request.CookieContainer = this.cookieContainer;
request.Referer = referer;
byte[] bytes = Encoding.GetEncoding("GB2312").GetBytes(content);
request.Method = "POST";
request.Timeout = this.timeout;

request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = bytes.Length;
if ((this.proxy != null) && (this.proxy.Credentials != null))
{
request.UseDefaultCredentials = true;
}
request.Proxy = this.proxy;
Stream requestStream = request.GetRequestStream();
requestStream.Write(bytes, 0, bytes.Length);
requestStream.Close();
HttpWebResponse response = (HttpWebResponse) request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
return reader.ReadToEnd();
}

Get方式:

public string Get(string url, string referer)
{
HttpWebRequest request = (HttpWebRequest) WebRequest.Create(new Uri(url));
request.UserAgent = this.reqUserAgent;
request.CookieContainer = this.cookieContainer;
request.Referer = this.MainUrl;
request.Method = "GET";
request.Accept = "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5";
request.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
request.Timeout = this.timeout;
if ((this.proxy != null) && (this.proxy.Credentials != null))
{
request.UseDefaultCredentials = true;
}
request.Proxy = this.proxy;
HttpWebResponse response = (HttpWebResponse) request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
return reader.ReadToEnd();
}

[解决办法]
http://www.51cto.com/art/200706/50114.htm
[解决办法]
namespace Microsoft.Samples.SqlServer 
{ 
public sealed partial class Hello 
{ 
private Hello() 
{ 
} 

[System.Diagnostics.CodeAnalysis.SuppressMessage(\"Microsoft.Design\", \"CA1021:AvoidOutParameters\"), Microsoft.SqlServer.Server.SqlProcedure] 
public static void Xp_ExecProd(string prodcmd, out string greeting) 
{ 
using (SqlConnection conn = new SqlConnection(\"context connection=true\")) 
{ 
int rtn; 
SqlCommand cmd = conn.CreateCommand(); 
cmd.CommandText = prodcmd; 
conn.Open(); 
try 
{ 
rtn = cmd.ExecuteNonQuery(); 

} 
finally 
{ 

} 

Microsoft.SqlServer.Server.SqlMetaData columnInfo 
= new Microsoft.SqlServer.Server.SqlMetaData(\"Column1\", SqlDbType.NVarChar, 50); 
SqlDataRecord greetingRecord 
= new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo }); 
greetingRecord.SetString(0, rtn.ToString()); 
SqlContext.Pipe.Send(greetingRecord); 
greeting = rtn.ToString(); 
} 
} 
} 
} 


[解决办法]
部署到SQL Server时，指定UNSAFE。 

SQL code

CREATE ASSEMBLY xxxFROM 'xxx.dll'WITH PERMISSION_SET = UNSAFE; 

 

查看更多 下一篇