Data connection at runtime

[Ed Andres]

Is there a way to change the connection information of the SQL server at runtime so a program can accomodate a different SQL server name but the tables are named the same?

I am beginning to write an app that needs to be able to connect to different server names but access the same database names on each server.

I currently have the connection string saved in the settings but that is read only and cannot be changed at runtime.

By the way, I am very new to C# and have Googled to find a solution but was not very successful.

Thanks for any help


Ed

 

[Foada]

Without seeing your code it is difficult to say but you should be able to store both connection strings in you settings. At run time you would just have to close the connection, change to the desired connection string, and then open the connection again. Another option would be to set up two different connection objects and then at run time determine which connection to perform your work on.

If you choose to battle wits with the witless be prepared to lose.

 

[Ed Andres]

I don't have any code yet per se. I have added a data set to my project using our local SQL server info and realized that the connection info for that dataset cannot be modified at runtime. I am reading an xml file at startup to get the server information so creating on the fly data connections is pretty easy. I was hoping to be able to use the dataset in the data sources which makes other tasks easier.

I hope this is not too confusing!

Ed

 

[phinoppix]

Normally, you'll be watching out for 3 components if you use VS code generators: the strong-typed DataSet, an SqlAdapter, and a SqlConnection.

The connection string is found in the SqlConnection object, not the dataset. And sure you can modify the connection string by code.

 

[Ed Andres]

Ok, so here is what I have come up with. It looks like you can change the connection string for the table adapter and then I can call the fill method. It seems to work but is this the best approach?

Just wanting to get some feedback before I proceed.

//Set Connection String
this.loginTableAdapter.Connection.ConnectionString = Global.GetConnString();
//Get Data
this.loginTableAdapter.FillByUserName(this.cTS_ProductsDataSet.Login, txtName.Text.Trim());

//Setup connection string based on location
public static string GetConnString()
{
string RetVal = "Data Source=" + Global.Server.Trim() + ";Initial Catalog=CTS_Products;Integrated Security=True";
return RetVal;
}

Ed