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

 

[Guest]

<connectionStrings>
<clear/>
<add name="AdventureWorksString"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;
Initial Catalog=AdventureWorks; Integrated Security=true"/>
</connectionStrings>

individualSettings=configurationManager.ConnectionStrings[0];

DbConnection MyConnection = null;
switch (individualSettings.ProviderName)
{
case "System.Data.SqlClient":
MyConnection = new SqlConnection(individualSettings.ConnectionString);
break;
case "System.Data.OracleClient":
MyConnection = new
OracleConnection(individualSettings.ConnectionString);
break;
case "System.Data.OleDb":
MyConnection = new OleDbConnection(individualSettings.ConnectionString);
break;
case "System.Data.Odbc":
MyConnection = new OdbcConnection(individualSettings.ConnectionString);
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.

 

[sloan]

Just to add a little extra info to the already good reply:


{
ConnectionStringSettingsCollection connectionStrings =
ConfigurationManager.ConnectionStrings;

ConnectionStringSettings connection;
foreach ( connection in connectionStrings) {



string connectionStringName = connection.Name;
string connectionString = connection.ConnectionString;
string providerName = connection.ProviderName;

Debug.Print(connectionStringName);
}



this.GridView1.DataSource = connectionStrings;
this.GridView1.DataBind();
}



You can bind a grid or anything to these objects above, and get a "picker".

<connectionStrings>
<clear/>
<add name="AdventureWorksString"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;
Initial Catalog=AdventureWorks; Integrated Security=true"/>
</connectionStrings>

individualSettings=configurationManager.ConnectionStrings[0];

DbConnection MyConnection = null;
switch (individualSettings.ProviderName)
{
case "System.Data.SqlClient":
MyConnection = new
SqlConnection(individualSettings.ConnectionString);
break;
case "System.Data.OracleClient":
MyConnection = new
OracleConnection(individualSettings.ConnectionString);
break;
case "System.Data.OleDb":
MyConnection = new
OleDbConnection(individualSettings.ConnectionString);
break;
case "System.Data.Odbc":
MyConnection = new
OdbcConnection(individualSettings.ConnectionString);
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.

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 DbProviderFactory class.

individualSettings=configurationManager.ConnectionStrings[0];

DbProviderFactory factory =
DbProviderFactories.GetFactory(individualSetting.ProviderName);
DbConnection MyConnection =
factory.CreateConnection(individualSettings.ConnectionString);

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

<connectionStrings>
<clear/>
<add name="AdventureWorksString"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;
Initial Catalog=AdventureWorks; Integrated Security=true"/>
</connectionStrings>

individualSettings=configurationManager.ConnectionStrings[0];

DbConnection MyConnection = null;
switch (individualSettings.ProviderName)
{
case "System.Data.SqlClient":
MyConnection = new
SqlConnection(individualSettings.ConnectionString);
break;
case "System.Data.OracleClient":
MyConnection = new
OracleConnection(individualSettings.ConnectionString);
break;
case "System.Data.OleDb":
MyConnection = new
OleDbConnection(individualSettings.ConnectionString);
break;
case "System.Data.Odbc":
MyConnection = new
OdbcConnection(individualSettings.ConnectionString);
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.

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]

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 "ConnectionModifier". This must be set to "
public".

Then each table adapters ConnectionString property can be set as follows:

this.myTableAdapter.Connection.ConnectionString = "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