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

Question

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

Until recently it ran smoothly through an app.config connectionString:

<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.

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.config no longer contains the connectionString, I must tell the database where to look.

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.Data saying Keyword not supported: 'provider'.

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

Removing port from the connectionString gives a third exception from .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?

What about setting it at runtime? something like this? http://stackoverflow.com/questions/360024/how-do-i-set-a-connection-string-config-programatically-in-net — Henrique Forlani, Aug 18 '16 at 12:56
@Forlani That is a solution, but then the password and username would be exposed on runtime... — Rasmus Bækgaard, Aug 18 '16 at 13:05

score 6 · Accepted Answer · edited May 23 '17 at 11:46

A solution emerged from this answer.

The app.config contains the providers:

<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 DbContext as such:

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.config with encrypted login parameters, retrieve them, and pass them to your DbContext.

score 1 · Answer 2 · answered Aug 18 '16 at 13:57

You can try using 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.

or perhaps, should it be server:port ? instead of server;port? — Henrique Forlani, Aug 18 '16 at 14:04
I tried that some time ago, @Forlani, but it also resulted in en exception where it said it were missing ``metadata`` in the ``entityBuilder``. I have no metadata to give, and what I tried resulted in more exceptions, which did not use npgsql to connect. However, I found a solution (see thread). — Rasmus Bækgaard, Aug 18 '16 at 14:24

score 0 · Answer 3 · answered Aug 19 '20 at 06:19

This is what I as able to get to work...

    public static DbConnection GetDatabaseConnection()
    {
      
        NpgsqlConnectionStringBuilder npgsqlConnectionStringBuilder = new NpgsqlConnectionStringBuilder();
        npgsqlConnectionStringBuilder.Host = "localhost";
        npgsqlConnectionStringBuilder.Port = 5432;
        npgsqlConnectionStringBuilder.Database = "database";
        npgsqlConnectionStringBuilder.Username = "postgres";
        npgsqlConnectionStringBuilder.Password = "postgres";

        var conn = new NpgsqlConnectionFactory().CreateConnection(npgsqlConnectionStringBuilder.ConnectionString.ToString());
        return conn;
    }

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

3 Answers3