Connecting to multiple SQL Servers dynamicly with C# Form?

Question

I have some SQL Servers that is Identical exept for the data stored and i want to be able to change between them and if i add more i want to easy add them with a windows form.

I have done a database first and this is the connection string that was added to the App.config file. I changed the username and password for security reasons

<add name="MigrateDBFaktura3Entities"
           connectionString="metadata=res://*/DB.ServerData.csdl|res://*/DB.ServerData.ssdl|res://*/DB.ServerData.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=FASTEC-ATTEST\SQLEXPRESS;initial catalog=MigrateDBFaktura5;persist security info=True;user id=**;password=**;MultipleActiveResultSets=True;App=EntityFramework&quot;"
           providerName="System.Data.EntityClient" />

Is there a easy way to change where i should get the data from? I was thinking to use a combobox where i could chose what SQL Server it should get the data from. If i manualy change the connection string it works. but how do i do it with code?

Combobox is a good option. You can store the latest value for next run; which may save time. — A3006, Feb 08 '17 at 12:52
Something like this: http://stackoverflow.com/questions/16511244/how-to-create-connection-string-dynamically-in-c-sharp maybe? — Eric Burdo, Feb 08 '17 at 12:52

Dan Nguyen · Accepted Answer · 2017-02-08T13:08:20.927

you can utilize class EntityConnectionStringBuilder to build your connection string. refer more here https://msdn.microsoft.com/en-us/library/orm-9780596520281-01-16.aspx and Programmatic Connection Strings in Entity Framework 6

// Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        string serverName = ".";
        string databaseName = "AdventureWorks";

        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = true;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                        res://*/AdventureWorksModel.ssdl|
                        res://*/AdventureWorksModel.msl";
        Console.WriteLine(entityBuilder.ToString());

        using (EntityConnection conn =
            new EntityConnection(entityBuilder.ToString()))
        {
            conn.Open();
            Console.WriteLine("Just testing the connection.");
            conn.Close();
        }

I followed the guide on Programmatic Connection Strings in Entity Framework 6 that you linked. But when i run the `MigrateDBFaktura3Entities temp = ConnectionHelper.CreateConnection(CurrentLocation);` Command and try to get information from it with `var temp2 = temp.Anvandare.ToList();` i get a `"The underlying provider failed on Open" Inner Exeption "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."` Do you know why this happens? — Drago87, Feb 08 '17 at 14:39
Never mind the guide used `sqlBuilder.IntegratedSecurity = true;` but what i needed to use was `sqlBuilder.PersistSecurityInfo = true;` — Drago87, Feb 08 '17 at 14:48
Need to make sure that your connection credential is valid. The message said that cant connect using windows authentication (Integrated Security=True in connection string). mean that need to provide UserId and Password in your connection string — Dan Nguyen, Feb 08 '17 at 14:51
need to set IntegratedSecurity=false and provide username and password to connect to the db — Dan Nguyen, Feb 08 '17 at 14:52

score 0 · Answer 2 · answered Feb 09 '17 at 08:11

What i did to make it work. In the Context file i changed

public MigrateDBFaktura3Entities ()
            : base("name=MigrateDBFaktura3Entities")
        {

        }

to

public MigrateDBFaktura3Entities (string connectionString)
            : base(connectionString)
        {

        }

Then i made a HelperClass

class ConnectionHelper
    {
        public static string CreateConnectionString(LocationModel LM, string metaData)
        {
            const string appName = "EntityFramework";
            const string providerName = "System.Data.SqlClient";

            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = LM.datasource;
            sqlBuilder.InitialCatalog = LM.catalog;
            sqlBuilder.UserID = LM.Username;
            sqlBuilder.Password = LM.Password;
            sqlBuilder.MultipleActiveResultSets = true;
            sqlBuilder.PersistSecurityInfo = true;
            sqlBuilder.ApplicationName = appName;


EntityConnectionStringBuilder efBuilder = new EntityConnectionStringBuilder();
        efBuilder.Metadata = metaData;
        efBuilder.Provider = providerName;
        efBuilder.ProviderConnectionString = sqlBuilder.ConnectionString;
        var t = efBuilder.ConnectionString;
        return efBuilder.ConnectionString;
    }

    public static FastecData CreateConnection(LocationModel locationmodel, string metaData = "res://*/DB.ServerData.csdl|res://*/DB.ServerData.ssdl|res://*/DB.ServerData.msl")
    {
        return new FastecData(ConnectionHelper.CreateConnectionString(locationmodel, metaData));
    }
}

The LocationModel is Database that purly contains the data for the different servers i will connect to to get data from.

Then when i need to connect to it i only need to

MigrateDBFaktura3Entities db = ConnectionHelper.CreateConnection(CurrentLocation)

where CurrentLocation is a LocationModel

Connecting to multiple SQL Servers dynamicly with C# Form?

2 Answers2