Uploading and storing files in SQL using ASP.NET
Version: VS2008 SP1
The sample code at the bottom of this post shows you how to upload files and store them in a binary column in SQL using ASP.net webforms and LINQ. See this post if you wish to upload an excel file, extract data from the cells and store them in a SQL table instead.
We start by creating a new table in the Northwind database called ArchivedFiles with the following schema:
CREATE TABLE [dbo].[ArchivedFiles](
[ArchivedFileId] [int] IDENTITY(1,1) NOT NULL,
[BinaryData] [varbinary](max) NOT NULL,
[Name] [varchar](255) NOT NULL,
[MimeType] [varchar](255) NOT NULL,
[UploadDate] [datetime] NOT NULL,
CONSTRAINT [PK_ArchivedFiles] PRIMARY KEY CLUSTERED
(
[ArchivedFileId] ASC
)WITH (PAD_INDEX = OFF) ON [PRIMARY]
) ON [PRIMARY]
The BinaryData column will be used to hold the uploaded file in binary format. The rest of the columns should be self explanatory.
The screen capture below shows the web UI. We use the FileUpload control for uploading the file (max 4 MB). The GridView is bound to the ArchivedFiles table through an ObjectDataSource with paging turned on. The web application using LINQ to SQL to talk to the database.
When the upload button is clicked, the following code executes:
protected void btnUpload_Click(object sender, EventArgs e)
{ if (fUpload.HasFile)
{ string contentType = fUpload.PostedFile.ContentType;
string fileName = fUpload.PostedFile.FileName;
byte[] byteArray = fUpload.FileBytes;
var wrapper = new ArchivedFilesWrapper();
wrapper.AddArchivedFile(byteArray, fileName, contentType);
gvFiles.DataBind();
}
}
which calls the following code in the ArchivedFilesWrapper:
public int AddArchivedFile(byte[] byteArray, string filename, string mimeType)
{ var context = new NorthwindDataContext();
Binary linqByteArray = new Binary(byteArray);
var archivedFile = new ArchivedFile()
{ BinaryData = linqByteArray,
MimeType = mimeType,
Name = filename,
UploadDate = DateTime.Now
};
context.ArchivedFiles.InsertOnSubmit(archivedFile);
context.SubmitChanges();
return archivedFile.ArchivedFileId;
}
Something interesting to note here is LINQ to SQL maps a binary data column in SQL to a System.Data.Linq.Binary type by default. This type is used because LINQ to SQL is unable to track changes that are made to a byte[]. The Binary type creates an immutable block of binary data which allows for change tracking. The constructor for the Binary type takes in a byte[]. Hence the line:
Binary linqByteArray = new Binary(byteArray);
When the download link in the GridView gets clicked, the following code gets executed:
protected void gvFiles_RowCommand(object sender, GridViewCommandEventArgs e)
{ if (e.CommandName == "Download")
{ int archivedFileId = Convert.ToInt32(e.CommandArgument);
var wrapper = new ArchivedFilesWrapper();
var archivedFile = wrapper.GetArchivedFile(archivedFileId);
Response.Clear();
Response.ContentType = archivedFile.MimeType;
Response.AddHeader("content-disposition", string.Format("attachment; {0}", archivedFile.Name)); Response.BinaryWrite(archivedFile.BinaryData.ToArray());
Response.End();
}
}
We clear the contents of the response stream, set the ContentType and add a content-disposition header to prompt the user to open or download the file. We then use the BinaryWrite method on the Response stream to send the binary data and finally call the Response.End() method.
Source Code
Mirror