Change connection string on runtime

Question

I have 3 identical sql server databases on 3 servers.

i have a project in vb.net with entity framework that is generated through wizard with one of databases.

I want to change the connection on runtime , so sometimes connect with database 1 , after disconnect and connect with database 2 ......

The connection information is :

database 1 :  

"Data Source=server1\SQLEXPRESS" ";Initial Catalog=udata1;Integrated Security=SSPI"

database 2 :  "Data Source=server2\SQLEXPRESS" ";Initial Catalog=udata2;Integrated Security=SSPI"

database 3 :  "Data Source=server3\SQLEXPRESS" ";Initial Catalog=udata3;Integrated Security=SSPI"

What can i do ?

Please help me !

Thank you !

Answer 1

The ObjectContext has several different overloaded constructors so you don't have to rely on app/web.config; you can pass in the connection at runtime while creating a new ObjectContext.

http://msdn.microsoft.com/en-us/library/bb738714.aspx

http://msdn.microsoft.com/en-us/library/bb739017.aspx

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 2

Thank you !

But , i read the pages and i don't understand. Can you please give me an example with my data as described on my post ?

in my project i have created entity through connection with first database with wizard inside vb.net.

i don't connect manually with database.

thank you

Answer 3

The problem is that the moment you do any kind of update to database1, your other databases will be out of sync and any further queries will be database-dependent. If you're only doing reads then I suppose you're fine going down this route but I'm wondering why you need three identical databases to query. If you want to actually synchronize updates through all of your databases you'll need a more comprehensive solution...

 

In any case, here are the details:

 

using (var oc1 = new ObjectContext("metadata=.\YourModel.csdl|.\YourModel.ssdl|.\YourModel.msl;provider=System.Data.SqlClient;provider connection string='Data Source=server1\SQLEXPRESS;Initial Catalog=udata1;Integrated Security=SSPI;multipleactiveresultsets=true'"))
{
  // Query over oc1 here 
}

using (var oc2 = new ObjectContext("metadata=.\YourModel.csdl|.\YourModel.ssdl|.\YourModel.msl;provider=System.Data.SqlClient;provider connection string='Data Source=server2\SQLEXPRESS;Initial Catalog=udata2;Integrated Security=SSPI;multipleactiveresultsets=true'"))
{
  // Query over oc2 here 
}

 

 

 

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 4

Thank you !

But maybe i have not explain correctly my situation :

I have to make a procject in vb.net to manage a database in sql server. During the making process , i work with my database model :
database 1 :  "Data Source=server1\SQLEXPRESS" ";Initial Catalog=udata1;Integrated Security=SSPI"

I create the entity model with this database and after i create the application connected with this database.

But i want to use this application with several databases on our network , and i want to make my final version of the project independent from database connection. So i want to put all the information for connecting with database on a text file .And when the application is starting should read the information from text file , create the connection and after do his normal operation. If something change , i want only to change the text file without touching the application. All the databases that i want to manage are identical with my first model that i use for creating application.

Maybe , this is not the right way , but i want to do. Can someone help me with this , or is impossible to do ?

Thank you !

Answer 5

Thanks for the clarification. However the clarification indicates that you don't necessarily need to change the connection at runtime; you just want the runtime connection to be database agnostic. Also do the multiple identical databases correspond to your dev/test/production databases? In any case, I think the solution above should work for your specific scenario of interacting with multiple databases. Your 'shared text file' should really just be your app.config file (http://msdn.microsoft.com/en-us/library/ms254494.aspx). Place your connection string there and your ObjectContext can read it as I have mentioned earlier.

So all you have to do is have your ObjectContext use the connection string specified by name: 

ObjectContext context =
    new ObjectContext("name=MyModel");

And when you want to switch databases you can just go into the app.config file and change it; your runtime code will use the new connection string.

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 6

ok , but after publishing the application , the file app.config will be editable ?

Answer 7

it looks you should read the configuration file on the fly. app.config file was the xml one. you can use like XmlTextReader or other xmlreader methods to wander each node was in the xml file. then you can pass this value dynamically insert back to your application and constructor different connections with different database.

Thanks,

Werewolf,

---

Just a newbie for everything.

Answer 8

Yes - also as I mentioned earlier, you can just pass in the name of the connection string name into the ObjectContext; you don't need to use XmlTextReader; also if you wanted to read the connection string outside of the ObjectContext you can just use the strongly-typed ConfigurationManager with app.config/web.config files to read the connection at runtime instead.

 

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 9

The file app.config exist on my application and was generated when i have used wizard process to create entities model with my local database.

The app.config is :

------------------------------------------

<?

xml version="1.0" encoding="utf-8" ?>

<

 configuration >
<startup >
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
</startup >
<connectionStrings >
<add name="udataEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=MyPC\SQLEXPRESS;Initial Catalog=udata1;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

</

connectionStrings >

</

configuration>

-----------------------------------------------------------

So what can i do ? Can someone explain to me with some code how to use my app.config file to resolve my situation ?

Thank you

 

Answer 10

Based on that connection string I assume you have:

a. Created a console project or something similar.
b. Added an ADO.NET Entity Data Model pointing to database 'udata1'

Now do this:

1. Add an assembly reference to System.Configuration.
2. In your program.cs's Main method, add this example code:

 

            while (true)
            {
                var cs = ConfigurationManager.ConnectionStrings[1].ConnectionString;
                Console.WriteLine("Connection String: " + cs);
                using (EntityConnection ec = new EntityConnection(cs))
                {
                    ec.Open();
                    using (var context = new udataEntities(ec))
                    {
                        // Write your EF application code which uses the ObjectContext here
                        Console.WriteLine("Connected to database: " + ((EntityConnection)context.Connection).StoreConnection.Database);
                    }
                }
                Console.WriteLine("Change connection string then hit enter");
                Console.ReadLine();
                ConfigurationManager.RefreshSection("connectionStrings");
            }

 

3. Run the program.
4. When prompted, go into your *.exe.config file in [ProjectDir]\bin\Debug and change the connection string.
5. Ctrl+C out of the app when you're done. 

 

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 11

Thank you !

But ,

1.it's not a console application , it's just a simple windows forms application

2.yes there is a entity data model pointing to database "udata1" 

i work with VB.net 2010 ( not with C )

And i want to publish my application and after that i want to change the connection string before running the application to connect with another identical database (on another Pc  on our network and with another database name). And i want to do this without changing my application but just app.config  (or another file where can i save the connection string).After change the connection string , i want to run the application and to work with the new database.

Answer 12

>> And i want to publish my application and after that i want to change the connection string before running the application to connect with another identical database (on another Pc  on our network and with another database name). And i want to do this without changing my application but just app.config  (or another file where can i save the connection string).After change the connection string , i want to run the application and to work with the new database.

 

Well in that case you don't even need the code above. If you want to do this before running the app just change the *.exe.config that sits next to your executable and it should just work. Have you tried that at all? Are you trying to change the connection string programmatically through another application? All you really have to do is:

1. Open the exe.config, change the connection string
2. Start the app 
3. The app finishes, make sure the process is shut down, then repeat from step (1) for your different databases.

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question

Answer 13

yes , this is what i want to do : Or to change manually the connection string , or to create another application just for changing the connection string , before starting my main application.

But  , the exe file is udata.exe , and i can't find udata.exe.config.

and , is possible to change this file (connection string inside ) programatically through another application ?

 

Thank you.

Answer 14

If you just want to change the underlying database connectionstring, I found that you can do that by changing the StoreConnection.ConnectionString, just like setting an ADO.NET SqlConnection.ConnectionString:

context=new udataEntities; ((system.data.entityclient.entityconnection)context.connection).StoreConnection.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;User ID=myuser;Password=mypassword";

Answer 15

When you build your application in VS or through MSBuild, you will find the *.exe.config file in your output directory. All you have to do is open that file and modify your connection string. Note the bin\Release path below.

And yes, it is possible to change your settings programmatically:
1. Go to this page: http://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager.aspx
2. Search for 'CreateAppSettings()' for an example. 

---

Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question