How to publish a document stored in SQL using the original file name as a valid URL?

Question

I have a database that stores PDF documents as a stream of bytes in a varbinary (max) column. The original document name is stored in the varchar column with the document extension, and the primary key is a guide. (This scheme is fixed, documents must be in the database)

There is no problem getting the document as a stream and either to write to a temporary file or to stream directly to a web browser.

My question is, how can a document be opened via a URL using the original file name?

A good example of what I'm trying to do is SharePoint. Coverages SharePoint stores files in an SQL database and provides the document as a URL (with the original file extension) that can be opened using a browser.

for example: http: //WebServer/Documents/MyDocument.pdf

(SQL 2008 platform, IIS 7.5, .NET 4, C #)

Answer 1

If your site uses ASP.Net MVC, you can simply add a controller for the document upload file by name:

routes.MapRoute( "GetFiles", "Documents/{fileName}", new { controller = "Files", action="GetFile", } ); 

And the controller class:

  public class FilesController : Controller { [AcceptVerbs(HttpVerbs.Get)] public ActionResult GetFile(string fileName) {... } } 

Answer 2

Without using MVC you can use something called IHttpHandler

This will allow you to process the request for the URL in any way convenient for you. You will add the code to your web.config, for example:

 <httpHandlers> <add verb="*" path="/documents/*.pdf" type="Documents.LoadDocument,NameSpace"/> </httpHandlers> 

Then you will have the Namespace.Documents.LoadDocument class. This implements the IHttpHandler interface (there are only two methods, and one of them is just a bool that indicates whether your object is thread safe)

There is information here that describes how to serve dynamic content using IHttpHandler.

I believe that this is exactly what you are looking for. You can download a document from the database based on the actual HTTP request.

Answer 3

You need to add the Content-Disposition header to the response:

 Content-Disposition: attachment; filename=original_name.pdf 

Answer 4

See Uploading and uploading images from SQL Server through ASP.Net MVC for a complete example containing routing that maps a file URL to a database, efficient loading using streams, and efficient loading using streams.

Part of routing is relatively straightforward with MVC:

 routes.MapRoute( "Media", "Media/{filename}", new { controller = "Media", action = "GetFile" }, new { filename = @"[^/?*:;{}\\]+" }); 

but other bits and pieces, especially the download, which uses the correct streaming semantics, may be a little more complex, which are usually expected. Most samples simply ignore the problems and create a copy in memory of the entire file before uploading it to the database.

The following FILESTREAM MVC: Downloading and loading images from SQL Server extends this article to cover the FILESTREAM repository.