ASP.NET Store uploaded file sql server table

Question

How do you store a file that was uploaded by an ASP.net webform into a sql server 2005 varbinary(max) field?

Here is what I have so far:

protected void btnUpload_Click(object sender, EventArgs e)
{
    for (int i = 0; i < Request.Files.Count; i++)
    {
        StoreFile(Request.Files[i]);
    }
}

private void StoreFile(HttpPostedFile file)
{
    // what do i do now?
}

A plain old ado.net example would be good. So would a linq to sql example.

Thanks

examples of this all over google. step 1: get byte array, step 2: command.Parameters.AddWithValue("@Data",data); step 3: execute. — Darren Kopp, Apr 17 '09 at 21:31
why not post this as an answer? from the looks of the competition, it would be accepted — Ronnie Overby, Apr 18 '09 at 01:52

score 3 · Accepted Answer · answered Apr 17 '09 at 21:41

3

There's nice tutorial on how to upload a file directly into the database at 4GuysFromRolla

answered Apr 17 '09 at 21:41

Jose Basilio

50,714
13
121
117

Looking at this today and finding the link is dead is a good example of why we should answer questions directly, rather than posting a link to an article.... – Jon Story Oct 31 '18 at 16:52

score 2 · Answer 2 · answered Apr 18 '09 at 14:10

Here's how I did this using Linq To Sql:

FilesDataContext db = new FilesDataContext();

protected void btnUpload_Click(object sender, EventArgs e)
{
    for (int i = 0; i < Request.Files.Count; i++)
    {
        StoreFile(Request.Files[i]);
    }

    db.SubmitChanges();
}

private void StoreFile(HttpPostedFile file)
{
    byte[] data = new byte[file.ContentLength];
    file.InputStream.Read(data, 0, file.ContentLength);

    File f = new File();
    f.Data = data;
    f.Filename = file.FileName;
    db.Files.InsertOnSubmit(f);
}

score 1 · Answer 3 · answered Apr 17 '09 at 21:21

1

This is generally considered bad form. It bloats your database and doesn't really offer any advantages over keeping all files in a hard drive folder and just storing the location of the file in the DB. Are you sure you want to do this?

answered Apr 17 '09 at 21:21

Spencer Ruport

34,865
12
85
147

there are pros and cons of both. compromise is sql server 2008's file stream. – Darren Kopp Apr 17 '09 at 21:29
I think if you are dealing with an internal application and have relatively small files or documents where you are concerned with versioning it can be very useful. I worked for an imaging company where it was very nice to store thumbnails and small files in the database with pointers to the larger files on central storage. – James Avery Apr 18 '09 at 00:21
1

@Spencer Ruport: Backups. Much easier to reliably backup the database and be sure that the files go with it and you're not left with a bunch of file links that no longer work. This is, I suppose, related to James Avery's comment about versioning above. The security is also more straightforward, as you needn't worry about granting the web user file system access of any kind. – Sean Hanley Jul 20 '09 at 16:46
@Yadyn: If the web user creates the file you don't have to worry anyway. And I don't feel that the hassle of a 16 gig database is worth the minor convenience of not setting up another backup task. – Spencer Ruport Jul 20 '09 at 17:04

score 0 · Answer 4 · answered Jul 26 '13 at 00:02

Here's a quick refactor of Ronnie's answer:

Public Shared Sub SaveUploadedFile(File As HttpPostedFile)
  Dim oFile As Db.File

  oFile = New Db.File
  oFile.Data = File.ToBytes
End Sub

<Extension()>
Public Function ToBytes(File As HttpPostedFile) As Byte()
  ToBytes = New Byte(File.ContentLength - 1) {}

  Using oStream As Stream = File.InputStream
    oStream.Read(ToBytes, 0, File.ContentLength)
  End Using
End Function

HTH

ASP.NET Store uploaded file sql server table

4 Answers4