Custom ADO.NET provider to intercept and modify sql queries

Question

Our client has an application that stores blob files in a database that have now grown so much that they affect the performance of SQL Server. To overcome this problem, we plan to upload all the drops to the file system and leave the file path in a new column in the user table.

As if the user had tabular documents with column identifiers, name and content (blob); we will ask him to add a new column "file path" in this table.

Our client is ready to make this change to this database. But when it comes to modifying sql queries to read and write to this table, they are not ready to accept this. In fact, they do not want any changes that lead to recompilation and deployment.

Now we plan to write a custom ADO.NET provider that will

• intercept selected requests
• add a column "file path" at the end of the select statement
• delete the result set and change the value of the "content" column based on the value of "filepath"

Is there any precedent that you think will fail with this approach?

I know this sounds dirty, but do we have a better way?

Answer 1

Have you tried switching the column to the FILESTREAM data type? This does everything that you described, pushing data from the database (to the file system). Of course, access should still go, although the server is like TDS, but you may find that the impact is less, and you can gradually change the code to directly access the file (to improve performance) - this last step will include changing the requests, however.