Switching between databases in WinForms

Neven Draskovic

14y3.6kSQL

Hey

I'm about to start a pretty large project, so I'm gathering informations about some problems that I predicted while reading project documentation and client requests. One of those problems is this:
My client is a company that has multiple subdivisions, and each of those subdivisions uses it's own database. Now, I've my task is to make an administration application that all of those subdivisions will be using (they all perform the same tasks, they are just divided because of their geographical locations and some legal regulations that are not important for my question). So, my problem is how to make an application that will be able to switch between databases. Usually, I could do it by creating a WinForm that would contain fields for input (e.g textBoxes or comboBoxes) where user could choose which connection to use, but I have a problem with this approach because of reports that these applications have to generate. Up until now I've been working with .rdlc reports that use datasets created by TableAdapters. So if I create a dataset programmaticaly, I don't know how to generate a report. But, if I create report based on a TableAdapters dataset, I don't know how to change TableAdapters connection string (except for duplicating TableAdapters so each one uses another connection string but that is not efficient at all). Please help me with this problem?
Thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
Remove the Open; do not open the TableAdapter; it will be opened automatically. If you must do the open explicitly, do the open after the ChangeDatabase.
Neven Draskovic14y ago
I found that method, and tried to use it but no luck so far - the application shows that the database is changed but the data displayed in the report remains the same. This is the code I use:

KontrolorRadTableAdapter.Connection.ConnectionString = "Data Source=mySource;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=user;Password=password";
KontrolorRadTableAdapter.Connection.Open();
KontrolorRadTableAdapter.Connection.ChangeDatabase("MyDatabase");

textBox4.Text = KontrolorRadTableAdapter.Connection.Database;
KontrolorRadBindingSource.ResetBindings(true);
KontrolorRadTableAdapter.Fill(PrincMarpromDataSet.KontrolorRad);
ReportDataSource RDS = new ReportDataSource();
RDS.Name = "KontrolorRad";
RDS.Value = this.KontrolorRadBindingSource;
this.reportViewer1.LocalReport.DataSources.Add(RDS);
this.reportViewer1.LocalReport.ReportEmbeddedResource = "Kontrolor.Izvjestaji.KontrolorRad.rdlc";
this.reportViewer1.RefreshReport();
this.reportViewer1.Refresh();
AktivnaBaza.Text = "Kontrolor rad";
NazivAktivneBaze = "KontrolorRad";

This code is placed inside a button_Click method, and when the button is clicked the textBox shows that the Database is the one I want, but the data in the report stays the same. Do you know what is causing that?

Also, I found out that if I change the connection string in app.config, it changes all of the data I want. This seems as a perfect solution because I don't have to write a lot of code to change what I need. Do you know how can I do that. My idea is this: Make an independent application (lets call it application A) that would construct the connection string (by using textboxes or comboboxes) and then, by clicking on a button, change the app.config of the application B (the one doing all the work). Is this plausible and, if yes, how would I do it?
Sam Hobbs14y ago
TableAdapters can be frustrating since they are generated code and there is very little documentation of the code.

When a TableAdapter is used, if the connection is not already open then it is opened and closed by whatever operation is to be performed. Normally we do not do the open ourselves. So I assume we can change the connection most any time and if the modified connection is compatible with the TableAdapter then it should work.

Note that TableAdapters have a "Connection" property that is a SqlConnection. A SqlConnection has a ChangeDatabase Method. I think it is likely you can use that to change the database, assuming that each database loooks the same except for the name.