C# 为动态连接字符串设置实体框架

Setup Entity Framework For Dynamic Connection String

提问by user70192

I am working on an app that will use the same database schema across multiple databases. For this reason, I've created a database called MyTemplate. When a new user is created, they will have their own instance of the database. So, a database called something like MyTemplate_[UserName]will be created. When a user logs in, I need to point their queries to their database. For this reason, I know that I need to set the connection string at runtime. My problem is, I also want to use the Entity Framework.

我正在开发一个应用程序，它将跨多个数据库使用相同的数据库架构。为此，我创建了一个名为MyTemplate. 创建新用户时，他们将拥有自己的数据库实例。因此，MyTemplate_[UserName]将创建一个名为 like 的数据库。当用户登录时，我需要将他们的查询指向他们的数据库。出于这个原因，我知道我需要在运行时设置连接字符串。我的问题是，我也想使用实体框架。

Currently, I created a new .edmx using MyTemplate as the source. I thought I would be able to update the code and set the connection string there. Unfortunately, I can't figure out how to set it. The constructor to TemplateEntities does not have an overload that allows me to pass in a connection string. I noticed that TemplateEntities derived from DbContext, I don't think this would be the problem.

目前，我使用 MyTemplate 作为源创建了一个新的 .edmx。我以为我可以更新代码并在那里设置连接字符串。不幸的是，我不知道如何设置它。TemplateEntities 的构造函数没有允许我传入连接字符串的重载。我注意到从 DbContext 派生的 TemplateEntities，我认为这不是问题。

string connectionString = GetUsersConnectionString();
using (TemplateEntities entities = new TemplateEntities())
{
  TemplateEntity entity = new TemplateEntity();

  // Save to the database
  entities.TemplateEntity.Add(entity);
  entities.SaveChanges();
}

Am I creating the .edmxincorrectly? Or am I missing something entirely? Everything I Google shows an overload that should allow a connection string passed in. However, I don't have that overload available.

我是否.edmx错误地创建了？还是我完全错过了什么？我谷歌显示的所有内容都应该允许传入连接字符串的重载。但是，我没有可用的重载。

采纳答案by Nicholas Butler

The generated TemplateEntitiesclass is marked as partial.

生成的TemplateEntities类被标记为partial。

All you have to do is add another file with another part of the partial class definition that exposes the constructor you want to use:

您所要做的就是添加另一个文件，其中包含公开您要使用的构造函数的部分类定义的另一部分：

partial class TemplateEntities
{
  public TemplateEntities( string nameOrConnectionString )
    : base( nameOrConnectionString )
  {
  }
}

Then pass your connection string in to this constructor.

然后将您的连接字符串传递给此构造函数。

You want to put this code in a different file so it doesn't get over-written when you update your edmx model.

您想将此代码放在不同的文件中，以便在更新 edmx 模型时不会被覆盖。

回答by Mark Meuer

Nicholas Butler's answer is quite correct. In addition to what he said, I was faced with the problem of taking an existing connection string for entity framework and just pointing it at a different database that had the same structure. I used the following code to change only the data source of the existing string:

尼古拉斯巴特勒的回答是非常正确的。除了他所说的之外，我还面临着为实体框架获取现有连接字符串并将其指向具有相同结构的不同数据库的问题。我使用以下代码仅更改现有字符串的数据源：

var originalConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CSName"].ConnectionString;
var ecsBuilder = new EntityConnectionStringBuilder(originalConnectionString);
var sqlCsBuilder = new SqlConnectionStringBuilder(ecsBuilder.ProviderConnectionString)
{
    DataSource = "newDBHost"
};
var providerConnectionString = sqlCsBuilder.ToString();
ecsBuilder.ProviderConnectionString = providerConnectionString;

string contextConnectionString = ecsBuilder.ToString();
using (var db = new SMSContext(contextConnectionString))
{
    ...
}

回答by José Lugo

This is the step by step I have used while building my solutions:

这是我在构建解决方案时逐步使用的步骤：

1. On your desired project, make sure Entity Framework was installed using "Manage Nuget Packages..." options menu.
2. On your desired project, right click, then Add->New Item, go to Data and select ADO.NET Entity Data Model.
3. Type the name of the model, let's say "ExampleModel". Click Add.
4. Four choices will appear to choose Model contents, I usually select the first one, in order to build the model from existing objects from the database. Click Next.
5. Set your data connection. Once done, type the name of your model entity, let's say "ExampleModelEntities", click Next.
6. Select the objects from the database that will be present on your EF model. On Model Namespace input box type the same model name from Step 3 ("ExampleModel"). Click Finish.

1. 在您想要的项目上，确保使用“管理 Nuget 包...”选项菜单安装了实体框架。
2. 在您想要的项目上，右键单击，然后添加->新建项目，转到数据并选择 ADO.NET 实体数据模型。
3. 输入模型的名称，比如说“ExampleModel”。单击添加。
4. 选择模型内容会出现四个选项，我通常选择第一个，以便从数据库中的现有对象构建模型。点击下一步。
5. 设置数据连接。完成后，输入模型实体的名称，比如说“ExampleModelEntities”，单击“下一步”。
6. 从数据库中选择将出现在 EF 模型上的对象。在模型命名空间输入框中输入与步骤 3 相同的模型名称（“ExampleModel”）。单击完成。

At this point a new .edmx file has been created and added to the project, containing all of your objects ready-to-work. Only non-desired detail is, so far the connection string has been specified and saved into the Web.config file of our project.

此时，一个新的 .edmx 文件已创建并添加到项目中，其中包含所有准备工作的对象。只有不想要的细节是，到目前为止连接字符串已被指定并保存到我们项目的 Web.config 文件中。

To remove this, just go to <connectionStrings></connectionStrings>section block of your Web.config and delete the details from there. We will now work on making the connection string dynamically readable from other sources.

要删除它，只需转到<connectionStrings></connectionStrings>Web.config 的部分块并从那里删除详细信息。我们现在将致力于使连接字符串可从其他来源动态读取。

As pointed out by Nicholas Butler, next thing will be to create a "version" of the original partial entity class created (ExampleModelEntities), that will allow us to pass the dynamic connection string. This is possible since the original entity class created inherits from DBContext which is the one that contains the constructor to pass such connection.

正如 Nicholas Butler 所指出的，接下来要做的是创建原始部分实体类（ExampleModelEntities）的“版本”，这将允许我们传递动态连接字符串。这是可能的，因为创建的原始实体类继承自 DBContext，后者包含传递此类连接的构造函数。

To do the aforementioned, add a new empty class to your project. Make sure to type the same name provided on Step 5, following our case-study "ExampleModelEntities". Below the code to implement:

要执行上述操作，请向您的项目添加一个新的空类。在我们的案例研究“ExampleModelEntities”之后，确保输入在第 5 步中提供的相同名称。下面是要实现的代码：

C#

C＃

public partial class ExampleModelEntities
{
public ExampleModelEntities(string connString) : base(connString)
{

}
}

VB.Net:

VB.Net：

Partial Public Class ExampleModelEntities

Public Sub New(ByVal connString As String)
    MyBase.New(connString)
End Sub
End Class

At this moment your code is ready to work with dynamic connection strings coming from other sources. One of these sources could be passing a connection string coming from another field stored on a different database or using EntityConnectionStringBuilderclass.

此时，您的代码已准备好处理来自其他来源的动态连接字符串。这些来源之一可能是传递来自存储在不同数据库上的另一个字段的连接字符串或使用EntityConnectionStringBuilder类。

The following example is implemented in VB.Net, but please use some tool like Telerikto translate. Let's say we are getting a list of objects from a certain database, only we want to pass dynamically the connection string coming from another field stored on a different database. To accomplish this, the code would look as follows:

下面的例子是在VB.Net中实现的，但请使用Telerik之类的工具进行翻译。假设我们要从某个数据库获取对象列表，但我们只想动态传递来自存储在不同数据库中的另一个字段的连接字符串。为了实现这一点，代码如下所示：

Public Shared Function Get_List(ByVal Param1 As String) As List(Of Stored_Procedure_Code_Result)

Try

Dim Object_List_Result As List(Of Stored_Procedure_Code_Result) = Nothing

Using dbContext As New ExampleModelEntities(Configuration.CONNECTION_STRING)

Object_List_Result = dbContext.Stored_Procedure_Code(Param1).ToList

dbContext.Dispose()
End Using
Return Object_List_Result

Catch ex As Exception
Throw ex
End Try

End Function

Where Configuration.CONNECTION_STRINGis the value of the dynamic connection string, expressed using a Module called "Configuration" and a function which retrieves such value.

其中Configuration.CONNECTION_STRING是动态连接字符串的值，使用名为“Configuration”的模块和检索此类值的函数表示。

In order to avoid format inaccuracies, the value should be stored using the following format:

为避免格式不准确，应使用以下格式存储该值：

For Windows authentication using Entity Framework:

对于使用实体框架的 Windows 身份验证：

UPDATE [DBConnections].[dbo].[ListOfConnectionsTable]
SET ConnValue = 'metadata=res://*/ExampleModel.csdl|res://*/ExampleModel.ssdl|res://*/ExampleModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True"'

For SQL authentication using Entity Framework:

对于使用实体框架的 SQL 身份验证：

UPDATE [DBConnections].[dbo].[ListOfConnectionsTable]
SET ConnValue = 'metadata=res://*/ExampleModel.csdl|res://*/ExampleModel.ssdl|res://*/ExampleModel.msl;provider=System.Data.SqlClient;provider connection string="Persist Security Info=False;User ID=XXXXXX;Password=XXXXXXX;Initial Catalog=DBName;Data Source=ServerName;App=YourAppName;Network Library=dbmssocn"'

Finally, extending the answer provided by Mark, at Microsoftthere is a detailed explanation on how to work with EntityConnectionStringBuilder class, which can also be used to build dynamic connection strings and then pass this value on demand.

最后，扩展 Mark 提供的答案，在Microsoft有关于如何使用 EntityConnectionStringBuilder 类的详细说明，该类也可用于构建动态连接字符串，然后按需传递此值。