Changre report connectionstring at runtime

[Tzach]

Hello,

I've created a library of reports as described in the documentation.
When building the report library, I've used a certain connection string for each report. However at runtime, the report connectionstring needs changing to reflect the current environment settings.
Searching through the documentation, I couldn't find such an example.

Your help is needed.

Regards,
T.

[Steve]

Hi Tzach,

I believe this forum post explains how to achieve what you are looking for. Another option is to change the connection string on the fly like shown in this help article.

Sincerely yours,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Tzach]

Hi Steve,

Thank you for theprompt response.
It appears that your answer do not solve my issue.
As I've stated earlier, I've created a report library (following your example in the documentation) with all the SQL code already embedded in the report files. What I like to do is just change the connectionstring parameter for each report and not to recreate all the SQL again.

Is there a way to do that?

[Steve]

Hello Tzach,

You can open the DataSetTableAdapter and go to configure to change the connection string manually (see screenshot). If you would like to change a querystring parameter at runtime, you can review the following video elaborating on this: Telerik Reporting - Design Time Support for Parameterized Queries.

All the best,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Tzach]

Hi Steve,

Changing the connection manually is not an option.
However passing the connection string as a parameter works likes a charm. When the connectionstring arrives at the report, I'm able to access the sqlConnection object and change it's connectionstring attribute.

It would be easier if I could access the sqlConnection object from the calling application and change the attribute without the need to pass it as parameter. Is this feasible?

Thanks.
Tzach.

[Svetoslav]

Hi Tzach,

As you may already seen Telerik Reporting depends on the standard .NET data source objects - DataSet, collections, etc. This means that you are responsible for feeding the reports with data. If I understand your scenario correctly you have a report library which you use from an application which you develop as well. There is no limitation how you can set the data source of the reports. Since Report.DataSource property is public you can access it from outside the reports. In short you can create and initialize your data source object on the application level and pass it to any report just before displaying it in the report viewer.

Kind regards,
Svetoslav
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Tzach]

Hello Svetoslav,

I beleive that using a datasource in my application is a good idea, but it contradicts Telerik documentation concept of a report library. In your documentation you suggest that a programmer should create a report library that shall be referenced from a developed application. In the report library all SQL text, connection strings etc. are already in place. Following your suggestion, then I need to recreate all SQL text in the developed application in addition to the one I did for the report library (as the SQL query resulting data is what datasource holds). This makes no sense. Either I should create a report library as per telerik examples or I should create a report library with just report GUI and pass it all data from the developed application.

As I've already created a whole bunch of reports in the library, I don't want to redo the SQL again in the developed application. This is why I just pass the connectionstring itslef to the report library.

I tried to find a public property for the Reportviewer.report ConnectionString, but one is not available.

Regards,
Tzach.

[Rossen Hristov]

Hello Tzach,

The Viewers do not have a property called ConnectionString and they will never have one. The Viewers' only job is to display a report, they do not need to "know" about connection strings. Imagine that you are exporting a report programmatically to PDF, let's say, then there is no Viewer at all. Where should the report get its data source from? Another example is binding a report to a Business Object -- the notion of connection string does not even exist in that case. In other words it is a responsibility of the Report instance to have a valid data source before it is ready for use, i.e. viewing, exporting, etc. And that can be achieved in numerous ways:

1. You can pass the connection string through your report's constructor, and then in the constructor, after the InitializeComponent() method call, replace the original connection string of your data source (i.e. SqlDataAdapter, etc.) with the passed in connection string.
2. You can create a property of your report called ConnectionString and in the setter do the same -- replace the original connection string of the DataSource with the new one.
3. You can directly type cast Report.DataSource to whatever your data source type is and then replace the connection string of that object before showing the report in the viewer.

There are probably lots of other ways to do that same thing. The main point is that you will not replace the Report.DataSource and thus keep your SQL. You will only replace connection string of the existing data source before you display the report.

I hope this helps.

Greetings,
Ross
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Tzach]

Hello Ross,

I've already resolved the ConnectionString issue by setting a property for the report (I've set this forum thread to resolved a few posts ago). My intention was to answer Svetoslav in regard to using DataSource.

In regard to the ReportViewer connectionstring property please observe that I'm refering to ReportViewer.Report and not to the ReportViewer itslef.

I use the following code to pass the connectionstring to the report:

ReportViewer1.Report = new myReport();
(ReportViewer1.Report as myReport).strConnectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

I might as well use Report testReport = new Report(myReport()); but it still shall do the same thing.

To summarize, I've already solved my issue using Steve advice, and would like to thank the rest of Telerik support team for their help.

[Rossen Hristov]

Hi Tzach,

I want to apologize for the misunderstanding. I did not realize that the problem was already solved. The important thing is that you have managed to make everything work. If anything else appears along the way -- feel free to drop us a line.

Sincerely yours,
Ross
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[AkAlan]

In case anyone is still following this post I have a question as to my solution to this problem. I understand there are a lot of ways to dynamically assign a connectionstring to a datasource but I was wondering if this technique is viable.

I have created a class to hold all the reports like advised by Telerik. I tried using the code here as sugessted in the code behind of the report but got the blue squiggly saying configurationmanager was not declared.

 If ConfigurationManager.ConnectionStrings("SampleDB") IsNot Nothing Then
            Me.conn.ConnectionString = ConfigurationManager.ConnectionStrings("SampleDB").ConnectionString
        End If

Not being an expert on asp.net I gave up on that approach.

For my asp.net web app I had always defined a connectionstring in my web.config file and just renamed it before deploying. I wanted the same approach here and ended up with this solution:

Created a class called in my report library class called ConnString.vb
In that class I created some  Public Functions that held various connectionstrings like this:

Public Function AWConnString()

Return "Data Source=ARCTEC-DEV-SVR;Initial Catalog=AdventureWorks;Integrated Security=True"

End Function

I use added a SqlDataAdapter and SqlConnection to my report and in the code behind I called the function to configure my SqlConnection like this:

Public Sub New()
        InitializeComponent()
        Dim cs As New ConnString
        Me.SqlConnection1.ConnectionString = cs.AWConnString()
    End Sub

Now all I have to do before deploying is go into the ConnString class and edit the connection string. I'm thinking that I could add some logic that looks to see which machine I'm running from and assigns the connectionstring dynamically without ever having to edit anything. Something like this

Public Class ConnString

    Public Function AwConnString()
        If HttpContext.Current.Request.IsLocal Then
            Return "Data Source=ARCTEC-DEV-SVR;Initial Catalog=AdventureWorks;Integrated Security=True"
        Else 'would probably do some better checking here but you get the idea
            Return "Data Source=ARCTEC-PROD-SVR;Initial Catalog=AdventureWorks;Integrated Security=True"
        End If

    End Function

End Class

The problem I am having with this is I get the blue squiggly under HttpContext saying it is not declared. If I could get that resolved I think it would work. Any thoughts??

[Steve]

Hi Alan,

ConfigurationManager requires reference to the System.Configuration namespace i.e. add the following:

Imports System.Configuration

Kind regards,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[AkAlan]

Hi Steve, Thanks for taking the time to help. I have imported all the following:

Imports System.ComponentModel
Imports System.Drawing
Imports System.Windows.Forms
Imports Telerik.Reporting
Imports Telerik.Reporting.Drawing
Imports MyTelerikLibrary.AdventureWorksDataSetTableAdapters
Imports System.Web
Imports System.Data.SqlClient
Imports System.Configuration

When I try to add ConfigurationManager to code in the NeedDataSource event, it still doesn't appear in intellisense. I am happy with my solution but would still like to know why I can't get it to work. I feel I have hijacked this post enough and I will start a new one asking that specific question. Thanks, you guys are really on top of this forum, good job.

[IT]

Right click on references then add system.configuration.

build application and try configurationmanager again

should work fine

[Erik]

Steve, that help article is exactly what I need, however, the link does not work...help?

[Steve]

Hi,

Do you refer to the Design Time Support for Parameterized Queries video?

All the best,
Steve
the Telerik team