Here we are going to use SQL Server 2008 R2 (x64 Developers Edition), external driver from Adobe, Full-Text Search technology and FileStream technology.Because this sems a little bit comlicated let`s make this topic clear and do it step by step.
1) Enable FileStream - this part is pretty easy, just check wheter You already have enabled filestream on Your SQL Server instance - if no simply enable it as in the picture below.
|Picture 1. Enable filestream in SQL Server instance.|
2) Create SQL table to store files - mainly ther will be PDF file stored but some others is also be allright. Out table DocumentFile will be created in dbo schema and contain one column primary key with default value as sequential GUID. Important this is out table contains FileStream_Id and FileSource columns which are required do FileStream. Additionaly don`t miss the Extension column because we going need it for Full-Text Search.
3) Installing additional component for PDF file support - by default PDF files is not supported in SQL Server. To check PDF support installed status just execute following T-SQL command:
If after query execution You have no rows returned, this means You have to install PDF support for SQL Server from here (version for x64). When installation complete, to see PDF file support is int the sys.fulltext_document_types You must restart You SQL Server instance and then validate extension is in the supported type list.
|Picture 2. Properly installed PDF extension.|
T-SQL query below, enable FTS on databse and then create full-text catalog named Document_Catalog which is required for creating any FTS index on any table in database.
Now it`s time for creating full-text index. But before this, its time for a small inclusion because when You are using Entity Framework Code-First name of You primary key in any table vary between each time table is created. The problem is when creating FTS index on table we have to specified primary key index name. Query presented below retrieves primary key name from system tables and pass it further queries. Other important thing, as I write above, is Extension column. Here stored file extension have to be stored in the following format '.pdf'. This is required because SQL Server uses it to determine which Full-Text Search driver should be use. Out newly created index has change tracking set to auto so each time new document is added to index it`s automatically added to it. If you want to decide by You own when documents are updated in index set change tracking mode to manually.
After section four completed its time for our solution testing. For insert file to DocumentFiles table first insert simple data (Insert Into....) except FileStream_Id and FileSource. After it next upload file to FileStream directly from SQL Server as presented here or from C# code.
When You have data inserted You are able to query it as simple Full-Text data by using query as in example below.