Archive for the ‘SSRS’ Category

12/17/09
Fahad Zia
tags:   ,

Download BLOB in SQL Server using SSRS and ASP.NET


I came across a requirement recently where I have to let the user download files from the links displayed in SSRS report. Here’s how I approached it:
1. Provide an ASP.NET page(requestfile.aspx) that would query the BLOB column in the database and return the file by reading required column values from querystring.
2. Provide link from SSRS report to the ASP.NET page and pass it the querystring values it needs

Here is the ASP.NET page (requestfile.aspx) code that reads information from querystring and returns the BLOB as file:

/// <summary>
    /// Query the database to download BLOB column as file
    /// </summary>    
    protected void Page_Load(object sender, EventArgs e)
    {
        string strTableName = "";
        string strColumnName = "";
        string strPKColumnName = "";
        string strPKColumnValue = "";
        string strFileName = "";
        string strConnectionString = "Data Source=.;Initial Catalog=xx;Trusted_Connection=True;";
        byte[] data=null;

        strTableName = Request.QueryString["tbl"].ToString();
        strColumnName = Request.QueryString["colName"].ToString();
        strPKColumnName = Request.QueryString["pkColName"].ToString();
        strPKColumnValue = Request.QueryString["pkColValue"].ToString();
        strFileName = Request.QueryString["filename"].ToString();

        string strSQL = string.Format("SELECT {0} FROM {1} WHERE {2}={3};",
                                        strColumnName,
                                        strTableName,
                                        strPKColumnName,
                                        strPKColumnValue);
        using (SqlConnection conn = new SqlConnection(strConnectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSQL,conn);
            SqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                data = (byte[])rdr[strColumnName];
                break;
            }
            if (data.Length > 0)
            {
                Response.Buffer = true;
                Response.ContentType = string.Format("application/{0}",Path.GetExtension(strFileName));
                Response.AddHeader("content-disposition",string.Format("attachment;filename={0}",strFileName));
                Response.BinaryWrite(data);
                Response.Flush();
            }
            conn.Close();
        }
    }

Now in SSRS i created an embedded function called WriteJavascriptString:

Function WriteJavascriptString (FileName As String, _
			TblName As String, _
			ColName As String, _
			PKColName As String, _
			PKColValue As String) As String
	Return "javascript:void window.open('http://localhost:4127/website/requestfile.aspx?filename=" & FileName & "&tbl=" & TblName & "&colName=" & ColName & "&pkcolName=" & PKColName & "&pkColValue=" & PKColValue & "','_blank')"
End Function

ColName is the BLOB column i.e. of type varbinary. TblName is the table that has this column, PKColName is the column name that unqiuely identifies the row and PKColValue is its value.

and finally this code is linked to the field text boxes on the report by right clicking the text box then “Properties”->”Navigation” tab->”Jump to URL” and writing the expression as shown below:

=IIf(IsNothing(First(Fields!Document_blob.Value, "dsDocuments")),"",Code.WriteJavascriptString(First(Fields!Document_filename.Value, "dsDocuments"),"DOCUMENTS_TBL","Document_blob","DocID",Parameters!DocID.Value))