Download binary file From SQL Server 2000

Question

I inserted binary files (images, PDF, videos..) and I want to retrieve this file to download it.

I used generic handler page as this

public void ProcessRequest (HttpContext context) {
    using (System.Data.SqlClient.SqlConnection con = Connection.GetConnection())
    {
        String Sql = "Select BinaryData From ProductsDownload Where Product_Id = @Product_Id";

        SqlCommand com = new SqlCommand(Sql, con);
        com.CommandType = System.Data.CommandType.Text;

        com.Parameters.Add(Parameter.NewInt("@Product_Id", context.Request.QueryString["Product_Id"].ToString()));

        SqlDataReader dr = com.ExecuteReader();

        if (dr.Read() && dr != null)
        {
            Byte[] bytes;
            bytes = Encoding.UTF8.GetBytes(String.Empty);
            bytes = (Byte[])dr["BinaryData"];
            context.Response.BinaryWrite(bytes);

            dr.Close();
        }
    }
}

and this is my table

CREATE TABLE [ProductsDownload] (
 [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [Product_Id] [int] NULL ,
 [Type_Id] [int] NULL ,
 [Name] [nvarchar] (200) COLLATE Arabic_CI_AS NULL ,
 [MIME] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
 [BinaryData] [varbinary] (4000) NULL ,
 [Description] [nvarchar] (500) COLLATE Arabic_CI_AS NULL ,
 [Add_Date] [datetime] NULL ,
 CONSTRAINT [PK_ProductsDownload] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_ProductsDownload_DownloadTypes] FOREIGN KEY 
 (
  [Type_Id]
 ) REFERENCES [DownloadTypes] (
  [ID]
 ) ON DELETE CASCADE  ON UPDATE CASCADE ,
 CONSTRAINT [FK_ProductsDownload_Product] FOREIGN KEY 
 (
  [Product_Id]
 ) REFERENCES [Product] (
  [Product_Id]
 ) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]
GO

And use data list has label for file name and button to download file as

<asp:DataList ID="DataList5" runat="server" 
              DataSource='<%#GetData(Convert.ToString(Eval("Product_Id")))%>'
              RepeatColumns="1" RepeatLayout="Flow">
     <ItemTemplate>
         <table width="100%" border="0" cellspacing="0" cellpadding="0">
             <tr>
                 <td class="spc_tab_hed_bg spc_hed_txt lm5 tm2 bm3">
                     <asp:Label ID="LblType" runat="server" Text='<%# Eval("TypeName", "{0}") %>'></asp:Label>
                 </td>
                 <td width="380" class="spc_tab_hed_bg">
                     &nbsp;
                 </td>
             </tr>
             <tr>
                 <td align="left" class="lm5 tm2 bm3">
                     <asp:Label ID="LblData" runat="server" Text='<%# Eval("Name", "{0}") %>'></asp:Label>
                 </td>
                 <td align="center" class=" tm2 bm3">
                     <a href='<%# "DownloadFile.aspx?Product_Id=" + DataBinder.Eval(Container.DataItem,"Product_Id") %>' >
                       <img src="images/downloads_ht.jpg" width="11" height="11" border="0" />
                     </a>
                     <%--<asp:ImageButton ID="ImageButton1" ImageUrl="images/downloads_ht.jpg" runat="server" OnClick="ImageButton1_Click1" />--%>
                  </td>
              </tr>
          </table>
      </ItemTemplate>
 </asp:DataList>

I tried more to solve this problem but I cannot.

Answer 1

Although you could work on your question to narrow it down, I'm guessing your browser does not recognize it as a file to download instead of trying to show it?

It's because your just sending bytes as a result, you should tell the browser what data is coming back from the server. The way to do that is to set the content headers.

// optionally provide the filename instead of 
// letting the browser create one based on the get url
context.Response.AddHeader("content-disposition", "attachement filename=" + dr["Name"]);
// set the MIME content type of the data.
context.Response.ContentType = dr["MIME"];

I don't remember if you need to do a typecast on the dr["MIME"] result to get a String, but that's left for the implementer.