Changing the connection string dynamically according to runtime data

Environment

Product	Progress® Telerik® Reporting
Report Viewer	Desktop Viewer

Description

The approach described in the solution section below is suitable for viewers using embedded Reporting engine, e.g. Desktop Viewers and the obsolete ASP.NET Web Forms Report Viewer.

In other cases, the recommended approach for modifying the connection string dynamically is through dedicated Report Parameter - check the KB article Change Connection String dynamically through a report parameter.

For the viewers using the Telerik Reporting REST Service check the KB article Action NavigateToReport does not work after updating the Connection String dynamically in a Custom Report Resolver.

Solution

Telerik Reporting can resolve and work with named connection strings provided in the application configuration file (web.config or app.config). Thus providing the required connection string with the same name in the application configuration file should be enough for most of the scenarios. Still if this is not applicable for your scenario and you have to provide the connection strings in runtime you can use the following example:

class ReportConnectionStringManager
{
    readonly string connectionString;

    public ReportConnectionStringManager(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public ReportSource UpdateReportSource(ReportSource sourceReportSource)
    {
        if (sourceReportSource is UriReportSource)
        {
            var uriReportSource = (UriReportSource)sourceReportSource;
            // unpackage TRDP report
            var reportInstance = UnpackageReport(uriReportSource);
            // or deserialize TRDX report(legacy format)
            // var reportInstance = DeserializeReport(uriReportSource);
            ValidateReportSource(uriReportSource.Uri);
            this.SetConnectionString(reportInstance);
            return CreateInstanceReportSource(reportInstance, uriReportSource);
        }

        if (sourceReportSource is XmlReportSource)
        {
            var xml = (XmlReportSource)sourceReportSource;
            ValidateReportSource(xml.Xml);
            var reportInstance = this.DeserializeReport(xml);
            this.SetConnectionString(reportInstance);
            return CreateInstanceReportSource(reportInstance, xml);
        }

        if (sourceReportSource is InstanceReportSource)
        {
            var instanceReportSource = (InstanceReportSource)sourceReportSource;
            this.SetConnectionString((ReportItemBase)instanceReportSource.ReportDocument);
            return instanceReportSource;
        }

        if (sourceReportSource is TypeReportSource)
        {
            var typeReportSource = (TypeReportSource)sourceReportSource;
            var typeName = typeReportSource.TypeName;
            ValidateReportSource(typeName);
            var reportType = Type.GetType(typeName);
            var reportInstance = (Report)Activator.CreateInstance(reportType);
            this.SetConnectionString((ReportItemBase)reportInstance);
            return CreateInstanceReportSource(reportInstance, typeReportSource);
        }

        throw new NotImplementedException("Handler for the used ReportSource type is not implemented.");
    }

    ReportSource CreateInstanceReportSource(IReportDocument report, ReportSource originalReportSource)
    {
        var instanceReportSource = new InstanceReportSource { ReportDocument = report };
        instanceReportSource.Parameters.AddRange(originalReportSource.Parameters);
        return instanceReportSource;
    }

    void ValidateReportSource(string value)
    {
        if (value.Trim().StartsWith("="))
        {
            throw new InvalidOperationException("Expressions for ReportSource are not supported when changing the connection string dynamically");
        }
    }

    Report UnpackageReport(UriReportSource uriReportSource)
    {
        var reportPackager = new ReportPackager();
        using (var sourceStream = System.IO.File.OpenRead(uriReportSource.Uri))
        {
            var report = (Report)reportPackager.UnpackageDocument(sourceStream);
            return report;
        }
    }

    Report DeserializeReport(UriReportSource uriReportSource)
    {
        var settings = new System.Xml.XmlReaderSettings();
        settings.IgnoreWhitespace = true;
        using (var xmlReader = System.Xml.XmlReader.Create(uriReportSource.Uri, settings))
        {
            var xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
            var report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
            return report;
        }
    }

    Report DeserializeReport(XmlReportSource xmlReportSource)
    {
        var settings = new System.Xml.XmlReaderSettings();
        settings.IgnoreWhitespace = true;
        var textReader = new System.IO.StringReader(xmlReportSource.Xml);
        using (var xmlReader = System.Xml.XmlReader.Create(textReader, settings))
        {
            var xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
            var report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
            return report;
        }
    }

    void SetConnectionString(ReportItemBase reportItemBase)
    {
        if (reportItemBase.Items.Count < 1)
            return;

        if (reportItemBase is Report)
        {
            var report = (Report)reportItemBase;

            if (report.DataSource is SqlDataSource)
            {
                var sqlDataSource = (SqlDataSource)report.DataSource;
                sqlDataSource.ConnectionString = connectionString;
            }
            foreach (var parameter in report.ReportParameters)
            {
                if (parameter.AvailableValues.DataSource is SqlDataSource)
                {
                    var sqlDataSource = (SqlDataSource)parameter.AvailableValues.DataSource;
                    sqlDataSource.ConnectionString = connectionString;
                }
            }
        }

        foreach (var item in reportItemBase.Items)
        {
            //recursively set the connection string to the items from the Items collection
            SetConnectionString(item);

            //set the drillthrough report connection strings
            var drillThroughAction = item.Action as NavigateToReportAction;
            if (null != drillThroughAction)
            {
                var updatedReportInstance = this.UpdateReportSource(drillThroughAction.ReportSource);
                drillThroughAction.ReportSource = updatedReportInstance;
            }

            if (item is SubReport)
            {
                var subReport = (SubReport)item;
                subReport.ReportSource = this.UpdateReportSource(subReport.ReportSource);
                continue;
            }

            //Covers all data items(Crosstab, Table, List, Graph, Map and Chart)
            if (item is DataItem)
            {
                var dataItem = (DataItem)item;
                if (dataItem.DataSource is SqlDataSource)
                {
                    var sqlDataSource = (SqlDataSource)dataItem.DataSource;
                    sqlDataSource.ConnectionString = connectionString;
                    continue;
                }
            }
        }
    }
}

Imports Telerik.Reporting

Class ReportConnectionStringManager
    ReadOnly connectionString As String

    Public Sub New(connectionString As String)
        Me.connectionString = connectionString
    End Sub

    Public Function UpdateReportSource(sourceReportSource As ReportSource) As ReportSource
        If TypeOf sourceReportSource Is UriReportSource Then
            Dim uriReportSource = DirectCast(sourceReportSource, UriReportSource)
            ' unpackage TRDP report
            Dim reportInstance = UnpackageReport(uriReportSource)
            ' or deserialize TRDX report(legacy format)
            ' var reportInstance = DeserializeReport(uriReportSource);
            ValidateReportSource(uriReportSource.Uri)
            Me.SetConnectionString(reportInstance)
            Return CreateInstanceReportSource(reportInstance, uriReportSource)
        End If

        If TypeOf sourceReportSource Is XmlReportSource Then
            Dim xml = DirectCast(sourceReportSource, XmlReportSource)
            ValidateReportSource(xml.Xml)
            Dim reportInstance = Me.DeserializeReport(xml)
            Me.SetConnectionString(reportInstance)
            Return CreateInstanceReportSource(reportInstance, xml)
        End If

        If TypeOf sourceReportSource Is InstanceReportSource Then
            Dim instanceReportSource = DirectCast(sourceReportSource, InstanceReportSource)
            Me.SetConnectionString(DirectCast(instanceReportSource.ReportDocument, ReportItemBase))
            Return instanceReportSource
        End If

        If TypeOf sourceReportSource Is TypeReportSource Then
            Dim typeReportSource = DirectCast(sourceReportSource, TypeReportSource)
            Dim typeName = typeReportSource.TypeName
            ValidateReportSource(typeName)
            Dim reportType = Type.[GetType](typeName)
            Dim reportInstance = DirectCast(Activator.CreateInstance(reportType), Report)
            Me.SetConnectionString(DirectCast(reportInstance, ReportItemBase))
            Return CreateInstanceReportSource(reportInstance, typeReportSource)
        End If

        Throw New NotImplementedException("Handler for the used ReportSource type is not implemented.")
    End Function

    Private Function CreateInstanceReportSource(report As IReportDocument, originalReportSource As ReportSource) As ReportSource
        Dim instanceReportSource = New InstanceReportSource() With { _
            .ReportDocument = report _
        }
        InstanceReportSource.Parameters.AddRange(originalReportSource.Parameters)
        Return InstanceReportSource
    End Function

    Public Sub ValidateReportSource(value As String)
        If value.Trim().StartsWith("=") Then
            Throw New InvalidOperationException("Expressions for ReportSource are not supported when changing the connection string dynamically")
        End If
    End Sub

    Private Function UnpackageReport(uriReportSource As UriReportSource) As Report
        Dim reportPackager = New ReportPackager()
        Using sourceStream = System.IO.File.OpenRead(uriReportSource.Uri)
            Dim report = DirectCast(reportPackager.UnpackageDocument(sourceStream), Report)
            Return report
        End Using
    End Function

    Public Function DeserializeReport(uriReportSource As UriReportSource) As Report
        Dim settings = New System.Xml.XmlReaderSettings()
        settings.IgnoreWhitespace = True
        Using xmlReader = System.Xml.XmlReader.Create(uriReportSource.Uri, settings)
            Dim xmlSerializer = New Telerik.Reporting.XmlSerialization.ReportXmlSerializer()
            Dim report = DirectCast(xmlSerializer.Deserialize(xmlReader), Telerik.Reporting.Report)
            Return report
        End Using
    End Function

    Public Function DeserializeReport(xmlReportSource As XmlReportSource) As Report
        Dim settings = New System.Xml.XmlReaderSettings()
        settings.IgnoreWhitespace = True
        Dim textReader = New System.IO.StringReader(xmlReportSource.Xml)
        Using xmlReader = System.Xml.XmlReader.Create(textReader, settings)
            Dim xmlSerializer = New Telerik.Reporting.XmlSerialization.ReportXmlSerializer()
            Dim report = DirectCast(xmlSerializer.Deserialize(xmlReader), Telerik.Reporting.Report)
            Return report
        End Using
    End Function

    Public Sub SetConnectionString(reportItemBase As ReportItemBase)
        If reportItemBase.Items.Count < 1 Then
            Return
        End If

        If TypeOf reportItemBase Is Report Then
            Dim report = DirectCast(reportItemBase, Report)

            If TypeOf report.DataSource Is SqlDataSource Then
                Dim sqlDataSource = DirectCast(report.DataSource, SqlDataSource)
                sqlDataSource.ConnectionString = connectionString
            End If
            For Each parameter As Telerik.Reporting.ReportParameter In report.ReportParameters
                If TypeOf parameter.AvailableValues.DataSource Is SqlDataSource Then
                    Dim sqlDataSource = DirectCast(parameter.AvailableValues.DataSource, SqlDataSource)
                    sqlDataSource.ConnectionString = connectionString
                End If
            Next
        End If

        For Each item As Telerik.Reporting.ReportItemBase In reportItemBase.Items
            'recursively set the connection string to the items from the Items collection
            SetConnectionString(item)

            'set the drillthrough report connection strings
            Dim drillThroughAction = TryCast(item.Action, NavigateToReportAction)
            If drillThroughAction IsNot Nothing Then
                Dim updatedReportInstance = Me.UpdateReportSource(drillThroughAction.ReportSource)
                drillThroughAction.ReportSource = updatedReportInstance
            End If

            If TypeOf item Is SubReport Then
                Dim subReport = DirectCast(item, SubReport)
                subReport.ReportSource = Me.UpdateReportSource(subReport.ReportSource)
                Continue For
            End If

            'Covers all data items(Crosstab, Table, List, Graph, Map and Chart)
            If TypeOf item Is DataItem Then
                Dim dataItem = DirectCast(item, DataItem)
                If TypeOf dataItem.DataSource Is SqlDataSource Then
                    Dim sqlDataSource = DirectCast(dataItem.DataSource, SqlDataSource)
                    sqlDataSource.ConnectionString = connectionString
                    Continue For
                End If

            End If
        Next
    End Sub
End Class

In order to set the connection strings at run-time you have to instantiate the ReportConnectionStringManager with the new connection string you want to use. Then you have to invoke the UpdateReportSource with a ReportSource. This method returns an updated ReportSource with the new connection string. The updated ReportSource then can be used for ReportViewer.ReportSource or for ReportProcessor. For example, check out the following sample:

var connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI";
var connectionStringHandler = new ReportConnectionStringManager(connectionString);
var sourceReportSource = new UriReportSource { Uri = "Employee Sales Summary.trdx" };
//var sourceReportSource = new InstanceReportSource { ReportDocument = new EmployeeSalesSummary() };
var reportSource = connectionStringHandler.UpdateReportSource(sourceReportSource);
this.reportViewer1.ReportSource = reportSource;
this.reportViewer1.RefreshReport();

Dim connectionString = "Data Source=(local)\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI"
Dim connectionStringHandler = New ReportConnectionStringManager(connectionString)
Dim sourceReportSource = New UriReportSource() With { _
    .Uri = "Employee Sales Summary.trdx" _
}

Dim reportSource = connectionStringHandler.UpdateReportSource(sourceReportSource)
Me.reportViewer1.ReportSource = reportSource
Me.reportViewer1.RefreshReport()

If you intend to use the above code for modifying reports displayed by an HTML5 Viewer, the code must be placed in the Resolve method of a custom resolver used by the Reporting REST service.

Note
The SetConnectionString method must be updated to skip the check for Drill-trough report actions (NavigateToReportAction). In case of a navigation to other report, the string description of the target report will be received by the resolver's Resolve method and it will go through the same modifications.