In this article I will explain with an example, how to save and retrieve files from SQL Server database in ASP.Net using C# and VB.Net.
The Files will be saved into the SQL Server Database table using ASP.Net File Upload control and then displayed in the ASP.Net GridView with Download LinkButton button, to download the file from SQL Server database.
 
Database
This article makes use of a table named tblFiles whose schema is defined as follows.
Save and Retrieve Files from SQL Server Database using ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
HTML Markup
The HTML Markup consists of:
FileUpload – For uploading file.
Button – For saving File in database.
The Button has been assigned with an OnClick event handler.
GridView:–
Columns
GridView consists of a BoundField column and a TemplateField column.
BoundField – For displaying the Name of the File.
TemplateField – For displaying Download LinkButton.
Controls
LinkButton – LinkButton has been assigned with an OnClick event handler.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="File Name"/>
        <asp:TemplateField ItemStyle-HorizontalAlign = "Center">
            <ItemTemplate>
                <asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
                    CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
 
Uploading the Files in SQL Server Database table
When Upload button is clicked, following event handler is executed and the name of the uploaded File is fetched using the GetFileName function of the Path class.
And them the ContentType (MIME type) of the File is fetched from ContentType property of the FileUpload control.
Note: The Content type (MIME type) is very important while downloading the files as it notifies the browser about type of the File.
 
The file is read from the FileUpload control using InputStream property and converted into Byte Array (Binary Data) using the ReadBytes method of the BinaryReader class.
Finally, the Byte Array (Binary Data) will be inserted into Database using ADO.Net and the page is redirected to itself.
Note: For more details on how to insert data into the database in ADO.Net, please refer my article Insert data into Database in ASP.Net using C# and VB.Net.
 
C#
protected void Upload(object sender, EventArgs e)
{
    string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string contentType = FileUpload1.PostedFile.ContentType;
    using (Stream fs = FileUpload1.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "INSERT INTO tblFiles VALUES (@Name, @ContentType, @Data)";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Name", filename);
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}
 
VB.Net
Protected Sub Upload(sender As Object, e As EventArgs)
    Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    Dim contentType As String = FileUpload1.PostedFile.ContentType
    Using fs As Stream = FileUpload1.PostedFile.InputStream
        Using br As New BinaryReader(fs)
            Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As New SqlConnection(constr)
                Dim query As String = "INSERT INTO tblFiles VALUES (@Name, @ContentType, @Data)"
                Using cmd As New SqlCommand(query)
                    cmd.Connection = con
                    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
                    cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType
                    cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        End Using
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub
 
 
Retrieving the uploaded files from Database Table in ASP.Net GridView
Inside the Page Load event, the BindGrid method is called and the GridView is populated with the records i.e. Files from the SQL Server Database Table.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
       this.BindGrid();
    }
}
private void BindGrid()
{
   string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT Id, Name FROM tblFiles";
            cmd.Connection = con;
            con.Open();
            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();
            con.Close();
        }
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
       Me.BindGrid()
    End If
End Sub
Private Sub BindGrid()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand()
            cmd.CommandText = "SELECT Id, Name FROM tblFiles"
            cmd.Connection = con
            con.Open()
            GridView1.DataSource = cmd.ExecuteReader()
            GridView1.DataBind()
            con.Close()
        End Using
    End Using
End Sub
 
 
Downloading File from Database Table
When the Download LinkButton is clicked, first the File ID of the file to be downloaded is fetched using the CommandArgument property of the LinkButton.
The details of the File such as Name, Content Type and the File in Byte Array are fetched from the database using SqlDataReader.
Then, the Response class properties are set.
 
Properties:
1. ContentType – It informs the Browser about the file type. In this case it is PDF.
2. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.
 
Finally, using the BinaryWrite method of the Response class inside which Byte Array is passed as parameter, the File is written to the Response Stream using its Path and File is downloaded.
C#
protected void DownloadFile(object sender, EventArgs e)
{
    int id = int.Parse((sender as LinkButton).CommandArgument);
    byte[] bytes;
    string fileName, contentType;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id";
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                sdr.Read();
                bytes = (byte[])sdr["Data"];
                contentType = sdr["ContentType"].ToString();
                fileName = sdr["Name"].ToString();
            }
            con.Close();
        }
    }
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = contentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
    Response.BinaryWrite(bytes);
    Response.Flush();
    Response.End();
}
 
VB.Net
Protected Sub DownloadFile(sender As Object, e As EventArgs)
    Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
    Dim bytes As Byte()
    Dim fileName As String, contentType As String
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand()
            cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id"
            cmd.Parameters.AddWithValue("@Id", id)
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                sdr.Read()
                bytes = DirectCast(sdr("Data"), Byte())
                contentType = sdr("ContentType").ToString()
                fileName = sdr("Name").ToString()
            End Using
            con.Close()
        End Using
    End Using
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.ContentType = contentType
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName)
    Response.BinaryWrite(bytes)
    Response.Flush()
    Response.End()
End Sub
 
 
Screenshot
Save and Retrieve Files from SQL Server Database using ASP.Net
 
 
Downloads
Download Code