Overriding Dataset Settings Connection String

So say you have a database connection [DataSet] that you have added to your project.  You do not want to store the password with it (which will end up in the .config file) and may want to change the database name at runtime from what you are using at runtime.  Doing this seems a bit off, since you can not write to the ConnectionString that is creating in the settings portion of the VS designer!  You can not change it to type User instead of Application scope either.

A useful tidbit that I discovered when looking for how to change this.  While it should seem immediately obvious how to modify a connection string at runtime, it is not.  After doing some digging I found the following code to place in Settings.cs [Go to settings, then click "View Code"].

In Settings.cs:
protected override object this[string propertyName] { get { if (propertyName == "myConnectinStringVariableName") { // Return the new connection string // Note, it is useful to create a Property getter method within your application // (UtilityClass.ConnectionString) rather than setting it here. I use MySQL and // create a MySqlConnectionStringBuilder class. // That code is listed below. // return "<CONNECTION STRING HERE>"; // return utilityClass.ConnectionString; } } } class utilityClass { private static MySqlConnectionStringBuilder cStringBuilder; public static string ConnectionString { get { // If cStringBuilder == null then generate a new object, and cache it for later reference if (cStringBuilder == null) { // Create a MySQL builder class cStringBuilder = new MySqlConnectionStringBuilder(); // Build out connection string cStringBuilder.AllowZeroDateTime = true; cStringBuilder.ConvertZeroDateTime = false; // Set specific settings for connecting to the database cStringBuilder.Server = ""; cStringBuilder.Database = ""; cStringBuilder.UserID = ""; cStringBuilder.Password = ""; } // Return the connection string return cStringBuilder.ConnectionString; } } } Now when working with your dataset, each time it needs to get the connection string it will return your custom connection string.  This is useful incase you do not want to store the password in your configuration files.  Also, this will allow switching different databases of the same type with your dataset at runtime.  I do that -- I have a "master" database that I use for development, and at runtime I switch to the live database.. both have exactly the same type of structure.

Hope you find this useful!

Matthew MacSuga