Dynamic connection string

18 posts, 0 answers
  1. James Molohon
    James Molohon avatar
    6 posts
    Member since:
    Jan 2009

    Posted 18 Jan 2011 Link to this post

    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.

  2. Richard Guo
    Richard Guo avatar
    17 posts
    Member since:
    Nov 2009

    Posted 24 Feb 2011 Link to this post

    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;
            }
  3. DevCraft banner
  4. Scott
    Scott avatar
    25 posts
    Member since:
    Nov 2010

    Posted 25 Mar 2011 Link to this post

    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
  5. Scott
    Scott avatar
    25 posts
    Member since:
    Nov 2010

    Posted 25 Mar 2011 Link to this post

    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
  6. Scott
    Scott avatar
    25 posts
    Member since:
    Nov 2010

    Posted 28 Mar 2011 Link to this post

    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?

  7. James Molohon
    James Molohon avatar
    6 posts
    Member since:
    Jan 2009

    Posted 29 Mar 2011 Link to this post

    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
  8. Scott
    Scott avatar
    25 posts
    Member since:
    Nov 2010

    Posted 29 Mar 2011 Link to this post

    Thanks.

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

    -Scott
  9. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 30 Mar 2011 Link to this post

    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
  10. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 06 Apr 2011 Link to this post

    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)
        {
  11. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 06 Apr 2011 Link to this post

    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.
  12. Scott
    Scott avatar
    25 posts
    Member since:
    Nov 2010

    Posted 14 Apr 2011 Link to this post

    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
  13. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 14 Apr 2011 Link to this post

    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
  14. William Lim
    William Lim avatar
    6 posts
    Member since:
    Dec 2008

    Posted 20 Oct 2011 Link to this post

    But does this work with  AvailableValues.DataSource for report parameters. We are also struggling in this issue. 
  15. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 21 Oct 2011 Link to this post

    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;
    }
  16. Ben Jones
    Ben Jones avatar
    12 posts
    Member since:
    Jun 2009

    Posted 22 Nov 2011 Link to this post

    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
  17. Squall
    Squall avatar
    174 posts
    Member since:
    Feb 2011

    Posted 23 Nov 2011 Link to this post

    Hi Ben,
    You can change the ObjectDataSource properties in the same manner. Have you tried?
    SN
  18. Grzegorz
    Grzegorz avatar
    6 posts
    Member since:
    Jan 2013

    Posted 03 Apr 2013 Link to this post

    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

  19. Hadib Ahmabi
    Hadib Ahmabi avatar
    181 posts
    Member since:
    Nov 2011

    Posted 05 Apr 2013 Link to this post

    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. 
Back to Top
DevCraft banner