Entity Framework changing connection string with c#

Question

I have an app that the connection string changes by per user and machine. that said I have made a test connection string just to get it testing. Then once that is working I will add the dynamic connection string.

After some research I have figured out that there is a problem with the connection string.

If someone could please tell me where I am wrong I would appreciate it.

var s = @"metadata=res://*/ProcurementModel.csdl|res://*/ProcurementModel.ssdl|
          res://*/ProcurementModel.msl;provider=System.Data.SqlClient;
          provider connection string=&';data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=User;password=*****;MultipleActiveResultSets=True;App=EntityFramework&';";

Update:

public ProcurementContext()
{
    var s =
            @"metadata=res://*/ProcurementModel.csdl|res://*/ProcurementModel.ssdl|
            res://*/ProcurementModel.msl;provider=System.Data.SqlClient;
            provider connection string=&';data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=jd;password=P@ssw0rd;MultipleActiveResultSets=True;App=EntityFramework&';";

    _connectionString = s;
}

Getting data:

public List<Procurement> ParcelListByUser(string userName)
{
    using (var context = new ProcurementEntities(_connectionString))
    {
        return context.Procurements.Where(p => p.UserName == userName).ToList();
    }
}

Constructor:

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

Error message:

Format of the initialization string does not conform to specification starting at index 165.

Configuration:

The configuration of the Procurement

public class ProcurementConfiguration:DbConfiguration
{
    public ProcurementConfiguration()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
        SetDefaultConnectionFactory(new SqlConnectionFactory(ConnectionStrings.LocalConnectionString));
    }
}

connection string=&'; looks wrong. should probably be something like connection string="? — default
– default, Commented Feb 8, 2016 at 14:33
how do you set this string? i mean, how do you use the string "s"? — Piero Alberto
– Piero Alberto, Commented Feb 8, 2016 at 14:33
can you detail how you ProcurementEntities constructor is made? — Piero Alberto
– Piero Alberto, Commented Feb 8, 2016 at 14:40
referring to your error: which dbms are you using? Sql server? which version? — Piero Alberto
– Piero Alberto, Commented Feb 8, 2016 at 14:45

Piero Alberto · Accepted Answer · 2016-02-08 14:44:34Z

I think the issue is in your connection string. I've made the same thing and I have a function to create the connection string in an automatic way.

The code:

    public static string GetConnectionString()
    {
        // Build the provider connection string with configurable settings
        string cn = "server=" + mdlImpostazioni.p.dbServer;
        cn += ";database=" + mdlImpostazioni.p.dbName;
        cn += ";uid=" + mdlImpostazioni.p.dbUser;
        cn += ";pwd=" + mdlImpostazioni.p.dbPassword + ";";
        var providerSB = new SqlConnectionStringBuilder(cn);
        var efConnection = new EntityConnectionStringBuilder();
        // or the config file based connection without provider connection string
        efConnection.Provider = "System.Data.SqlClient";
        efConnection.ProviderConnectionString = providerSB.ConnectionString;
        // based on whether you choose to supply the app.config connection string to the constructor
        efConnection.Metadata = @"res://*";  //-----> very important
        return efConnection.ToString();
    }

In this way, I pass the return value to my db context constructor, like in your code. My constructor is:

public partial class Entities : DbContext
{
    public Entities(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {

    }

    public void Close()
    {
        this.Dispose();
    }
}

Try it and ask if you have some problem.

where do you implement the GetConnectionString()? before you call Entities?
I use it in this way: dbContext = new Entities(GetConnectionString());
this works for me with SQL Server, with version from 2008 to 2014 (never tested 2016, but I think it's ok too)

Alexei - check Codidact · Accepted Answer · 2016-02-08 14:45:15Z

One way to change your connection string dynamically is to only change entity framework underlying connection's connection string. Something like this:

myDbContext.Database.Connection.ConnectionString = "Data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=DynamicUser;password=DynamicPassword;MultipleActiveResultSets=True;App=EntityFrameworkForUser";

This way, your application does not have to hardcode tokens related to entity framework (i.e. metadata).

Collectives™ on Stack Overflow

Entity Framework changing connection string with c#

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related