Set database connection at runtime

Posted by: novotx on 3 August 2017, 3:19 pm EST

  • Posted 3 August 2017, 3:19 pm EST

    I am looking for some guidance on setting the SQL Data Connection at runtime for the web viewer control. I have reviewed the forum and found differing opinions on either setting the ConnectString property on the ReportDefinition object using the ConfigurationProvider versus setting it on the ReportRuntime. Either way, it doesn't appear to be working on my end? Can someone verify that it is indeed working in version 1.6.2122.0 and not something I am doing in my code (see file attachment for my code).
    2012/01/SampleCode.txt
  • Replied 3 August 2017, 3:19 pm EST

    Hello,

    From the code that you posted, it seems that you have stored the connectionstring for the report in the web.config of your web application with name MyTestConnectionString. And you wish to pull the same thereof. If so then mere code as below should be working for you

    Dim _report As New ReportDefinition(New FileInfo(Server.MapPath("~/Report1.rdlx")))
    For i As Integer = 0 To _report.Report.DataSources.Count - 1
        _report.Report.DataSources(i).ConnectionProperties.ConnectString = ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString
    Next
    Dim _runtime As New ReportRuntime(_report)
    Me.WebReportViewer1.SetReport(_runtime)

    Please let me know if I have misunderstood your requirement.

    Regards,
    Prantik
  • Replied 3 August 2017, 3:19 pm EST

    Thanks Prantik,

    I think I have resolved this with the understanding that you must somewhat match the method of passing credentials. In other words, on my dev machine and report file I was connecting to SQL using Integated Security, this would break once posted to the production server which passes the SQL username/password in the connection string. If I change the report to use the same method of username and password, it works.
  • Replied 3 August 2017, 3:19 pm EST

    I think if you need to switch between windows authentication in a dev environment to a user/password environment in a production environment, or vice versa, you need to set the IntegratedSecurity option on the datasource connection property. I did this by loading the connectionstring from my web.config into a SqlConnectionStringbuilder and using that to check the integrated security. See below for example (code is C#).



    System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();

    connectionStringBuilder.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

    reportDefinition.Report.DataSources[0].ConnectionProperties.ConnectString = connectionStringBuilder.ConnectionString;

    reportDefinition.Report.DataSources[0].ConnectionProperties.IntegratedSecurity = connectionStringBuilder.IntegratedSecurity;
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels