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

Change Connection String for Reports designed in the Standalone Designer

7 Answers 886 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
erwin
Top achievements
Rank 1
Veteran
Iron
erwin asked on 27 Aug 2012, 02:44 PM

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

7 Answers, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 30 Aug 2012, 01:55 PM
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

BLOGGERS WANTED! Write a review about Telerik Reporting or the new Report Designer, post it on your blog and get a complimentary license for Telerik Reporting. We’ll even promote your blog and help bring you a few fresh readers. Yes, it’s that simple. And it’s free. Get started today >

0
Ted
Top achievements
Rank 1
Iron
Iron
answered on 13 Nov 2012, 12:12 AM
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?


0
Myth
Top achievements
Rank 1
answered on 28 Nov 2012, 12:15 PM
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.
0
Myth
Top achievements
Rank 1
answered on 28 Nov 2012, 03:32 PM
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.
0
erwin
Top achievements
Rank 1
Veteran
Iron
answered on 28 Nov 2012, 07:08 PM
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
0
Myth
Top achievements
Rank 1
answered on 04 Dec 2012, 07:40 AM
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.
0
Myth
Top achievements
Rank 1
answered on 22 May 2013, 12:16 PM
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]
Tags
General Discussions
Asked by
erwin
Top achievements
Rank 1
Veteran
Iron
Answers by
Steve
Telerik team
Ted
Top achievements
Rank 1
Iron
Iron
Myth
Top achievements
Rank 1
erwin
Top achievements
Rank 1
Veteran
Iron
Share this question
or