Solved

Connectionstring in app.config

Posted on 2009-04-13
38
4,554 Views
Last Modified: 2013-12-17
Hi Experts,
I have created a Outlook Shared Add-in.I am creating coneection to my sql database.
I am able to create connection but how to store it in a config file and access the connectionstring from there?
Currently I am trying below code but it is throwing an exception.
<configuration>	
  <appSettings>
     <add key="ConnectionString" value="Data Source=192.168.40.46;Initial Catalog=Spot;Persist Security Info=True;User ID=sa;Password=sqlad;Min Pool Size=05;Max Pool Size=160;Connection Timeout = 250" />
</appSettings>
</configuration>
 
 string connectionstr = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                SqlConnection PacktSQLConnection = new SqlConnection(connectionstr);

Open in new window

0
Comment
Question by:johny_bravo1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 12
  • 4
  • +1
38 Comments
 
LVL 10

Expert Comment

by:MrClyfar
ID: 24129025
Hi there.
Try this instead.
Cheers.
Jas.

Imports System.Configuration
 
Module Module1
 
    Sub Main()
        Console.WriteLine("Connection string = {0}", ConfigurationManager.ConnectionStrings("MyConnection"))
    End Sub
 
End Module
 
App Config File
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.diagnostics>
        <sources>
            <!-- This section defines the logging configuration for My.Application.Log -->
            <source name="DefaultSource" switchName="DefaultSwitch">
                <listeners>
                    <add name="FileLog"/>
                    <!-- Uncomment the below section to write to the Application Event Log -->
                    <!--<add name="EventLog"/>-->
                </listeners>
            </source>
        </sources>
        <switches>
            <add name="DefaultSwitch" value="Information" />
        </switches>
        <sharedListeners>
            <add name="FileLog"
                 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" 
                 initializeData="FileLogWriter"/>
            <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
            <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
        </sharedListeners>
    </system.diagnostics>
 
  <connectionStrings>
    <add name="MyConnection" connectionString="this is a test"/>
  </connectionStrings>
</configuration>

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129030
In the line
string connectionstr = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;

that ConnectionString1 is ConnectionString.

And the Exception I am getting is
"Object Reference Not set to an instance of the object"
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129052
I  tried changing this,
string connectionstr = ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString();

But still getting same exception.
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129098
string connectionstr = ConfigurationManager.ConnectionString.["MyConnection"].ConnectionString;
<configuration>
  <connectionStrings>
    <add name="MyConnection" connectionString="this is a test"/>
  </connectionStrings>
</configuration>

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129123
Thanks gauthampj, it works in normal application but in shared add-in where all my code is in Connect.cs file
I am still getting the same exception.What could be the reason?
0
 
LVL 2

Expert Comment

by:T0ni
ID: 24129125
Hi johny_bravo1,
I don't think that is the best way to go with storing connection strings, a safer way would be to hard code the connection string.

Here is the code to do it, just create a static class and call it whenever you need the connection string:
    static class Connection
    {
        public static SqlConnection conn()
        {
            return new SqlConnection("Data Source=192.168.40.46;Initial Catalog=Spot;Persist Security Info=True;User ID=sa;Password=sqlad;Min Pool Size=05;Max Pool Size=160;Connection Timeout = 250");
        }
    }
 
//To get the connection string from another class or form simply call it using the following line of code.
 
SqlConnection conn = Connection.conn();

Open in new window

0
 
LVL 10

Expert Comment

by:MrClyfar
ID: 24129137
Hi there.
I just used the name 'MyConnectionString' in the code as an example, but to copy your code, you should rename this:
<configuration>
     <connectionStrings>
            <add name="MyConnection" connectionString="this is a test"/>
     </connectionStrings>
</configuration>
to this:

<configuration>
     <connectionStrings>
            <add name="ConnectionString" connectionString="Data Source=192.168.40.46;Initial Catalog=Spot;Persist Security Info=True;User ID=sa;Password=sqlad;Min Pool Size=05;Max Pool Size=160;Connection Timeout = 250"/>
     </connectionStrings>
</configuration>  
As per gauthampj's advice.
Cheers
Jas.
0
 
LVL 10

Expert Comment

by:MrClyfar
ID: 24129148
Hi.
In response to t0ni's comment, the best way to store the connection string is to encrypt it in the config file. Here's a link that explains this:
http://www.4guysfromrolla.com/articles/021506-1.aspx
Having the connection string in the code is suicide unless the code is obfuscated, since using .NET Reflector would allow anyone to view the code for the app and get the connection string details.
Also storing the connection string in the app.config file unencrypted is risky, since anyone can open the file in the Notepad and have a look.
Cheers.
Jas.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129152
Thanks MrClyfar,
it's working in other applications but not here.
0
 
LVL 2

Expert Comment

by:T0ni
ID: 24129168
That's right MrClyfar, I didn't mean to say it's the best way to store sensitive data, but it's by far safer than storing in config file unencrypted.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129194
can u try this
<configuration>	
  <appSettings>
     <add key="ConnectionString" value="Data Source=192.168.40.46;Initial Catalog=Spot;Persist Security Info=True;User ID=sa;Password=sqlad;Min Pool Size=05;Max Pool Size=160;Connection Timeout = 250" />
</appSettings>
</configuration>
 
string connectionstr = ConfigurationSettings.AppSettings["ConnectionString1"];

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129200
I just need to clear this again that I am developing a Shared add-in and I am still not able to read values any value from app.config.
It's working getting it through class but I need to get it from config file.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129204
Thanks gauthampj, already tried with it.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129224
how are u testing the add in ?  are u installing it with a installer package ? if the app.config file is copied to the insatall directore i think it shud work no matter what
0
 
LVL 10

Expert Comment

by:MrClyfar
ID: 24129256
Hi there.
The app.config way of reading the config should work, if it;s not, then I also think that the app.config is in the wrong folder and your code cannot find it.
One other way to get around this is to put your connection string in the machine.config file. I have to go now, but here is a link that explains how to use machine.config:
http://msdn.microsoft.com/en-us/library/ms229697.aspx

http://forums.asp.net/p/1117776/1736929.aspx

Cheers.
Jas.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129430
Yes te app.config was not installed on the machine when I set up installer package.
Now I added app.config to setup folder.
But still the error is there?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129475
name shud be urdllname.config not app.config
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129529
You mean where my dll is copied there shud b urdllname.config file instead of app.config
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129555
s..
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129594
I renamed app.config as urdllname and copied it physically to installation folder but it is still throwing exception
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24129634
:-( wierd..what is the name of the ur dll/project  and that of the  app config file ?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129663
it is 'TestAddIn" and now as u said config is TestAddin.config
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129754
any  other idea?
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 24129982
no idea what going on. it seems that u wont be able to read from app config from an add in. u can use this class which can be used instead of the configuration amanger to read values from a xml config file.
public class ConfigurationSettings
{
 
  public static NameValueCollection AppSettings;
 
  public static void LoadConfig()
  { 
    try
    {
 
        string AppPath = Path.GetDirectoryName( Assembly.GetExecutingAssembly().GetName().CodeBase);
        string ConfigFile = Path.Combine(AppPath,"App.config");
 
        if(File.Exists(ConfigFile) == false)
        {
          System.Windows.Forms.MessageBox.Show("Config file does not exist");
          Application.Exit(); 
          return;
        }
				 
        XmlDocument oXml = new XmlDocument();
 
        oXml.Load(ConfigFile);
				
        XmlNodeList oList =  oXml.GetElementsByTagName("appSettings"); 
 
        AppSettings = new NameValueCollection();
 
        foreach(XmlNode oNode in oList)
        {
           foreach(XmlNode oKey in oNode.ChildNodes)
           {
              AppSettings.Add(oKey.Attributes["key"].Value, oKey.Attributes["value"].Value);
           }
        }
 
        System.Windows.Forms.MessageBox.Show(ConfigurationSettings.AppSettings["Item1"]);  
 
    }
    catch (Exception) { throw; }
  }
 
}

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24137227
what is the best way of putting connectionstring when I will install that add-in on client machine?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24137256
u can store as an encrypted string in the app.config file or in a registry value; if u are going to use app.config file then u will have to use the class i posted above to read the values
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24137602
I have created a new class named ConfigurationSettings.
I have made little modification to it.
as my app.config looks like
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
      <connectionStrings>
            <add name="MyConnection" connectionString="coneection to database"/>

      </connectionStrings>
</configuration>
Now what next?
using System;
using System.Collections.Generic;
using System.Text;
 
namespace TestAddin
{
    public class ConfigurationSettings
    {
        public static NameValueCollection AppSettings;
 
        public static void LoadConfig()
        {
            try
            {
 
                string AppPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
                string ConfigFile = Path.Combine(AppPath, "App.config");
 
                if (File.Exists(ConfigFile) == false)
                {
                    System.Windows.Forms.MessageBox.Show("Config file does not exist");
                    Application.Exit();
                    return;
                }
 
                XmlDocument oXml = new XmlDocument();
 
                oXml.Load(ConfigFile);
 
                XmlNodeList oList = oXml.GetElementsByTagName("connectionStrings");
 
                AppSettings = new NameValueCollection();
 
                foreach (XmlNode oNode in oList)
                {
                    foreach (XmlNode oKey in oNode.ChildNodes)
                    {
                        AppSettings.Add(oKey.Attributes["name"].Value, oKey.Attributes["connectionString"].Value);
                    }
                }
 
                System.Windows.Forms.MessageBox.Show(ConfigurationSettings.AppSettings["Item1"]);
 
            }
            catch (Exception) { throw; }
        }
 
 
    }
}

Open in new window

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24137652
u can access the information in app.config as ConfigurationSettings.AppSettings ["MyConnection"]; the file name of the app config file shud be "urdllName.dll.App.config"
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24137729
Ok according to you I just need to implement that class exactly as it is.Create a config file with my dllname.dll.App.Config
and access the Appsetting in code.
That's it.
0
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 500 total points
ID: 24137788
s use this to access the conection string -> TestAddin.ConfigurationSettings.AppSettings["MyConnection"]
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24137803
I will work on it tomorrow and inform you about it.
Let me know if I need to know something to get it work.
Thanks.Have a nice day :)
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24146174
In ur class code I am getting error for 'Path' and 'Assembly'
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24147664
what is the error ?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24147752
'Path' / 'Assembly' does not exist in current context
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24147798
ok that error is resolved now.let me try after installing it.
The config file should be named as mydllname.dll.App.config.Right?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24147951
I chaged my app.config file to TestAddin.dll.App.config and set its properties Build Action=Compile,
Copy To Output Directory=Always.
Now it is throwing error for it.
A namespace doesnot directly contain members such as fields or methods.
:(
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24148350
build action shud be content
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24155457
Ya tha's done now but still it's not working.
I don't understand what I am doing wrong?but still I m getting the same exception 'Object reference not set for instance of the object'.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question