Use a Generic Handler (HTTP) to retrieve files from a SQL blob VARBINARY(MAX)

Here are some basic instructions for setting up a file system that relies on SQL blobs. I use a generic handler (Http Handler) using a query string to retrieve files by ID.

1. Create an Attachment table and INSERT and SELECT stored procedures.
2. Create a Web Service or WCF project.
3. Add a “LINQ to SQL Classes” class to your project and drag the SELECT stored procedure in the the Methods Pane.
4. Add a “Generic Handler” (.ashx file) to your project. I called mine “DownloadFile”.
   • Check for a query string that contains an attachmentId and then pass it to the SELECT method in the LINQ to SQL class.
   • Use the HttpContext Response method to stream the file which is returned as a byte array. Make sure to set the Content Type.
5. Set the Web Service or WCF project up in IIS.
6. You can now download a file from the blob using a URL like http://[servicename]/DownloadFile.ashx?attachmentId=123

SQL CODE

--CREATE TABLE

CREATE TABLE [dbo].[Attachments]([ID] [int] IDENTITY(1,1) NOT NULL, [Attachment] [varbinary](max) NULL, [FileName] [nvarchar](255) NOT NULL
CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED
(
    [AttachmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--CREATE INSERT STORED PROCEDURE

CREATE PROCEDURE [dbo].[usp_InsertAttachment]
    @attachment varbinary(max), @fileName nvarchar(255)
AS
INSERT INTO Attachments (Attachment, Attachments.[FileName]) VALUES (@attachment, @fileName)

--CREATE SELECT STORED PROCEDURE

CREATE PROCEDURE [dbo].[usp_SelectAttachment]
    @attachmentID int
AS SELECT [FileName], Attachment FROM Attachments WHERE AttachmentID = @attachmentID

C# Service Code

using System;
using System.Web;
using System.Web.Services;
using System.IO;

namespace Acme.Service
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    public class DownloadFile : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            int attachmentId = 0;
            try
            {
                Service s = new Service();                
                if (context.Request.QueryString["attachmentId"] != null)
                {
                    attachmentId = Convert.ToInt32(context.Request.QueryString["attachmentId"]);
                }
                if (attachmentId != 0)
                {
                    usp_SelectAttachmentResult r = s.GetAttachment((int?)attachmentId);
                    context.Response.AddHeader("Content-Length", r.Attachment.Length.ToString());
                    context.Response.AddHeader("Content-Disposition", "application;filename=" + r.FileName);
                    context.Response.ContentType = FileExtension.GetContentType(Path.GetExtension(r.FileName));
                    context.Response.BinaryWrite(r.Attachment.ToArray());
                    context.Response.Flush();
                    context.Response.End();
                }
                else
                {
                    throw new Exception("The Attachment ID (" + attachmentId.ToString() + ") does not exist.");
                }
            }
            catch (Exception ex)
            {                               
                context.Response.Write("<b>" + ex.Message + "<br /><br />Error:</b><br />" + ex.ToString() + "<br /><br /><b>Stack Trace:</b><br />" + ex.ToString());
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

C# Code for Retrieving File Extensions

public static class FileExtension
{
    public static string GetContentType(string fileExtension)
    {
        string contentType = string.Empty;
        switch (fileExtension)
        {
            default: contentType = "application/octet-stream"; break;
            case ".323": contentType = "text/h323"; break;
            case ".3g2": contentType = "video/3gpp2"; break;
            case ".3gp": contentType = "video/3gpp"; break;
            case ".3gp2": contentType = "video/3gpp2"; break;
            case ".3gpp": contentType = "video/3gpp"; break;
            case ".aac": contentType = "audio/aac"; break;
            case ".ac3": contentType = "audio/ac3"; break;
            case ".accda": contentType = "application/msaccess"; break;
            case ".accdb": contentType = "application/msaccess"; break;
            case ".accdc": contentType = "application/msaccess"; break;
            case ".accde": contentType = "application/msaccess"; break;
            case ".accdr": contentType = "application/msaccess"; break;
            case ".accdt": contentType = "application/msaccess"; break;
            case ".acrobatsecuritysettings": contentType = "application/vnd.adobe.acrobat-security-settings"; break;
            case ".AddIn": contentType = "text/xml"; break;
            case ".ade": contentType = "application/msaccess"; break;
            case ".adp": contentType = "application/msaccess"; break;
            case ".adts": contentType = "audio/aac"; break;
            case ".ai": contentType = "application/postscript"; break;
            case ".aif": contentType = "audio/aiff"; break;
            case ".aifc": contentType = "audio/aiff"; break;
            case ".aiff": contentType = "audio/aiff"; break;
            case ".air": contentType = "application/vnd.adobe.air-application-installer-package+zip"; break;
            case ".amc": contentType = "application/x-mpeg"; break;
            case ".application": contentType = "application/x-ms-application"; break;
            case ".asa": contentType = "application/xml"; break;
            case ".asax": contentType = "application/xml"; break;
            case ".ascx": contentType = "application/xml"; break;
            case ".asf": contentType = "video/x-ms-asf"; break;
            case ".ashx": contentType = "application/xml"; break;
            case ".asm": contentType = "text/plain"; break;
            case ".asmx": contentType = "application/xml"; break;
            case ".aspx": contentType = "application/xml"; break;
            case ".asx": contentType = "video/x-ms-asf"; break;
            case ".au": contentType = "audio/basic"; break;
            case ".avi": contentType = "video/avi"; break;
            case ".bcf": contentType = "application/vnd.belarc-cf"; break;
            case ".bci": contentType = "application/vnd.belarc-bci"; break;
            case ".blogthis": contentType = "application/x-blogthis"; break;
            case ".bmp": contentType = "image/bmp"; break;
            case ".c": contentType = "text/plain"; break;
            case ".caf": contentType = "audio/x-caf"; break;
            case ".cat": contentType = "application/vnd.ms-pki.seccat"; break;
            case ".cc": contentType = "text/plain"; break;
            case ".cd": contentType = "text/plain"; break;
            case ".cdda": contentType = "audio/aiff"; break;
            case ".cer": contentType = "application/x-x509-ca-cert"; break;
            case ".cod": contentType = "text/plain"; break;
            case ".config": contentType = "application/xml"; break;
            case ".coverage": contentType = "application/xml"; break;
            case ".cpp": contentType = "text/plain"; break;
            case ".crl": contentType = "application/pkix-crl"; break;
            case ".crt": contentType = "application/x-x509-ca-cert"; break;
            case ".cs": contentType = "text/plain"; break;
            case ".csdproj": contentType = "text/plain"; break;
            case ".csproj": contentType = "text/plain"; break;
            case ".csv": contentType = "application/vnd.ms-excel"; break;
            case ".cur": contentType = "text/plain"; break;
            case ".cxx": contentType = "text/plain"; break;
            case ".datasource": contentType = "application/xml"; break;
            case ".dcr": contentType = "application/x-director"; break;
            case ".def": contentType = "text/plain"; break;
            case ".der": contentType = "application/x-x509-ca-cert"; break;
            case ".dib": contentType = "image/bmp"; break;
            case ".dif": contentType = "video/x-dv"; break;
            case ".dir": contentType = "application/x-director"; break;
            case ".divx": contentType = "ICM.DIV6"; break;
            case ".dll": contentType = "application/x-msdownload"; break;
            case ".dlm": contentType = "text/dlm"; break;
            case ".doc": contentType = "application/msword"; break;
            case ".docm": contentType = "application/vnd.ms-word.document.macroEnabled.12"; break;
            case ".docx": contentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"; break;
            case ".dot": contentType = "application/msword"; break;
            case ".dotm": contentType = "application/vnd.ms-word.template.macroEnabled.12"; break;
            case ".dotx": contentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.template"; break;
            case ".dsp": contentType = "text/plain"; break;
            case ".dsw": contentType = "text/plain"; break;
            case ".dtd": contentType = "application/xml-dtd"; break;
            case ".dtsConfig": contentType = "text/xml"; break;
            case ".dv": contentType = "video/x-dv"; break;
            case ".dwfx": contentType = "model/vnd.dwfx+xps"; break;
            case ".dxr": contentType = "application/x-director"; break;
            case ".eml": contentType = "message/rfc822"; break;
            case ".eps": contentType = "application/postscript"; break;
            case ".EVR": contentType = "audio/evrc-qcp"; break;
            case ".EVRC": contentType = "audio/evrc-qcp"; break;
            case ".exe": contentType = "application/x-msdownload"; break;
            case ".fdf": contentType = "application/vnd.fdf"; break;
            case ".fif": contentType = "application/fractals"; break;
            case ".flv": contentType = "video/x-flv"; break;
            case ".gif": contentType = "image/gif"; break;
            case ".gsm": contentType = "audio/x-gsm"; break;
            case ".gz": contentType = "application/x-gzip"; break;
            case ".h": contentType = "text/plain"; break;
            case ".hpp": contentType = "text/plain"; break;
            case ".hqx": contentType = "application/mac-binhex40"; break;
            case ".hta": contentType = "application/hta"; break;
            case ".htm": contentType = "text/html"; break;
            case ".html": contentType = "text/html"; break;
            case ".hxa": contentType = "application/xml"; break;
            case ".hxc": contentType = "application/xml"; break;
            case ".hxd": contentType = "application/octet-stream"; break;
            case ".hxe": contentType = "application/xml"; break;
            case ".hxf": contentType = "application/xml"; break;
            case ".hxh": contentType = "application/octet-stream"; break;
            case ".hxi": contentType = "application/octet-stream"; break;
            case ".hxk": contentType = "application/xml"; break;
            case ".hxq": contentType = "application/octet-stream"; break;
            case ".hxr": contentType = "application/octet-stream"; break;
            case ".hxs": contentType = "application/octet-stream"; break;
            case ".hxt": contentType = "application/xml"; break;
            case ".hxv": contentType = "application/xml"; break;
            case ".hxw": contentType = "application/octet-stream"; break;
            case ".hxx": contentType = "text/plain"; break;
            case ".i": contentType = "text/plain"; break;
            case ".ico": contentType = "image/x-icon"; break;
            case ".idl": contentType = "text/plain"; break;
            case ".iii": contentType = "application/x-iphone"; break;
            case ".inc": contentType = "text/plain"; break;
            case ".infopathxml": contentType = "application/ms-infopath.xml"; break;
            case ".inl": contentType = "text/plain"; break;
            case ".ins": contentType = "application/x-internet-signup"; break;
            case ".ipproj": contentType = "text/plain"; break;
            case ".iqy": contentType = "text/x-ms-iqy"; break;
            case ".ismv": contentType = "video/ismv"; break;
            case ".isp": contentType = "application/x-internet-signup"; break;
            case ".jfif": contentType = "image/jpeg"; break;
            case ".jnlp": contentType = "application/x-java-jnlp-file"; break;
            case ".jpe": contentType = "image/jpeg"; break;
            case ".jpeg": contentType = "image/jpeg"; break;
            case ".jpg": contentType = "image/jpeg"; break;
            case ".jtx": contentType = "application/x-jtx+xps"; break;
            case ".khub": contentType = "application/x-khuboffline"; break;
            case ".latex": contentType = "application/x-latex"; break;
            case ".lst": contentType = "text/plain"; break;
            case ".m1v": contentType = "video/mpeg"; break;
            case ".m2v": contentType = "video/mpeg"; break;
            case ".m3u": contentType = "audio/x-mpegurl"; break;
            case ".m4a": contentType = "audio/x-m4a"; break;
            case ".m4b": contentType = "audio/x-m4b"; break;
            case ".m4p": contentType = "audio/x-m4p"; break;
            case ".m4v": contentType = "video/x-m4v"; break;
            case ".mac": contentType = "image/x-macpaint"; break;
            case ".mak": contentType = "text/plain"; break;
            case ".man": contentType = "application/x-troff-man"; break;
            case ".map": contentType = "text/plain"; break;
            case ".master": contentType = "application/xml"; break;
            case ".mda": contentType = "application/msaccess"; break;
            case ".mdb": contentType = "application/msaccess"; break;
            case ".mde": contentType = "application/msaccess"; break;
            case ".mdp": contentType = "text/plain"; break;
            case ".mfp": contentType = "application/x-shockwave-flash"; break;
            case ".mht": contentType = "message/rfc822"; break;
            case ".mhtml": contentType = "message/rfc822"; break;
            case ".mid": contentType = "audio/mid"; break;
            case ".midi": contentType = "audio/mid"; break;
            case ".mk": contentType = "text/plain"; break;
            case ".mod": contentType = "video/mpeg"; break;
            case ".mov": contentType = "video/quicktime"; break;
            case ".mp2": contentType = "video/mpeg"; break;
            case ".mp2v": contentType = "video/mpeg"; break;
            case ".mp3": contentType = "audio/mpeg"; break;
            case ".mp4": contentType = "video/mp4"; break;
            case ".mpa": contentType = "video/mpeg"; break;
            case ".mpd": contentType = "application/vnd.ms-project"; break;
            case ".mpe": contentType = "video/mpeg"; break;
            case ".mpeg": contentType = "video/mpeg"; break;
            case ".mpf": contentType = "application/vnd.ms-mediapackage"; break;
            case ".mpg": contentType = "video/mpeg"; break;
            case ".mpp": contentType = "application/vnd.ms-project"; break;
            case ".mpt": contentType = "application/vnd.ms-project"; break;
            case ".mpv2": contentType = "video/mpeg"; break;
            case ".mpw": contentType = "application/vnd.ms-project"; break;
            case ".mpx": contentType = "application/vnd.ms-project"; break;
            case ".mqv": contentType = "video/quicktime"; break;
            case ".NMW": contentType = "application/nmwb"; break;
            case ".nws": contentType = "message/rfc822"; break;
            case ".odc": contentType = "text/x-ms-odc"; break;
            case ".odh": contentType = "text/plain"; break;
            case ".odl": contentType = "text/plain"; break;
            case ".odp": contentType = "application/vnd.oasis.opendocument.presentation"; break;
            case ".ods": contentType = "application/vnd.oasis.opendocument.spreadsheet"; break;
            case ".odt": contentType = "application/vnd.oasis.opendocument.text"; break;
            case ".ols": contentType = "application/vnd.ms-publisher"; break;
            case ".orderedtest": contentType = "application/xml"; break;
            case ".p10": contentType = "application/pkcs10"; break;
            case ".p12": contentType = "application/x-pkcs12"; break;
            case ".p7b": contentType = "application/x-pkcs7-certificates"; break;
            case ".p7c": contentType = "application/pkcs7-mime"; break;
            case ".p7m": contentType = "application/pkcs7-mime"; break;
            case ".p7r": contentType = "application/x-pkcs7-certreqresp"; break;
            case ".p7s": contentType = "application/pkcs7-signature"; break;
            case ".pct": contentType = "image/pict"; break;
            case ".pdf": contentType = "application/pdf"; break;
            case ".pdfxml": contentType = "application/vnd.adobe.pdfxml"; break;
            case ".pdx": contentType = "application/vnd.adobe.pdx"; break;
            case ".pfx": contentType = "application/x-pkcs12"; break;
            case ".pic": contentType = "image/pict"; break;
            case ".pict": contentType = "image/pict"; break;
            case ".pko": contentType = "application/vnd.ms-pki.pko"; break;
            case ".png": contentType = "image/png"; break;
            case ".pnt": contentType = "image/x-macpaint"; break;
            case ".pntg": contentType = "image/x-macpaint"; break;
            case ".pot": contentType = "application/vnd.ms-powerpoint"; break;
            case ".potm": contentType = "application/vnd.ms-powerpoint.template.macroEnabled.12"; break;
            case ".potx": contentType = "application/vnd.openxmlformats-officedocument.presentationml.template"; break;
            case ".ppa": contentType = "application/vnd.ms-powerpoint"; break;
            case ".ppam": contentType = "application/vnd.ms-powerpoint.addin.macroEnabled.12"; break;
            case ".pps": contentType = "application/vnd.ms-powerpoint"; break;
            case ".ppsm": contentType = "application/vnd.ms-powerpoint.slideshow.macroEnabled.12"; break;
            case ".ppsx": contentType = "application/vnd.openxmlformats-officedocument.presentationml.slideshow"; break;
            case ".ppt": contentType = "application/vnd.ms-powerpoint"; break;
            case ".pptm": contentType = "application/vnd.ms-powerpoint.presentation.macroEnabled.12"; break;
            case ".pptx": contentType = "application/vnd.openxmlformats-officedocument.presentationml.presentation"; break;
            case ".prf": contentType = "application/pics-rules"; break;
            case ".ps": contentType = "application/postscript"; break;
            case ".psc1": contentType = "application/PowerShell"; break;
            case ".pub": contentType = "application/vnd.ms-publisher"; break;
            case ".pwz": contentType = "application/vnd.ms-powerpoint"; break;
            case ".qcp": contentType = "audio/qcelp"; break;
            case ".qht": contentType = "text/x-html-insertion"; break;
            case ".qhtm": contentType = "text/x-html-insertion"; break;
            case ".qt": contentType = "video/quicktime"; break;
            case ".qti": contentType = "image/x-quicktime"; break;
            case ".qtif": contentType = "image/x-quicktime"; break;
            case ".qtl": contentType = "application/x-quicktimeplayer"; break;
            case ".ra": contentType = "audio/vnd.rn-realaudio"; break;
            case ".ram": contentType = "audio/x-pn-realaudio"; break;
            case ".rat": contentType = "application/rat-file"; break;
            case ".rc": contentType = "text/plain"; break;
            case ".rc2": contentType = "text/plain"; break;
            case ".rct": contentType = "text/plain"; break;
            case ".rdlc": contentType = "application/xml"; break;
            case ".resx": contentType = "application/xml"; break;
            case ".rgs": contentType = "text/plain"; break;
            case ".rjt": contentType = "application/vnd.rn-realsystem-rjt"; break;
            case ".rm": contentType = "application/vnd.rn-realmedia"; break;
            case ".rmi": contentType = "audio/mid"; break;
            case ".rmj": contentType = "application/vnd.rn-realsystem-rmj"; break;
            case ".rmm": contentType = "audio/x-pn-realaudio"; break;
            case ".rmp": contentType = "application/vnd.rn-rn_music_package"; break;
            case ".rms": contentType = "application/vnd.rn-realmedia-secure"; break;
            case ".rmvb": contentType = "application/vnd.rn-realmedia-vbr"; break;
            case ".rmx": contentType = "application/vnd.rn-realsystem-rmx"; break;
            case ".rnx": contentType = "application/vnd.rn-realplayer"; break;
            case ".rp": contentType = "image/vnd.rn-realpix"; break;
            case ".rpm": contentType = "audio/x-pn-realaudio-plugin"; break;
            case ".rpt": contentType = "application/x-rpt"; break;
            case ".rqy": contentType = "text/x-ms-rqy"; break;
            case ".rsml": contentType = "application/vnd.rn-rsml"; break;
            case ".rt": contentType = "text/vnd.rn-realtext"; break;
            case ".rtf": contentType = "application/msword"; break;
            case ".rv": contentType = "video/vnd.rn-realvideo"; break;
            case ".s": contentType = "text/plain"; break;
            case ".sct": contentType = "text/scriptlet"; break;
            case ".sd2": contentType = "audio/x-sd2"; break;
            case ".sdp": contentType = "application/sdp"; break;
            case ".settings": contentType = "application/xml"; break;
            case ".shtml": contentType = "text/html"; break;
            case ".sit": contentType = "application/x-stuffit"; break;
            case ".sitemap": contentType = "application/xml"; break;
            case ".skin": contentType = "application/xml"; break;
            case ".sldm": contentType = "application/vnd.ms-powerpoint.slide.macroEnabled.12"; break;
            case ".sldx": contentType = "application/vnd.openxmlformats-officedocument.presentationml.slide"; break;
            case ".slk": contentType = "application/vnd.ms-excel"; break;
            case ".sln": contentType = "text/plain"; break;
            case ".smi": contentType = "application/smil"; break;
            case ".smil": contentType = "application/smil"; break;
            case ".snd": contentType = "audio/basic"; break;
            case ".snippet": contentType = "application/xml"; break;
            case ".sol": contentType = "text/plain"; break;
            case ".sor": contentType = "text/plain"; break;
            case ".spc": contentType = "application/x-pkcs7-certificates"; break;
            case ".spl": contentType = "application/futuresplash"; break;
            case ".srf": contentType = "text/plain"; break;
            case ".SSISDeploymentManifest": contentType = "text/xml"; break;
            case ".sst": contentType = "application/vnd.ms-pki.certstore"; break;
            case ".stl": contentType = "application/vnd.ms-pki.stl"; break;
            case ".svc": contentType = "application/xml"; break;
            case ".swf": contentType = "application/x-shockwave-flash"; break;
            case ".tar": contentType = "application/x-tar"; break;
            case ".testrunconfig": contentType = "application/xml"; break;
            case ".tga": contentType = "image/targa"; break;
            case ".tgz": contentType = "application/x-compressed"; break;
            case ".thmx": contentType = "application/vnd.ms-officetheme"; break;
            case ".tlh": contentType = "text/plain"; break;
            case ".tli": contentType = "text/plain"; break;
            case ".trx": contentType = "application/xml"; break;
            case ".txt": contentType = "text/plain"; break;
            case ".uls": contentType = "text/iuls"; break;
            case ".user": contentType = "text/plain"; break;
            case ".vb": contentType = "text/plain"; break;
            case ".vbdproj": contentType = "text/plain"; break;
            case ".vbproj": contentType = "text/plain"; break;
            case ".vcproj": contentType = "Application/xml"; break;
            case ".vddproj": contentType = "text/plain"; break;
            case ".vdp": contentType = "text/plain"; break;
            case ".vdproj": contentType = "text/plain"; break;
            case ".vdx": contentType = "application/vnd.visio"; break;
            case ".vscontent": contentType = "application/xml"; break;
            case ".vsd": contentType = "application/vnd.visio"; break;
            case ".vsi": contentType = "application/ms-vsi"; break;
            case ".vsl": contentType = "application/vnd.visio"; break;
            case ".vsmdi": contentType = "application/xml"; break;
            case ".vspscc": contentType = "text/plain"; break;
            case ".vss": contentType = "application/vnd.visio"; break;
            case ".vsscc": contentType = "text/plain"; break;
            case ".vssettings": contentType = "text/xml"; break;
            case ".vssscc": contentType = "text/plain"; break;
            case ".vst": contentType = "application/vnd.visio"; break;
            case ".vstemplate": contentType = "text/xml"; break;
            case ".vsu": contentType = "application/vnd.visio"; break;
            case ".vsw": contentType = "application/vnd.visio"; break;
            case ".vsx": contentType = "application/vnd.visio"; break;
            case ".vtx": contentType = "application/vnd.visio"; break;
            case ".wav": contentType = "audio/wav"; break;
            case ".wax": contentType = "audio/x-ms-wax"; break;
            case ".wbk": contentType = "application/msword"; break;
            case ".wdp": contentType = "image/vnd.ms-photo"; break;
            case ".wiz": contentType = "application/msword"; break;
            case ".wm": contentType = "video/x-ms-wm"; break;
            case ".wma": contentType = "audio/x-ms-wma"; break;
            case ".wmd": contentType = "application/x-ms-wmd"; break;
            case ".wmv": contentType = "video/x-ms-wmv"; break;
            case ".wmx": contentType = "video/x-ms-wmx"; break;
            case ".wmz": contentType = "application/x-ms-wmz"; break;
            case ".wpl": contentType = "application/vnd.ms-wpl"; break;
            case ".wpost": contentType = "application/x-wpost"; break;
            case ".wsc": contentType = "text/scriptlet"; break;
            case ".wsdl": contentType = "application/xml"; break;
            case ".wvx": contentType = "video/x-ms-wvx"; break;
            case ".xaml": contentType = "application/xaml+xml"; break;
            case ".xbap": contentType = "application/x-ms-xbap"; break;
            case ".xdp": contentType = "application/vnd.adobe.xdp+xml"; break;
            case ".xdr": contentType = "application/xml"; break;
            case ".xfdf": contentType = "application/vnd.adobe.xfdf"; break;
            case ".xht": contentType = "application/xhtml+xml"; break;
            case ".xhtml": contentType = "application/xhtml+xml"; break;
            case ".xla": contentType = "application/vnd.ms-excel"; break;
            case ".xlam": contentType = "application/vnd.ms-excel.addin.macroEnabled.12"; break;
            case ".xlk": contentType = "application/vnd.ms-excel"; break;
            case ".xll": contentType = "application/vnd.ms-excel"; break;
            case ".xlm": contentType = "application/vnd.ms-excel"; break;
            case ".xls": contentType = "application/vnd.ms-excel"; break;
            case ".xlsb": contentType = "application/vnd.ms-excel.sheet.binary.macroEnabled.12"; break;
            case ".xlsm": contentType = "application/vnd.ms-excel.sheet.macroEnabled.12"; break;
            case ".xlsx": contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; break;
            case ".xlt": contentType = "application/vnd.ms-excel"; break;
            case ".xltm": contentType = "application/vnd.ms-excel.template.macroEnabled.12"; break;
            case ".xltx": contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.template"; break;
            case ".xlw": contentType = "application/vnd.ms-excel"; break;
            case ".xml": contentType = "text/xml"; break;
            case ".xmta": contentType = "application/xml"; break;
            case ".XOML": contentType = "text/plain"; break;
            case ".xps": contentType = "application/vnd.ms-xpsdocument"; break;
            case ".xsc": contentType = "application/xml"; break;
            case ".xsd": contentType = "application/xml"; break;
            case ".xsl": contentType = "text/xml"; break;
            case ".xslt": contentType = "application/xml"; break;
            case ".xss": contentType = "application/xml"; break;
            case ".z": contentType = "application/x-compress"; break;
            case ".zip": contentType = "application/x-zip-compressed"; break;
        }
        return contentType;
    }
}

Note: In order to get ContentTypes, you can create a simple console app with the following code. I haven’t really determined a better way to do this. This is local to the machine, and is only a sample of possible content types. Another alternative is to store them in a column in the Attachments table on the INSERT. However, .NET does not have a GetContentType method for the FileInfo object.

using System;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Microsoft.Win32;

namespace GetFileExtensionsContentTypes
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            string fileExtensions = string.Empty;
            using (RegistryKey key = Registry.ClassesRoot)
            {
                fileExtensions = SearchSubKeys(key, ".");
            }
            Console.WriteLine(fileExtensions);
            SaveCsvFile(fileExtensions, "FileExtensions");
            Console.Read();
            
        }

        public static string SearchSubKeys(RegistryKey root, String searchKey)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string keyName in root.GetSubKeyNames())
            {
                try
                {
                    using (RegistryKey key = root.OpenSubKey(keyName))
                    {
                        if (keyName.StartsWith(searchKey))
                        {
                            foreach (string valueName in key.GetValueNames())
                            {
                                if (key.GetValue("Content Type") != null)
                                {
                                    sb.Append(keyName + "," + key.GetValue("Content Type") + "\r\n");
                                    break;
                                }                                
                            }
                        }
                        
                    }
                }
                catch (System.Security.SecurityException)
                {
                }
            }
            return sb.ToString();
        }

        private static void SaveCsvFile(string fileText, string fileName)
        {
            try
            {
                Stream myStream;
                SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                saveFileDialog1.Filter = "CSV Files (*.csv)|*.csv";
                saveFileDialog1.FileName = fileName + ".csv";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    if ((myStream = saveFileDialog1.OpenFile()) != null)
                    {
                        StreamWriter wText = new StreamWriter(myStream);
                        wText.Write(fileText);
                        wText.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}