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

Dynamic connection string

17 Answers 751 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
James Molohon
Top achievements
Rank 2
James Molohon asked on 18 Jan 2011, 05:41 PM

Hello,

I have a Silverlight report viewer to render the report from a class library within the same solution as the Silverlight project, following your best practices guide for creating reports.

In general I need a report to connect to multiple database(s) based on user selected options in a Silverlight application.  I also have a Report Parameter which runs a SQL query on the database and returns a list of data that will be used to filter the report data. I’m having difficulty dynamically changing the connection string to the database, the report parameter still points to the initial database set during design time. It seems the report parameter can be accessed only in need_datasource event, but the report parameter needs this connection string to display the list of data that will be used to filter the report data.

 1)      Is there a way we could set reportParameter  AvailableValues.DataSource in the need_datasource event or dynamically.

2)      Is there a way to pass this connection string  into the constructor in order to set the reportParameter in the constructor. Setting the value here seems to work but I cannot pass a value into the constructor.

3)      Is there different design approach that we should be using?

Thanks for your help in advance.

Thanks,
James.

17 Answers, 1 is accepted

Sort by
0
Richard Guo
Top achievements
Rank 1
answered on 25 Feb 2011, 02:43 AM
What I did is to create a base class inheriting the Report class and use this base class to replace the default base class (Report) for each report. This class contains a public method SetConnectionString which can be called from the show report code.

        private List<SqlDataSource> _sqlDataSources;
        private List<SqlDataSource> SqlDataSources
        {
            get
            {
                if (_sqlDataSources == null)
                    BuildSqlDataSourceList();
                return _sqlDataSources;
            }
        }
        private void BuildSqlDataSourceList()
        {
            _sqlDataSources = new List<SqlDataSource>();
            var fields = GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic);
            foreach (var field in fields)
            {
                if (field.FieldType == typeof(SqlDataSource))
                    _sqlDataSources.Add((SqlDataSource)field.GetValue(this));
            }
        }
        public void SetConnectionString(string connectionString)
        {
            foreach (var dataSource in SqlDataSources)
                dataSource.ConnectionString = connectionString;
        }
0
Scott
Top achievements
Rank 1
answered on 26 Mar 2011, 01:39 AM
Thanks for this, but is this the recommended way to dynamically set the connection string during runtime?   This code basically modifies private members to get the job done.  If this is the only way, that's fine, but I just want to be sure.

I need to be able to set the connection string (server and database name) just before the report is run.

What does Telerik recommend?

Thanks,

-Scott
0
Scott
Top achievements
Rank 1
answered on 26 Mar 2011, 02:18 AM
I should also point out that I am using Silverlight and using the above code would be problematic from the client.  Any help is greatly appreciated.

-Scott
0
Scott
Top achievements
Rank 1
answered on 28 Mar 2011, 11:25 PM
This is turned into a major issue for us.  We need to be able to dynamically change the connection string in our Silverlight apps.  For example, each of our customers has multiple databases.  Depending on the job they are working on, we want to report based on the correct database.  We will only know this at runtime and need to be able to specify the server and the database to run the report.  There is no way we can have a connection string for every customer and database in our web.config file.  How can I accomplish this?

I posted this last Friday.   Should I just send in a support ticked, or start a new thread?

0
James Molohon
Top achievements
Rank 2
answered on 29 Mar 2011, 02:33 PM
Scott, 
I'd recommend opening a new support ticket with Telerik.  We have a work around in place, we're going to have to revisit this later.
J
0
Scott
Top achievements
Rank 1
answered on 29 Mar 2011, 07:02 PM
Thanks.

I submitted a new ticket today.  I'll post back here with the solution...

-Scott
0
Peter
Telerik team
answered on 30 Mar 2011, 06:28 PM
Hi Scott,

As you probably know, there is no Reporting object on the Silverlight client (in the works for subsequent versions) and the reports reside on the server.  However you can change the SqlDataSouce.ConnectionString with a Report Parameter:

  • add a Report Parameter with Visibility set to false
  • configure your SqlDataSource component as usual
  • add the following User Function
public static SqlDataSource ChangeConnectionString(object reportItem, string parameterConnectionString)
{
    var report = (reportItem as Telerik.Reporting.Processing.Report);
    var dataSource = (Telerik.Reporting.SqlDataSource)report.DataSource;
    dataSource.ConnectionString = parameterConnectionString;
    return dataSource;
}


Greetings,
Peter
the Telerik team
0
danparker276
Top achievements
Rank 2
answered on 06 Apr 2011, 09:26 PM
I'm a little confused about how to bind to the DataSource property.
If I go to the Telerik.Reporting.SqlDataSource I'm using.  Then look under ConnectionString, it doesn't let me put in an expression like
=ChangeConnectionString(ReportItem, Parameters.Parameter1.Value)
Just that wizard pops up to create a string value.

I put the public static SqlDataSource ChangeConnectionString
in my MyReport.cs file  is this correct?
public partial class MyReport: Telerik.Reporting.Report
{
    public MyReport()
    {
        //
        // Required for telerik Reporting designer support
        //
        InitializeComponent();
        //
        // TODO: Add any constructor code after InitializeComponent call
        //
    }
    public static SqlDataSource ChangeConnectionString(object reportItem, int zoneNo)
    {
0
danparker276
Top achievements
Rank 2
answered on 06 Apr 2011, 09:50 PM
Ok, I got it.  I have to go to the report designer on properites and open up 'Bindings'  then add a property path of DataSource.
For the expression then put "= MyReports.MyReport.ChangeConnectionString(ReportItem,2)" 

This is working for me thanks.
0
Scott
Top achievements
Rank 1
answered on 14 Apr 2011, 06:41 AM
I got a little side-tracked on another project and am just now coming back to this.  I just created a quick test report using  your suggestions and it looks like everything is working perfectly.  One question, when binding to the user function, the returned value is staying on the server and not being sent back to the client, correct?  I just want to be sure I'm not passing a connection string over the wire.

Thanks for coming up with a solution for us.

-Scott
0
danparker276
Top achievements
Rank 2
answered on 14 Apr 2011, 07:08 AM
Yeah, I don't pass any information on the client.  I have a webservice that calls the report dll.  Depending on what's passed to the dll, the dll will take the connection string from the web.config from the webservice and construct a sql query in the ChangeConnectionString function
0
William Lim
Top achievements
Rank 1
answered on 21 Oct 2011, 03:50 AM
But does this work with  AvailableValues.DataSource for report parameters. We are also struggling in this issue. 
0
danparker276
Top achievements
Rank 2
answered on 21 Oct 2011, 06:29 PM
I just create the connection string with a string value.  I have different connection strings saved in my main database table.  I'm not sure if this helps you at all.

public static SqlDataSource ChangeConnectionString(object reportItem, int zoneNo, int reportType)
{           
    var report = (reportItem as Telerik.Reporting.Processing.Report);
    var dataSource = (Telerik.Reporting.SqlDataSource)report.DataSource;
    dataSource.ConnectionString = DBCalls.getConnectionStr(zoneNo, 3);  // This is my personal function to return a string value for a database connection string
 
 
    return dataSource;
}
0
Ben Jones
Top achievements
Rank 1
answered on 22 Nov 2011, 10:04 AM
Hi Dan,

Is this approach possible with an ObectDataSource or would we have to take another angle if we needed to achieve the same without using a SqlDataSource?

Kind regards

Ben
0
Squall
Top achievements
Rank 1
answered on 23 Nov 2011, 03:06 PM
Hi Ben,
You can change the ObjectDataSource properties in the same manner. Have you tried?
SN
0
Grzegorz
Top achievements
Rank 1
answered on 03 Apr 2013, 11:30 AM
Hi.

I have the following problem, the post code grade works for datasource set for the report, but not for the datasource parameters, can I change the data connections for parameters, or maybe preview in SL require a refresh.

Gratings

Gregor

0
Hadib Ahmabi
Top achievements
Rank 1
answered on 05 Apr 2013, 11:42 AM
I believe you can. You just need to apply the same scheme to the DataSource that's responsible for the parameters. There shouldn't be no difference. 
Tags
General Discussions
Asked by
James Molohon
Top achievements
Rank 2
Answers by
Richard Guo
Top achievements
Rank 1
Scott
Top achievements
Rank 1
James Molohon
Top achievements
Rank 2
Peter
Telerik team
danparker276
Top achievements
Rank 2
William Lim
Top achievements
Rank 1
Ben Jones
Top achievements
Rank 1
Squall
Top achievements
Rank 1
Grzegorz
Top achievements
Rank 1
Hadib Ahmabi
Top achievements
Rank 1
Share this question
or