在 app.config 之外的实体框架中为 PostgreSQL 数据库设置 connectionString

Set a connectionString for a PostgreSQL database in Entity Framework outside the app.config

提问by Rasmus B?kgaard

I have a database build with PostgreSQL, which I access through Entity Framework 6.

我有一个使用 PostgreSQL 构建的数据库，我通过 Entity Framework 6 访问它。

Until recently it ran smoothly through an app.configconnectionString:

直到最近，它通过app.configconnectionString顺利运行：

<connectionStrings>
    <add
        name="fancyName"
        connectionString="Host=localhost; user id=allCanSee; password=notSoSecret; database=notHidden"
        providerName="Npgsql" />
</connectionStrings>

Our lead programmer is not happy about an open connectionString, since every computer we install the software on can read it. We therefore encrypted everything, and stored the encryption in the app.config.

我们的首席程序员对打开的 connectionString 并不满意，因为我们安装软件的每台计算机都可以读取它。因此，我们加密了所有内容，并将加密存储在app.config.

Now I have a new problem - I have accessed my database the following way:

现在我遇到了一个新问题 - 我通过以下方式访问了我的数据库：

public class VersionContext
{
    public virtual DbSet<DatabaseNumber> DatabaseVersion { get; set; }

    public VersionContext() : base("name=fancyName")
    {
        System.Data.Entity.Database.SetInitializer<DatabaseContext>(null);
    }
}

But since my app.configno longer contains the connectionString, I must tell the database where to look.

但是由于我的app.config不再包含 connectionString，我必须告诉数据库在哪里查找。

My current attempt is something like this:

我目前的尝试是这样的：

public static class VersionContextConnection
{
    public static string GetConnectionString() //Accessable form everywhere
    {
        var providerName = "Npgsql";
        var databaseName = decryptedName;
        var userName = decryptedUserName;
        var password = decryptedPassword;
        var host = decryptedHostName
        var port = 5432;

        return $"Provider={providerName}; " + $"Server={host}; " + $"Port={port}; " + 
            $"User Id={userName}; " + $"Password={password}; " + $"Database={databaseName};";
    }
}

public class VersionContext : DbContext
{
    public virtual DbSet<DatabaseNumber> DatabaseVersion { get; set; }

    public VersionContext() : base(VersionContextConnection.GetConnectionString())
    {
        System.Data.Entity.Database.SetInitializer<DatabaseContext>(null);
    }
}

Then I'd access it as follow:

然后我会按如下方式访问它：

using (var context = new VersionContext())
{
    var entry = context.DatabaseVersion.FirstOrDefault();
    ...
}

But this gives an exception from System.Datasaying Keyword not supported: 'provider'.

但是，这给出了一个异常System.Data说Keyword not supported: 'provider'.

Removing providerfrom the connectionString gives another exception: Keyword not supported: 'port'.

provider从 connectionString 中删除会产生另一个异常：Keyword not supported: 'port'.

Removing portfrom the connectionString gives a third exception from .Net SqlClient Data Provider: Login failed for user 'secretSecret'.

port从 connectionString 中删除给出了第三个例外.Net SqlClient Data Provider：Login failed for user 'secretSecret'.

So - how do I set my connectionString, if it's not set through the :base(connectionString)property?

那么 - 如果不是通过:base(connectionString)属性设置，我该如何设置我的 connectionString ？

回答by Rasmus B?kgaard

A solution emerged from this answer.

这个答案出现了一个解决方案。

The app.configcontains the providers:

在app.config包含供应商：

<providers>
    <provider 
        invariantName="Npgsql" <!-- this is what we need -->
        type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
</providers>

By letting the code refer to that, it can create the connection:

通过让代码引用它，它可以创建连接：

public static class VersionContextConnection
{
    public static DbConnection GetDatabaseConnection()
    {
        var providerName = "Npgsql"; //Get this
        var databaseName = decryptedDatabaseName;
        var userName = decryptedUserName;
        var password = decryptedPassword;
        var host = decryptedHostName
        var port = 5432;

        //Insert it here
        var conn = DbProviderFactories.GetFactory(providerName).CreateConnection(); 
        conn.ConnectionString = $"Server={host}; " + $"Port={port}; " + 
            $"User Id={userName};" + $"Password={password};" + $"Database={databaseName};";

        return conn;
    }
}

Set the DbContextas such:

设置DbContext如下：

public class VersionContext : DbContext
{
    public virtual DbSet<DatabaseNumber> DatabaseVersion { get; set; }

    public VersionContext() : base(VersionContextConnection.GetDatabaseConnection(), true)
    {
        System.Data.Entity.Database.SetInitializer<DatabaseContext>(null); 
    }
}

And call your code:

并调用您的代码：

using (var context = new VersionContext())
{
    var entry = context.DatabaseVersion.FirstOrDefault();
    ...
}

With such, you can populate your app.configwith encrypted login parameters, retrieve them, and pass them to your DbContext.

这样，您可以app.config使用加密的登录参数填充您的登录参数，检索它们，并将它们传递给您的DbContext.

回答by Henrique Forlani

You can try using EntityConnectionStringBuilder:

您可以尝试使用 EntityConnectionStringBuilder：

Modify your static method to:

将您的静态方法修改为：

public static string GetConnectionString() //Accessable form everywhere
{
       var providerName = "Npgsql";
       var databaseName = decryptedName;
       var userName = decryptedUserName;
       var password = decryptedPassword;
       var host = decryptedHostName;
       var port = 5432;

       // Initializing the connection string builder for the provider
       SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
       sqlBuilder.ConnectionString = String.Format("Host={0};user id={1},password={2},database={3}",
       host, userName, password, databaseName);

       // Initialize the EntityConnectionStringBuilder.
       EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
       entityBuilder.Provider = providerName;
       entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

       return entityBuilder.ToString();
}

And add the using statements: using System.Data.SqlClient;and using System.Data.EntityClient;By the way, is port supported? In the connectionString that you showed first, there is no port parameter.

并添加using语句：using System.Data.SqlClient;和using System.Data.EntityClient;顺便说一句，在端口支持？在您首先显示的 connectionString 中，没有端口参数。