Changing a DataAdapter connection

[G-Fit]

Hello group,

I have several servers hosting SQL databases. On each of them, I have
several databases. All those databases have the same structure (even those
on different servers), only the data changes.
I made a winforms application that allows me to manage those databases
easily, and used several SqlDataAdapter to achieve this, all of them filling
the same Dataset which has relations, and this Dataset being the DataSource
of a DataGrid. I can then play with the data and hit my "Save" button to do
an Update on all the DataAdapters.
I have a ComboBox that let me chose the server, and another one for the
database (user ID and password are the same everywhere).

I first made my DataAdapters in design mode, because I can just ask for a
"SELECT *" and all the long code for inserting/updating/deleting will be
written for me. At this step, I had to provide an SqlConnection and chose
one of the database on one of the servers. All works fine.

Now I'd like to take my two comboboxes into account, and change the
connection used by the DataAdpaters. Is this possible ?
Here is what is called when either combobox changes (connection is a private
member of the Form):

public void cbb_SelectedIndexChanged(object sender, System.EventArgs e)

{

if (cbbServer.SelectedIndex > -1 && cbbDatabase.SelectedIndex > -1)

connection = new SqlConnection("User ID='sa';Password='';Server='" +
cbbServer.Items[cbbServer.SelectedIndex].ToString() +

"';Database='" + cbbDatabase.Items[cbbDatabase.SelectedIndex].ToString() +
"'");

dataset = new DataSet();

dataset.Tables.Add("T1");

dataset.Tables.Add("T2");

dataset.Tables.Add("T3");

dataset.Tables.Add("T4");

connection.Open();

adapter1.Fill(dataset, "T1");

adapter2.Fill(dataset, "T2");

adapter3.Fill(dataset, "T3");

adapter4.Fill(dataset, "T4");

connection.Close();

dataset.Relations.Add("R1-2", dataset.Tables["T1"].Columns["T1_ID"],
dataset.Tables["T2"].Columns["T2_ID_T1"]);

dataset.Relations.Add("R2-3", dataset.Tables["T2"].Columns["T2_ID"],
dataset.Tables["T3"].Columns["T3_ID_T2"]);

dataset.Relations.Add("R3-4", dataset.Tables["T3"].Columns["T3_ID"],
dataset.Tables["T4"].Columns["T4_ID_T3"]);

dgModeles.DataSource = dataset.Tables[0];

}

Karine Proot

G-Fit

[Miha Markic [MVP C#]]

Hi,

Yes, it is possible.
Assign new connection to adapter's command instances (SelectCommand, ...).

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"G-Fit" <ms**@g-fit.fr> wrote in message
news:uj*************@TK2MSFTNGP11.phx.gbl...

Hello group,

I have several servers hosting SQL databases. On each of them, I have
several databases. All those databases have the same structure (even those
on different servers), only the data changes.
I made a winforms application that allows me to manage those databases
easily, and used several SqlDataAdapter to achieve this, all of them filling the same Dataset which has relations, and this Dataset being the DataSource of a DataGrid. I can then play with the data and hit my "Save" button to do an Update on all the DataAdapters.
I have a ComboBox that let me chose the server, and another one for the
database (user ID and password are the same everywhere).

I first made my DataAdapters in design mode, because I can just ask for a
"SELECT *" and all the long code for inserting/updating/deleting will be
written for me. At this step, I had to provide an SqlConnection and chose
one of the database on one of the servers. All works fine.

Now I'd like to take my two comboboxes into account, and change the
connection used by the DataAdpaters. Is this possible ?
Here is what is called when either combobox changes (connection is a private member of the Form):

public void cbb_SelectedIndexChanged(object sender, System.EventArgs e)

{

if (cbbServer.SelectedIndex > -1 && cbbDatabase.SelectedIndex > -1)

connection = new SqlConnection("User ID='sa';Password='';Server='" +
cbbServer.Items[cbbServer.SelectedIndex].ToString() +

"';Database='" + cbbDatabase.Items[cbbDatabase.SelectedIndex].ToString() +
"'");

dataset = new DataSet();

dataset.Tables.Add("T1");

dataset.Tables.Add("T2");

dataset.Tables.Add("T3");

dataset.Tables.Add("T4");

connection.Open();

adapter1.Fill(dataset, "T1");

adapter2.Fill(dataset, "T2");

adapter3.Fill(dataset, "T3");

adapter4.Fill(dataset, "T4");

connection.Close();

dataset.Relations.Add("R1-2", dataset.Tables["T1"].Columns["T1_ID"],
dataset.Tables["T2"].Columns["T2_ID_T1"]);

dataset.Relations.Add("R2-3", dataset.Tables["T2"].Columns["T2_ID"],
dataset.Tables["T3"].Columns["T3_ID_T2"]);

dataset.Relations.Add("R3-4", dataset.Tables["T3"].Columns["T3_ID"],
dataset.Tables["T4"].Columns["T4_ID_T3"]);

dgModeles.DataSource = dataset.Tables[0];

}

Karine Proot

G-Fit

[Cor Ligthert]

Hi Karine,

Why not have a look at this page

http://msdn.microsoft.com/library/de...ctortopic3.asp

If it is not clear ask again?

Cor

[G-Fit]

"Miha Markic [MVP C#]" <miha at rthand com> a écrit dans le message de
news:OY**************@TK2MSFTNGP11.phx.gbl...

Hi,

Yes, it is possible.
Assign new connection to adapter's command instances (SelectCommand, ...).

"Cor Ligthert" <no**********@planet.nl> a écrit dans le message de
news:eZ****************@TK2MSFTNGP09.phx.gbl... Hi Karine,

Why not have a look at this page

http://msdn.microsoft.com/library/de...ctortopic3.asp
Ah, thanks. I was looking for a property like DataAdapter.Connection, and
didn't see it was its commands which had it. My bad.
Works great now !

Solution below :
Just added in my "if" block :

if (cbbServer.SelectedIndex > -1 && cbbDatabase.SelectedIndex > -1)

{

connection = new SqlConnection("User ID='sa';Password='';Server='" +
cbbServer.Items[cbbServer.SelectedIndex].ToString() + "';Database='" +
cbbDatabase.Items[cbbDatabase.SelectedIndex].ToString() + "'");

adapter1.SelectCommand.Connection = connection;

adapter1.InsertCommand.Connection = connection;

adapter1.UpdateCommand.Connection = connection;

adapter1.DeleteCommand.Connection = connection;

adapter2.SelectCommand.Connection = connection;

adapter2.InsertCommand.Connection = connection;

adapter2.UpdateCommand.Connection = connection;

adapter2.DeleteCommand.Connection = connection;

adapter3.SelectCommand.Connection = connection;

adapter3.InsertCommand.Connection = connection;

adapter3.UpdateCommand.Connection = connection;

adapter3.DeleteCommand.Connection = connection;

adapter4.SelectCommand.Connection = connection;

adapter4.InsertCommand.Connection = connection;

adapter4.UpdateCommand.Connection = connection;

adapter4.DeleteCommand.Connection = connection;

}