none
Create and get a connection string at runtime

    Question

  • Dear all,

    I’ve  a big issue, please help me to solve it.

    I developed a c#/wpf application with framework 4.0 and entity framework 4.

    What I need is not code connection string in app.config, but creating it at runtime from a xml file, for each application start.

    To do it I need to let empty connection strings in app.config and create at runtime a connection string.

    Could you please post me some code to :

    1)      Create a connection string at runtime

    2)      Get connection string at runtime

    I can’t get it work.

    I try to explain with more details.

    When I run application I don't know location of database. This location can change during the time and it's stored in a xml file.

    So what I need is:

    1) User starts application

    2) At application beginning xml configuration file is checked, db location is retrieved and a connection string is created, that's what I mean with create a connection string at runtime.

    If I put this in app.config

    <connectionStrings>
     <add name="MyWebDBEntities"
       connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SQLite;provider connection string=&quot;data source=C:\xxx\dbSqliteMyWeb.db3&quot;"
       providerName="System.Data.EntityClient"/>
     </connectionStrings>

    it works correctly. What I need now is that at application beginning connectionstring is replaced with connection read from xml file.

    So I acted in this way.

    I cleared app.config, so I have now

    <connectionStrings>
     </connectionStrings>

    and put this code at application starts (csFileName is db location taken from xml file)

     

     

    EntityConnectionStringBuilder cBuilder = null;
    
    var sqlConnString = new SqlConnectionStringBuilder();
    sqlConnString.DataSource = csFileName;
    //Crete connection string of entity framework entering file csdl,ssdl,msl, setting connection provider ( SqlClient )
    cBuilder = new EntityConnectionStringBuilder();
    cBuilder.Metadata = "res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl";
    cBuilder.Provider = "System.Data.SQLite";
    cBuilder.ProviderConnectionString = sqlConnString.ConnectionString;
    cBuilder.Name = "MyWebDBEntities";

    When in the code I try to access to my db context I write

    MyWebDBEntities ctx = new MyWebDBEntities();
    When I had connection fixed in the app.config it worked, now that I tried to have it dynamic I have this exception
    The connection specified is not found in the configuration, or it's not for use with provider EntityClient or it's not valid.


    Can you help me?
    Thanks

    Thursday, July 14, 2011 8:53 PM

Answers

  • This is how I generally make connection string dynamic: 

    I use the settings.settings to store and create the connection string ( wich updates also the app.config ), then I change the code of the settings.cs (to create this file automatically simply hit the button "View Code" in the setting designer buttons bar) ovveriding public override object this[string propertyName]

    Assuming the setting name for your connection string is "ConStr"

        internal sealed partial class Settings {
            
            public Settings() {
                // // To add event handlers for saving and changing settings, uncomment the lines below:
                //
                // this.SettingChanging += this.SettingChangingEventHandler;
                //
                // this.SettingsSaving += this.SettingsSavingEventHandler;
                //
            }
            
            private void SettingChangingEventHandler(object sender, System.Configuration.SettingChangingEventArgs e) {
                // Add code to handle the SettingChangingEvent event here.
            }
            
            private void SettingsSavingEventHandler(object sender, System.ComponentModel.CancelEventArgs e) {
                // Add code to handle the SettingsSaving event here.
            }
     
            public override object this[string propertyName]
            {
             get
             {
              if (propertyName == "ConnStr")
              {
               return GetConStr(); // GetConStr retrive the run time value for the connection string
              }
              return base[propertyName];
             }
             set
             {
              base[propertyName] = value;
             }
            }
    
    private string GetConnStr()
    {
    string connstr;
    // build your connStr here
    return connStr;
    }     }

    This way you don't have to change the app.config manually
    And you still have a design time setting for the conn str which may point to a development db
    While at run time the conn str will be always replaced by the one you created, every time you access the setting.

    Hope this was wath you needed


    Friday, July 15, 2011 10:48 AM

All replies

  • Hi,

    I guess you want to poke the extra configuration dynamically able able to get them as well .. 

    After modifying app.config you might need to call Application.Restart()...

    Here is few useful link to get more information about how to modify app. config dynamically.

    http://chiragrdarji.wordpress.com/2008/09/25/how-to-change-appconfig-file-run-time-using-c/

    http://www.codeproject.com/KB/cs/modconfigruntime.aspx

    http://stackoverflow.com/questions/272097/net-dynamically-refresh-app-config

    Hope this helps

    Regards, 


    Md. Masudur Rahman
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, July 15, 2011 9:59 AM
  • This is how I generally make connection string dynamic: 

    I use the settings.settings to store and create the connection string ( wich updates also the app.config ), then I change the code of the settings.cs (to create this file automatically simply hit the button "View Code" in the setting designer buttons bar) ovveriding public override object this[string propertyName]

    Assuming the setting name for your connection string is "ConStr"

        internal sealed partial class Settings {
            
            public Settings() {
                // // To add event handlers for saving and changing settings, uncomment the lines below:
                //
                // this.SettingChanging += this.SettingChangingEventHandler;
                //
                // this.SettingsSaving += this.SettingsSavingEventHandler;
                //
            }
            
            private void SettingChangingEventHandler(object sender, System.Configuration.SettingChangingEventArgs e) {
                // Add code to handle the SettingChangingEvent event here.
            }
            
            private void SettingsSavingEventHandler(object sender, System.ComponentModel.CancelEventArgs e) {
                // Add code to handle the SettingsSaving event here.
            }
     
            public override object this[string propertyName]
            {
             get
             {
              if (propertyName == "ConnStr")
              {
               return GetConStr(); // GetConStr retrive the run time value for the connection string
              }
              return base[propertyName];
             }
             set
             {
              base[propertyName] = value;
             }
            }
    
    private string GetConnStr()
    {
    string connstr;
    // build your connStr here
    return connStr;
    }     }

    This way you don't have to change the app.config manually
    And you still have a design time setting for the conn str which may point to a development db
    While at run time the conn str will be always replaced by the one you created, every time you access the setting.

    Hope this was wath you needed


    Friday, July 15, 2011 10:48 AM
  • Hi Danibat,

    Have you tried Stefano Marcias's solution?

     

    Best regards,


    Sheldon _Xiao[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 18, 2011 6:59 AM
    Moderator
  • Here is a nice article about reading and writing into app.config with a sample code in C#

    http://geekswithblogs.net/akraus1/articles/64871.aspx

     

    Saturday, August 13, 2011 5:11 PM
  • Thanks very much, I had been searching for this for the last one week. So I must ask - how did you find this? Is there a resource that describes these files, and for example, what the method public override object this[string propertyName] does.

    Thanks, Manish

    Saturday, March 03, 2012 2:51 AM
  • I also find that the settings.cs file is auto-generated, which means that the code will get deleted when the file is next auto-generated.

    Is there a way to prevent auto-generation of the settings.cs file

    Thanks

    Manish

    Tuesday, March 06, 2012 2:33 PM