Friday, June 1, 2012

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

Search Flipkart Products:
Flipkart.com

1 comment:

Anonymous said...

cut and pasted and it worked first time. Only thing I did different was use Entity Framework to connect to the database.