sql server 2008-C#CLR用户定义函数SqlBytes-InvalidOperationExceptio

我一直在写一个sql clr udf，它是从存储过程中调用的，用于将未知文件类型（jpg、doc、pdf等）保存到文件系统中。UFD接受SqlBytes类型的参数，该参数将从调用存储过程（这是文件blob）传递varbinary（max）。问题是，在CLR UDF的上下文中，我无法访问SqlBytes文件参数的值属性甚至读取方法，因为它返回以下无效异常错误。

我缩短了我的UDF，只是为了突出提到的问题。任何帮助都将不胜感激。

提前感谢。

---

错误

在此上下文中不允许数据访问。上下文是未标记为DataAccessKind.Read或SystemDataAccessKind.Read的函数或方法，是从表值函数的FillRow方法获取数据的回调，或者是UDT验证方法。

---

堆栈跟踪

at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
   at System.Data.SqlServer.Internal.ClrLevelContext.XvarProxyRead(CClrXvarProxy* pXvarProxy, UInt64 iPosition, Byte* pbBuffer, UInt32 cbCount)
   at System.Data.SqlServer.Internal.ClrLevelContext.System.Data.SqlServer.Internal.IXvarProxyAccessor.XvarProxyRead(CClrXvarProxy* , UInt64 , Byte* , UInt32 )
   at System.Data.SqlServer.Internal.StreamOnBlobHandle.Read(Byte* pbBuffer, UInt64 offset, UInt32 count)
   at System.Data.SqlServer.Internal.XvarBlobStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at System.Data.SqlTypes.SqlBytes.Read(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
   at UserDefinedFunctions.SaveFileToFS(SqlBytes file, String fileName, String fileExtension, String path)

---

CLR代码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Permissions;
using System.Security.Principal;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{ 
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString SaveFileToFS(SqlBytes file)
    {
        WindowsImpersonationContext newContext = null;
        WindowsIdentity newIdentity = SqlContext.WindowsIdentity;
        try
        {           
            if (newIdentity != null) newContext = newIdentity.Impersonate();
            byte[] buffer = new byte[8040 * 4];
            long offset = 0;
            long read = 0;
            //This file.Read will throw an error
            read = file.Read(offset, buffer, 0, buffer.Length);
            //this line will throw the same error
            buffer = (byte[])file.Value;
        catch (System.Exception ex1)
        {
            throw ex1;
        }
        finally
        {
            if (newContext != null) newContext.Undo();
        }
        return new SqlString("Success");
    }
};

---

因此，为了完成这个线程，这里是CLR UDF的基本POC代码，它接受varbinary（max）文件blob、文件名、文件扩展名和要写入的路径，然后将其保存到定义的文件系统位置（前提是它具有适当的文件系统权限）。希望它能帮助到别人：-）

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Permissions;
using System.Security.Principal;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read , SystemDataAccess = SystemDataAccessKind.Read )]
    public static SqlString SaveFileToFS(SqlBytes file, string fileName, string fileExtension, string path)
    {
        WindowsImpersonationContext newContext = null;
        WindowsIdentity newIdentity = SqlContext.WindowsIdentity;
        try
        {
            long length = file.Length ;
            byte[] buffer = file.Value;
            long offset = 0;
            long read = 0;
            int times = 0;
            if (newIdentity != null) newContext = newIdentity.Impersonate();
            FileStream fs = new FileStream(path + fileName + fileExtension, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            while (length > 1000)
            {
                fs.Write(buffer, 1000 * times, 1000);          
                length -= 1000;
                times++;
            }
            fs.Write(buffer, 1000 * times, (int)length);
            fs.Close();
        }
        catch (System.Exception ex1)
        {
            throw ex1;
        }
        finally
        {
            if (newContext != null) newContext.Undo();
        }
        return new SqlString(string.Format("Saved file: {0}{1} to path: {2}", fileName, fileExtension, path));
    }

};

WindowsImpersonationContext newContext = null;
WindowsIdentity newIdentity = SqlContext.WindowsIdentity;

if (newIdentity != null) newContext = newIdentity.Impersonate();