Sep 18 2006

Binary data in database - howto part 4

Posted by admin under In practice

We will now continue to work on your "customer" editing application  we started in Part 1 - so please read it and Part 2 as well. In Part 3 we stored the data in SQL Server. Now it's time to get the data out of there as well.

This is how the GUI looks when editing an existing customer. Now we should make the name links clickable - when clicking it should simply show the document.



   <hr />
   Existing files:<br />
   <asp:Repeater ID="rptExistingFiles" runat="server" OnItemDataBound="rptExistingFiles_ItemDataBound">
   <HeaderTemplate>
   <table border="1">
   <tr>
   <td>
   Delete
   </td>
   <td>
   Name
   </td>
   </tr>
   </HeaderTemplate>
   <ItemTemplate>
   <tr>
   <td>
   <asp:CheckBox ID="chkBox" runat="server" />
   </td>
   <td><asp:HyperLink ID="hlGetFile" runat="server"></asp:HyperLink></td>
   </tr>
   </ItemTemplate>
   <FooterTemplate>
   </table>
   </FooterTemplate>
   </asp:Repeater>

I am using a repeater here. Lets see in the code how the databinding is done:



    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataRow row = GetDataRow();
            if (row == null)
            {
                header.InnerText = "New customer";
            }
            else
            {
                header.InnerText = "Edit customer";
                txtName.Text = row["custname"].ToString();

                //Setup repeater existing
                SqlParameter[] paramsToSP = new SqlParameter[3];
                paramsToSP[0] = new SqlParameter("@customer_id", SqlDbType.Int);
                paramsToSP[0].Value = Convert.ToInt32(row["id"]);

                rptExistingFiles.DataSource = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["MainConn"].ToString(),
                    CommandType.StoredProcedure, "cust_ListFiles", paramsToSP).Tables[0];
                rptExistingFiles.DataBind();

                

            }

        }
    }

By using the stored procedure (cust_ListFiles) we simply retrieves all the files. While I have been lazy here remember it's not so smart to do a select * in the sp as I do. Just get the id and filename, otherwise all binary data is read from the database and it's not needed as of this moment.

The ItemDataBound is handled like this:



            DataRowView row = e.Item.DataItem as DataRowView;

            HyperLink hlGetFile = e.Item.FindControl("hlGetFile") as HyperLink;
            hlGetFile.NavigateUrl = "readfile.ashx?id=" + row["id"].ToString();
            hlGetFile.Text = row["filename"].ToString();

We set the navigateurl to a file called readfile.ashx.  This is a http handler which looks like this:



		public void ProcessRequest (HttpContext context)
		{
            
            //Get from database...
            SqlParameter[] paramsToSP = new SqlParameter[1];
            paramsToSP[0] = new SqlParameter("@id", SqlDbType.Int);
            paramsToSP[0].Value = Convert.ToInt32(context.Request["id"]);

            DataTable dt = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["MainConn"].ToString(),
                CommandType.StoredProcedure, "cust_OpenFile", paramsToSP).Tables[0];
            if (dt.Rows.Count == 0)
                return;

            context.Response.Clear();
            context.Response.ContentType = dt.Rows[0]["contenttype"].ToString();
            context.Response.OutputStream.Write((byte[])dt.Rows[0]["filedata"], 0, Convert.ToInt32(dt.Rows[0]["length"]));
            context.Response.End();
		}

We reads the data from the database table using the sp cust_OpenFile - and simply writes the content of the filedata column to the Response.OutputStream. This is actually all we need to do!

Now in the download file you will find database scripts and sourcecode - but as I said - to be used professionally at least change the cust_ListFiles stored procedure to just read the id and filename - not select *!

I will change that myself as this article serie goes on.