How to Dynamically Change Entity Framework Database Connections at Runtime?

Changing Entity Framework Connections at Runtime

In Entity Framework (EF) Web API projects, dynamically switching database connections is a common requirement. This article discusses key aspects of connection management and answers the following questions:

How to change the connection:

You can change the data context connection by following these steps:

<code>public void Connect(Database database)
{
    //构建 SQL 连接字符串
    SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
    {
        DataSource = database.Server,
        InitialCatalog = database.Catalog,
        UserID = database.Username,
        Password = database.Password,
    };

    //构建实体框架连接字符串
    EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
    {
        Provider = database.Provider,
        Metadata = Settings.Default.Metadata,
        ProviderConnectionString = sqlString.ToString()
    };

    //将新的连接字符串分配给数据上下文
    // ...
}</code>

Connection persistence:

In Web API projects, the connection string should be passed to the data context every time the context is used. This ensures that the context uses the correct connection based on the user's most recent login.

EF connection management:

The code provided in the question relies on EF's default behavior, where the connection string is stored in the application configuration file. However, there are other methods that provide greater flexibility and control. For example, you can use the following extension method to dynamically change the connection string based on the current user's selections:

<code>public static class ConnectionTools
{
    public static void ChangeDatabase(
        this DbContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        string configConnectionStringName = "")
    {
        var sqlCnxStringBuilder = new SqlConnectionStringBuilder
        (System.Configuration.ConfigurationManager
            .ConnectionStrings[configConnectionStringName].ConnectionString);

        // ... [设置连接属性] ...

        //将新的连接字符串分配给数据上下文
        source.Database.Connection.ConnectionString
            = sqlCnxStringBuilder.ConnectionString;
    }
}</code>

This method allows you to modify specific connection properties, such as the database name or server address, without modifying the connection string in the configuration file.

By using these technologies, you can effectively manage database connections in your EF Web API application, ensuring that your data access layer is flexible and dynamic enough to adapt to changing user needs.

The above is the detailed content of How to Dynamically Change Entity Framework Database Connections at Runtime?. For more information, please follow other related articles on the PHP Chinese website!