Use the connectionString from your app's config file

Requirements
Reporting version	2.5+
.NET version	2.0+
Visual Studio version	VS2005
programming language	C#
To convert code	Telerik online converter

 
HOW TO

Use the database connection from your app's config file (both win and web), rather than always having to code/input the connection string within every report.

PROJECT DESCRIPTION

There are two approaches to achieve the desired effect:

• Use the Data Source Wizard to setup a typed DataSet as DataSource for the report. This would allow you to use the Reporting Designer and all goodies that come with it. Then override the ConnectionString provided by the DataSet with the one specified in your config file:
  ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["SampleDB"]; 

      if ((connSettings != null) && (connSettings.ConnectionString != null)) 

          { 

            this.myDataSetTableAdapter1.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["SampleDB"].ConnectionString; 

          } 

            this.myDataSetTableAdapter1.Fill(this.myDataSet.MyDataSetTable);
• Setup the DataSource entirely in code, which would mean the data would be still available, but there would be no design time support:
  SqlCommand sqlSelectCommand1 = new SqlCommand(); 

  SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(); 

  SqlConnection sqlConnection1 = new SqlConnection(); 

  ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["SampleDB"]; 

     if ((connSettings != null) && (connSettings.ConnectionString != null)) 

       { 

         sqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings["SampleDB"].ConnectionString; 

       } 

  sqlSelectCommand1.CommandText = "SELECT TOP 50  AddressID, AddressLine1, City, PostalCode, ModifiedDate FROM Person.Address"; 

  sqlSelectCommand1.Connection = sqlConnection1; 

  sqlDataAdapter1.SelectCommand = sqlSelectCommand1; 

  this.DataSource = sqlDataAdapter1;

You can find attached fully working Win and Web applications that show both approaches at hand.

As I was working with this process for Windows Forms I came across a few gotchas which took me a while to figure out.

1. The first problem was when I tried the code in the example my call to "ConfigurationManager"  was undefined. Evidently, in a Windows forms application "System.Configuration" is not referenced by default (as it is in a web project). I had added "Imports System.Configuration"  to the code but even that did not inspire VS 2008 to include it as a reference. To get this to work I needed to explicitly add "System.Configuration" to the reference section of the reports project.  
2. The second problem was the location of the app.config file. In the Q3 2008 SP2 version Reporting will automatically create an app.config file with the connection string. (Very helpful, Thanks for the addition!) However, the connection section from that file needs to be moved to the program's main app.config file to be accessed. Pretty simple and obvious after you spend some time debugging but this may save someone the minutes needed for that exercise.
3. When I used the Telerik translator from C# to VB "(connSettings != null)" was changed to "(connSettings <> Nothing)." This gave me an error. But using "(Not IsNothing(connSettings)" worked giving me the desired result.

Hope this is of use,

Matthew

Hi Matthew,

Thank you for the additional explanations you've provided, so that other community members can benefit from them.

All the best,
Steve
the Telerik team

The first solution is the best in my opinion, but we deal with the situation that developers on their local machines have different connectionstrings. So in that case there has to be an agreement on how to name the connectionstring in de app.config. This name should not change while the string itself can be different. On deployment there will be no problem when the name of the connectionstring is the same.

The way we set the connectionstring for our report (we use typed datasets btw) is through the constructor of the report.

I have found the second method unfortunately to be unstable. It seems that most of the time I cannot call the connectionstring when storing it in my app config when I try to preview the report in VS. I keep getting the error "Exception has been thrown by the target of an invocation. Object reference not set to an instance of an object."  My app.config looks like this:

<?

 

xml version="1.0" encoding="utf-8" ?>

 

<

 

configuration>

 

<

 

configSections>

 

</

 

configSections>

 

<

 

connectionStrings>

 

<

 

add name="Adv" connectionString="Server=cmdivst004\Jason08;Integrated Security=true;Database=AdventureWorks" />

 

</

 

connectionStrings>

 

</

 

configuration>

The call from the report code-behind looks like this:

 

 

SqlConnection conn = new SqlConnection();

 

conn.ConnectionString =

ConfigurationManager.ConnectionStrings["Adv"].ConnectionString;

 

 

string selectCommand = "SELECT * FROM Production.Product";

 

 

SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, conn);

 

 

DataSet dataSet = new DataSet();

 

adapter.Fill(dataSet);

 

this.DataSource = dataSet;

 

 

this.DataMember = "Table";

This gives me the error.
However, when I call the db directly instead with the line below in the report codebehind, it works fine.

 

 

SqlConnection conn = new SqlConnection(@"Server=cmdivst004\Jason08;Integrated Security=true;Database=AdventureWorks");

Any ideas on how to fix this as i need to be able to store my connection info in one place if i create multiple reports.

Jason

 

 

 

 

A little bit of further help here:

If you move the connectionsStrings item out of the app.config file and into a separate file, the Publish or Deployment builder won't modify it and you only have a small file to change when migrating from DEV to TEST or Production.

app.config: 
Connections.config:

Also Nice Joan. Very handy of course.

An other thing which might be usefull is encryption of your connectionstring. Especially when you deploy a winforms project. Customers could open de config-file and read your connectionstring. 

Of course there are several options here, but I like this one. You can use this solution after deploying.