使用 C# 窗体动态连接到多个 SQL 服务器

Question

我有一些SQL服务器，它们与存储的数据相同，我希望能够在它们之间更改，如果我添加更多，我想使用Windows表单轻松添加它们。

我首先做了一个数据库，这是添加到 App.config 文件中的连接字符串。出于安全原因，我更改了用户名和密码

<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-ATTESTSQLEXPRESS;initial catalog=MigrateDBFaktura5;persist security info=True;user id=**;password=**;MultipleActiveResultSets=True;App=EntityFramework&quot;"
           providerName="System.Data.EntityClient" />

有没有一种简单的方法可以更改我应该从哪里获取数据？我正在考虑使用一个组合框，我可以在其中选择它应该从哪个SQL Server获取数据。如果我手动更改连接字符串，它可以工作。但是我如何使用代码来做到这一点呢？

Answer 1

可以使用 class EntityConnectionStringBuilder 来生成连接字符串。 请参阅此处的详细信息 实体框架 6 中的 https://msdn.microsoft.com/en-us/library/orm-9780596520281-01-16.aspx 和编程连接字符串

// 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();
        }

Answer 2

我做了什么让它工作。在上下文文件中，我更改了

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

自

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

然后我做了一个助手类

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));
    }
}

位置模型是数据库，它完全包含我将连接到的不同服务器的数据以从中获取数据。

然后当我需要连接到它时，我只需要

MigrateDBFaktura3Entities db = ConnectionHelper.CreateConnection(CurrentLocation)

其中当前位置是位置模型