This is a migrated thread and some comments may be shown as answers.

How to change connection for trdp report in REST service resolver

10 Answers 1177 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Jordan
Top achievements
Rank 1
Jordan asked on 10 Jan 2019, 07:42 PM

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 build types like me you will need to add them to the Project Properties => Build => Conditional 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
.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

10 Answers, 1 is accepted

Sort by
0
Silviya
Telerik team
answered on 15 Jan 2019, 12:58 PM
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

0
SSirica
Top achievements
Rank 3
Iron
Iron
Iron
answered on 20 Feb 2019, 05:08 PM
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"
0
Jordan
Top achievements
Rank 1
answered on 21 Feb 2019, 08:18 PM

Hello,

There is not enough information to help with the error.  Where does it occur, what file line method etc...

0
Silviya
Telerik team
answered on 22 Feb 2019, 03:42 PM
Hello SSirica,

Could you provide more information why this approach didn't work for you, so we could help you further?

As a side note, the "Format of the initialization string does not conform to specification starting at index 0" error message is usually shown when the configuration file of the application does not contain the required named connection string. For more information please check the Connection Strings and Configuration Files MSDN page.
Please check the configuration file and make sure that the named connection string used in the report definition exists there.
In case the issue persists, please open a new support ticket and send us a runnable sample project exhibiting the issue, so we can review it locally and advise accordingly.

Best 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
0
Juan
Top achievements
Rank 1
answered on 15 Jun 2020, 05:36 PM

Please, do you have an example?

I have the same problem, I want my connection string to be dynamic and not have them in the appseting.json (I use net core)

I have reports but I want them to work for a multi-tenant scheme, each tenant has their connection to a database

0
Eric R | Senior Technical Support Engineer
Telerik team
answered on 15 Jun 2020, 10:39 PM

Hi Juan,

I just wanted to give you a heads up, I replied to your support ticket with a sample for the same question.

Please refer to the ticket and let me know if you have any additional questions. Thank you.

Regards,


Eric R | Senior Technical Support Engineer
Progress Telerik

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
Manish
Top achievements
Rank 1
commented on 11 Nov 2021, 01:50 AM

Hi Eric, 

I am also in the same boat as Juan, Can you please share the link of the the support ticket or share the solution. I am also using the same setup, HTML5Viewer and REST Service and I wish to use different connection string when running the reports on DEV/TEST/PROD Servers.

Thank you,
Manish Thouri

Eric R | Senior Technical Support Engineer
Telerik team
commented on 11 Nov 2021, 03:22 PM

My colleague Dimitar has responded to ticket 1542570 with the recommended approach for your requirement. Unfortunately, providing examples for this is not suitable because of the complexities involved with managing multiple environments. 

0
SL
Top achievements
Rank 1
Veteran
answered on 26 Jun 2020, 06:07 AM

Hi Silviya,how about when using the html5 or blazor viewer which is a pure client.  Passing the connection string from the viewer to the ReportController is not an elegant solution since connection strings should remain on the server.  Jordan's solution is the only way to do it by subclassing the ResolverBase and applying this: https://www.telerik.com/support/kb/reporting/details/changing-the-connection-string-dynamically-according-to-runtime-data

Any thoughts?

 

 

0
Eric R | Senior Technical Support Engineer
Telerik team
answered on 26 Jun 2020, 01:51 PM

Hi SL,

You are correct. A best practice is to keep the connection string out of the client to avoid security exploits. Generally, there are two scenarios where multiple databases are needed. These are deploying to multiple environments and multi-tenancy. Let me go over these below.

Deploying to Multiple Environments

The provided solution in the original post is for switching on build configurations that are deployed to different environments. ASP.NET has several mechanisms for handling different build configurations out-of-the-box. For example, see Config Transforms in ASP.NET Core and web.config transforms in ASP.NET Full Framework. 

In Telerik Reporting, when using the proper configuration transformation method, a Shared Connection String will work without any additional code.

This works because when creating a DataSource Component with shared connection string, the ConnectionString property is set to the name of the shared connection in the Report Designer. See the following screenshot for a reference.

When using the above report in a .NET application, Telerik Reporting will match this ConnectionString property to the Connection String name provided in the application configuration. See the following app.config for an example. 

Since the configuration transforms the connectionString value per environment, multiple database connections can be used.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString"
            connectionString="Data Source=(localdb)\mssqllocaldb;Initial Catalog=AdventureWorks;Integrated Security=SSPI"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

An example of this is provided in the default installation folder. The two configurations to reference are listed below.

  1. The ReportLibrary app.config: C:\Program Files (x86)\Progress\[Telerik Reporting Release Version]\Examples\CSharp\ReportLibrary\app.config
  2. The MVC Demo Web.config: C:\Program Files (x86)\Progress\[Telerik Reporting Release Version]\Examples\CSharp\MvcDemo\Web.config
    1. NOTE: The build configs are named Web.Debug.config and Web.Release.config. If deploying to Release a new connection string could be used pointing to a different server and database.

Multi-Tenancy

Multi-tenancy is far more complex and requires careful planning. Ultimately, Telerik Reporting and multi-tenancy are unrelated which is primarily why we don't have a demo for it. It's technically not part of the Telerik Support options.

However, once multi-tenancy is set up, the TenantID can be used as a Report Parameter allowing preparation of the data accordingly.

Wrapping Up

Another example that I thought of as I was writing this is when a report parameter is used as a binding expression. In this case, the connection string could be generated dynamically and bound to a Data Item without having the connection string in the application or the report.

I hope this provides a better understanding of why we recommend using a Report Parameter for dynamic connection strings.

Please let me know if you need any additional information. Thank you for using the Reporting Forums.

Regards,


Eric R | Senior Technical Support Engineer
Progress Telerik

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
SL
Top achievements
Rank 1
Veteran
answered on 26 Jun 2020, 10:48 PM

Thank you Eric for the insight.

Unfortunately, this will not work for me since my connection strings are "loaded" at runtime since I am getting it from the Azure KeyVault so this means I cannot use web.config to store my connection strings.  

0
Eric R | Senior Technical Support Engineer
Telerik team
answered on 29 Jun 2020, 07:36 PM

Hi SL,

Thank you for providing the additional information. The methods from the previous post was a general explanation. Each scenario may have different requirements. 

As a result, I have created support ticket 1473971 for you that where we can review your scenario and offer any possible solutions. I will follow up there with any additional information there.

Please refer to the support ticket if you have any additional questions. Thank you.

Regards,


Eric R | Senior Technical Support Engineer
Progress Telerik

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
Paul
Top achievements
Rank 1
commented on 03 May 2021, 06:10 PM | edited

Was there a more simple solution that came out of the ticket? I think it would be easier to share than me submitting a new ticket for the exact same issue.

Paul
Eric R | Senior Technical Support Engineer
Telerik team
commented on 03 May 2021, 07:07 PM

Hi Paul, 

Due to the complexity of changing the connection string at runtime, we can only offer support for the recommended approaches previously outlined. 

Ultimately, we are unable to create a sample that would work for every scenario.

For reference, see the Change Connection String Dynamically through a Report Parameter the, How to Implement a Custom Report Source Resolver, and the Changing the Connection String Dynamically according to Runtime Data articles.

Please let me know if you need any additional information. Thank you.

 

Tags
General Discussions
Asked by
Jordan
Top achievements
Rank 1
Answers by
Silviya
Telerik team
SSirica
Top achievements
Rank 3
Iron
Iron
Iron
Jordan
Top achievements
Rank 1
Juan
Top achievements
Rank 1
Eric R | Senior Technical Support Engineer
Telerik team
SL
Top achievements
Rank 1
Veteran
Share this question
or