none
set connection string in linq dbml file dynamically for app.config file RRS feed

  • Question

  • In a C# 2010 console application I am having the same problem that is listed in the following url:
    http://ddkonline.blogspot.com/2008/02/set-connection-string-in-linq-dbml-file.html.

    However I am having the problem wih an app.config file.

    My question is in the article, that refers to the "InvestmentManagementConnectionString", how should I setup the correlation between this connection to the app.config file?

    Can you show me me how the app.config file would be setup to reference the "InvestmentManagementConnectionString"?

    • Moved by CoolDadTx Thursday, November 8, 2012 3:28 PM Not IDE related (From:Visual C# IDE)
    Thursday, November 8, 2012 6:59 AM

Answers

  • Hi Jazz_dog,

    Why do you want to connect another database with one dbml file?

    Are the schema of all the tables the same?

    If they are not same, you cannot do this.

    If they are same, please check this:

    Change the name of connection string “esample.Properties.Settings.devtestConnectionString” in App.config to the following:

      <connectionStrings>  
        <add name="newdevtestConnectionString"
          connectionString="Data Source=dev;Initial Catalog=devtest;Integrated Security=True"
          providerName="System.Data.SqlClient" />    
      </connectionStrings>

    Then, try to connect the database:

    class Program
    {
        static void Main(String[] args)
        {
            using (var context = new esampleDataContext(ConfigurationManager.ConnectionStrings["newdevtestConnectionString"].ConnectionString)
            {
                //...
            }
        }
    }

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by jazz_dog Monday, November 19, 2012 3:48 AM
    Thursday, November 15, 2012 9:25 AM

All replies

  • Hi Jazz_dog,

    Welcome to the MSDN forum.

    First, add a connection string to App.config. Then, to create the corresponding datacontext based on new connection string, you just pass the connection string to dataconext constructor. For example:

    using (var context = new DataClasses1DataContext(ConfigurationManager.ConnectionStrings["newConnectionString"].ConnectionString)
    {
    }

    The String “newConnectionString” is the name of the connection string you added in App.config.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, November 9, 2012 3:20 AM
  •  I have a question about how to change my code so that it works the way you have your code setup.

    You have the following code:

    using (var context = new DataClasses1DataContext(ConfigurationManager.ConnectionStrings["newConnectionString"].ConnectionString)
    {

    I have the following:

    using System.Configuration;

    public esampleDataContext() :
     base(ConfigurationManager.ConnectionStrings["sample.Properties.Settings.DEVConnectionString"].ConnectionString,mappingSource)

    **Note:  base(global::sample.Properties.Settings.Default.DEVConnectionString, mappingSource) is the line of code that I replaced that was generated by the  .net framework

    I also have a reference to System.Configuration;

    Thus can you tell me how to change my code so that it would work like yours? I apparently need to do something with the base constructor.

    Saturday, November 10, 2012 11:17 PM
  • You can create separate class and using .NET xml classes read Connection string section and store those values in a Dictionary as a Property .

    In your application use this Dictionary object to set dynamic connection strings .


     public class AppConfigurationManager
        {
            private static string _UserFrom = null;
            private static Dictionary<string, string> _ConnectionStrings = null;
            public static string UserFrom
            {
                get { return _UserFrom; }
                set 
                { 
                    _UserFrom = value;
                    switch (value)
                    {
                        case "USA":
                            CreateConnectionStringsForUSA();
                            break;
                        case "UK":
                            CreateConnectionStringsForUK();
                            break;
                    }
                }
            }
            public static Dictionary<string, string> ConnectionStrings
            {
                get
                {
                    return _ConnectionStrings;
                }
            }
            private static void CreateConnectionStringsForUSA()
            {
                _ConnectionStrings = new Dictionary<string, string>();
                XmlDocument doc = new XmlDocument();
                
                doc.Load(HttpContext.Current.Server.MapPath("~/Config/USADev.config"));
                XmlNodeList elemList = doc.GetElementsByTagName("add");
                foreach (XmlNode e in elemList)
                {
                    string name = e.Attributes["name"].Value;
                    string connectionString = e.Attributes["connectionString"].Value;
                    _ConnectionStrings.Add(name, connectionString);
                }
            }
            private static void CreateConnectionStringsForUK()
            {
                _ConnectionStrings = new Dictionary<string, string>();
                XmlDocument doc = new XmlDocument();
                doc.Load(HttpContext.Current.Server.MapPath("~/Config/UKDev.config"));
                XmlNodeList elemList = doc.GetElementsByTagName("add");
                foreach (XmlNode e in elemList)
                {
                    string name = e.Attributes["name"].Value;
                    string connectionString = e.Attributes["connectionString"].Value;
                    _ConnectionStrings.Add(name, connectionString);
                }
            }
        }

    Let me know if problem persists .
    • Edited by Shyam Kr Monday, November 12, 2012 1:43 PM Added example code
    Monday, November 12, 2012 1:36 PM
  • Thank you for your answer!

    Since the code was generated by the *.dbml visual designer, isn't there some way to work with the desginer and not setup all the code you just displayed?

    Your code looks like it works, but isn't there a way to acommplish this taks with less code?

    Tuesday, November 13, 2012 3:28 AM
  • Thank you for your answer!

    Since the code was generated by the *.dbml visual designer, isn't there some way to work with the desginer and not setup all the code you just displayed?

    Your code looks like it works, but isn't there a way to acommplish this taks with less code?


    Yes , you can edit the code of dbml cs file . You can change the context creation code . When you look the code carefully , you will see at the top of source code there is code for Context creation . In the constructor you can pass the appropriate connection string directly as you wish .
    Tuesday, November 13, 2012 4:00 AM
  • Hi Jazz_dog,

    Your code seems like the constructor of context class. Could you please let me know what specific error you have?

    If you want to use dynamic connection string to connect different databse, you are not required to change something about constructor. You should be able to find several overload constructor, and it should have a constructor with a String parameter. When you initialize the datacontext, pass a connection string which is stored in App.config as a parameter to this constructor. The code I posted previously is the initialization of datacontext.

    If I misunderstood anything, please feel free to let me know. If you encounter any error, please let me know the specific error message and related code snippet.

    Good day!


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us


    Tuesday, November 13, 2012 8:51 AM
  • I am not getting an error message. The problem is the database that is being used is contained in the settings.settings file. The value in the app.config file is not being used.

    Here is the situation being more with clarification.

    In a C# 2008 desktop/console application, I created the connections to the database using linq to sql. Everything worked fine until I moved the
    code to point to a different database. Somehow the original database connections are being saved and the values from the app.config file
    are not being used.

    I tried to do what the following link: http://refat38.wordpress.com/2012/01/30/unpick-linq-to-sql-connection-string-from-application-settings/,
    said to do however, I do not know what the following statement means to do from this link:
    Now Right Click and open the Properties on your DAL or project containing your LINQ to SQL classes and remove the connection string “Application Setting” reference on the Settings tab.

    1. The following code is from the app.config file: <connectionStrings> <add name="esample.Properties.Settings.devtestConnectionString" connectionString="Data Source=dev;Initial Catalog=devtest;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> 2. The following is from the settings.settings file <?xml version='1.0' encoding='utf-8'?> <SettingsFile xmlns="http://schemas.microsoft.com/VisualStudio/2004/01/settings" CurrentProfile="(Default)" GeneratedClassNamespace="esample.Properties" GeneratedClassName="Settings"> <Profiles /> <Settings> <Setting Name="devtestConnectionString" Type="(Connection string)" Scope="Application"> <DesignTimeValue Profile="(Default)">&lt;?xml version="1.0" encoding="utf-16"?&gt; &lt;SerializableConnectionString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt; &lt;ConnectionString&gt;Data Source=dev;Initial Catalog=devtest;Integrated Security=True&lt;/ConnectionString&gt; &lt;ProviderName&gt;System.Data.SqlClient&lt;/ProviderName&gt; &lt;/SerializableConnectionString&gt;</DesignTimeValue> <Value Profile="(Default)">Data Source=dev;Initial Catalog=devtest;Integrated Security=True</Value> </Setting> </Settings> </SettingsFile> The following is from the settings.designer.cs file namespace esample.Properties { [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "9.0.0.0")] internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase { private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings()))); public static Settings Default { get { return defaultInstance; } } [global::System.Configuration.ApplicationScopedSettingAttribute()] [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] [global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)] [global::System.Configuration.DefaultSettingValueAttribute("Data Source=dev;Initial Catalog=devtest;Integrated Security=True")] public string devtestConnectionString { get { return ((string)(this["devtestConnectionString"])); } } } } The following is from the *.designer.cs namespace esample { using System.Data.Linq; using System.Data.Linq.Mapping; using System.Data; using System.Collections.Generic; using System.Reflection; using System.Linq; using System.Linq.Expressions; using System.ComponentModel; using System; using System.Configuration;

    [System.Data.Linq.Mapping.DatabaseAttribute(Name="devtest")] public partial class esampleDataContext : System.Data.Linq.DataContext { private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource(); public esampleDataContext() : base(global::esample.Properties.Settings.Default.devtestConnectionString, mappingSource) //base(ConfigurationManager.ConnectionStrings["esample.Properties.Settings.devtestConnectionString"].ConnectionString, mappingSource) { OnCreated(); } public esampleDataContext(string connection) : base(connection, mappingSource) { OnCreated(); }

    Can you show me code and/or tell me how to solve this problem so I can obtain the value from the app.config file?
    Wednesday, November 14, 2012 4:37 AM
  • Hi Jazz_dog,

    Why do you want to connect another database with one dbml file?

    Are the schema of all the tables the same?

    If they are not same, you cannot do this.

    If they are same, please check this:

    Change the name of connection string “esample.Properties.Settings.devtestConnectionString” in App.config to the following:

      <connectionStrings>  
        <add name="newdevtestConnectionString"
          connectionString="Data Source=dev;Initial Catalog=devtest;Integrated Security=True"
          providerName="System.Data.SqlClient" />    
      </connectionStrings>

    Then, try to connect the database:

    class Program
    {
        static void Main(String[] args)
        {
            using (var context = new esampleDataContext(ConfigurationManager.ConnectionStrings["newdevtestConnectionString"].ConnectionString)
            {
                //...
            }
        }
    }

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by jazz_dog Monday, November 19, 2012 3:48 AM
    Thursday, November 15, 2012 9:25 AM