Change Connection String for Reports designed in the Standalone Designer

[erwin]

I would like to run reports designed by the standalone designer from my application.
So far I have managed to load the report definition (.trdx file) and run it in the app.

What's the best way to specify the connection string dynamically from the application?
I did not find a pointer in the docs.
Changing the ConnectionString attribute on the SqlDataSource Node in the XML Document in memory would probably be an option, but is there a direct Telerik API for that?

I am confused a bit with the different options to set the Windows Forms Report Viewer ReportSource property:

If I use XmlReportSource to load a trdx file, I apparently do not get all the functionality of the Report object (DataSource, ReportParameters),

However, the following code seems to work:

// ReportDefinitionFile is the path to my .trdx file

 

var xmlSerializer = new ReportXmlSerializer();

var reportDocument = (Report)xmlSerializer.Deserialize(ReportDefinitionFile);

  

var reportSource = new InstanceReportSource();

reportSource.ReportDocument = reportDocument;

  

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();


.... // initialize connection string
   

SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;

sds.ConnectionString = scsb.ConnectionString;

 

this.reportViewer1.ReportSource = reportSource;

this.reportViewer1.RefreshReport();

Regards
Erwin

[Steve]

Hi Erwin,

In the standalone report designer, when you use SqlDatasource component, you're asked to save the connectionString with a desired name. This name is the name of the connectionString, so basically you can have a connectionString with the same name attribute in your application config file (app.config) and the SqlDatasource component would handle the binding automatically.

If you prefer to work with CLR types and objects, you can deserialize the xml report definition and proceed from there like you would normally do following the basic concepts of the programming language and the .NET platform.

Greetings,
Steve
the Telerik team

[Ted]

Huh?  The previous response from Telerik does not answer the question.  

There is a Telerik.ReportViewer.WinForms.ReportViewer control in a Windows Form app.  The user selects a pregenerated *.trdx report file within the app.  The app binds the file to the ReportViewer control's ReportSource property using a Telerik.Reporting.UriReportSource instance.  

The questions is: how can the app DYNAMICALLY set the connection string for the report?  The user wants to enter/choose this connection string DYNAMICALLY (i.e., it is not in the app.config file or any other pre-defined config file deployed with the app).  How should this be done using the ReportViewer control?

[Myth]

Interested in this is well. I believe there's many reasons why a connection string wouldn't want to be saved in an app.config file. And for now I see no option to configure this dynamically.

[Myth]

The above trick works for basic reports, but when using multiple datasources in a dashboard like report, I can't seem to find out how to update the connection strings. For example in datasources referenced by crosstabs or charts.

[erwin]

If all your DataSources point to the same Database, you can inspect the Report Object and replace the Datasource Configuration:

(my App's name is Octopus, so replace that with your Name ...)

// use the telerik ReportXmlSerializer to create a Report object from XML
                  var xmlSerializer = new ReportXmlSerializer();
                  var reportDocument = (Telerik.Reporting.Report)xmlSerializer.Deserialize(this.ReportDefinitionFile);

                  // create a ReportSource object
                  var reportSource = new InstanceReportSource();
                  reportSource.ReportDocument = reportDocument;

                  // get the applications connection string (I'm using LLBLGEN here)
                  SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
                  scsb.ConnectionString = CommonDaoBase.ActualConnectionString;

                  // ... needed by other parts ot the app
                  this.connectionString = CommonDaoBase.ActualConnectionString;
                  this.databaseDatabase = scsb.InitialCatalog;
                  this.databaseServer = scsb.DataSource;
                  this.databaseLogin = scsb.UserID;
                  this.databasePassword = scsb.Password;
                  this.integratedSecurity = scsb.IntegratedSecurity;

                  // fill in well-known Report Parameters if the report defines them
                  // if ReportParameter "OctopusConnectionString" is present, the report uses Binding to set up it's DataSource objects
                  if (!this.SetReportParameters(reportDocument))
                  {
                      // no OctopusConnectionString Parameters, set the ConnectionString on the Report DataSource
                      SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;
                      sds.ConnectionString = scsb.ConnectionString;

                      // try to patch DataItems in the report (Table, Crosstab, List, Subreports have their own DataSource objects)
                      this.SetConnectionStrings(reportDocument.Items, scsb.ConnectionString);
                  }

This is experimental, seems to work for my cases so far

private void SetConnectionStrings(Telerik.Reporting.ReportItemBase.ItemCollection items, string connectionString)
    {
        foreach (Telerik.Reporting.ReportItemBase ib in items)
        {
            if (ib.Items != null)
            {
                if (ib.Items.Count > 0)
                {
                    this.SetConnectionStrings(ib.Items, connectionString);
                }
            }

            SqlDataSource ds = null;

            if (ib is Telerik.Reporting.Chart)
            {
                Telerik.Reporting.Chart chart = ib as Telerik.Reporting.Chart;
                ds = chart.DataSource as SqlDataSource;
            }
            else if (ib is Telerik.Reporting.Crosstab)
            {
                Telerik.Reporting.Crosstab crosstab = ib as Telerik.Reporting.Crosstab;
                ds = crosstab.DataSource as SqlDataSource;
            }
            else if (ib is Telerik.Reporting.Table)
            {
                Telerik.Reporting.Table table = ib as Telerik.Reporting.Table;
                ds = table.DataSource as SqlDataSource;
            }
            else if (ib is Telerik.Reporting.List)
            {
                Telerik.Reporting.List list = ib as Telerik.Reporting.List;
                ds = list.DataSource as SqlDataSource;
            }
            else if (ib is Telerik.Reporting.SubReport)
            {
                Telerik.Reporting.SubReport rpt = ib as Telerik.Reporting.SubReport;
                ds = rpt.Report.DataSource as SqlDataSource;
            }

            if (ds != null)
            {
                ds.ConnectionString = connectionString;
            }
        }
    }

For advanced reports that bind to multiple datasources (multiple different databases) i have found 2 possible solutions so far:

1: Use naming conventions for your datasources so that you can find out which datasource configuration to overwrite with which dynamic database connection after you have created the Report object from the .trdx (or use XML Tools to manipulate the .trdx before de-serializing into a Report object).

2: Use Report Parameters and use Binding within the datasource configuration in the designer. While this is cleaner conceptually,
it has the problem that you now have to enter the values for the Report Parameters for tests in the designer.

Regards
Erwin

[Myth]

Silly thing is there is a 'Datasources' property in the report, but it's 'protected' and not public. Simply changing this would make all our problems go away.

I tried creating an inherited class that would open the property, but naturally it's not possible to deserialize the report to the inherited class.

[Myth]

I've 'improved' upon Erwin's solution using reflection:

[code]
private void SetConnectionString(Report reportDocument, String connectionString)
        {
            SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;

            if (sds != null)
                sds.ConnectionString = connectionString;

            this.SetConnectionString(reportDocument.Items, connectionString);
        }

        private void SetConnectionString(Telerik.Reporting.ReportItemBase.ItemCollection items, String connectionString)
        {
            foreach (Telerik.Reporting.ReportItemBase ib in items)
            {
                if (ib.Items != null)
                {
                    this.SetConnectionString(ib.Items, connectionString);
                }

                Type type = ib.GetType();
                PropertyInfo dataSourceProperty = type.GetProperty("DataSource");
                if (dataSourceProperty == null)
                    continue;

                var dataSource = dataSourceProperty.GetValue(ib, null) as SqlDataSource;
                dataSource.ConnectionString = connectionString;
            }
        }
[/code]