File Upload to Database for ASP.Net Webpages

Question

Im having some trouble finding a way to Upload a document to the database in varbinary(max) with ASP.Net Webpages 2 and I would also like to download it.

So far what i have is this below which supposed to upload a file to a directory on the website but it isn't doing anything. Any help would be great. Thanks

         var fileName = "";
         var fileSavePath = "";
         int numFiles = Request.Files.Count;
         int uploadedCount = 0;
         for (int i = 0; i < numFiles; i++)
         {
             var uploadedFile = Request.Files[i];
             if (uploadedFile.ContentLength > 0)
             {
                 fileName = Path.GetFileName(uploadedFile.FileName);
                 fileSavePath = Server.MapPath("~/UploadedFiles/" +
                   fileName);
                 uploadedFile.SaveAs(fileSavePath);
                 uploadedCount++;
             }
         }
         message = "File upload complete. Total files uploaded: " +
           uploadedCount.ToString();

Does it throw exceptions. Is that part of the code ever called? What doesn't it do? — Alexander, Oct 12 '14 at 19:23
Yes it is called. It runs without any errors but it doesn't upload anything. I'm using an Azure database so that's why I thought I'd try using a varbinary(max) way of storing files instead but I can't seem to find any examples of using it in WebPages. — Patient Professional, Oct 12 '14 at 19:50
Unless my eyes deceive me, where in the code above is a `database` involved? — EdSF, Oct 12 '14 at 22:55

score 1 · Accepted Answer · answered Oct 13 '14 at 09:15

The following code goes at the top of the page where you have your file upload. Note that you should amend the table and field names according to your database. Also, you should ensure that the form that includes your upload control has the enctype attribute set to multipart/form-data:

@{ 
    int id = 0;
    var fileName = "";
    var fileMime = "";
    if (IsPost) {
        var uploadedFile = Request.Files[0];
        fileName = Path.GetFileName(uploadedFile.FileName);
        if(fileName != String.Empty)
        {
            fileMime = uploadedFile.ContentType;
            var fileStream = uploadedFile.InputStream;
            var fileLength = uploadedFile.ContentLength;

            byte[] fileContent = new byte[fileLength];
            fileStream.Read(fileContent, 0, fileLength);
            var db = Database.Open("FileUploading");
            var sql = "INSERT INTO Files (FileName, FileContent, MimeType) VALUES (@0,@1,@2)";
            db.Execute(sql, fileName, fileContent, fileMime);
        }
    }
}

To display a file from the database, you need a separate "handler" file that contains just this code:

@{
    int id = 0;
    if(Request["Id"].IsInt()){
        id = Request["Id"].AsInt();
        var db = Database.Open("FileUploading");
        var sql = "Select * From Files Where FileId = @0";
        var file = db.QuerySingle(sql, id);
        if(file.MimeType.StartsWith("image/")){
            Response.AddHeader("content-disposition", "inline; filename=" + file.FileName);
        } else {
            Response.AddHeader("content-disposition", "attachment; filename=" + file.FileName);
        }
        Response.ContentType = file.MimeType;
        Response.BinaryWrite((byte[])file.FileContent);
    }
}

This file is used as the src attribute for an image file or as the URL for a link to a file that should be downloaded such as a PDF or Word file. If you call this handler file "download.cshtml", the link for an image file saved in the database should look like this:

<img src="download.cshtml?Id=@id" alt="" />

where the Id parameter value is the id fo the file in the database. A download link looks like this:

<a href="download.cshtml?Id=@id">Click Here</a>

All of this has been taken from my article: http://www.mikesdotnetting.com/Article/148/Save-And-Retrieve-Files-From-a-Sql-Server-CE-Database-with-WebMatrix. The only difference between the article which features a SQL Compact database is that the data type for files in SQL CE is image as opposed to varbinary(max) in SQL Server.

score 0 · Answer 2 · answered Oct 13 '14 at 00:38

based on your code..you are not uploading the image to the database. instead u're saving the image on your folder which is located in your root / UploadedFiles

to store the image in the database..you should use this code..

using (Stream fs = uploadedFile.PostedFile.InputStream)
            {
                using (BinaryReader br = new BinaryReader(fs))
                {
                    byte[] bytes = br.ReadBytes((Int32)fs.Length);
                    string contentType = uploadedFile.PostedFile.ContentType;
                    SqlParameter[] arParams = new SqlParameter[2];
                    arParams[0] = new SqlParameter("@ID", SqlDbType.Int);
                    arParams[0].Value = 1; 'example
                    arParams[1] = new SqlParameter("@contentType", SqlDbType.Varchar(50));
                    arParams[1].Value = contentType;
                    arParams[2] = new SqlParameter("@document", SqlDbType.Varbinary(MAX));
                    arParams[2].Value = bytes;
                    SqlHelper.ExecuteNonQuery(SQLConn, CommandType.StoredProcedure,                     "Upload_Attachment", arParams);

                }
            }

Could you please tell me how I would go about implementing this code? — Patient Professional, Oct 13 '14 at 06:37
first you need to create a table that contain id, contenttype, and document. Then you need to create a stored procedure that contain an insert statement and named the stored procedure as Upload_Attachment. then you can use this code. — mokh223, Oct 15 '14 at 00:19

File Upload to Database for ASP.Net Webpages

2 Answers2