c# - SQL Server 2008 : FileStream Insertion Failure w/. NET 3.5SP1

Question

我已经配置了一个带有 FileStream 组的数据库，并且上面有一个带有文件类型的表。尝试插入流式文件时，在创建表行后，我查询读取文件路径和缓冲区返回空文件路径。我似乎无法弄清楚为什么。这是表创建脚本:

    /****** Object:  Table [dbo].[JobInstanceFile]    Script Date: 03/22/2010 18:05:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[JobInstanceFile](    [JobInstanceFileId] [int] IDENTITY(1,1) NOT NULL,    [JobInstanceId] [int] NOT NULL,    [File] [varbinary](max) FILESTREAM  NULL,    [FileId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,    [Created] [datetime] NOT NULL, CONSTRAINT [PK_JobInstanceFile] PRIMARY KEY CLUSTERED (    [JobInstanceFileId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [JobInstanceFilesGroup],UNIQUE NONCLUSTERED (    [FileId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] FILESTREAM_ON [JobInstanceFilesGroup]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[JobInstanceFile] ADD  DEFAULT (newid()) FOR [FileId]GO

这是我在流式传输文件之前调用以创建行的过程:

    /****** Object:  StoredProcedure [dbo].[JobInstanceFileCreate]    Script Date: 03/22/2010 18:06:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate proc [dbo].[JobInstanceFileCreate]    @JobInstanceId int,    @Created datetime    as    insert into JobInstanceFile (JobInstanceId, FileId, Created)    values (@JobInstanceId, newid(), @Created)    select scope_identity()GO

最后，这是我使用的代码:

    public int CreateJobInstanceFile(int jobInstanceId, string filePath)        {            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsumerMarketingStoreFiles"].ConnectionString))            using (var fileStream = new FileStream(filePath, FileMode.Open))            {                connection.Open();                var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);                try                 {                    //create the JobInstanceFile instance                    var command = new SqlCommand("JobInstanceFileCreate", connection) { Transaction = tran };                    command.CommandType = CommandType.StoredProcedure;                    command.Parameters.AddWithValue("@JobInstanceId", jobInstanceId);                    command.Parameters.AddWithValue("@Created", DateTime.Now);                    int jobInstanceFileId = Convert.ToInt32(command.ExecuteScalar());                    //read out the filestream transaction context to stream the file for storage                    command.CommandText = "select [File].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() from JobInstanceFile where JobInstanceFileId = @JobInstanceFileId";                    command.CommandType = CommandType.Text;                    command.Parameters.AddWithValue("@JobInstanceFileId", jobInstanceFileId);                    using (SqlDataReader dr = command.ExecuteReader())                    {                        dr.Read();                        //get the file path we're writing out to                        string writePath = dr.GetString(0);                        using (var writeStream = new SqlFileStream(writePath, (byte[])dr.GetValue(1), FileAccess.ReadWrite))                        {                            //copy from one stream to another                            byte[] bytes = new byte[65536];                            int numBytes;                            while ((numBytes = fileStream.Read(bytes, 0, 65536)) > 0)                                writeStream.Write(bytes, 0, numBytes);                        }                    }                    tran.Commit();                    return jobInstanceFileId;                }                catch (Exception e)                {                    tran.Rollback();                    throw e;                }            }        }

有人可以让我知道我做错了什么吗？在代码中，以下表达式为文件路径返回 null，不应为:

//获取我们要写入的文件路径string writePath = dr.GetString(0);

服务器与运行代码的计算机不同，但必要的共享似乎是有序的，我还运行了以下命令:

执行 sp_configure filestream_access_level, 2

如有任何帮助，我们将不胜感激。谢谢!

Answer 1

您不能在 NULL 值上打开 SqlFileStream。更改 INSERT 语句以设置 File = 0x，它应该开始工作。

附言。一些性能考虑因素:

1. 您可以通过使用 OUTPUT clause 发出 INSERT 来避免与服务器的额外往返返回新插入的 blob 的事务上下文和文件路径。
2. 如果您不打算从 SqlFileStream 中读取，请将其打开为只写。在这种特殊情况下，这可能不是什么大问题，因为您打开的文件是空的。但是，如果您正在更新一个大文件，以 ReadWrite 方式打开它，Sql 将首先复制整个文件，然后才让您使用它。