Recently I was doing one POC for bringing the share point library documents to external data base. All this is done by passing authentication details by network credentials object with in a web application.SQL server 2008 has this add on feature where we can hold the documents on file system and SQL server 2008 is providing the var binary max FILESTREAM data type for it. Before using this data type SQL server must need to configure to use this data type. I am giving some code snippets and the steps to bring share point library docs and put it in other SQL server 2008 db in filestream.
1.Configuring the SQL server 2008 for using the FILESTREAM data type
To enable FILESTREAM storage at the instance level, Open the SQL Server 2008 Configuration Manager, Click on SQL Server Services on left side and then in the right side, right-click on the SQL Server instance you want to enable FILESTREAM storage on, choose Properties, then click on the FILESTREAM tab, and the following dialog box appears.
FILESTREAM storage has
now been enabled for the SQL Server instance.
The Transact-SQL code used to create a FILESTREAM-enabled database
looks like this:
CREATE DATABASE MyFileStream_Database
ON
PRIMARY ( NAME = Info1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM( NAME = FILESTREAM_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database)
LOG ON ( NAME = Log1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database.ldf')
GO
Create a new tables that include the VARBINARY(MAX) data type. The only difference between creating a standard VARBINARY(MAX) column in a table and a FILESTREAM-enabled VARBINARY(MAX) column is to add the keyword FILESTREAM after the VARBINARY(MAX). For example, to create a very simple table that can store FILESTREAM data, you can use code similar to this:
CREATE TABLE dbo.DocumentDetails
(
Doc_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE ,
Name varchar(100),
DocData VARBINARY(MAX) FILESTREAM
)
2 Bringing Share Point Data in Byte Array
below code snippet bring the file data in byte array when we pass the doc id and file url
";
Introducing FILESTREAM
FILESTREAM is a SQL Server 2008 feature that lets you store
unstructured BLOB data directly in the file system. FILESTREAM is not a
data type; it is an attribute imposed on a varbinary column to indicate that the data is to be stored
directly on the file system, thus maintaining transactional consistency.
A non-FILESTREAM storage format uses the buffer pool when
the data pages are accessed. FILESTREAM uses the NT system cache for caching
the file data. This approach helps reduce the effects that FILESTREAM data has
on database engine performance. While the buffer pool is relieved of managing
the varbinary(max) data pages, it is important to appreciate that the virtual
address space (VAS) is still shared between FILESTREAM data and SQL Server
data.
When using FILESTREAM, it is important to differentiate
between traditional data (called row data) and FILESTREAM data.
1.Configuring the SQL server 2008 for using the FILESTREAM data type
To enable FILESTREAM storage at the instance level, Open the SQL Server 2008 Configuration Manager, Click on SQL Server Services on left side and then in the right side, right-click on the SQL Server instance you want to enable FILESTREAM storage on, choose Properties, then click on the FILESTREAM tab, and the following dialog box appears.
The next step is to open SQL Server Management Studio (SSMS) and run the following
Transact-SQL code from a query window.
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
CREATE DATABASE MyFileStream_Database
ON
PRIMARY ( NAME = Info1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM( NAME = FILESTREAM_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database)
LOG ON ( NAME = Log1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ MyFileStream_Database.ldf')
GO
Create a new tables that include the VARBINARY(MAX) data type. The only difference between creating a standard VARBINARY(MAX) column in a table and a FILESTREAM-enabled VARBINARY(MAX) column is to add the keyword FILESTREAM after the VARBINARY(MAX). For example, to create a very simple table that can store FILESTREAM data, you can use code similar to this:
CREATE TABLE dbo.DocumentDetails
(
Doc_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE ,
Name varchar(100),
DocData VARBINARY(MAX) FILESTREAM
)
2 Bringing Share Point Data in Byte Array
below code snippet bring the file data in byte array when we pass the doc id and file url
public []byte
GetStreamFromFile(string docid, , string fileurl, ClientContext
clientContext)
{
byte[] bytesarr;
try
{
List
LibraryName=clientContext.Web.Lists.GetByTitle(lib);
clientContext.Load(LibraryName);
clientContext.ExecuteQuery();
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = ""
Microsoft.SharePoint.Client.ListItemCollection
collListItem = LibraryName.GetItems(camlQuery);
clientContext.Load(collListItem, items =>
items.Include(item => item.Id, item => item["FileLeafRef"],
item => item["LinkFilename"],
item => item["FileRef"],
item => item["File_x0020_Size"],
item => item["DocIcon"]));
//clientContext.Load(collListItem);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.ListItem oListItem in
collListItem)
{
string fileurl1 = (string)oListItem["FileRef"];
FileInformation ffl =
Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext,
fileurl1);
bytesarr = ReadFully(ffl.Stream);
obj.UpdateDocumentDetailForData(Convert.ToInt16(docid),
bytesarr);
}
}
catch (Exception
ex)
{
//Catch
exception here
}
return bytesarr;
}
3. Uploading file to filestream enabled table
Thid code upload the file to related table. I have called the dml quereis method directly here which calls the update store proc. Hope you can do that.. :)
//insert the file into database
public bool UpdateDocumentDetailForData(int docid , byte[]
data)
{
bool val = false;
try
{
//Add parameter of project id
ArrayList lstParametName = new ArrayList();
ArrayList lstParameterValue = new ArrayList();
ArrayList lstParameterTypes = new ArrayList();
lstParametName.Add("@ID");
lstParameterValue.Add(docid.ToString());
lstParameterTypes.Add(SqlDbType.VarChar);
lstParametName.Add("@DocumentData");
lstParameterValue.Add(data);
lstParameterTypes.Add(SqlDbType.VarBinary);
val = SqlHelper.executeProcIn("usp_UpdateDocumentDetailsByData",
lstParametName, lstParameterTypes, lstParameterValue);
}
catch (Exception
ex)
{
}
return val;
4. Download file from filestream enabled table
Here we are fetching the byte array from file stream enabled table and writing it to the local physical folder
private void DownloadFileToLocal(DataTable dt)
{
try
{
Byte[] bytes = (Byte[])dt.Rows[0]["DocumentData"];
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
string ContentType = string.Empty;
string filename = dt.Rows[0]["DocumentName"].ToString();
string extension = dt.Rows[0]["DocumentCategory"].ToString(); ;
if (extension == "txt")
{
ContentType = "text/plain";
}
else if (extension == "gif")
{
ContentType = "image/GIF";
}
else if (extension == "jpeg" || extension == "jpg")
{
ContentType = "image/JPEG";
}
else if ((extension == "doc") || (extension == "docx"))
{
ContentType = "application/x-msword";
}
else if ((extension == "xls") || (extension == "xlsx"))
{
ContentType = "application/x-msexcel";
}
else if (extension == "pdf")
{
ContentType = "application/pdf";
}
else if (extension == "vsd")
{
ContentType = "application/vsd";
}
else
{
ContentType = "application/" + extension;
}
Response.ContentType = ContentType;
Response.AddHeader("content-disposition", "attachment;filename="
+ dt.Rows[0]["DocumentName"].ToString());
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
catch (Exception ex)
{
LoggingHelper.getLogger().Error("Error occured in DownloadFileToLocal method of Transmittal template class of type " + ex);
}
}
Hope it will help you in this kind of scenario. Happy..coding..!
