How to select the connnection string during runtime?

Question

Hello, How to select the connection string from multiple instances in web config file using web api controller? Please help Thanks

Sachin Singh · Answer

better is to change connection string in web.config as soon as a client select it, so that you do not need to worry about connection strings anymore, as per the client the connection will be changed in the web.config directly. "ChangeConnection" > "Client1" >client1 "client2" >client2 "client3" >client3 $( "#ChangeConnection" ).change(function(){ var val=$( this ).val(); $.ajax({ type: "post" , url: "/home/ModifyConnection?val=" +val, cache: false , success:function(){} }) }); public void ModifyConnection(string val) { Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); string datasrc,IntialCat,UserID;password; if (val== "client1" ) { datasrc= "" ; IntialCat= "" ; UserID= "" ; password= "" ; } if (val== "client2" ) { datasrc= "" ; IntialCat= "" ; userId= "" ; password= "" ; } if (val== "client3" ) { datasrc= "" ; IntialCat= "" ; UserID= "" ; password= "" ; } config.ConnectionStrings.ConnectionStrings.Add( new ConnectionStringSettings(val,String.Format( "DataSource={0};InitialCatalog={1};User ID={2}" ;Password={3},datasrc, InitialCat,UserId,Password))); config.Save(ConfigurationSaveMode.Modified, true ); ConfigurationManager.RefreshSection( "connectionStrings" ); } as you can see i am changing connection string based on dropdown , the ajax will call a method which will modify the connection string as per the client need.

Sachin Singh · Answer

follow below code to send selected client name private void pickQuestion_SelectedIndexChanged(object sender, EventArgs e) { var picker = sender as Picker; var selectedItem = picker.Client; // property inside picker using (var client = new HttpClient()) { var response = await client.PostAsJsonAsync( "api/methodName" , selectedItem); } }

Demo Cloud · Answer

Hello, I wanted to pass the selected connectionstring to all api controllers, please help how to achieve this? Thanks

Rajeesh Menoth · Answer

Hi, Based on the name you can filterout the connection string. foreach (ConnectionStringSettings con in ConfigurationManager.ConnectionStrings) { if (con.Name == "client_db3" ) { string name = con.Name; string connString = con.ConnectionString; string provider = con.ProviderName; } } Reference : https://stackoverflow.com/questions/1530284/multiple-sql-server-connection-strings-in-app-config-file

Rajanikant Hawaldar · Answer

you can retieve all connection strings as below and store it in List object then you can take use from this object. List string > allConstrings = new List string >(); foreach (ConnectionStringSettings c in System.Configuration.ConfigurationManager.ConnectionStrings) { allConstrings.Add(c.Name); }

Jignesh Kumar · Answer

Hi, Please use this link which will help you, https://www.c-sharpcorner.com/article/how-to-changeswitch-the-database-dynamically-in-an-asp-net-mvc-application/

Demo Cloud · Answer

Hello, I have done the same using below code public static string connStr; SqlConnection con = new SqlConnection(connStr); But I wanted to select a connectionstring from multiple? Thanks

Rajanikant Hawaldar · Answer

You need to add a reference to System.Configuration.dll and then use: // Add a using directive at the top of your code file using System.Configuration; Within the code body set your variable string constr = System.Configuration.ConfigurationManager. ConnectionStrings[ "client_db1" ].ConnectionString;

Demo Cloud · Answer

hello, From where are we getting the index value? Thanks

Rajanikant Hawaldar · Answer

Pass index or connection name. below i am passing index as 0 to retrive first connection string string constr = ConfigurationManager.ConnectionStrings[0];