Retrieve files from SQL Server 2005 using Asp.net

In this post “Retrieve files from SQL Server 2005 using Asp.net”, we will see how to retrieve Document, Excel and PDF files saved in SQL Server 2005 database, in Byte format using Asp.Net.  

Refer the post Save files to SQL Server 2005 using Asp.net, to see on how to save the documents to the database using Asp.Net

Add a button to the .aspx page as follows

<asp:Button ID="cmdDownload"

runat="server"

Text="Download"

onclick="cmdDownload_Click" / >

Once the button is added move to the code-behind file to write the download logic as follows.

protected void cmdDownload_Click(object sender, EventArgs e)

{

    DownloadDocument();

}

//

private void DownloadDocument()

{

    // Retrieve Details from the DB

    string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

    objConn = new SqlConnection(strConn);

    objCmd = new SqlCommand("SELECT FileName, Extension, FileContent From Profiles Where ID = 1", objConn);

    //

    objConn.Open();

    SqlDataReader dr = objCmd.ExecuteReader();

    dr.Read();

    strFileName = dr.GetString(0);

    strFileExtension = dr.GetString(1);

    Byte[] byteDoc = new Byte[(dr.GetBytes(2, 0, null, 0, int.MaxValue))];

    dr.GetBytes(2, 0, byteDoc, 0, byteDoc.Length);

    dr.Close();

    objConn.Close();

    //

    Response.Clear();

    Response.Buffer = true;

    if (strFileExtension == ".doc" || strFileExtension == ".docx")

    {

       Response.ContentType = "application/vnd.ms-word";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    else if (strFileExtension == ".xls" || strFileExtension == ".xlsx")

    {

       Response.ContentType = "application/vnd.ms-excel";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    else if (strFileExtension == ".pdf")

    {

       Response.ContentType = "application/pdf";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    //

    Response.Charset = "";

    Response.Cache.SetCacheability(HttpCacheability.NoCache);

    Response.BinaryWrite(byteDoc);

    Response.End();

}

Run the application click on the Download button, the file will be retrieved from the Database, converted to the actual format and allows the user to save the file.

That’s it we have Retrieved a file stored in SQL Server 2005 using Asp.net

Related Post

Save files to SQL Server 2005 using Asp.net