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
