FILESTREAM - Read and write a large file in FILESTREAM column

Question

Should I use System.Data.SqlTypes.SqlFileStream to read and write a FILESTREAM column?

For example:

var sqlCommand = sqlConnection3.CreateCommand();
sqlCommand.CommandText = "Select FileData.PathName() As Path, GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext From PictureTable Where PkId = (Select Max(PkId) From PictureTable)"; 

var reader = sqlCommand.ExecuteReader();
reader.Read();
var filePath = (string)reader["Path"];
var transactionContext = (byte[])reader["TransactionContext"];
var sqlFileStream = new SqlFileStream(filePath, transactionContext, FileAccess.Read);
var data = new byte[sqlFileStream.Length];
sqlFileStream.Read(data, 0, Convert.ToInt16(sqlFileStream.Length));

In above code I can use the query shown here and directly access the FileData:

Select FileData From PictureTable Where PkId = (Select Max(PkId) From PictureTable)

What happens if don't use SqlFileStream and use varbinary(max) to read and write?

When SQL Server and Application installed in separate server, I have some problem in setup Windows Authentication to access SqlFileStream file path. How I can avoid this problem?

score 2 · Accepted Answer · answered Dec 31 '17 at 13:22

According to Microsoft's doc, SqlFileStream is the appropriate solution (see here). The SqlFileStream handles other things like file handles as data stored in a file stream is different from data stored in a row-based data file (see here).

Hope this helps!

FILESTREAM - Read and write a large file in FILESTREAM column

1 Answers1