SQL Server 2008 filestream read into web page

Question

I have a SQL Server table with filestream enabled. I am able to upload files here and update as well. How do I read these files into an .aspx web page? The files are .pdf files.

There are 2 files are associated with each row.

I am reading the row into a .aspx page and at the bottom I want the files to open up one below the other. This is for the user to print the row with the files.

I have checked out the code suggested by @dinglemeyer. I do want to conver my pdf to image. I am assuming there is a simple way:

   <asp:GridView ID="gridview_f" runat="server" ShowHeaderWhenEmpty="false" PageSize="2" DataKeyNames="ID" DataSourceID="sql_files" AutoGenerateColumns="false" >
       <Columns>
           <asp:TemplateField Visible="false">
               <ItemTemplate>
                   <asp:Label ID="selID" runat="server" Text='<%# Bind("ID") %>' />
               </ItemTemplate>
           </asp:TemplateField>
           <asp:TemplateField>
               <ItemTemplate>
                   <asp:Label ID="selFname" runat="server" Text='<%# Bind("filename") %>' />
                   <br />
                   <asp:image ID="selFile" runat="server" ImageUrl='<%# "Handler.ashx?id="+Eval("ID") %>' />
               </ItemTemplate>
           </asp:TemplateField>
       </Columns>
   </asp:GridView>


   // code behind for ginfing the grid as the parentId is extracted from other form values.
    private void gridView_Data(string id)
           {
               sql_files.SelectCommand = "SELECT ID, filename from myFILESTABLE where PARENTID=" + id;
    }


    // Handler.ascx
          public void ProcessRequest(HttpContext context)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader sdr = null;

            conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["my_dbconn"].ConnectionString);
            try
            {
                    cmd = new SqlCommand("SELECT filecontent from myFILESTABLE WHERE ID=" + context.Request.QueryString["ID"], conn);
                conn.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    context.Response.ContentType = "content/pdf";
                    context.Response.BinaryWrite((byte[])sdr["filecontent"]);
                }
                sdr.Close();
            }
            finally
            {
                conn.Close();
            }

Answer 1

My apologies for the delayed reply.From the link:http://weblogs.asp.net/aghausman/saving-and-retrieving-file-using-filestream-sql-server-2008 Here is my working code:

   <asp:GridView ID="mygrid1" runat="server" AutoGenerateColumns="false" DataKeyNames="ID" DataSourceID="sql_file" OnRowCommand="gridview_f_RowCommand"  GridLines="None" >
       <Columns>
           <asp:TemplateField HeaderText="S.No">
               <ItemTemplate>
                   <asp:Label ID="Label12" runat="server" Text='<%#  Container.DataItemIndex + 1 %>'  />
               </ItemTemplate>
           </asp:TemplateField>
           <asp:TemplateField HeaderText="Attachments" >
               <ItemTemplate>
                   <asp:Label ID="Label11" runat="server" Text='<%# Bind("ID") %>' Visible="false" />
                   <asp:LinkButton ID="lLabel12" runat="server" Text='<%# Bind("myFileName") %>' CommandName="GetFile" CommandArgument='<%#Eval("ID") + "|" + Eval("myFileName") %>'>                           
                   </asp:LinkButton>
               </ItemTemplate>
           </asp:TemplateField>
       </Columns>
   </asp:GridView>

" >

The code behind:

     protected void gridview_f_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName=="GetFile")
        {
            string[] ca = e.CommandArgument.ToString().Split('|');
            SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConn"].ConnectionString);
            objSqlCon.Open();
            SqlTransaction objSqlTran = objSqlCon.BeginTransaction();

            SqlCommand objSqlCmd = new SqlCommand("spGet_getMyFile", objSqlCon, objSqlTran);
            objSqlCmd.CommandType = CommandType.StoredProcedure;

            SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
            objSqlParam1.Value = ca[0]; // e.CommandArgument;

            objSqlCmd.Parameters.Add(objSqlParam1);

            string path = string.Empty;
            using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    path = sdr[0].ToString();
                }
            }

            objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);

            byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();

            SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);

            byte[] buffer = new byte[(int)objSqlFileStream.Length];
            objSqlFileStream.Read(buffer, 0, buffer.Length);
            objSqlFileStream.Close();
            objSqlTran.Commit();
            Response.AddHeader("Content-disposition", "attachment; filename=" + ca[1]);
            Response.ContentType = "application/pdf";

            Response.BinaryWrite(buffer);


        }
    }

The Stored procedure SpGet_getMyfile is:

CREATE PROCEDURE [dbo].[spGet_getMyFile] ( @id BIGINT )

AS

SELECT   filestreamColumn.PathName()
   FROM dbo.TableThatHasTheFileStream
      WHERE Id=@id