How to change connection for trdp report in REST service resolver

3 posts, 0 answers
  1. Jordan
    Jordan avatar
    1 posts
    Member since:
    Feb 2017

    Posted 10 Jan Link to this post

    Hello,

    It took me a long time to get this figure out so I thought I would share it.  I found a lot of PARTIAL examples in the forums and I had to piece this together from them. So I wanted to post something that shows the entire solution to the problem of changing connections for reports.

    GOAL: I needed a simple way to manage database connections for .trdp files in a Reporting Rest Service.

    SET UP:
    1. I have a local development reporting Rest Service running in my local IIS
    2. I have a reporting rest service as its own site in an Azure Virtual Machine IIS
    3. I have defined 2 connection strings in my local rest service web.config for my local and remote database
    4. This service is used by an Angular (version 7 as of post) App with an HTML 5 report viewer

    REQUIREMENTS:
    1. have a way to use the local connection string when testing locally
    2. have a way to use the remote connection string when I "publish/deploy" my rest service tot he remote VM IIS
    3. This project will ONLY use .trdp files so I wanted to keep it as simple as possible
    4. I had to enable CORS to get this all to work

    HOW IT WORKS:
    1. Connections defined in web.config
    2. a: use C# preprocessors to change the connection:  see here https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/preprocessor-directives/
        b: used custom build types (Publish_Release & Publish_Debug in addition to the default Debug and Release) for the logic of what connection string to use.
        c: Note that if you want to use custom build types like me you will need to add them to the Project Properties => Build => Conditional compiliation Symbols
    3. build custom report resolver to unpackage and assign the connection
    4. Use custom resolver in default ReportsController of rest service
    5. Note that I moved the definition for the location of the reports folder to the custom resolver
    6. I built in a report parameter ("connection") that you will have to add to your report that displays the database server in the report (with username and password removed).  This is just for debugging and can be removed in your context.

     

    NOTE: I will show all code including the using statements to make it easier to understand everything going on

    1: the web.config connections:

        <add name="conn_dev" connectionString="YourLocalConnection" providerName="System.Data.SqlClient" />
        <add name="conn_live" connectionString="YourRemoteConnection" providerName="System.Data.SqlClient"  />

    2 - 3: Custom Resolver

    using System;
    using System.Configuration;
    using System.IO;
    using System.Web;
    using Telerik.Reporting;
    using Telerik.Reporting.Services.Engine;

    namespace Regent.Reporting.Resolvers
    {
      public class MyResolver : IReportResolver
      {
        private static string _serverPath;
        private static string _conn;

        public MyResolver()
        {
          /* GET server Path */
          _serverPath = HttpContext.Current.Server.MapPath("~/");

          /* GET CONNECTION STRINGS */
          /* Conditionally set connection string based on build mode */
    #if (Publish_Release || Publish_Debug)
          _conn = ConfigurationManager.ConnectionStrings["conn_live"].ConnectionString;
    #endif
    #if (!Publish_Release && !Publish_Debug)
          _conn = ConfigurationManager.ConnectionStrings["conn_dev"].ConnectionString;
    #endif

        }

        public ReportSource Resolve(string reportId)
        {
          try
          {
            /* get path to report */
            var reportsPath = Path.Combine(_serverPath, "Reports"); // path to folder where the .trdp reports are kept
            var sourceReportSource = new UriReportSource { Uri = reportsPath + "\\" + reportId };

            //retrieve an instance of the report     
            var reportPackager = new ReportPackager();
            using (var sourceStream = File.OpenRead(sourceReportSource.Uri))
            {
              var report = (Report)reportPackager.UnpackageDocument(sourceStream);
              /* gets server name of connection string without user/password
               and assigns this value to report parameter for display in report */
              report.ReportParameters["connection"].Value = _conn.Split(new [] {';'},2)[0];
              var reportInstance = new InstanceReportSource
              {
                ReportDocument = report
              };
              // assign the connection
              ((SqlDataSource)report.DataSource).ConnectionString = _conn;
              
              return new InstanceReportSource { ReportDocument = reportInstance.ReportDocument };
            }

          }
          catch (Exception e)
          {
            Console.WriteLine(e);
            return null;
          }

        }
      }
    }

     

    4. Use the custom resolver in the ReportsController

    using Regent.Reporting.Resolvers;
    using System.Web.Http.Cors;
    using Telerik.Reporting.Cache.File;
    using Telerik.Reporting.Services;
    using Telerik.Reporting.Services.WebApi;

    namespace Regent.Reporting.Controllers
    {

      //The class name determines the service URL. 
      //ReportsController class name defines /api/report/ service URL.
      [EnableCors(origins: "*"              , headers: "*"              , methods: "*")]
      public class ReportsController : ReportsControllerBase
      {
        private static readonly ReportServiceConfiguration configurationInstance;

        static ReportsController()
        {
          //Setup the ReportServiceConfiguration
          configurationInstance = new ReportServiceConfiguration
          {
            HostAppId = "Regent.Reporting",
            Storage = new FileStorage(),
            ReportResolver = new MyResolver()
          };
        }

        public ReportsController()
        {
          //Initialize the service configuration
          this.ReportServiceConfiguration = configurationInstance;
        }
      }
    }

     

     

    In conclusion, I hope this helps someone else.  This was as simple as I could make it change the connection string based on build types for only .trdp reports.  Unfortunately, this forum does not allow posting formatted code so you will have to pull it into your own editor to see it clearly.

    Cheers

  2. Silviya
    Admin
    Silviya avatar
    260 posts

    Posted 15 Jan Link to this post

    Hello Jordan,

    Thank you for sharing a possible approach for changing the named connection strings in TRDP reports dynamically.
    However, I noticed that initially your web.config file includes two named connection strings: conn_dev and conn_live. But you could use only one and change it's connectionString value.

    Additionally, I could recommend to introduce a new Report Parameter in the report definition and assign the value of this parameter to the DataSource.ConnectionString property as explained in the Change Connection String dynamically through a report parameter KB article. The value of the Report Parameter can be passed from the viewer run time, and will be automatically assigned to the DataSource when processing the report. This approach requires minor modifications in the report itself and does *not* involve additional programming

    Regards,
    Silviya
    Progress Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

  3. SSirica
    SSirica avatar
    71 posts
    Member since:
    Jan 2007

    Posted 20 hours ago in reply to Silviya Link to this post

    I tried your solution it doesn't work.  All I get is "Format of the initialization string does not conform to specification starting at index 0"
Back to Top