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

Save files to SQL Server 2005 using Asp.net

In this post “Save files to SQL Server 2005 using Asp.net”, we will see how to save Document, Excel and PDF files into SQL Server 2005 database from an Asp.Net form.  

Uploading files is a common activity in Asp.net application, in general we upload the files and save them to the file system and store the path of the saved file in the database, but there are specific scenarios which demand us to store the entire file in the database in binary format, we shall see how this can be achieved using Asp.Net

First create a table to store the Documents uploaded, add a column with type varbinary (MAX), this column will be used to store the content of the uploaded file in binary format.

Also create a Stored Procedure which will be used to insert the document content.

CREATE PROCEDURE [dbo].[InsertDocument]

      @FileName varchar(50),

      @Extension varchar(5),

      @FileContent varbinary(max)

AS

BEGIN

      INSERT INTO Profiles (FileName, Extension, FileContent)

      Values (@FileName,@Extension,@FileContent);

END

Next create an Asp.net application and add a new .aspx file to the application.
In the .aspx file add a FileUpload control as follows

<asp:FileUpload ID="fileUploadDocument" runat="server" /> 

<asp:Button ID="cmdUpload" runat="server" Text="Upload"

    onclick="cmdUpload_Click" /><br /><br />

<asp:Label ID="lblMsg" runat="server" ForeColor="Red"></asp:Label>

Once the controls are added move to the code-behind file to write the upload logic as follows.

protected void cmdUpload_Click(object sender, EventArgs e)

{

    try

    {

        if (fileUploadDocument.PostedFile.ContentLength > 0)

        {

            // Get the File name and Extension

            strFileName = Path.GetFileName(fileUploadDocument.PostedFile.FileName);

            strFileExtension = Path.GetExtension(fileUploadDocument.PostedFile.FileName);

            //

            // Extract the content of the Document into a Byte array

            int intlength = fileUploadDocument.PostedFile.ContentLength;

            Byte[] byteData = new Byte[intlength];

            fileUploadDocument.PostedFile.InputStream.Read(byteData, 0, intlength);

            //

            // Save the file to the DB

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

            objConn = new SqlConnection(strConn);

            objCmd = new SqlCommand("InsertDocument", objConn);

            //

            objCmd.CommandType = CommandType.StoredProcedure;

            //

            SqlParameter pFileName = new SqlParameter("FileName", SqlDbType.VarChar);

            pFileName.Size = 50;

            pFileName.Direction = ParameterDirection.Input;

            pFileName.Value = strFileName;

            //

            SqlParameter pExtension = new SqlParameter("Extension", SqlDbType.VarChar);

            pExtension.Size = 5;

            pExtension.Direction = ParameterDirection.Input;

            pExtension.Value = strFileExtension;

            //

            SqlParameter pFileContent = new SqlParameter("FileContent", SqlDbType.VarBinary);

            pFileContent.Size = byteData.Length;

            pFileContent.Direction = ParameterDirection.Input;

            pFileContent.Value = byteData;

            //

            objCmd.Parameters.Add(pFileName);

            objCmd.Parameters.Add(pExtension);

            objCmd.Parameters.Add(pFileContent);

            //

            objConn.Open();

            objCmd.ExecuteNonQuery();

            objConn.Close();

            //

            lblMsg.Text = "Document Uploaded Succesfully" ;

        }

    }

    catch (Exception ex)

    {

        lblMsg.Text = " Error uploading Document: " + ex.Message.ToString();

    }

}

Run the application, browse the required file and click on the Upload button, the file will be conterted into BYTEs and will be stored in the SQL Server 2005 database.

That’s it we have Stored a file into SQL Server 2005 database using Asp.net

Refer the post Retrieve files from SQL Server 2005 using Asp.net, to see on how to retrieve the saved files from the database.

Related Post
Retrieve files from SQL Server 2005 using Asp.net