2012-10-26

ASP.NET - Deploy Crystal Reports on web page

There is a new page - CrystalReportViewer.aspx, its HTML structure as follows:



The client doesn't want to install Crystal Reports software in the server, originally their IT team use Crystal Report 10 to develop reports, but they will upgrade to Crystal Reports 2008 in the future, so I download Runtime package from Crystal Reports website (https://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=56787567) and install it in the server. As you can see from the structure above, according to client's requirement, I setup crystalreportviewer object's parameter to control its layout, then add assembly section and match with the Crystal Reports version, also I add some assembly sections in the Web.config file:


In CrystalReportViewer.aspx.cs, please see the following coding and explanation:
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;

public partial class CrystalReportViewer_AuditReport : System.Web.UI.Page
{
    CrystalDecisions.CrystalReports.Engine.Database crDatabase;
    CrystalDecisions.CrystalReports.Engine.Table crTable;
    ReportDocument oRpt;     
    string report_code = "";
    string new_querystring = "";
    HttpRequest new_request;
    string rpt_name = "";
    string report_name = "";
    string db_type = "";
    string status = "";
    string altstr = "";
    string t_new = "";
    string t_old = "";

    protected void Page_Init(object sender, EventArgs e)
    {
    //Retrieve QueryString, because it has been encoded to base64 format, so decoded to normal string.
    try
        {
        byte[] decbuff = Convert.FromBase64String(Request.QueryString.ToString());
           new_querystring = System.Text.Encoding.ASCII.GetString(decbuff);
        }
        catch(Exception e1)
       {
        altstr += "Sorry, there is an error occurred while running report, please try to execute again, thank you.<br>";
            Response.Write(altstr);
        Response.End();       
        }
    new_querystring = Server.UrlDecode(new_querystring);               
    new_request = new HttpRequest("", "http://localhost/AuditingSystemReport/CrystalReportViewer_AuditReport.aspx", new_querystring);   
    report_code = new_request.QueryString["report_code"];

    //In this case, there are houndreds of reports which came from 3 different databases .
    //There is an admin page used for IT team to control which reports are public or closed , and maintain reports' information.
    //Create a table to store reports' information, including database type, file name, public name, and status.
    string sqlcnstr = ConfigurationManager.ConnectionStrings["FubonRPT"].ConnectionString;
        using (SqlConnection sqlcn = new SqlConnection(sqlcnstr))
        {
        sqlcn.Open();
        string sqlstr = "SELECT ISNULL(DB_TYPE, '') AS DB_TYPE, ISNULL(rpt_name, '') AS rpt_name, ISNULL(REPORT_NAME, '') AS REPORT_NAME, STATUS FROM Audit_Report_Setting WHERE REPORT_CODE = '" + report_code + "'";
        SqlDataAdapter sqlda = new SqlDataAdapter(sqlstr, sqlcn);
        DataSet sqlds = new DataSet();
                sqlda.Fill(sqlds, "ARS");
        int sqlcount = sqlds.Tables["ARS"].Rows.Count;
                if (sqlcount > 0)
                {
            db_type = sqlds.Tables["ARS"].Rows[0]["DB_TYPE"].ToString().Trim();
            rpt_name = sqlds.Tables["ARS"].Rows[0]["rpt_name"].ToString().Trim();
            report_name = sqlds.Tables["ARS"].Rows[0]["REPORT_NAME"].ToString().Trim();
            status = sqlds.Tables["ARS"].Rows[0]["STATUS"].ToString().Trim();
        }
        sqlcn.Close();
    }

    //Identify if user refresh page
    t_new = new_request.QueryString["t"];
    t_old = new_request.QueryString["t"];   
    if (Session["Time"] == null)
        Session["Time"] = t_new;
    else
        t_old = Session["Time"].ToString();
   
    //Cache report
    if (t_new == t_old)
    {
        try
        {       
            if (Session["Report"] != null)
            {
                oRpt = (ReportDocument)Session["Report"];
            }
            else
            {
                oRpt = new ReportDocument();
                oRpt.Load(Server.MapPath("ReportFile/" + rpt_name));
                Session["Report"] = oRpt;
            }   
        }
        catch(Exception e1)
        {
            altstr += "Sorry, we cannot find the report, please contact IT team, thanks.<br>";
                Response.Write(altstr);
            Response.End();   
        }
        }
    else
    {
        oRpt = new ReportDocument();
        try
        {
            oRpt.Load(Server.MapPath("ReportFile/" + rpt_name));
            Session["Report"] = oRpt;           
        }
        catch(Exception e1)
        {
            altstr += "Sorry, we cannot find the report, please contact IT team, thanks.<br>";
                Response.Write(altstr);
            Response.End();   
        }
    }

    CrystalReportViewer1.ReportSource = oRpt;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
    //Prevent from manually typing URL
    if(Request.ServerVariables["HTTP_REFERER"] == null)
    {
        altstr += "Error way to execute report, please re-execute, thanks.<br>";
        Response.Write(altstr);
        Response.End();
    }


    formtitle.Text = report_name;   

    if(status == "N")
    {
        altstr += "Sorry, this report is closed, please contact IT team, thank you.<br>";
            Response.Write(altstr);
        Response.End();
    }   

    //Configure database connection
        SetDBConnection();
       
    CrystalReportViewer1.DisplayGroupTree = false;
   
    //Retrieve report's parameters
        SetParameterField();
    }

    protected void SetDBConnection()
    {
        string strServer = "";
        string strDBName = "";
        string strUserID = "";
        string strPassword = "";
   
        if (db_type == "DB2")
        {
            strServer = ConfigurationManager.ConnectionStrings["DB2_SERVER"].ConnectionString;
            strDBName = ConfigurationManager.ConnectionStrings["DB2_DB"].ConnectionString;
            strUserID = ConfigurationManager.ConnectionStrings["DB2_UID"].ConnectionString;
            strPassword = ConfigurationManager.ConnectionStrings["DB2_PWD"].ConnectionString;
        oRpt.SetDatabaseLogon(strUserID, strPassword);
        }

        if (db_type == "ORACLE")
        {
            strServer = ConfigurationManager.ConnectionStrings["ORACLE_SERVER"].ConnectionString;
            strDBName = ConfigurationManager.ConnectionStrings["ORACLE_DB"].ConnectionString;
            strUserID = ConfigurationManager.ConnectionStrings["ORACLE_UID"].ConnectionString;
            strPassword = ConfigurationManager.ConnectionStrings["ORACLE_PWD"].ConnectionString;   
        oRpt.SetDatabaseLogon(strUserID, strPassword);   
        }

        if (db_type == "MSSQL")
        {
            strServer = ConfigurationManager.ConnectionStrings["MSSQL_SERVER"].ConnectionString;
            strDBName = ConfigurationManager.ConnectionStrings["MSSQL_DB"].ConnectionString;
            strUserID = ConfigurationManager.ConnectionStrings["MSSQL_UID"].ConnectionString;
            strPassword = ConfigurationManager.ConnectionStrings["MSSQL_PWD"].ConnectionString;
            dbConn.ConnectionInfo.ServerName = strServer;
            dbConn.ConnectionInfo.DatabaseName = strDBName;           
            dbConn.ConnectionInfo.UserID = strUserID;
            dbConn.ConnectionInfo.Password = strPassword;
        //oRpt.SetDatabaseLogon(strUserID, strPassword);
        }


    }

    protected void SetParameterField()
    {
    ParameterDiscreteValue discreteVal = new ParameterDiscreteValue();
    ParameterRangeValue rangeVal = new ParameterRangeValue();
    ParameterValues curvalues = new ParameterValues();
    foreach (ParameterFieldDefinition parafld in oRpt.DataDefinition.ParameterFields)
    {
      if (parafld.DiscreteOrRangeKind.ToString() == "DiscreteValue")
      {
        discreteVal.Value = new_request.QueryString[parafld.ParameterFieldName];
        if (discreteVal.Value != null)
        {   
          curvalues.Add(discreteVal);
          parafld.ApplyCurrentValues(curvalues);
        }   
      }
    }
    }

    private void Page_Unload(object sender, EventArgs e)  
    { 
    Session["Time"] = t_new;
    } 
}

1 comment:

  1. I benefit reading this article. I demand to learn more on this topic
    development asp.net

    ReplyDelete