Best way to set strongly typed dataset connection string at runtime?

Question

My Windows Forms application uses a strongly typed dataset created using the designer in Visual Studio. At runtime I would like to be able to select either the live or test database.

What is the best way to programmatically set the connection string for the dataset at runtime?

Caracole

As of VS2010 the visibility of the Connection can be specified (e.g. public) and a Base Class for the DataTableAdapters generated can specified... (that is, this post is mostly related to VS2008 and prior issues :-) – Microcopy

Answer 1

Connection property in TableAdapters is defined as internal.

internal global::System.Data.SqlClient.SqlConnection Connection

So in case your TypedDataset is not in the same assembly as your main windows forms app, you will not be able to access Connection property. This problem might popup later when you refactor your dataset code and move it into a seperate project which will produce its own independant assembly.

To solve this problem, you can do as mentioned below.

create partial class for your TableAdapter and add another constructor beside the default public parameterless constructor. Assuming TableAdapter type as MyTableAdapter

public partial class MyTableAdapter
{
    public MyTableAdapter(SqlConnection connection)
    {
        thisSetConnection(connection);
        this.ClearBeforeFill = true;
    }

    public void SetConnection(SqlConnection connection)
    {
        this._connection = connection;
    }
}

You will need to do this for as many as TableAdapters you have in your project. TableAdapter does not have any common base class but thanks that they are declared as partial classes so we are able to do it the way mentioned above.

Now at runtime, you can create an instance of your TableAdapter like this..

SqlConnection connection;
//create the connection here at runtime..
MyTableAdapter adapter = new MyTableAdapter(connection);

or may be even assign it later after you create the TableAdapter instance with default parameterless public constructor..

SqlConnection connection;
//create the connection here at runtime..
MyTableAdapter adapter = new MyTableAdapter();
adapter.SetConnection(connection);

Psid

Answer 2

By default the Connection property is set to be internal. This can be changed in the DataSet's designer.

1. Right-click the TableAdapter.

2. Then change the ConnectionModifier property to public.

3. You can now access the Connection property in your project.

var loginsTableAdapter = new MyDataSetTableAdapters.LoginsTableAdapter();
loginsTableAdapter.Connection.ConnectionString = _myConnectionString;

Truesdale

Answer 3

Store connection strings for them both in an app.config and then you can switch based on a command line / start up switch. Or if you want to give the user the flexibility you could give them an options page where they can select which connection to use.

Below is the code to read a start-up switch:

string[] args = Environment.GetCommandLineArgs();
// The first (0 index) commandline argument is the exe path.
if (args.Length > 1)
{
    if (Array.IndexOf(args, "/live") != -1)
    {
        // connection string = 
        // ConfigurationSettings.AppSettings["LiveConString"];
    }
}
else
{
    // connection string = 
    // ConfigurationSettings.AppSettings["TestConString"];
}

So now you start your app by calling:

MyApp.exe /live

Using MyApp.exe alone or with any other switch will get you the test configuration.

Katiakatie

Thanks for your response, but my question is how can you set the connection string of a strongly typed dataset at runtime. – Caracole

Answer 4

Re: wethercotes comment

The wizard stores the connection string when you set up the dataset, but that doesn't mean you can't make it dynamic. How depends on which version you are using, but in general if you expand the files under your dataset you will find a file like Designer.cs, or DataTableNameAdapter.xsd. You can open those files and search for _connection. This is usually a private variable and is set in an init function in the class.

You can make the setting dynamic by adding code like the following:

public string ConnectionString
{
    get { return this._connection.ConnectionString; }
    set
    {
        if (this._connection == null)
        {
            this._connection = new System.Data.SqlClient.SqlConnection();
        }
        this._connection.ConnectionString = value;
    }
}

Note that if you regenerate the dataset you will likely lose this section of code, and without refactoring the dataset you may have to add it to several objects.

Katiakatie

Thanks again Gary. I've created a partial class with your code in it which stops it being lost when the dataset is regenenerated. Unfortunately this has to be done for each data adaptor, which I have dozens of. – Caracole

Answer 5

It's a pain to edit the designer file.

I created a Settings entry under "User' called 'ConnectionString', which makes Visual Studio create an application string 'Connection String1' when you add a strongly typed data set.

So, I just replace all 'ConnectionString1' with 'ConnectionString' in the dataset designer file, and that will allow you to use a 'User' string setting to load your connection string at runtime.

IMHO it's a shortcoming allowing users to modify connection strings at runtime. (Anyone listening in Redmond?)

Cosmogony

Answer 6

So all these years later, I was having this problem with someone else's very legacy code from 2009, and a truck load of table adapters.

I went with extending Settings in a partial class and changing the connection string in the OnSettingsLoaded event.

namespace LegacyProgram.Properties
{
    using System.Configuration;

    internal sealed partial class Settings
    {

        protected override void OnSettingsLoaded(object sender, SettingsLoadedEventArgs e)
        {
            base.OnSettingsLoaded(sender, e);
            SetConnectionString(App.Settings.ConnectionString);
        }

        internal void SetConnectionString(string value)
        {
            this["ConnectionString"] = value;
        }     
    }
}

In my case, I have added a static "App.Settings" that loads from JSON instead of ye olde exe.config, and the connection string is actually being built using a SqlConnectionStringBuilder.

Kagoshima

Answer 7

Using the TableAdapterManager might work for you. Please read more at: http://rajmsdn.wordpress.com/2009/12/09/strongly-typed-dataset-connection-string/

Antlion

Answer 8

Best Solution I have found so far:

Add another program setting which holds your preffered connection string as set by the client at runtime (eg. newConnectionString)

then before using the Table Adapter:

this.myTableAdapter.Connection.ConnectionString = Properties.Settings.Default.newConnectionString;

Scutter

No you cannot do it this way. Connection property in TableAdapters is defined as internal. – Psid