Home » SQL ServerRSS

How to dynamically change connection string in generated dataset class?

I have a project with database classes which are generated from database objects. That is I add a dataset to the project and drag and drop a database object onto it. The problem with this is that I have to give a connectionstring which is stored in the code. This is ok while developing the application. But runtime I want to use another connectionstring, both for security reasons and because each user of the application will correspond to a database user (ms sql) and shall have a corresponding connectionstring. Is there an easy way to do this without having to restore to plain all datasets and do everything myself?

 

53 Answers Found

 

Answer 1

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
 

Answer 2

Thanks for the answer, but I think that one of us misunderstands the other, but I am not sure whoBig Smile.

What I have in my code  is something like:

menyDS = newMenySel();
adapter =
newMenySelTableAdapter();
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:

privatevoid 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 = newMenySel();
adapter =
newMenySelTableAdapter();
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.

 

Answer 3

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>

<addname="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:

publicstring 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

 

Answer 4

Thanks again, CesarSmile
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-beatSmile
 

Answer 5

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!
 

Answer 6

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 ;)
 

Answer 7

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;
        }
    }
}
 

Answer 8

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,

 

Answer 9

"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. 

 

Answer 10

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

 

Answer 11

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?

 

Answer 12

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?

internalsealedclassSettings
{
  privatestaticSettings defaultInstance = newSettings();
  publicstaticSettings Default
  {
     get { return defaultInstance; }
  }

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

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

 

Answer 13

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

internalsealedclassSettings

{

privatestaticSettings defaultInstance = newSettings();

publicstaticSettings Default

{

get { return defaultInstance; }

}

///<summary>

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

///</summary>

publicstring 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

 

Answer 14

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:

publicstring [MyConnectionString]

{

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

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

}

 

Answer 15

Not sure if this will help, but under My Project settings I changed the scope of the connection  string I was using to user  so I could change  the connection string  during runtime

 Then it was a simple

My.Settings.VisionConnectionString = ConnectionString.ConnectionString.ToString

Seems to work for me.
 

Answer 16

You have to explain me how you managed to change  the scope from application  to user  for a connectionstring. The settings.settings file won't allow you to do that. In fact variables of type 'ConnectionString' have their scoped default to Application and it is readonly.

 

Answer 17

After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime  with new string  settings.  The only thing you have to know is the full name of the connection  string.

Hope this helps someone :-)


        public static void ChangeConnectionString(string strConn)
        {             
             Configuration _config = ConfigurationManager.OpenExeConfiguration(
                        ConfigurationUserLevel.None);

            //the full name of the connection string can be found in the app.config file
            // in the "name" attribute of the connection string
             _config.ConnectionStrings.ConnectionStrings[FULL NAME OF CONNECTION STRING HERE].ConnectionString = strConn;

            //Save to file
            _config.Save(ConfigurationSaveMode.Modified);

            //force changes to take effect so that we can start using
            //this new connection string immediately
             ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name);
            Properties.Settings.Default.Reload();
        }
 

Answer 18

To solve this problem  I changed the the ConnectionModifier in the TableAdapter's properties  to public. By default this is set to Assembly. This then exposed the connection  object of the adapter where you can specify the connectionstring  property.

Note That when you change  one of the adapter's ConnectionModifier property all adapters in the Dataset will be changed.

Examples are always nice... I used this method with sql  Reporting Services 2005

DsRptPaySlips.PaySummaryDataTable DtPaySummary = newDsRptPaySlips.PaySummaryDataTable();

DsRptPaySlipsTableAdapters.PaySummaryTableAdapter AdptPaySummary = new DsRptPaySlipsTableAdapters.PaySummaryTableAdapter();

AdptPaySummary.Connection.ConnectionString = StrDataseConnection;

AdptPaySummary.Fill(

DtPaySummary,

Convert.ToDateTime(StrPaySummaryDateTime),

newGuid(m_StrEmployeeUniqueID)

);

AdptPaySummary = null;

ReportViewerX.LocalReport.DataSources.Add(newReportDataSource(

"DsRptPaySlips_PaySummary",

DtPaySummary)

);

Ta

Allan

http://www.peopleroster.com

 

Answer 19

Noyabronok wrote:
After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime  with new string  settings.  The only thing you have to know is the full name of the connection  string.

Hope this helps someone :-)

Thanks a lot!

The main trick is to use RefreshSection. I was also very close to this solution - but was lack of this tiny secret :)

I need to change  connection strings in run-time because I'm use M$ Data Access App.Block, where the creation of database  (and that's the thing you always start with) looks like DatabaseFactory.CreateDatabase(stringargument), where this argument - is not the connection string itself (unfortunately!), but the name of the setting key in the "connectionStrings" section of the config-file:(( Well, my application  starts with a login dialog, where user  selects sql  Server, database, login method and (if not using integrated security) username/password. Constructing connection string from this dialog's fields is easy, but after that I need to store this connection string into the config-file - otherwise that CreateDatabase method will fail !

 

Answer 20

work great ~~ thx very much

and the other easy  method is :

create different dns odbc  at the control pane

then , it can easily to switch between each other

 

Answer 21

I had a very very bad experience with typed datasets  2.0, its PITA. There are a loyt of magic goin on, and sometimes I spent hours trying to figure out how things happened. I had a lot of frustrations using this and sometimes it makes me feel stupid and Noob to data access.

This InitConnection method is very frustrating and I must admin this is one of my biggest mistake in my current project, the use pure typed datasets. Now am cleaning up the mess created with typeddatsets and I willonly use this for reporting and grid databinding and nothing more.

 

Answer 22

Could someone post an example of this in VB?

Thanks.

 

Answer 23

Hello i dont know if is to late for an answer, and sory for my por english, normaly I speak french.

So there is my solutions. Is to override the methode who go get de property in the datasetclass, in the dataset.cs.

Let's see an example.

namespace NameSpace.Properties
{
  partial class  Settings
  {  
    public override object  this[string propertyName]
    {
      get
      {
        if (propertyName == "MyConnectionString")
        {
          return ConfigurationManager.ConnectionStrings["WebConfigConnectionString"].ConnectionString;
        }
        else
        {
          return base[propertyName];
        }
      }
      set
      {
        base[propertyName] = value;
      }
    }

  }
}

Give me your feedback of this answer.

 

Answer 24

I've seen a lot of posts about this issue. Here is the way I handle it. This allows the connection  string to be set at runtime  if required.

Changing the generated  code won't work unless you want to change  it again every time you use the designer.

I create a factory class  to create instances of my table adapters. Here is some sample code.

Code Snippet

namespace Myapp.Data.MyappDataSetTableAdapters
{
    internal class AdapterFactory
    {
        private string  _connectionString = "";

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        internal MytableTableAdapter GetMytableTableAdapter(string connectionString)
        {
            MytableTableAdapter result = new MytableTableAdapter();
            result.Connection.ConnectionString = connectionString;
            return result;
        }

        internal MytableTableAdapter GetMytableTableAdapter(System.Data.OleDb.OleDbConnection connection)
        {
            MytableTableAdapter result = new MytableTableAdapter();
            result.Connection = connection;
            return result;
        }

        internal MytableTableAdapter GetMytableTableAdapter()
        {
            return GetMytableTableAdapter(_connectionString);
        }
}

In the factory you need to create methods to return each type of table adapter.

When the factory creates a new instance of a table adapter that instance will initially get the default connection string but the string will never be used. It is immediately overwritten by the connection string you assign to the adapter factory. The connection string isn't used until the first time you try to access the database  using the table adapter.

You can use this with code  like

Code Snippet

 Myapp.Data.MyappDataSetTableAdapters.AdapterFactory adapterFactory =

    new Myapp.Data.MyappDataSetTableAdapters.AdapterFactory();
 adapterFactory.ConnectionString = myConnectionString;
 Myapp.Data.MyappDataSetTableAdapters.MytableTableAdapter myTableAdapter = adapterFactory.GetMytableTableAdapter();

Send feedback if this isn't what you're looking for.

 

Answer 25

The best way i finally find is this one

using System.Configuration;

namespace Nms.Inventory.Properties {

// This class  allows you to handle specific events on the settings class:

// The SettingChanging event is raised before a setting's value is changed.

// The PropertyChanged event is raised after a setting's value is changed.

// The SettingsLoaded event is raised after the setting values are loaded.

// The SettingsSaving event is raised before the setting values are saved.

internalsealedpartialclassSettings {

public Settings() {

// // To add  event handlers for saving and changing settings, uncomment the lines below:

//

// this.SettingChanging += this.SettingChangingEventHandler;

//

// this.SettingsSaving += this.SettingsSavingEventHandler;

//

this["SiteConnectionString"] = ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString; //(Configuration manager came from my web.config)

// List all the connection  string you whant to override.

}

privatevoid SettingChangingEventHandler(object sender, System.Configuration.SettingChangingEventArgs e) {

// Add code  to handle the SettingChangingEvent event here.

}

privatevoid SettingsSavingEventHandler(object sender, System.ComponentModel.CancelEventArgs e) {

// Add code to handle the SettingsSaving event here.

}

}

}

 

Answer 26

This is the solution I've sucessfully used in this scenario:

Problem:
We have a 3-tier application  developed using the new features of VS 2005. connection  string is stored  as a setting in the DAL (Data Access Layer).
We want to change  the connection in run-time from upper layers (for example, in GUI layer, user  wants to change the access database  file used)

Solution: The Data Access Layer will expose that setting to upper layers. For that, we need to perform two simple steps:

1. In DAL, open the "settings.setting" file in design-mode and change the connection string  setting:
- "Type" must be "String"
- "Scope" must be "User" (because "Application" scope would made this setting readonly)

2.In some class  of the Data Access Layer, expose that setting. Something like:

Public Class DTADBManager
    Public Shared Property ConnectionString() As String
        Get
            Return DTACuestionario.My.MySettings.Default.cuestionariosConnectionString
        End Get
        Set(ByVal value As String)
            DTACuestionario.My.MySettings.Default.cuestionariosConnectionString = value
        End Set
    End Property
    ...
End Class


Once this is done, upper layers can change the connection string at runtime, so when a new DataAdapter object  is created it use the new connection.
 

Answer 27

JesusAbizanda wrote:
This is the solution I've sucessfully used in this scenario:

Problem:
We have a 3-tier application  developed using the new features of VS 2005. connection  string is stored  as a setting in the DAL (Data Access Layer).
We want to change  the connection in run-time from upper layers (for example, in GUI layer, user  wants to change the access database  file used)

Solution: The Data Access Layer will expose that setting to upper layers. For that, we need to perform two simple steps:

1. In DAL, open the "settings.setting" file in design-mode and change the connection string  setting:
- "Type" must be "String"
- "Scope" must be "User" (because "Application" scope would made this setting readonly)

2.In some class  of the Data Access Layer, expose that setting. Something like:

Public Class DTADBManager
Public Shared Property ConnectionString() As String
Get
Return DTACuestionario.My.MySettings.Default.cuestionariosConnectionString
End Get
Set(ByVal value As String)
DTACuestionario.My.MySettings.Default.cuestionariosConnectionString = value
End Set
End Property
...
End Class


Once this is done, upper layers can change the connection string at runtime, so when a new DataAdapter object  is created it use the new connection.


I've had the same thought... but what happens to the wizard when you want to update the data structure?  It wizard freaks out:
---------------------------
Error
---------------------------
Configure TableAdapter XXX failed.

Unable to find connection 'xxxConnectionString' for object 'Settings'. The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded.
---------------------------
OK  
---------------------------

Someone must have a reference to setting a specific connection/connection class object when using the wizard.  Anyone?
 

Answer 28

Hi SobyOne,

if you want to update the data structure via VS2005 wizard, you have to restore  original application  setting type and scope.

After updating you can modify application setting again.

 

Answer 29

could you please post a VB.net version of this code..
 

Answer 30

ajmcalister has something close to the most usable solution to this problem.  I prefer this model, which I wrote up in a very long post that can be found at the end of http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1986118&SiteID=1.

When you create a table adapter in your typed dataset, set the access modifier on its connection  to Public.  Then implement this:

Code Snippet

publicstaticclassAdapters

{

   publicstatic MyDataSet.Table1TableAdapter Table1;

   publicstatic MyDataSet.Table2TableAdapter Table2;

   // other adapters go here

   publicstaticvoid Init(string csName)

   {

      string  cs = ConfigurationManager.ConnectionStrings[csName].ToString();

      Table1 = new MyDataSet.Table1TableAdapter();

      Table1.Connection = new SqlConnection(cs);

      Table2 = new MyDataSet.Table2TableAdapter();

      Table2.Connection = new SqlConnection(cs);

      // other adapters go here

   }

}

When you launch your program, call Adapters.Init() with the name of the connection string from app.config.  (Or you can just implement Init so that it takes the connection string as its parameter.  Whatever.)  From then on, whenever you need a table adapter you just use the Adapters, e.g.

Code Snippet

Adapters.Table1.Fill(Table1DataTable);

This is so much better than the horrible idiom you see in nearly all ADO sample code:

Code Snippet

MyDataSet.Table1TableAdapter ta = new MyDataSet.Table1TableAdapter();

ta.Fill(Table1DataTable);

As with all static classes, don't use it in ADO.NET unless you know what you're getting into.

 

Answer 31

Please, someone, post this code  in visual basic.net. Not all of us use C#.

 

Answer 32

Hi,

I tried the code  published at the start of this thread, i.e. directly change  the connection  string by:

LoginAdapt.Connection.ConnectionString = ConnString

However, this worked if you stepped through the debugger, examining LoginAdapt but not if you ran the code.

I have now found the following VB.NET code works (at least for an Access database)

Imports Ole = System.Data.OleDb

Dim LoginAdapt As DALTableAdapters.LogonIDsTableAdapter

Dim Conn As Ole.OleDbConnection

Conn = New Ole.OleDbConnection

Conn.ConnectionString = ConnString

LoginAdapt.Connection = Conn

Hope this helps

Paul Ireland

 

Answer 33

I has having a simular issue but the link provided by NTDS totally helped out. In my code, if the prog is in debug mode I set it to use a local SQL server that I set up in My.Settings. Otherwise, just skip it and use the production version. Here's my code:

My.Settings.DB2008 is the connection  string to the production server

My.Settings.DB2008_Local is the connection to the local server

NamespaceMy

PartialFriendNotInheritableClass MySettings

Sub SetLocalMode(ByVal sender AsObject, ByVal e As System.Configuration.SettingsLoadedEventArgs) HandlesMyBase.SettingsLoaded

If System.Diagnostics.Debugger.IsAttached Then

Me.Item("DB2008") = My.Settings.DB2008_Local

EndIf

EndSub

EndClass

EndNamespace

 

Answer 34

I've solved this problem  with the following:

(1) Open the partial class  for the table adapter and expose the connection  property so it can be changed

(2) In your code, you will add  1 line of code  to change  the connection (not the connection string) on the Constructor() of the object  using the data

(3) Call the .Fill() or .FillByX() statement in the Object_Load() event

For example:

Code Block

// This code refereces Browsable(false)

// the property does not need to appear in the IDE properties

// since we are setting the property with code

// (1) Expose the connection property

partialclass AnyTableAdapter

{

[System.ComponentModel.Browsable(false)]

public System.Data.SqlClient.SqlConnection Connection

{

get

{

returnthis.Connection;

}

set

{

this.Connection = value;

}

}

}

Code Block

// This code references a class called GLOBAL

// Replace GLOBAL with your global or static class property that

// contains the current connection

publicpartialclassDocument : Form

{

public Document()

{

InitializeComponent();

// (2) Change the connection of the TableAdapter object generated  by VS2005

AnyTableAdapter.Connection = GLOBAL.Connection; // Any active data connection

}

privatevoid Document_Load(object sender, EventArgs e)

{

try

{

// This line of code loads data into the 'AnyDataSet.Any' table. You can move, or remove it, as needed.

// (3) Fill the table with your newly connected table adapter

this.AnyTableAdapter.Fill(this.AnyDataSet.Any);

}

.

.

.

}

.

.

.

}

 

Answer 35

Hello SobyOne,

The only disadvantage in your proposal is that you have to set the attribute "Connection" for each object  you create. It would be better to change  the connection  once for the whole data access layer. However, it's a pretty reasonable solution.

 

Answer 36

Noyabronok wrote:
After 5 hours of figuring out this stuff, I came up with this simple solution to update the app.config file at runtime  with new string  settings.  The only thing you have to know is the full name of the connection  string.

Hope this helps someone :-)


        public static void ChangeConnectionString(string strConn)
        {             
             Configuration _config = ConfigurationManager.OpenExeConfiguration(
                        ConfigurationUserLevel.None);

            //the full name of the connection string can be found in the app.config file
            // in the "name" attribute of the connection string
             _config.ConnectionStrings.ConnectionStrings[FULL NAME OF CONNECTION STRING HERE].ConnectionString = strConn;

            //Save to file
            _config.Save(ConfigurationSaveMode.Modified);

            //force changes to take effect so that we can start using
            //this new connection string immediately
             ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name);
            Properties.Settings.Default.Reload();
        }

VB Version to change  App.Exe.Config file:

Imports System.Configuration

PublicSharedSub ChangeConnectionString(ByVal strConn AsString)

Dim _config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

'Note: VB wants to use ConnectionStrings "Index number", and not name!

'Turns out Index 0 is "LocalSqlServer", but Index 1 was our connection string "Name" we wanted to change

'You might need to play with this...

_config.ConnectionStrings.ConnectionStrings(1).ConnectionString = strConn

'Save to file

_config.Save(ConfigurationSaveMode.Modified)

'force changes to take effect so that we can start using

'this new connection string immediately

ConfigurationManager.RefreshSection(_config.ConnectionStrings.SectionInformation.Name)

My.MySettings.Default.Reload()

EndSub

 

Answer 37

You are an absolute Star!!!

been looking for an 'elegant' solution for some time, in a lot of other areas no luck - but you set me on the path to the solution.

I use seperate projects for BL & DAL and these naturally compile the connection  string into the dll's at build time.

We however have to deploy our code  to TEST & QA servers before we hit Production. this also naturally means we hit different sql  servers at each stage as well.

Using your idea as a base, i now do the following:

in the DAL I add  a public property to hold the desired connection string.

in Global.asax in Application_Start I read the required context (DEV/QA/TEST/PROD)  and the associated connection string  from Web.Config and Set the Public prop(above)

Then when the XSD requests the Connstring your "fake" settings class  does it's job.

moving the DB connection to TEST for a Debug session is a simple as setting the key in web.config!

I owe you many many beers!

re the first post, it's not quantity, it's QUALITY Smile
 

Answer 38

I think someone else did something similar to this in one of the previous posts, but I'm not sure.

Here's the problem: Typed Datasets in VS 2005 use a read-only application  setting to establish the connection  string for all of it's tableadapters. We can't easily change  the setting because it is read only. We can use partial classes  for each and every table adapter or expose the Connection property for each table adapter and then every time we instantiate a table adapter, we can set the connection string. To me, this is not a great solution because I am bound to forget to set that everytime I use any table adapter.

If you look into the generated code  of the dataset designer, you'll see that each tableadapter is set to use the setting from my.settings. Honestly I have no idea why MS developed it this way. Why is the ConnectionString property read only? Why does it have to be an application setting and not a user  setting? Why not derive the connection string  from the dataset, and make that an alterable property? Well, who knows, that's microsoft for you. I guess they figured that nobody will ever need to change the connection string to a database  huh? 

So here is another possible solution that works, but to be honest I'm not 100% certain why it works.

We want to be able to set the connection string for all table adapters easily, and without having to remember to add  code for every new tableadapter we add to the dataset. We can't change the value of the setting in My.Settings. But, we can create a sub within our settings.vb file that can modify the application setting in memory.

This way, you can set your connection string application setting once and then all of the tableadapters will use that connection string. In this example I created two functions, one that accepts a string and one that accepts a connection object, but they both basically work the same way. Just access the connection string property using the Item or Default collection.

Code Snippet

NamespaceMy

PartialFriendNotInheritableClass MySettings

PublicSub SetConnectionString(ByVal oConn As System.Data.OleDb.OleDbConnection)

My.Settings.Item("LocalConnectionString") = oConn.ConnectionString

EndSub

PublicSub SetConnectionString(ByVal strConnection AsString)

My.Settings.Item("LocalConnectionString") = strConnection

EndSub

EndClass

EndNamespace

 

Answer 39

Thank you.

 

Answer 40

I have a 3-tier VS 2005 application. This is a problem  that I encountered. Depending on the user's region, he/she needs to access his/her region's database. (All the region's databases have the same table structure.)  One user  changing the setting/connection string  shouldn't affect all users of the application.  Any idea on how to do it?  Thanks for your hlep.
 

Answer 41

Sonic8888- Try any of the solutions posted in this thread. Any one of these approaches would work for you. The approach I listed above would work for you as well as the settings file is not actually changed, just the in memory collection for the settings is changed. Since the connection  string is an Application setting it cannot be changed and so it would not affect any of your users other than the one who is currently using the program.
 

Answer 42

Thanks a lot.  I will try it.
 

Answer 43

Lots of replies to this post... A very simple two step solution is:

edit Settings.cs class  file

partial class settings

{

// add  this property, can make it gettable too

publicstring SetmydatasetConnectionString

{

set

{

this["mydatasetConnectionString"] = value;

} // eos

} // eop

} // eoc

then in a startup location (such as ApplicationContext) do

public void SetGlobalDataSource(string new_datasource_path_and_filename)

{

string datasource = "Data Source=" + new_datasource_path_and_filename;

string conn = myApplicationObject.Properties.Settings.Default.mydatasetConnectionString;

conn = SetDataSource(conn, datasource); // swap out default data source for new data source

myApplicationObject.Properties.Settings.Default.SetmydatasetConnectionString = conn;

}

where

- new_datasource_path_and_filename is the full path and file name of the Access/Sql data file

- mydataset is the name of the generated  dataset (see the Settings.Designer.cs for actual name)

- myApplicationObject is the object  name of the application  (created by default)

privatestring SetDataSource(string connection, string datasource)

{

string result = "";

try

{

string[] elements = connection.Split(newchar[] { ';' });

for (int i = 0; i < elements.Length; i++)

{

if (elementsIdea.StartsWith("Data Source="))

{

elementsIdea = datasource; // swap in new datasource

break;

}

}

result = String.Join(";", elements);

}

catch (Exception)

{

result = connection; // no change

}

return result;

} // eom

Lance Kujala

Driftwood Technology

 

Answer 44

I have been fighting the same issue.  With the connection  string saved in plain  text in the config file, anyone could find it and gain access to my database. 

I tried Lance's post (above) and it worked flawlessly.  I have seen many attempts and all require much work and many modifications.  This is the only one that makes sense and doesn't save the information back to the config file.

THANKS LANCE!

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

Kyle

 

Answer 45

I was looking at the Settings.cs code  but I was not quite sure if making such a change  will work.
Thanks man !
 

Answer 46


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 Wink




Hi Vittorio,
this works fine for me. But I don't understood very well the rename of app.config.
Maybe the app.config it's needed if I want to modifier in design the dataset...I  guess... but i'll try.
IN my solution I have an assembly with some dataset...and now with  a setting at runtime  from another assembly I'm able to take the right connection!
Now i want to  try this on network , with domain etc...



 

Answer 47

After much searching, on how to dynamically  update the connection  string, or come to that ANY configuration value I have found the best solution is to override the settings namespace. This has the advantage that you can create design time values (in this example UxOnlineConnectionString, etc) allowing you to use the design tools, then at run-time, override the setting to provide a different value.

Sorry the example below is a "bit complicated". It uses my UxConfig assembly which returns configuration values from "elsewhere" which override the design time settings. I even have a "UxDynamConnectionString" can be repointed by the calling programme.

Using this code, I can use a single Design time connection string  in any of MS controls or code  that uses my.settings and it is overridden at run time.

I have found this solution both flexible and reliable, just a little difficult to debug.

Hope this helps

Paul

OptionStrictOn

OptionExplicitOn

NamespaceMy

PartialFriendNotInheritableClass MySettings

InheritsGlobal.System.Configuration.ApplicationSettingsBase

PublicEnum DynamSystem

DefForWebSite = 0

UxOnline = 1

UxIntranet = 2

EndEnum

Private ConfOverrides() AsString = { _

"UxOnlineConnectionString", _

"UxIntranetConnectionString", _

"UxDynamConnectionString" _

}

Private g_CurrSystem As DynamSystem = DynamSystem.DefForWebSite ' Sets dynmic configuration string

PrivateSub userOverride_SettingsLoaded(ByVal sender AsObject, ByVal e As System.Configuration.SettingsLoadedEventArgs) _

HandlesMe.SettingsLoaded

' Used to intercept requests for application  properties and if "ours" replace them with the

' system dependent ones

Dim appProperty AsString

Dim DefSystem AsString

Using Conf As UxConfig = New UxConfig

ForEach appProperty In ConfOverrides

SelectCase appProperty

Case"UxOnlineConnectionString"

' request for a connection string

Me(appProperty) = Conf.GetNetConnString(SystemName.Online)

Case"UxIntranetConnectionString"

' request for a connection string

Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)

Case"UxDynamConnectionString"

' use the connection string for the current dynamic system

SelectCase g_CurrSystem

Case DynamSystem.DefForWebSite

' Default for web site set to appropriate value

................

CallMe.userOverride_SettingsLoaded(sender, e)

Case DynamSystem.UxIntranet

Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)

Case DynamSystem.UxOnline

Me(appProperty) = Conf.GetNetConnString(SystemName.Online)

CaseElse

ThrowNew Exception("Unrecognised dynamic system: " & g_CurrSystem.ToString)

EndSelect

CaseElse

' request for a value

Me(appProperty) = Conf.GetNetValue(appProperty)

EndSelect

Next

EndUsing

EndSub

PrivateSub userOverride_SettingsSaving(ByVal sender AsObject, ByVal e As System.ComponentModel.CancelEventArgs) _

HandlesMe.SettingsSaving

' Settings are read only

EndSub

PublicSub SetDynamSystem(ByVal Sys As DynamSystem)

g_CurrSystem = Sys

EndSub

EndClass

EndNamespace

 

Answer 48

I think someone else did something similar to this in one of the previous posts, but I'm not sure.

Here's the problem: Typed Datasets in VS 2005 use a read-only application  setting to establish the connection  string for all of it's tableadapters. We can't easily change  the setting because it is read only. We can use partial classes  for each and every table adapter or expose the Connection property for each table adapter and then every time we instantiate a table adapter, we can set the connection string. To me, this is not a great solution because I am bound to forget to set that everytime I use any table adapter.

If you look into the generated code  of the dataset designer, you'll see that each tableadapter is set to use the setting from my.settings. Honestly I have no idea why MS developed it this way. Why is the ConnectionString property read only? Why does it have to be an application setting and not a user  setting? Why not derive the connection string  from the dataset, and make that an alterable property? Well, who knows, that's microsoft for you. I guess they figured that nobody will ever need to change the connection string to a database  huh? 

So here is another possible solution that works, but to be honest I'm not 100% certain why it works.

We want to be able to set the connection string for all table adapters easily, and without having to remember to add  code for every new tableadapter we add to the dataset. We can't change the value of the setting in My.Settings. But, we can create a sub within our settings.vb file that can modify the application setting in memory.

This way, you can set your connection string application setting once and then all of the tableadapters will use that connection string. In this example I created two functions, one that accepts a string and one that accepts a connection object, but they both basically work the same way. Just access the connection string property using the Item or Default collection.

Code Snippet

NamespaceMy

PartialFriendNotInheritableClass MySettings

PublicSub SetConnectionString(ByVal oConn As System.Data.OleDb.OleDbConnection)

My.Settings.Item("LocalConnectionString") = oConn.ConnectionString

EndSub

PublicSub SetConnectionString(ByVal strConnection AsString)

My.Settings.Item("LocalConnectionString") = strConnection

EndSub

EndClass

EndNamespace

This is a great solution Josh, many thanks!

I have a simple wizard-type form application written in VS 2008 Express that was originally created to connect to a single (SQL Express) database - therefore, the dreaded table adaptors were used for quickness!

However, several new databases (SQL Server 2005) have since been created and so the application needs the functionality to connect to any of them based upon which one a user selects from a dynamically  populated combo on the initial form.

I had a real nightmare trying to amend the app.config file programmatically and was about to give  up and rework the app by using SQLClient connectors - however, Josh's VB solution above has resovled the problem  perfectly.

To make the solution work, I simply define a global string variable that receives the database name that the user selects in the combo-box, and then I call the SetConnectionString Sub and pass the selected database name as a string.

The result - all table adaptors now retrieve data from the user-selected database and everything works perfectly! :-)

For reference, here is my code snippet:

Private Sub Fm1NxtBt_Click(ByVal sender As System.Object, 
ByVal e As System.EventArgs) Handles Fm1NxtBt.Click 'Write selected database into Global var for connection on next page SelectDB = ComboBox1.SelectedItem 'Call Sub to set DB Connection String for all bound table adaptors
by passing selected DB Name to Sub Call My.Settings.SetConnectionString(SelectDB) End Sub Namespace My Partial Friend NotInheritable class  MySettings Public Sub SetConnectionString(ByVal strConnection As String) My.Settings.Item("DevConnectionString") =
"Data Source=192.168.22.101;Initial Catalog=" & strConnection &
";Integrated Security=True" End Sub End Class End Namespace


Keep up the good work guys!

Cheers,

Steve
 

Answer 49

I have a client app that consumes a web service that accesses an Oracle database. I have several table adapters in few different projects in the same web service solution.  I wanted to be able to change  only one place my connection  string for deployment of the web service (to point to different instances of the database).  The solution I got is rather simple and is very easy  to implement.

Similar to Cedric Boivin's proposed solution, this is what I did:

1. In my web.config (of my web service of course) I added a section for the connection string:
<connectionStrings>
      <add name="ConnectionString"
             connectionString="DATA SOURCE=ORCL;USER ID=uuuu;PASSWORD=pppp"
             providerName="Oracle.DataAccess.Client" />
  </connectionStrings>


2. For EACH project  in my solution I added a line like this on the Settings.cs file (see Cedric's code  for the place to insert the line):

this["ConnectionString"] = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 

Make sure that the name of the connection string  used in the web.config and the Settings.cs match.

This has to be done only once per project, as every table adapter on the project (even the ones created after this is implemented) will pick up the "ConnectionString" connection string at runtime. 

NOTE: To change the Settings.cs file see https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

3. Done.  I did not have to change client app at all.

So now, I only change the web.config file to change the connection string (to point to test, qa, prod, etc.). 

Juan Camaney
 

Answer 50

Karamasov, This is just 4 years late, but this might work for you: http://rajmsdn.wordpress.com
:-)

 

Answer 51

What's the difference between "web.config" and "app.config" ? and where & Inwhich purposes they used?
 

Answer 52

This is a great solution Josh, many thanks!

I have a simple wizard-type form application  written in VS 2008 Express that was originally created to connect to a single (SQL Express) database  - therefore, the dreaded table adaptors were used for quickness!

However, several new databases (SQL Server 2005) have since been created and so the application needs the functionality to connect to any of them based upon which one a user  selects from a dynamically  populated combo on the initial form.

I had a real nightmare trying to amend the app.config file programmatically and was about to give  up and rework the app by using SQLClient connectors - however, Josh's VB solution above has resovled the problem  perfectly.

To make the solution work, I simply define a global string  variable that receives the database name that the user selects in the combo-box, and then I call the SetConnectionString Sub and pass the selected database name as a string.

The result - all table adaptors now retrieve data from the user-selected database and everything works perfectly! :-)

For reference, here is my code  snippet:

Private Sub Fm1NxtBt_Click(ByVal sender As System.Object, <br/>
ByVal e As System.EventArgs) Handles Fm1NxtBt.Click

'Write selected database into Global varfor  connection  on next page
SelectDB = ComboBox1.SelectedItem

'Call Sub to set DB Connection String for all bound table adaptors <br/>
by passing selected DB Name to Sub
Call My.Settings.SetConnectionString(SelectDB)

End Sub

Namespace My

Partial Friend NotInheritable class  MySettings

Public Sub SetConnectionString(ByVal strConnection As String)

My.Settings.Item("DevConnectionString" ) = <br/>
"Data Source=192.168.22.101;Initial Catalog=" & strConnection &<br/>
";Integrated Security=True"

End Sub

End Class

End Namespace

Keep up the good work guys!

Cheers,

Steve

Worked like a charm; however, using:

 

ConfigurationManager.ConnectionStrings("ConName").ConnectionString

 

would still resolve as the original text in the app.config file. You have to use:

 

My.Settings.ConName

 

 

Thanks to all contributors!

 

 

Answer 53

I think that if you changed the Type from Connection string  to just plain  string, you can change  the scope to User and then it should work. You might have to remove surrounding XML tags.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter