Hi
Our application allows you to save any file type into the MS SQL DB as a blob/image. I now have to provide the feature to search for text within files. Similar to the standard Windows "find in files" search.
What is the best way of achieving this? I've used a StreamReader to read all text from the file and then used Regex to do a match. Just not sure if this is the most efficient way to search within files.
Thanks
You should look into SQL Server's full text searching feature.
Here are some good articles:
Full-Text Search
SQL Server Full Text Search
SQL Server Full-Text Indexing
I think you will find that trying to pull back many large records from the database and then searching them in memory to be quite inefficient. This is an area where your RDBMS excels, and if configured correctly, can make your life a lot simpler.
You will probably save a lot of time if you use full text search in the sql server ? That will let you query the files as well as handle some very complex queries. It is able to search inside blobs using iFilters (like microsoft frontpage)
This is a good primer to the basics http://aspalliance.com/1512_understanding_full_text_search_in_sql_server_2005 .
Doing it this way you can leverage the work MS did in full text search; and gain from their interfaces if your file types are not supported (most comment document formats are supported directly or but their producer doc, docx, xls, pdf etc)
In my opinion the search should be done by the sql server that is optimized for doning things like this