1.
Right click on the Server that you want to
enable file stream ->
Properties
2.
Check the enable filestream checkboxes
Step2 : In the SQLServer Management Studio
1.
Enable filestream by running below command
EXEC sp_configure filestream_access_level,
2
RECONFIGURE
Go
2.
Add File Group to the DB
ALTER DATABASE [Your_Database_Name]
ADD FILEGROUP
[Your_FileGroup_Name]
CONTAINS FILESTREAM
Go
3.
Add file directory to the filegroup
ALTER DATABASE
[Your_Database_Name]
ADD FILE
(NAME=Your_FileStreamName,
FILENAME='E:\FileStreamDir'
)
TO FILEGROUP
[Your_Database_Name]
Go
Note: to check the filestream access level your Sql
Server
SELECT * FROM
sys.configurations
WHERE name = 'filestream access level'
The “value in use” means:
- 0 = Disables FILESTREAM support for this instance.
- 1 = Enables FILESTREAM for Transact-SQL access.
- 2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.
Step3 : Create table that contains FileStream column
The table that has
filestream column must contain a uniqueIdentifier ROWGUIDCOL.
The File Stream column is defined as Varbinary (Max).
Use [Your_Database_Name];
Go
CREATE TABLE [dbo].[TblDocuments]
([Id] [uniqueidentifier]
ROWGUIDCOL DEFAULT
NEWID() NOT NULL UNIQUE,
[DocNumber] nvarchar(30) PRIMARY KEY CLUSTERED,
[FileName]
nvarchar(300),
[Description] nvarchar(300) ,
[DocFile] [varbinary]
(max) Filestream not null
)
ON [PRIMARY]
Go
Step 4 : Insert data to the filestream table
insert into TblDocuments (Id, DocNumber, Description, DocFile, FileName)
values (NEWID(),
'F001', 'test filestream'
CAST('Just a string' AS VARBINARY(MAX)),’test.pdf’)
APPLICATION WITH
FILESTREAM
To save file contents to
filestream from .Net application, you just convert your file to Byte[] and can
insert by Sql or any appropriate method.
byte[] bytes =
System.IO.File.ReadAllBytes(Myfile.FullName);
Read
filestream & write to a file in .NET
public
class Document
{
public string
FilePath { get; set;
}
public byte[]
ObjContext { get; set;
}
public string
FileName { get; set;
}
}
public void ReadFileStreamAndWriteToFile()
{
string
ConnectionString = Settings.GetConnectionString("main");
DataContext
context = CreateContext(ConnectionString);
context.Connection.Open();
//
File_dest needs to be modified to a directory you have access to.
string
file_loc = @"E:\\Temp\\";
DirectoryInfo
folder = new DirectoryInfo(file_dest);
using
(new TransactionScope(TransactionScopeOption.RequiresNew))
{
var
result = context.ExecuteQuery(typeof(Document), "SELECT
[DocFile].PathName() FilePath,
GET_FILESTREAM_TRANSACTION_CONTEXT() ObjContext, " +" FileName FROM [TblDocuments] where DocNumber=’F001'");
foreach
(Document doc in
result)
{
byte[] objContext = doc.ObjContext;
string filepath = doc.FilePath;
SqlFileStream sfs = new SqlFileStream(filepath,
objContext, System.IO.FileAccess.Read);
byte[] buffer = new byte[(int)sfs.Length];
sfs.Read(buffer, 0,
buffer.Length);
sfs.Close();
System.IO.FileStream
fs = new System.IO.FileStream(file_loc
+ doc.FileName, FileMode.Create, FileAccess.Write, FileShare.Write);
fs.Write(buffer, 0,
buffer.Length);
fs.Flush();
fs.Close();
}
}
}
private static WetaDataContext
CreateContext(string connectionString)
{
return
new CustomWetaDataContext(connectionString)
{
DeferredLoadingEnabled = true,
CommandTimeout = Settings.GetOptionalSetting("SqlCommandTimeout").AsInt(60)
};
}
Notes:
1.
If you have problem in creating
filestream on any directory of your PC
--->Check
and make sure the log in user of Sql Server service has full access to the
filestream location
2.
If you have problem in reading filestream
from the application:
--->The
user used to connect to the Sql Server from the .NET application should have Administrator
privilege on the PC and has access to the file stream directory.
<connectionStrings>
<add
connectionString="Server=localhost;Database=Your_DB_Name;User ID=test_user;Password=test123;integrated
security=true" name="main" />
</connectionStrings>
** “test_user” should be the
Window or Domain user with full access to the filestream directory AND can log
in to the Sql Server.
3.
To read data from filestream in .NET, you
must use transaction scope. Otherwise, GET_FILESTREAM_TRANSACTION_CONTEXT() will
return null.
