Haz 19 2009

Call Crystal report from ASP.Net & Changing connectionInfo of Crystal report at runtime

Category: ASP.NET | Crystal ReportLatif @ 17:57

 Code with descriptions: file.cs

    try

    {

        //Get params from QueryString

        string startdate = context.Request.QueryString["StartDate"];

        string enddate = context.Request.QueryString["EndDate"];

        //Define report format

        context.Response.ContentType = "application/pdf";

 

        ReportDocument crReport = new ReportDocument();

        crReport.Load(context.Server.MapPath("rpRevenueSharingAll.rpt"));

        //Set the parameters

  crReport.SetParameterValue(0, startdate); //STARTDATE

        crReport.SetParameterValue(1, enddate);//ENDDATE

       

        //Retrieve Web.config's connection string information

        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);

        string password = builder.Password;

        string username = builder.UserID;

        string dataSource = builder.DataSource;

        string initialCatalog = builder.InitialCatalog;

 

        //Define new connection for Crystal Report

        CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

        connectionInfo.DatabaseName = initialCatalog;

        connectionInfo.UserID = username;

        connectionInfo.Password = password;

        connectionInfo.ServerName = dataSource;

 

        // set report connection for main report

        SetDBLogonForReport(connectionInfo, crReport);

        // set report connection for any subreports

        SetDBLogonForSubreports(connectionInfo, crReport);

 

        MemoryStream ms = (MemoryStream)crReport.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);

        context.Response.BinaryWrite(ms.ToArray());

 

    }

    catch (System.Threading.ThreadAbortException ex)

    {

       

    }

    finally

    {

        context.Response.End();

    }

 You can see the functions I have used above. I found the functions from http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=1249&PID=4186

        private void SetDBLogonForReport

          (CrystalDecisions.Shared.ConnectionInfo connectionInfo,

            ReportDocument reportDocument)

        {

 

            Tables tables = reportDocument.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

            {

                TableLogOnInfo tableLogonInfo = table.LogOnInfo;

                tableLogonInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(tableLogonInfo);

            }

        }

 

        private void SetDBLogonForSubreports

          (CrystalDecisions.Shared.ConnectionInfo connectionInfo,

           ReportDocument reportDocument)

        {

            Sections sections = reportDocument.ReportDefinition.Sections;

            foreach (Section section in sections)

            {

                ReportObjects reportObjects = section.ReportObjects;

                foreach (ReportObject reportObject in reportObjects)

                {

                    if (reportObject.Kind == ReportObjectKind.SubreportObject)

                    {

                        SubreportObject subreportObject = (SubreportObject)reportObject;

                        ReportDocument subReportDocument =

                          subreportObject.OpenSubreport(subreportObject.SubreportName);

                        SetDBLogonForReport(connectionInfo, subReportDocument);

                    }

                }

            }

        }

 

Tags: ,

Comments

1.
Dors Feline Dors Feline United Kingdom says:

Don't really agree but you said it well

2.
dentist dentist United States says:

I hope you will countinue your work. I want a blogengie blog as well. Try to implemet it.