Use the connectionString from your app's config file

7 posts, 0 answers
  1. Telerik Admin
    Telerik Admin avatar
    1505 posts
    Member since:
    Oct 2004

    Posted 03 Sep 2008 Link to this post

    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.

  2. Matthew Hile
    Matthew Hile avatar
    23 posts
    Member since:
    Aug 2008

    Posted 05 Feb 2009 Link to this post

    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

  3. Steve
    Admin
    Steve avatar
    10942 posts
    Member since:
    May 2014

    Posted 05 Feb 2009 Link to this post

    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


    Check out Telerik Trainer, the state of the art learning tool for Telerik products.

  4. Stephan
    Stephan avatar
    71 posts
    Member since:
    Jul 2007

    Posted 10 Mar 2009 Link to this post

    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.

    1 public ObservationReport(string strConnectionString) 
    2         { 
    3             InitializeComponent(); 
    4  
    5             this.observationReportDataSetTableAdapter1.Connection.ConnectionString = strConnectionString; 
    6  
    7             try 
    8             { 
    9                 this.observationReportDataSetTableAdapter1.Fill(this.observationReportDataSet.ObservationReportDataSetTable); 
    10             } 
    11             catch (System.Exception ex) 
    12             { 
    13                 System.Diagnostics.Debug.WriteLine(ex.Message); 
    14             } 
    15         } 

  5. Jason
    Jason avatar
    6 posts
    Member since:
    Jun 2009

    Posted 09 Jun 2009 Link to this post

    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

     

     

     

     





  6. Joan
    Joan avatar
    44 posts
    Member since:
    Dec 2011

    Posted 07 Nov 2013 Link to this post

    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: 

    <connectionStrings configSource="Connections.config">

    Connections.config:
    <?xml version="1.0"?>
    <connectionStrings>
      <add name="<alias name>" connectionString="<connection string>"  providerName="System.Data.SqlClient" />
    </connectionStrings>

  7. Symax
    Symax avatar
    3 posts
    Member since:
    Apr 2007

    Posted 08 Nov 2013 Link to this post

    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.

Back to Top