How to dynamically change connection string in generated dataset class?

How to dynamically change connection string in generated dataset class?

• Voldemort

  Thanks again, Cesar
  I can see that your solution is fine in most cases, but I actually want to change the connection string at runtime. The reason is that the user is interacting with the database via a webservice. When the user logs on to the webservice (gets a Session), there is a fixed connectionstring in the webservice that is used to check the users password in the database (this is the password to the application stored in a user table). But then if the user is approved, he also corresponds to an sql-user in the database, and the webservice fetches the sql-password for the user in the db and builds dynamically the connectionstring. The rest of the communication between the webservice and the database is done using this connectionstring. The reason that we want each user also to be an sql-user is that a lot of logic in the db (stored procedures, triggers etc.) is based on the knowledge of which sql-user is doing what. (The database was developed for other applications originally.)
  
  So in our case, as you say, I guess the solution is OK. However, I have no problem agreeing that the solution is a bit off-beat
  
  

• Michael Shorten

  I have the same issue.

  I've added a data source to my Windows Solution and I need to change at run-time to work online or offline. I have two connection strings in app.config and I need to switch between them.

  If you excluded the app.config from your solution then how can you switch between different DB servers that you don't know at design-time

  I've tried the SettingsLoaded solution but is not working. On main form I can switch between online and offline work, I can change the value for connection string but somehow when I open a application form the connection string is resented to the initial value from app.config.

  Thank you,

  
  

• chongqing

  I had this same problem a week or so ago and blogged about the solution I came up with:

  http://blogs.dev.bayshoresolutions.com/roger/archive/2006/04/10/3976.aspx

  Basically, I added code to the partial class which re-routed the call to the settings indexer to look for the correct name in the web.config.

  This is a really frustrating scenario though, especially if you are trying to convince someone to use a 2.0 dataset, but then you have to explain all of this to them.

  If anyone finds a better way I'd love to know!

  Thanks,
  Roger

  
  

• Leo H

  Yesterday I googled a lot but I didn't find the solution I was lloking for.
  So this is my workaround, it works GREAT and doesn't need an app.config at runtime:
  
  1) I set the settings.designer.cs build action to "none" (prev. "compile")
  2) create a fake Settings class where I can call Properties.Settings.Default.ConnectionString (get and set my connectionstring)
  3) I exclude the app.config from the project
  
  VS will use the connectionString in settings.settings at design-time, while run-time will be used the connection script in the "fake" class. No app.config is created.
  
  Feedbacks are wellcome!!
  
  PS: my first post ever... what a newbie ;)
  
  

• x-wing

  Ok, so if you are using TableAdapters, even having the fact that there is a hard-coded conn-string within it, it will be used just in case that your conn-string defined within the Web.config or App.config is not found.
  So, unless you want to change it dynamically at-runtime, normally what you do is just change the conn-string defined within the Web.config (it is first updated by TableAdapter wizard), like this:
  

  <connectionStrings>

  <add name="MyProject.Properties.Settings.NorthwindConnectionString"

  connectionString="Data Source=MYSERVER;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=mypwd"

  providerName="System.Data.SqlClient" />

  </connectionStrings>

  Within TableAdapter code, you can see that it is  getting the ConnString from the Web.config, doing it like that:
  
  

  public string NorthwindConnectionString {

  get {

  return ((string)(this["NorthwindConnectionString"]));

  }

  }

  And just in case Web.config is  wrong and it does not find it, it will use the Hard-coded connstring which is defined using a .NET Attribute like this:
  

  [global::System.Configuration.DefaultSettingValueAttribute("Data Source=MYSERVER;Initial Catalog=Northwind;Persist Security Info=True;Use" +"r ID=sa;Password=mypwd")]
  
  1.-
  So, if you want to change it dynamically at-runtime, JUST in that case, what you are doing with "PARTIAL CLASS" would be right, OR you can just change the TableAdapter 'ConnectionModifier' property to 'public', and use its own published conn-property like "myTableAdapterObject.Connection".
  
  2.-
  BUT, normally, when you move your development App. to a production-stage, you just have to change the Web.config Conn-string.
  
  CESAR DE LA TORRE
  [MVP - XML-WebServices]
  Software Architect
  
  Renacimiento 
  www.renacimiento.com
  
  

  
  
  

• MichaelVictoria

  I want to use your solution for this frustration problem...

  Code you post the Fake Settings code... I am unsure how you can get or set on the Properties.Settings.Default.ConnectionString property

  
  

• Tanvir Huda

  Of course you can.
  You  can get it at run-time from Web.config strings or you can get it from your own configuration file. Just get your connection string before opening the SqlConnection (or IDbConnection) object.
  
  CESAR DE LA TORRE
  [MVP - XML-WebServices]
  Software Architect
  
  Renacimiento 
  www.renacimiento.com
  
  

• Karol Rewera

  I am sorry to bother you again, I tried using the code provided modified a little bit but it still does not work.

  internal sealed class Settings

  {

  private static Settings defaultInstance = new Settings();

  public static Settings Default

  {

  get { return defaultInstance; }

  }

  /// <summary>

  /// Returns the datasource connection string provided by the caller

  /// </summary>

  public string McmsConnectionString

  {

  //This is a static object property set by the caller

  get { return (MCMSDataAccessComponents.CustomDataSource.ConnectionString); }

  }

  }

  No data is being bound to a UI control at runtime. When I do a data preview int the desigmer it works but at runitme when a pass a new connection string to the dll no data is bound.

  Am I missing an attribute

  Any Ideas

  
  

• cfgumtow

  gdexter wrote:

  I want to use your solution for this frustration problem... Code you post the Fake Settings code... I am unsure how you can get or set on the Properties.Settings.Default.ConnectionString property

  internal sealed class Settings
  {
  private static Settings defaultInstance = new Settings();
  public static Settings Default
  {
  get { return defaultInstance; }
  }

  public string ConnectionString
  {
  get { return [my Property / GetMethod() returning the connection string];}
  }
  }

  Settings.Default.ConnectionString points to a method where I calculate/retrive my connection string.
  

  
  

• GregCox

  I found a link on the forums:
  https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

  Basically I had a situation where I wanted to change the connectionstring that M$ store when using the funky project data sources way of coding.

  All I did is added this sub to the settings.vb file (instructions how to view the setting code in included in that link above)

  Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded

  Me.Item("cnDatabase") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Item("UserSettingDatabaseLocation")

  End Sub
   
  Where cnDatabase is the name of the Connection you have used for your datasets, and UserSettingDatabaselocation was just a User setting that i have used, but you could set that to anything like your own XML based setting or anything.

  Good Luck!

  
  
  

• Mike Dempsey

  I find myself in almost the exact situation you describe. The solution I am currently trying to add code like the following to a partial class definition in a separate file. Since if you inspect the Connection property you can see that the connection shuffled out to many different places. This down side is that the Connection property is set twice once by the autgenerated code and then once for this custom method.
  
  namespace somepackage.MyDataSetTableAdapters
  {
  public partial class ClientsTableAdapter
  {
  public void SetCustomConnection(string connectionString)
  {
  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
  conn.ConnectionString = connectionString;
  this.Connection = conn;
  }
  }
  }
  
  

• DTUK

  Thanks for the answer, but I think that one of us misunderstands the other, but I am not sure who.
  
  What I have in my code is something like:
  
  

  menyDS = new MenySel();
  adapter = new MenySelTableAdapter();
  adapter.Fill(...);
  
  where MenySel is a typed dataset and MenySelTableAdapter the corresponding adapter. Both are generated when dragging a stored procedure from a database onto a dataset in the designer. If I look in the generated code for MenySelTableAdapter I see the following:
  
  

  private void InitConnection() {
     this._connection = new System.Data.SqlClient.SqlConnection();
     this._connection.ConnectionString 
        =   global::DBAccess.Properties.Settings.Default.TestConnectionString;
  }
  
  where Test is the name of the database. This connectionstring is made at design time. And MenySelTableAdapter does not have a public method to change it. The Settings class also only has a set method, and I don't want to edit generated files anyway.
  
  The solution I have tried now, is (since MenySelTableAdapter is defined using "partial") to have an extra file with a public method SetConnectionString for all my generated adapters. That way I don't need to worry if I regenerate the datasets or Visual Studio in some way mess up generated files (it has been known to happen...). So now my code looks like:
  
  menyDS = new MenySel();
  adapter = new MenySelTableAdapter();
  adapter.SetConnectionString(Session["connectionstring"]);
  adapter.Fill(...);
  
  (This is on a webservice, hence the Session-part.)
  I don't know if this is an optimal or even a very good solution, so I would be happy for comments.
  

  
  
  

• Roman Mandeleil

  "If you excluded the app.config from your solution then how can you switch between different DB servers that you don't know at design-time "

  the custom tool used to edit desig-time the dataset class reads the connection strings from the app.config even if it's not included in the solution.

  
  

• WoodrowS

  The name of the property you named McmsConnectionString should have the same name of the connection you are using design time.

  Go to the xsd designer view, select your TableAdapter and in the Properties window look at the Connection->Name property. It's something like "MyConnectionString (Settings)"
  (default is "ConnectionString (Settings)").
  The properties returning the connectionstring in the Settings class must have the same name:

  public string [MyConnectionString]

  {

  //This is a static object property set by the caller

  get { return (MCMSDataAccessComponents.CustomDataSource.ConnectionString); }

  }

  
  
• How to dynamically change connection string in generated dataset class?