Tuesday, January 19, 2010

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);
}
}
}
}