CODEWITHSUNDEEP
wpfreporting-servicesdatasourcereportviewerreportserver
jadavparesh06
jadavparesh06

Reputation: 946

I want to update the Shared DataSource of SSRS Report from c# code

I have a report deployed on my ReportServer. This Report is using a shared dataSource which is also deployed on the ReportServer. I am using a ReportViewer in WPF application using WindowsFormsHost control. I am able to display the Report from the ReportServer within my application properly by configuring the ServerReport property of the ReportViewer.

My question is that is there anyway to change the connectionstring of the shared DataSource deployed on the ReportServer which is being used by my Report.

Actually I want to use the same Report for multiple copies of the same database for Testing and Migration.

Can anyone please tell me a solution to update the Shared Datasource in c# code?

Upvotes: 3

Views: 874

Answers (1)

JC Ford
JC Ford

Reputation: 7066

There's no way to change the data sources of a report via the ReportViewer. You could change the data source using the web service, but that would actually change the data source on the server for all users - likely not what you want to do.

I think the closest you can get is to build your report with an embedded data source that uses a parameter value to control its connection string. You could build a shared dataset that provides connection strings by name ("Test","Migration",etc) and pass just that name as a parameter to the report.

You would need:

  1. A shared data source that does not change.
  2. A shared dataset that returns a list of connection names such as "Test" and "Migration". Let's call that NamedConnections. These could come from a table in the shared data source or could be hard-coded in the dataset's query.
  3. A shared dataset that takes a @NamedConnection parameter and returns a single string value that is a complete connection string. Again, these could come from the database or be hard-coded. We'll call it SelectedConnection
  4. A @NamedConnection parameter on the report. This should be visible and should use the NamedConnections dataset for its available values.
  5. A @ConnectionString internal parameter on the report that uses the SelectedConnection dataset for its default value.
  6. An embedded data source in the report that does not use the @ConnectionString parameter. This allows you to use the dataset designer to build your dataset(s). I'll call it StaticConnection.
  7. An embedded data source in the report that does use the @ConnectionString parameter as its connection string. Once the report design is complete and ready to be deployed, switch your dataset(s) to use this data source. Let's call it DynamicConnection.

Now using the ReportViewer, for instance, you pass the value "Test" to the @NamedConnection parameter. Then the SelectedConnection dataset can run and provide the appropriate connection string to the @ConnectionString parameter which is then used by the DynamicConnection data source.

The actual data source reference never changes, but the connection string within it does.

Upvotes: 3

Related Questions