How to Store and Retrieve files from SQL Server Database
The forum I joined recently got lot of queries like How to Save Images in the Database, How to save files in SQL Server, How read files from Database etc. So I thought of writing a post regarding this. Even though I am part of a Web project, I am doing some Windows applications for the client. So I thought it will nice to brush-up the ASP.Net skills.
Here is the code. I am using SQL Server 2008, but I am not using FileStream for the current project, I already wrote a post to how to
manage files with FileStream feature. In this post I am using nvarchar(MAX) datatype for storing the file content.
Table Design
CREATE TABLE [dbo].[tblFiles]( [FileId] [uniqueidentifier] NOT NULL, [Filename] [nvarchar](255) NOT NULL, [FileContent] [varbinary](max) NULL )
And the I set FileId default to newId() and FileContent default to NULL
ALTER TABLE [dbo].[tblFiles] ADD CONSTRAINT [DF_tblFiles_FileId] DEFAULT (newid()) FOR [FileId] GO ALTER TABLE [dbo].[tblFiles] ADD CONSTRAINT [DF_tblFiles_FileContent] DEFAULT (NULL) FOR [FileContent] GO
I wrote the code in C#. I am having a Asp FileUpload control and a button to upload the file, and a Repeater control with two controls, a label for displaying the filename and hyper link control for downloading the file.
<body>
<form runat="server">
<asp:FileUpload runat="server" ID="fileUploadImage" />
<asp:Button runat="server" ID="cmdUpload" Text="Upload File" OnClick="cmdUpload_Click" />
<asp:Repeater runat="server" ID="rptrFiles">
<HeaderTemplate>
<table>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label runat="server" ID="lblFilename" Text='<%# Eval("FileName")%>' />
</td>
<td>
<asp:HyperLink runat="server" Target="_blank" ID="lbtDownload" Text="Download" NavigateUrl='<%# "Download.aspx?File=" + Eval("FileId").ToString() %>' />
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
Uploading the File to the Database
Code behind
protected void cmdUpload_Click(object sender, EventArgs e)
{
string fileName = Path.GetFileName(this.fileUploadImage.FileName);
byte[] fileContent = this.fileUploadImage.FileBytes;
using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
{
connection.Open();
using (SqlCommand command = new SqlCommand("INSERT INTO tblFiles(Filename, FileContent) VALUES(@Filename, @FileContent)", connection))
{
SqlParameter fileNameParameter = new SqlParameter("@Filename", System.Data.SqlDbType.NVarChar, 255);
fileNameParameter.Value = fileName;
SqlParameter fileContentParameter = new SqlParameter("@FileContent", System.Data.SqlDbType.VarBinary);
fileContentParameter.Value = fileContent;
command.Parameters.AddRange(new SqlParameter[] { fileNameParameter, fileContentParameter });
command.ExecuteNonQuery();
}
}
}
And here is code to bind the repeater from the Database
DataTable dtFiles = new DataTable("Files");
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT FileId, FileName FROM tblFiles", "Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
{
adapter.Fill(dtFiles);
}
this.rptrFiles.DataSource = dtFiles;
this.rptrFiles.DataBind();
Download / Read the file from Database
And to download / read the file from Database, I am passing the File unique id to another page(download.aspx).I this page I am checking for the File querysting and based on that reading filecontent from Sql and writing it to Asp.net output stream. You can get more information about how to download files from IIS in this post.
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["File"] != null)
{
string fileId = Request.QueryString["File"];
using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=Sample"))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT Filename, FileContent FROM tblFiles WHERE FileId = @FileId", connection))
{
command.Parameters.AddWithValue("@FileId", fileId);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
reader.Read();
byte[] content = reader["FileContent"] as byte[];
string filename = reader["FileName"].ToString();
Response.Clear();
Response.ClearContent();
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
Response.AddHeader("Content-Length", content.Length.ToString());
Response.OutputStream.Write(content, 0, content.Length);
Response.End();
}
}
}
}
}
Please write to me if I missed something. Happy Programming .
