Same DataSet... Different ConnectionString 1

[jv6886]

In building a simple C# application, I used the built-in tools on the DataSources tab to build a DataSet. We have three SQL Servers... development, model, and production.

I was wondering if it's possible to use the same DataSet, but just change the connection string, depending on which SQL server I want to hit against? If not, what might be a better solution for a quick-and-dirty data-bound form?

Thanks for your help!
wcprog

 

[Zathras]

This works for me... I have to connect to different databases but all of them have the same table schema

mydataConnection.Close();
mydataConnection.ConnectionString = connectionString2;
mydataConnection.Open();

(Not the actual code, simplified for illustration.)

 

[jv6886]

Zathras,

Thank you for your response.

I could understand how to apply your answer if I had created the connection and the dataset manually. However, I created the dataset using the "Datasource Configuration Wizard" from the "Data Sources" tab, and so all of the DataSet code is generated.

I'm not sure how to apply it under these circumstances.

Thanks,
wcprog

 

[Zathras]

If I knew the name of your datasource, I could tailor my answer to your situation.

Let's just assume the name is "Charlie"

If you look in the Solution Explorer you should find an entry called "Charlie.xsd" Expanding that you should find a file called "Charlie.Designer.cs" In there you can find the namespace that defines the table adapters where you will find the definiton for a table adapter for each table you specified in the datasource.

In the start-up code for my application, I create a table adapter for each table I need to reference. Something like this: (code changed to protect the complicated)

Charlie charlieDataSet = new Charlie();
SqlConnection charlieConnection = new SqlConnection();
accountsTableAdapter accountsTA = new accountsTableAdapter();
storesTableAdapter storesTA = new storesTableAdapter();
// etc.
accountsTA.Connection = charlieConnection;
storesTA.Connection = charlieConnection;
// etc.
charlieConnection.ConnectionString = "Data Source=.....";
charlieConnection.Open();

Of course, there is a reference to CharlieTableAdapters in the using section of the code.

Then when I need to connect to a specific database:

charlieConnection.Close();
charlieConnection.ConnectionString = connectionString2;
charlieConnection.Open();

I need to switch databases in the middle of my processing. If you only have to decide at the start which database to use, your code can be a bit simpler.

 

[jv6886]

Hi Zathras,

Thanks again for your response.

The only places that I'm finding the SqlConnection being set is in the various TableAdapter classes. These each appear to be drawing their values from a ConnectionString value in the Application Settings individually. I'm not finding any central place in the app that the connection string is set apart from that Application Setting.

I tried the idea of modifying the connection string in the Application Settings, but, apparently Application-scope settings cannot be modified at runtime.

I'm experimenting right now with making the setting User-scope which can be modified at runtime. But in order to do that, I have to make the Type string, instead of (Connection string). I'm not sure, yet, if that will cause any problems.

Thanks,
wcprog

 

[Zathras]

As I indicated, you can change the Table Adapter .Connection property at run time. I'm not modifying the Application settings, just ignoring them.

 

[jv6886]

Okay. I see where you're coming from. I'll give it a try.

Thanks again.
wcprog