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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

answer removed...

Answer 5

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;

Answer 6

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.(connection);