Set connection string at run time for typed DataSet (Framework 2.0

[Guest]

I have typed DataSets in a data access tier and want to pass a connection
string from the client at run time (for use by the TableAdapters created
using VS2005). However, at design time, VS2005 sets the connection string as
a property (i.e., serialized to app.config).

This is ok for design time, but at run time, I need to pass the connection
string from the client. Also, I don't want an app.config in my data layer,
and the property that VS creates is read-only, so I don't see a natural way
to set this at run time.

Setting a single static property (similar to what VS does with the
app.config setting) seems like a fine way to keep the connection string, but
I'd like to be able to configure the DataSet (TableAdapters) to use a public
static property that I create instead of forcing me to use app.config.

Am I missing something? Is there a good way to handle this without mucking
around with the code that VS generates for the typed dataset?

 

[Kevin Yu [MSFT]]

Hi Thomas,

As far as I know, if you don't want the app.config file in the data access
layer, the only way is to set the connection string of the TableAdapter by
expanding the TableAdapter class in the DataSet. You needn't modify the
code that the designer has generated. If you double click the designer, it
will create a DataSet1.cs file. You can add your own code in the partial
class and make a public property to assign ConnectionString to it.

One thing that you need to notice is, you must specify the ConnectionString
before using the TableAdapter.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thanks Kevin. I was hoping to avoid having to reset the connection string
for every TableAdapter.

Here's a workaround that I am considering:

(1) Modify the generated settings code by adding this partial class like this:

namespace MyRootName.Data.Properties
{
internal sealed partial class Settings
{
internal void SetConnectionString(string connectionString)
{
this["DbConnectionString"] = connectionString;
}
}
}

This gets around the read-only characteristic of the generated setting
property.

(2) Add a public method to allow calling this internal code:

namespace MyRootName.Data
{
public static class App
{
public static void SetConnectionString(string connectionString)
{

MyRootName.Data.Properties.Settings.Default.SetConnectionString(connectionString);
}
}
}

(3) From the client, pass the connection string to the data layer:

MyRootName.Data.App.SetConnectionString(MyRootName.Client.Properties.Settings.Default.DbConnectionString);

This seems to work ok, but there will still be an unnecessary app.config
file hanging around at runtime, and I'm wondering if I will ever run into
trouble as a result of bypassing the read-only characteristic of the
generated property.

If anyone has further thoughts or suggestions, they would be appreciated.

 

[Kevin Yu [MSFT]]

Hi Thomas,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Sam]

I am experiencing some difficulty in this area as well. When the
designer sets a connectionstring for a tableadapter or queries it
places the value within settings.settings file. It can be overridden
in app.config, but these do not always sync up. Have 2 copies around
makes things confusing at least.

My main problem is that I don't want to use settings.settings at all,
I'd like to keep the connectionstring within app.config like we used
to. How can we stop the designer from forcing it upon
settings.settings each time? With 20 projects in a solution that all
have their own settings files it gets confusing.

Thx,
Sam

 

[Guest]

This is exactly what we are trying to do as well. We also have a large
number of table adapters that we all need to bankswitch to another setting.
Can someone advise how this is really done? We don't get the connection
string setting from runtime.

Our Data Access Layer.dll is in a web service's bin/ directory. Simply
putting DataAccessLayer.dll.config in the directory, changing the value of
the connection string, and restarting doesn't to help.

 

[Guest]

Phew. We dodged that bullet. It turns out that you can just take your DLL's
App.config file, extract the <connectionStrings> section that VS has created
for you, and drop that in the <connectionStrings> section of your
Web.config,or your application's config file. Apparently, VS only bakes the
connection string into the dll as a resource in case it is not present in the
application settings. This helped us out enormously.