How to let user select a database connection?

[Edwin Smith]

Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow the
user to select the database. We have 2 different databases with the same
schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am very
new to VS2005 so please forgive me for being naïve.

Thanks

Edwin

[=?Utf-8?B?U29tIE5hdGggU2h1a2xh?=]

<connectionStri ngs>
<clear/>
<add name="Adventure WorksString"
providerName="S ystem.Data.SqlC lient"
connectionStrin g="Data Source=localhos t;
Initial Catalog=Adventu reWorks; Integrated Security=true"/>
</connectionStrin gs>

individualSetti ngs=configurati onManager.Conne ctionStrings[0];

DbConnection MyConnection = null;
switch (individualSett ings.ProviderNa me)
{
case "System.Data.Sq lClient":
MyConnection = new SqlConnection(i ndividualSettin gs.ConnectionSt ring);
break;
case "System.Data.Or acleClient":
MyConnection = new
OracleConnectio n(individualSet tings.Connectio nString);
break;
case "System.Data.Ol eDb":
MyConnection = new OleDbConnection (individualSett ings.Connection String);
break;
case "System.Data.Od bc":
MyConnection = new OdbcConnection( individualSetti ngs.ConnectionS tring);
break;
}

what u need to do in app.cnfig use connection string and based on provider
name u can change the connection type u also need to initialize coomand and
other object u r using.

now when u change the connection string in app.config it generate the
connection, command object based on coonection string.

"Edwin Smith" wrote:

Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow the
user to select the database. We have 2 different databases with the same
schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am very
new to VS2005 so please forgive me for being naïve.

Thanks

Edwin

[sloan]

Just to add a little extra info to the already good reply:
{
ConnectionStrin gSettingsCollec tion connectionStrin gs =
ConfigurationMa nager.Connectio nStrings;

ConnectionStrin gSettings connection;
foreach ( connection in connectionStrin gs) {

string connectionStrin gName = connection.Name ;
string connectionStrin g = connection.Conn ectionString;
string providerName = connection.Prov iderName;

Debug.Print(con nectionStringNa me);
}

this.GridView1. DataSource = connectionStrin gs;
this.GridView1. DataBind();
}

You can bind a grid or anything to these objects above, and get a "picker".
"Som Nath Shukla" <So***********@ discussions.mic rosoft.comwrote in message
news:19******** *************** ***********@mic rosoft.com...

<connectionStri ngs>
<clear/>
<add name="Adventure WorksString"
providerName="S ystem.Data.SqlC lient"
connectionStrin g="Data Source=localhos t;
Initial Catalog=Adventu reWorks; Integrated Security=true"/>
</connectionStrin gs>

individualSetti ngs=configurati onManager.Conne ctionStrings[0];

DbConnection MyConnection = null;
switch (individualSett ings.ProviderNa me)
{
case "System.Data.Sq lClient":
MyConnection = new
SqlConnection(i ndividualSettin gs.ConnectionSt ring);
break;
case "System.Data.Or acleClient":
MyConnection = new
OracleConnectio n(individualSet tings.Connectio nString);
break;
case "System.Data.Ol eDb":
MyConnection = new
OleDbConnection (individualSett ings.Connection String);
break;
case "System.Data.Od bc":
MyConnection = new
OdbcConnection( individualSetti ngs.ConnectionS tring);
break;
}

what u need to do in app.cnfig use connection string and based on provider
name u can change the connection type u also need to initialize coomand
and
other object u r using.

now when u change the connection string in app.config it generate the
connection, command object based on coonection string.

"Edwin Smith" wrote:

>Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow
the
user to select the database. We have 2 different databases with the same
schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am
very
new to VS2005 so please forgive me for being naïve.

Thanks

Edwin

[Andrew Faust]

You can make it even easier by using the DbProviderFacto ry class.

individualSetti ngs=configurati onManager.Conne ctionStrings[0];

DbProviderFacto ry factory =
DbProviderFacto ries.GetFactory (individualSett ing.ProviderNam e);
DbConnection MyConnection =
factory.CreateC onnection(indiv idualSettings.C onnectionString );

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com
"Som Nath Shukla" <So***********@ discussions.mic rosoft.comwrote in
message news:19******** *************** ***********@mic rosoft.com...

<connectionStri ngs>
<clear/>
<add name="Adventure WorksString"
providerName="S ystem.Data.SqlC lient"
connectionStrin g="Data Source=localhos t;
Initial Catalog=Adventu reWorks; Integrated Security=true"/>
</connectionStrin gs>

individualSetti ngs=configurati onManager.Conne ctionStrings[0];

DbConnection MyConnection = null;
switch (individualSett ings.ProviderNa me)
{
case "System.Data.Sq lClient":
MyConnection = new
SqlConnection(i ndividualSettin gs.ConnectionSt ring);
break;
case "System.Data.Or acleClient":
MyConnection = new
OracleConnectio n(individualSet tings.Connectio nString);
break;
case "System.Data.Ol eDb":
MyConnection = new
OleDbConnection (individualSett ings.Connection String);
break;
case "System.Data.Od bc":
MyConnection = new
OdbcConnection( individualSetti ngs.ConnectionS tring);
break;
}

what u need to do in app.cnfig use connection string and based on
provider
name u can change the connection type u also need to initialize coomand
and
other object u r using.

now when u change the connection string in app.config it generate the
connection, command object based on coonection string.

"Edwin Smith" wrote:

>Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow
the
user to select the database. We have 2 different databases with the same
schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am
very
new to VS2005 so please forgive me for being naïve.

Thanks

Edwin

[Edwin Smith]

Thanks for the responses everyone. I guess I neglected to mention that my
data set and connections were generated with the VS 2005 designer. I can't
find any of the classes you have mentioned in your responses. To my very
inexperienced brain the designer has obfuscated this beyond belief.

Is there any way to identify the object which contains the information I
need to change?

Thanks

Edwin

"Edwin Smith" <sm**********@a ol.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow
the user to select the database. We have 2 different databases with the
same schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am very
new to VS2005 so please forgive me for being naïve.

Thanks

Edwin

[Edwin Smith]

OK, I finally I figured out how to change connection strings for datasets
built with the designers.

First, in the Dataset designer window for each table adapter there is a
property called "ConnectionModi fier". This must be set to "
public".

Then each table adapters ConnectionStrin g property can be set as follows:

this.myTableAda pter.Connection .ConnectionStri ng = "Database Name =
MyDatabase; Host = MyHost";

Of coursr the Connection String will be whatever your provider needs. The
queries and provider can still be setup with the GUI tools as before then by
use of the above code can be set to whatever connection string you want.

Edwin
"Edwin Smith" <sm**********@a ol.comwrote in message
news:uE******** ******@TK2MSFTN GP02.phx.gbl...

Thanks for the responses everyone. I guess I neglected to mention that my
data set and connections were generated with the VS 2005 designer. I can't
find any of the classes you have mentioned in your responses. To my very
inexperienced brain the designer has obfuscated this beyond belief.

Is there any way to identify the object which contains the information I
need to change?

Thanks

Edwin

"Edwin Smith" <sm**********@a ol.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

>Hello:

I'm using VS2005 with an ODBC database.

I am trying to add a Dialog to an existing Database application to allow
the user to select the database. We have 2 different databases with the
same schema so this is a very desirable function.

I can configure 2 different connection strings in app,config but I can't
figure out how to allow the user to select one or the other.

This should be a simple task but I seem to be missing something. I am
very new to VS2005 so please forgive me for being naïve.

Thanks

Edwin