Friday, February 24, 2012

Dynamic Connection string (config file)

Hi,

I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.

The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.

1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.

If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.

2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?

Sorry for the long post, but required to explain the scenario.

Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.

In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).

General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||

dynamic variable sql database Connection String

this is work easy

http://www.codeproject.com/useritems/PersistConnectionStrings.asp

No comments:

Post a Comment