How can I insert a file in SQL Server using ASP.Net?

Question

Hello!

I'm developing an ASP.NET app (C#) that connect to SQL Server 2008 using ADO.NET entity framework.

The user of this app can select an image from his local hard drive to insert in SQL Server. How can I do this?

I know how to store the image into SQL Server but I don't know how to upload the image to the server (using FileUpload server control?).

Thank you!

Answer 1

Not sure about the entity framework, but this is how you do this in vanilla ado.net

Answer 2

You can use a varbinary(MAX) column: http://msdn.microsoft.com/en-us/library/ms188362.aspx

Search on Google yielded this: http://www.informit.com/articles/article.aspx?p=398883&seqNum=2

Note that in his example, he is using the Image data type in SQL Server 2005. That type is being phased out in favor of varbinary. You would have to change that code to fit your specific schema needs as well.

Answer 3

Well, to upload to the web-server you just need an <input type="file" .../>. At ASP.NET, this should be available in the Request.Files collection.

In SQL Server, you'll want a varbinary(max) (or image in older versions).

The big question before pushing them into SQL Server is: how big are they? If they aren't massive, you can just treat them as a byte[]; just buffer from the InputStream and send. Here's a fairly generic way of buffering a Stream to a single byte[]:

        byte[] buffer = new byte[1024];
        int bytesRead;
        MemoryStream ms = new MemoryStream();
        while ((bytesRead = inputStream.Read(buffer, 0, buffer.Length)) > 0)
        {
            ms.Write(buffer, 0, bytesRead);
        }
        byte[] data = ms.ToArray();

For massive files (which perhaps don't belong in the db unless you are using the SQL 2008 filestream extensions), you'll want to chunk it. Here's an old reply I used for chunking (before SQL 2005) - but it shows the overall idea.

Answer 4

For storing uploaded file to SQL server you can look at this answer of mine :

How do you store a picture in an image column?

In this example, uploadInput is a file input control, you can use it as FileUpload control in ASP.NET.

For using File upload Control in ASP.NET here is a good MSDN article.