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 .

5 thoughts on “How to Store and Retrieve files from SQL Server Database

  1. I have some qustions:
    Q1- I bought error in Code behind
    1- there is error in (Path.GetFileName), the blue line under path that told me the name path dose not exist
    2- there is error in (SqlConnection), the blue line under path that told me the type or namespase could not be found.
    3- there is error in (SqlCommand), the blue line under path that told me the type or namespase could not be found.
    4- there is error in (SqlParameter), the blue line under path that told me the type or namespase could not be found.

    Q2- your code”And here is code to bind the repeater from the Database” where i put it ???

    Q3- Can I use this way to Store and Retrieve video file????

    thanks in advance and i wish you to sending me a project file if can???

    • Thank you for adding comment :) I will try to help you with this. Sorry I don’t have the project file with me. :(

      1. You need to import System.IO namespace to use Path class. You also need to import System.Data.SqlClient to use SqlConnection, SqlCommand, and SqlParameter classes.
      2. You can put it in PageLoad event or in the function, if there is a function to bind the repeater or gridview.
      3. Yes. You can use this to store and retrieve video file. You may need a HTTP handler to display video file.

      Hope I answered all your questions.

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>