All -

I've run across a strange annoyance that I've scoured the forums trying to find an answer for, but found nothing. Basically I have a need in my application to take a connection string of one of my connections and modify it slightly. When I tried to change the connection string I found that it could be set any number of times until I read it. After that it could no longer be set. No exception was throw, no errors, it simply didn't change the value of the connection string. I tried this on multiple machines and multiple test projects thinking I was crazy and it had to be something I was doing wrong, but I came to the same conclusion every time.

I'll post some code below to show this. Please let me know if anyone has any insight to this issue.

Thanks,

Matt

esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString = "Integrated Security=SSPI;Initial Catalog=DATABASE;Data Source=MyServer"; // sets the correct connection string
string conn = esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString; // gets the connection set above, works fine
esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString = conn.Replace("DATABASE", "NEWDATABASE"); // set does nothing
string test = esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString; // test still equals original DATABASE connection

esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString = "Integrated Security=SSPI;Initial Catalog=DATABASE;Data Source=MyServer"; // sets the correct connection string
esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString = "Integrated Security=SSPI;Initial Catalog=NEWDATABASE;Data Source=MyServer"; // sets the correct NEW connection string
string conn = esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString; // gets the NEW connection set above, works fine
esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString = conn.Replace("NEWDATABASE", "THIRDDATABASE"); // set does nothing
string test = esConfigSettings.ConnectionInfo.Connections["MyDB"].ConnectionString; // test equals the NEWDATABASE connection

I'm surprised you do not get an error. There are a couple of ways to change connections at runtime, but, as Mike points out, we do not really support just changing the connection string.

http://community.entityspaces.net/forums/thread/4769.aspx

See, that's the thing... I don't get any error at all, but I feel that I should. This is a very unique requirement. Although I agree with both of you that I should not be changing the value of the connection string, this situation calls for it. I'll give you more explanation of what I'm trying to do...

1. Load EntitySpaces settings explicitly in the code at startup (I have nothing in the config file). I add two connections, an EnterpriseDBConnection and a StoreDBConnection, however, for the store connection string I place something like this... "Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Source=Server@STOREID" where @STOREID will be replaced at runtime by the store originating the call.

2. If my enterprise database is unavailable, or down, I need to call the store database on the store server from the originating call. However, in order to do so I need to replace the connnection string @STOREID with the actual store id, for example a store id of "A" would give me a connection string of "...Data Source=ServerA". I then flip the default connection to the StoreDBConnection.

3. Load my ES objects using the store connection.

Now I could load ALL of the store connections at startup, but there are a lot of stores and it seems overkill considering their connections strings, database schema, etc are all the same with the exception of the STORE ID. In theory this should work. I have created some tests without ES and had success. I've also been able to get this to work by hard coding the store server, for example, "ServerA", then flipping the connection. But again, I won't know which server to go to until runtime, so that won't work in production.

Again, the problem I'm having with ES is when I try to replace the @STOREID from the original connection string with the actual STORE ID. It seems that once the connection string is read, it can no longer be changed. Is there something that prohibits this in the get/set for the esConnectionElement.ConnectionString object/property? If so, it would be nice to get an exception stating that there was an error and the set could not be completed.

Thanks again for the help,

Matt

Once you get the ConnectionInfo ConnectionString, you cannot change it. Can't you set the actual store id at runtime when you create the connection? People do something similar when they want to set user credentials at runtime, and not have them stored anywhere. Then, if the main connection fails, you just change your default to the store specific connection.

string storeString = "...Data Source=Server@STOREID";
    
esConnectionElement conn = new esConnectionElement();
conn.Name = "EnterpriseDBConnection ";
conn.ConnectionString = "...Data Source=Server@Enterprise";
...
esConfigSettings.ConnectionInfo.Connections.Add(conn);

conn = new esConnectionElement();
conn.Name = "StoreDBConnection";
conn.ConnectionString = 
    storeString.Replace("STOREID", actualStoreId);
...
esConfigSettings.ConnectionInfo.Connections.Add(conn);

esConfigSettings.ConnectionInfo.Default = "EnterpriseDBConnection";