CODEWITHSUNDEEP
c#sqlsql-serverwinforms
William
William

Reputation: 6610

Allow User Input To Determine SQL Server Connection String

As the application I am developing requires access to more than one server, I want to allow the user to select the 'site' they wish to access from a drop-down list and for the connection string to updated accordingly.

I've had two ideas (Pseudo-code);

IF (drop-down value = Site2)
{string connectionString = "Server=server2; Database=db......"}

ELSE IF(drop-down value = Site3.....

Or;

string @server = drop-down value;

string connectionString = "Server=@server; Database=db......"
"pass connection variable??"

Which of these methods is best? Or is there a more efficient alternative?

I'm unsure about how to pass a variable to the connection string (Option 2).

Upvotes: 0

Views: 2747

Answers (4)

granadaCoder
granadaCoder

Reputation: 27852

If you need "super dynamic", then Davio has the correct one.

If you need "I got N number of databases, that may change from time to time", then I suggest:

The below is typical Dot Net 2.0 and above configuration.

<connectionStrings>
<add name="EastCoastConnectionString" connectionString="server=server01;database=myDb;uid=myUser;password=myPass;" />
<add name="WestCoastConnectionString" connectionString="server=server02;database=myDb;uid=myUser;password=myPass;" />
</connectionStrings>

And then create a custom configuration section that will map a "friendly name" to a connection string.

Find this code: How to include simple collections in ConfigurationSection

  <TransformationToDirectoryMappingsSection>
    <TransformationToDirectoryMappings>
      <add FriendlyName="NY Giants" PickupFolder="EastCoastConnectionString" />
      <add FriendlyName="NE Patriots" PickupFolder="EastCoastConnectionString" />
      <add FriendlyName="Carolina Panthers" PickupFolder="EastCoastConnectionString" />

      <add FriendlyName="San Francisco 49ers" PickupFolder="WestCoastConnectionString" / >
      <add FriendlyName="Seattle Seahawks" PickupFolder="WestCoastConnectionString" />
      <add FriendlyName="Arizona Cardinals" PickupFolder="WestCoastConnectionString" />
    </TransformationToDirectoryMappings>
  </TransformationToDirectoryMappingsSection>
</configuration >

Then populate a drop down box with all the "FriendlyName"'s.......and then you can map that back to a connection string.

Now, you could just populate the drop down lists with the name of the connection strings......but I don't like that idea...............I prefer a "friendly name" to "actual name" mapping.

At the very least, setup the Connection-Strings in your config file....., and populate your drop down with this:

http://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager.connectionstrings.aspx

  ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;

    foreach (ConnectionStringSettings connection in connections)
    {
      string name = connection.Name;

    }

Upvotes: 1

rgunawan
rgunawan

Reputation: 314

In addition to using SqlConnectionStringBuilder, I would wrap the display name and the connection string in a nested class in the forms. Like this (in the form/user control):

    private void PopulateComboBox(ComboBox comboBox)
    {
        var items = new[]
            {
                new ConnectionListItem {DisplayName = "Site 1", ConnectionString = new SqlConnectionStringBuilder("Server=...")},
                new ConnectionListItem {DisplayName = "Site 2", ConnectionString = new SqlConnectionStringBuilder("Server=...")},
            };

        comboBox.Items.AddRange(items);

    }

    class ConnectionListItem
    {
        public string DisplayName { get; set; }
        public SqlConnectionStringBuilder ConnectionString { get; set; }

        public override string ToString()
        {
            return DisplayName;
        }
    }

Upvotes: 0

Michael Ross
Michael Ross

Reputation: 570

    string ConnectionString;
    switch (comboBox1.SelectedIndex)
    {
        case 0:
            ConnectionString = "Data Source=server1;Initial Catalog=database1;User ID=user1;Password=password1";
            break;
        case 1:
            ConnectionString = "Data Source=server2;Initial Catalog=database2;User ID=user1;Password=password2";
            break;
        case 3:
            ConnectionString = "Data Source=server3;Initial Catalog=database3;User ID=user1;Password=password3";
            break;
    }
    SqlConnection Con = new SqlConnection(ConnectionString);
    Con.Open();

Upvotes: 2

Davio
Davio

Reputation: 4737

Take a look at the SqlConnectionStringBuilder class.

For instance

var builder = new SqlConnectionStringBuilder();
builder.DataSource = /* your server here */;
builder.InitialCatalog = "db";
/* etc.... */
var connString = builder.ConnectionString;

Upvotes: 3

Related Questions