我在windows 7 (Version 6.1 Build 7601: Service Pack 1)和visual studio 2010上安装了SQL server express 2008 SP1。
我正在尝试使用以下代码创建一个存储过程CLR,用于将文件插入到文件流中。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Security.Principal;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_fileController(String friendlyName, String filePath)
{
SqlParameter fDataParam = new System.Data.SqlClient.SqlParameter("@fData", SqlDbType.VarBinary, -1);
SqlParameter fNameParam = new System.Data.SqlClient.SqlParameter("@fName", SqlDbType.NVarChar, 300);
WindowsIdentity newId = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedUser = newId.Impersonate();
try
{
string cs = @"Server=[myservername];Integrated Security=true";
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlTransaction objSqlTran = con.BeginTransaction();
//string sql = "INSERT INTO fileStreamTest VALUES ((Cast('' As varbinary(Max))), @fName, default); Select fData.PathName() As Path From fileStreamTest Where fId = SCOPE_IDENTITY()";//OUTPUT inserted.fid
SqlCommand insertFileCommand = con.CreateCommand();
insertFileCommand.Transaction = objSqlTran;
insertFileCommand.CommandText = "INSERT INTO fileStreamTest.dbo.fileStreamTest (RowGuid, fData) VALUES (@FileID, CAST ('' as varbinary(max)))";
Guid newFileID = Guid.NewGuid();
insertFileCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;
insertFileCommand.ExecuteNonQuery();
SqlCommand getPathAndTokenCommand = con.CreateCommand();
getPathAndTokenCommand.Transaction = objSqlTran;
getPathAndTokenCommand.CommandText =
"SELECT fData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " +
"FROM fileStreamTest.dbo.fileStreamTest " +
"WHERE rowGuid = @FileID";
getPathAndTokenCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;
SqlDataReader tokenReader = getPathAndTokenCommand.ExecuteReader(CommandBehavior.SingleRow);
tokenReader.Read();
SqlString filePathName = tokenReader.GetSqlString(0);
SqlBinary fileToken = tokenReader.GetSqlBinary(1);
tokenReader.Close();
SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.ReadWrite);
sqlFile.Close();
objSqlTran.Rollback();
//objSqlTran.Commit();
con.Close();
}
}
finally
{
impersonatedUser.Undo();
}
}
};
但是当它到达这一行时:
SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.ReadWrite);
我:
.NET Framework在执行用户定义例程或聚合"sp_fileController"时发生错误:
System.ComponentModel.Win32Exception: The request is not supported
System.ComponentModel.Win32Exception:
at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
at StoredProcedures.sp_fileController(String friendlyName, String filePath)
谁能告诉我如何解决这个问题?简单地说,我不能以这种方式执行代码与sql 2008 express edition?
我想我已经找到了可行的解决方案:
https://social.msdn.microsoft.com/forums/sqlserver/en us/f49def09 - 3 - b47 4 e54 - 8 a53 - 2 - dd47762821e/filestream - windows -服务器- 2012 -请求- -不- supported?forum=sqldatabaseengine
总结:添加注册表项解决了SQL Server 11.0.7001:
[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesLanmanServerParametersFsctlAllowlist]
"FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT"=dword:0x00092560
听起来很奇怪,Microsoft 故意阻止在SQL CLR程序集中使用SqlFileStream
类(即使您授予EXTERNAL_ACCESS
或将程序集声明为UNSAFE
),您可以在Microsoft Connect问题768308中阅读。
然而,你可以通过SqlBytes类型访问FILESTREAM作为流(在博客文章中找到一个非常好的技巧)。至少对于只读用途,我从来没有尝试过写。
我复制了代码,以防blog消失(在稍微改进的版本中,适当地处理了对象):
using System;
using System.IO;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None)]
public static SqlBinary Hash(SqlBytes source, SqlString hashAlgorithmName)
{
if (Source.IsNull)
{
return null;
}
using (HashAlgorithm ha = GetHashAlgotithm(hashAlgorithmName.Value))
using (Stream stream = Source.Stream)
{
return new SqlBinary(ha.ComputeHash(source.Stream));
}
}
}
我可以确认这绝对适用于只读访问。我从未尝试过写访问(我正在从外部c# Windows服务或Web应用程序编写FILESTREAM数据)。