filestream in sql server and C# for aspx

Question

I am developing a website for educational domain. I want to store a document (MS Word or text file) in database in binary format using Filestream in SQL Server 2008. but I am unable to retrieve the document in a textbox.

My code is as follows:

string path = reader.GetString(0);
SqlFileStream stream1 = new SqlFileStream(path, (byte[])reader.GetValue(1), FileAccess.Read, FileOptions.SequentialScan, 0);   
StreamReader fs = new StreamReader(stream1);

fs = File.OpenText(path);
string s = fs.ReadToEnd();

txtInput.Text = s;
//lblStatus.Text = "File Succesfully Read!"
fs.Close();

This code only works for documents that are stored on the Filesystem not in the database. So I tried the following code:

string path = reader.GetString(0);
SqlFileStream stream1 = new SqlFileStream(path, (byte[])reader.GetValue(1), FileAccess.Read, FileOptions.SequentialScan, 0);   
StreamReader fs = new StreamReader(stream1);

fs = File.OpenText(path);
string s = fs.ReadToEnd();

txtInput.Text = s;
//lblStatus.Text = "File Succesfully Read!"
fs.Close();

In this code, it gives error on line fs = File.OpenText(path); as "Access denied to path".

Please help!

Answer 1

Check out this article - it shows in great detail how the filestream operations with SQL Server 2008 work.

Marc

Answer 2

As per my understanding, you need to connect to the server Via Windows Authentication. It will not work with SQL Server Authentication. And the Windows User should able access the Shared folder created by SQL Server for storing the Data.

Answer 3

You should read your data using stream1. The StreamReader and File.OpenText approaches will not work, you can only read filestream data using T-SQL or SqlFileStream object.

Answer 4

Could you please check whether you have enabled the filestream and checked the "Allow remote clients to have streaming access to FILESTREAM data" at the remote sql server instance.In my case it was not checked hence the win32 API was unable to get a valid handler while accessing the file at the filepath. You can check this link "http://msdn.microsoft.com/en-us/library/cc645923.aspx" (How to: Enable FILESTREAM ) Step # 8.

Answer 5

I'm facing the same problem when i'm trying to write an image file in my sql 2008 with the following code

Dim sqlFile As SqlFileStream
sqlFile = New SqlFileStream(filePathName.ToString, fileToken.Value, FileAccess.Write, FileOptions.WriteThrough, 0)

I have create an account in my server with which i loged in from my sql with indegrated security, because of the shared directory in my server but i'm receiving the same error 'Access Denied'

Have you find any way out of this?

Please tell me if you solved your problem