In practical operations, sometimes it is necessary to query the content of uploaded files via SQL Server. We mainly use MSDN to understand Windows Indexing Service, which can implement full-text retrieval of files and allow querying through SQL Server. This article combines both technologies to achieve full-text retrieval of uploaded files.
### Solution Description:
1. Change the filename when storing files.
2. Configure the indexing server and associate it with MS SQL Server.
3. Modify the SQL statements to include full-text SQL Server query conditions.
### File Storage Method:
To facilitate storage and indexing, uploaded files are stored in a single directory. To ensure that filenames do not repeat, GUIDs are used as filenames and linked to database records via this GUID. Additionally, the original file extension is retained so that the indexing service can identify the document type.
### Configuration of Indexing Service:
Access the Computer Management program (right-click "My Computer" -> "Manage") and locate the Indexing Service.
#### Configuring Indexing Service Functionality:
1. Right-click on the Indexing Service to bring up a menu.
2. Select New -> Catalog (create a new catalog).
#### Setting Up the New Catalog:
1. Input the catalog name (this name will be associated with the database; naming should be carefully considered, assuming DCSII here).
2. Click Browse to select the directory where the indexing service runs.
3. Choose the previously created index directory (any location is fine).
4. Confirm to complete the operation.
After creating the catalog, you need to add directories to the catalog:
1. Right-click on the newly created catalog.
2. Select New -> Directory (add a new directory).
#### Creating a New Indexed Directory:
1. Click Browse to enter the directory selection page.
2. Select the directory containing the files for full-text search.
3. Confirm to complete the operation.
Once completed, start the Indexing Service:
1. Start the Indexing Service.
2. After successful startup, you can see the indexed catalog information, indicating that the service has started successfully.
### Associating Indexing Service with SQL Database:
Execute the following stored procedure in SQL Query Analyzer (SQL Query Analyer):
```sql
EXEC sp_addlinkedserver Dcs, -- Name of the linked server, needed for later SQL Server queries
'Index Server',
'MSIDXS',
'DCSII' -- Name of the newly created catalog in the Indexing Service
```
#### Performance Tuning Methods for Indexing Service:
**Method 1:**
1. Right-click on the Indexing Service.
2. Select All Tasks -> Tune Performance.
3. Choose custom settings.
4. Click Customize to enter the performance tuning form.
5. Move the properties of Index and Querying all the way to the right.
6. Confirm to complete the operation.
**Method 2:**
If the server does not need to index all system files, you can stop or delete the System catalog.
### MS SQL Calling the Indexing Server:
1. Query the content of the linked server using SQL Server:
```sql
SELECT Q.*
FROM OpenQuery(dcs, -- Name of the linked server
'SELECT FileName, Size, DocAuthor, path -- SQL statement within the linked server
FROM SCOPE() WHERE CONTAINS(''番号 and abc'') ') AS Q
```
2. Since SQL statements do not support dynamic string input, the SQL Server query string must be concatenated in C# code.
### Other Solutions:
In SQL Server 2000, enable the full-text search feature and store file contents in an image field, adding a new field to store the file type. Then, during the creation of a full-text index using Enterprise Manager, match these two fields. However, from the perspective of database efficiency and future backup and recovery efficiency, storing files directly in the database may have some drawbacks.
### Article Address:
[Eden Network](http://www.edenw.com/tech/devdeloper/database/2010-07-14/4681.html)