Store MS Word .doc in database and then get it out again

Question

To store the word doc in SQL I have this:

        byte[] bytes = new byte[uploader.UploadedFiles[0].InputStream.Length];
        var storedFile = new email_attachment();
        string strFullPath = uploader.UploadedFiles[0].FileName;
        string strFileName = Path.GetFileName(strFullPath);

        storedFile.email_attachment_id = Guid.NewGuid();
        storedFile.emailer_id = new Guid(dropMailers.SelectedValue);
        storedFile.file_name = strFileName;
        storedFile.file_data = bytes;
        db.email_attachments.InsertOnSubmit(storedFile);
        db.SubmitChanges();

To get it back I use this:

 var storedFile = db.email_attachments.Where(a => a.email_attachment_id.ToString() == dropAttachments.SelectedValue).Single();

        string strPath = System.Web.Hosting.HostingEnvironment.MapPath("~/Storage/Email/Attachments");

        File.WriteAllBytes(Path.Combine(strPath, storedFile.file_name), storedFile.file_data.ToArray());
        System.IO.FileInfo file = new System.IO.FileInfo(Path.Combine(strPath, storedFile.file_name));

        if (file != null && file.Exists)
        {
            Response.Clear();
            Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
            Response.AddHeader("Content-Length", file.Length.ToString());
            Response.ContentType = "application/octet-stream";
            Response.WriteFile(file.FullName);
            Response.End();
        }
        else
        {

            //Response.Write("This file does not exist.");

        }

Problems: The doc I get back is blank, and I also believe saving it in the file system and then writing it to the response stream isn't very efficient. I'm thinking that step could be skipped. Any help or info would be much appreciated.

Answer 1

Let's look at this code to start with:

byte[] bytes = new byte[uploader.UploadedFiles[0].InputStream.Length];
var storedFile = new email_attachment();
string strFullPath = uploader.UploadedFiles[0].FileName;
string strFileName = Path.GetFileName(strFullPath);

storedFile.email_attachment_id = Guid.NewGuid();
storedFile.emailer_id = new Guid(dropMailers.SelectedValue);
storedFile.file_name = strFileName;
storedFile.file_data = bytes;
db.email_attachments.InsertOnSubmit(storedFile);
db.SubmitChanges();

I can see you creating an empty array - but I can't see where you're populating it anywhere. Shouldn't you be reading from the InputStream instead of just finding out its length?

(You may well want to copy from the input stream into a MemoryStream, and then use ToArray to get a byte array out at the end. There's plenty of sample code around for copying a stream - and in .NET 4 there's even the WriteTo method which makes it easier.)

I haven't even looked at the later code yet - first let's make sure you actually get some data into the database first...

Answer 2

To write to the db:

                byte[] bytes = new byte[uploader.UploadedFiles[0].InputStream.Length];
                uploader.UploadedFiles[0].InputStream.Read(bytes, 0, bytes.Length);

                var storedFile = new document();
                string strFullPath = uploader.UploadedFiles[0].FileName;
                string strFileName = Path.GetFileName(strFullPath);

                storedFile.document_id = Guid.NewGuid();
                storedFile.content_type = uploader.UploadedFiles[0].ContentType;
                storedFile.original_name = strFileName;
                storedFile.file_data = bytes;
                storedFile.date_created = DateTime.Now;
                db.documents.InsertOnSubmit(storedFile);
                db.SubmitChanges();

And to get the document back out:

var storedFile = db.documents.Where(a => a.document_id.ToString() == dropAttachments.SelectedValue).Single();

            Response.Clear();
            Response.ContentType = "application/x-unknown";
            Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + storedFile.original_name + "\"");
            Response.BinaryWrite(storedFile.file_data.ToArray());
            Response.End();

Found my answer here: http://imar.spaanjaars.com/414/storing-uploaded-files-in-a-database-or-in-the-file-system-with-aspnet-20#Downloads