468,273 Members | 2,166 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,273 developers. It's quick & easy.

Changing a DataAdapter connection

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
Jul 21 '05 #1
3 3499
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

Jul 21 '05 #2
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
Jul 21 '05 #3
"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;

}
Jul 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by TJ Doherty | last post: by
3 posts views Thread by Stephen Noronha | last post: by
13 posts views Thread by Doug Bell | last post: by
3 posts views Thread by G-Fit | last post: by
5 posts views Thread by Emil | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.