While going through my last project I learn a lot about custom assembly in reporting services. My current article is very important if you are interested to use config file for custom assembly to connect database. Just for the information, config file provide us facility to use keys in your project rather hardcoded values.
In my last reporting services project it was required to build a generic custom assembly which should use a config file rather hardcoded values. So that we could use this custom assembly on our numerous customer computers without hard coding their database information each time we are going to deploy this assembly.
The major problem to use config file with custom assembly is their "class library" project type. You are all aware that class library assembly is usually called by any other assembly through window base / web base project so whenever you will add config file into the same folder it will not work because to make this custom assembly run under reporting services you have to copy this custom assembly under the "ReportServer/bin" folder in SQL Server installation whether reporting services project location is different so it does not work.
Another way is using a text file as a config file to extract key values but another problem to use text file from custom assembly in reporting services is to set permission first for that text file in your custom assembly which needs Physical Path of that file (again hard coded value), so this trick will not work as well.
Using the code
Steps to solve the problem
Now, there is a very simple solution which allows you to use key values into your custom assembly while you are using reporting services. The trick is to use reporting services "web.config" file to set key values and then use them in our custom assembly.
Steps to add key values in web.config file
1. I assume that you have set your custom assembly permission etc and it is working fine with hard coded values. If you have not done these steps then please go through my another article Creating and Deploying Reporting services using custom assembly (step by step)
2. Find the reporting services installation folder usually C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer and open web.config file.
3. Add key and values under <appSettings> ---here---</appSettings>
<add key="ConnectionString" value="Data source=192.168.0.1;initial catalog=PMTest;User Id=sa;Password=sa" />
4. Just for the safe side and to avoid any error, do not forget to add <appSetings> at the end of web.config file before </configuration> tag.
Steps to extract key values from web.config file in your Custom Assembly
1. Use System.Configuration.ConfigurationSettings.AppSettings("ConnectionString") to extract connection string.
Festival Business Solutions
Points of Interest
Pleas do not forget to write comments that how can I imporve this article. There may have few things which i have overlook and if you will write comment, i will update my article accordingly and it will be helpful for other readers. Thanks.
Ali Faraz is a software developer in Festival Business Solutions and having 8 years of experience using .net, sql server etc.