Using FileStream for holding the share point document out side the Sharepoint

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.

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

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

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 = "" + fileurl + "

";

                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..!