sql2005调用c# dll 读取磁盘文件总结

项目需求：

              sqlserver2005编写存储过程或者函数调用.net项目读取磁盘Html。

编写过程，出现各种各样的错误，网上找了很多解决方案都不是很好。

 

开发环境用得是win7+sql2005+vs2008

一、c#代码

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString getMyHtmls(string cityName)
    {
        string[] newContent = new string[5];
        StringBuilder strhtml = new StringBuilder();

        try
        {

            using (StreamReader sr = new StreamReader("E:\\AspNet\\Jacky\\web\\template\\template.html"))
            {
                String oneline;
                while ((oneline = sr.ReadLine()) != null)
                {
                    strhtml.Append(oneline);

                }
                sr.Close();
                return "Hello world" + strhtml.ToString() ;
            }

        }
        catch (Exception err)
        {

            return err.ToString();
        }

    }
};

我们编写了一个很简单的代码，返回一个字符串，字符串的内容来自磁盘的模板文件

 

二、部署此项目到sql server，运行

 dbo.getmyhtmls('test')

查询结果有错误：

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.     at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)     at System.Security.CodeAccessPermission.Demand()     at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)     at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)     at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)     at System.IO.StreamReader..ctor(String path)     at UserDefinedFunctions.getMyHtmls(String cityName)  The action that failed was:  Demand  The type of the first permission that failed was:  System.Security.Permissions.FileIOPermission  The Zone of the assembly that failed was:  MyComputer

解决方法：

在部署项目时候--数据库--权限级别 默认是安全，把它勾选为不安全。

然后重新 编译部署该项目。

 

运行sql：

select  dbo.getmyhtmls('a')

 

我们发现，返回的就是我们要的磁盘文档。