Share via

Changing the connection string for typed DataSets

  • Article

If you're still developing new code using typed DataSet, you may have run into the situation where you want to read connection information at runtime, maybe from a centralized configuration file, or perhaps by simply asking the user.

The place where the connection information comes into place is in the generated table adapters. The adapters are configured to read the default connection information from the project settings anytime they need a connection.

So there are really two ways of programmatically changing this at runtime: you can either change the Connection property on the adapters, or you can change the value directly on the project settings.

The following bit of sample code shows how to change this information on a generated dataset. Just so you can cross-reference the types with your project, the dataset name is DataSet1, the dataset has a single table called Documents, and the connection string is DistXsltDbConnectionString (from a long-forgotten sample I had on my machine).

DataSet1 ds;
DataSet1TableAdapters.DocumentsTableAdapter adapter;
string desiredConnectionString;

// Create an adapter and verify it's reading the default settings.
adapter = new DataSet1TableAdapters.DocumentsTableAdapter();
MessageBox.Show("Connection from settings: \r\n" +
    Properties.Settings.Default.DistXsltDbConnectionString);
MessageBox.Show("Original connection: \r\n" +
    adapter.Connection.ConnectionString);

// Customize the connection string.
var builder = new SqlConnectionStringBuilder(
    Properties.Settings.Default.DistXsltDbConnectionString);
builder.DataSource = builder.DataSource.Replace(".", Environment.MachineName);
desiredConnectionString = builder.ConnectionString;

// Set it directly on the adapter.
adapter.Connection.ConnectionString = desiredConnectionString;
MessageBox.Show("Modified connection: \r\n" +
    adapter.Connection.ConnectionString);

ds = new DataSet1();
adapter.Fill(ds.Documents);
MessageBox.Show("Row count: " + ds.Documents.Rows.Count);

// Or change the setting directly before creating the adapter:
Properties.Settings.Default["DistXsltDbConnectionString"] = desiredConnectionString;
adapter = new DataSet1TableAdapters.DocumentsTableAdapter();
MessageBox.Show("Modified setting connection: \r\n" +
    adapter.Connection.ConnectionString);

ds = new DataSet1();
adapter.Fill(ds.Documents);
MessageBox.Show("Row count: " + ds.Documents.Rows.Count);

Enjoy!

Comments

  • Anonymous
    September 23, 2016
    The information herein was very helpful, however i was able to overcome the challenge by modifying the connection string from the application setting, that was all.Thanks for the info.