SQL Server 2008: FileStream Insertion Failure w/ .NET 3.5SP1
- by James Alexander
I've configured a db w/ a FileStream group and have a table w/ File type on it. When attempting to insert a streamed file and after I create the table row, my query to read the filepath out and the buffer returns a null file path. I can't seem to figure out why though. Here is the table creation script:
/****** Object: Table [dbo].[JobInstanceFile] Script Date: 03/22/2010 18:05:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[JobInstanceFile] ADD DEFAULT (newid()) FOR [FileId]
GO
Here's my proc I call to create the row before streaming the file:
/****** Object: StoredProcedure [dbo].[JobInstanceFileCreate] Script Date: 03/22/2010 18:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[JobInstanceFileCreate]
@JobInstanceId int,
@Created datetime
as
insert into JobInstanceFile (JobInstanceId, FileId, Created)
values (@JobInstanceId, newid(), @Created)
select scope_identity()
GO
And lastly, here's the code I'm using:
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;
}
}
}
Can someone please let me know what I'm doing wrong. In the code, the following expression is returning null for the file path and shouldn't be:
//get the file path we're writing out to
string writePath = dr.GetString(0);
The server is different then the computer the code is running on but the necessary shares appear to be in order and I have also run the following:
EXEC sp_configure filestream_access_level, 2
Any help would be greatly appreciated. Thanks!
